Users, Groups/Roles, and Permissions

There are some differences between the Adaptive Server Enterprise and SQL Anywhere and SAP Sybase IQ models of users and roles/groups.

In Adaptive Server Enterprise, users connect to a server, and each user requires a login ID and password to the server as well as a user ID for each database they want to access on that server.

SQL Anywhere and SAP Sybase IQ users do not require a server login ID. All SQL Anywhere and SAP Sybase IQ users receive a user ID and password for a database.

User Roles/Groups

To allow you to grant permissions to many users at one time, SQL Anywhere and SAP Sybase IQ support user roles while Adaptive Server Enterprise supports user groups. Though basically roles are groups are equivalent, there are some behavioral differences:

Adaptive Server Enterprise allows each user to be a member of only one group.
SQL Anywhere and SAP Sybase IQ allow users to be members of multiple roles , and role hierarchies are allowed.

All three products have a public role or group, for defining default permissions. Every user automatically becomes a member of the public role or group.

Database Object Permissions

GRANT and REVOKE statements for granting permissions on individual database objects are very similar in all three products.

All three products allow SELECT, INSERT, DELETE, UPDATE, and REFERENCES permissions on database tables and views, and UPDATE permissions on selected columns of database tables. SQL Anywhere and SAP Sybase IQ also allow LOAD and TRUNCATE permissions on databsse tables and views.

For example, the following statement is valid in all three products:
```
GRANT INSERT, DELETE
ON TITLES
TO MARY, SALES
```
This statement grants permission to use the INSERT and DELETE statements on the TITLES table to user MARY and to the SALES role or group.
All three products allow EXECUTE permissions to be granted on stored procedures.
Adaptive Server Enterprise also supports GRANT and REVOKE on additional items:
- Objects: columns within tables, columns within views, and stored procedures
- User abilities: CREATE DATABASE, CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW
SQL Anywhere and SAP Sybase IQ require a user to have the MANAGE ANY OBJECT PRIVILEGE system privilege to grant database objects permissions. (A closely corresponding Adaptive Server Enterprise permission is GRANT ALL, used by a Database Owner.)
All three products support the WITH GRANT OPTION clause, allowing the recipient of permissions to grant them in turn, although SAP Sybase IQ and SQL Anywhere do not permit WITH GRANT OPTION to be used on a GRANT EXECUTE statement.

Database-wide Permissions

Adaptive Server Enterprise uses a different model for database-wide user permissions.

SQL Anywhere and SAP Sybase IQ use the SYS_AUTH_DBA_ROLE compatibility role to allow a user full permissions within a database, assuming that the SYS_AUTH_DBA_ROLE compatibility role has not been migrated to a hierarchy of roles to meet customer security requirements..
The System Administrator in Adaptive Server Enterprise enjoys this permission for all databases on a server.
The database owner must use the Adaptive Server Enterprise SETUSER statement to gain permissions on objects owned by other users.

Adding Users

Adaptive Server Enterprise requires a two-step process to add a user: sp_addlogin followed by sp_adduser.

SQL Anywhere and SAP Sybase IQ add users in a single step.

SAP Sybase IQ Login Management stored procedures, although not required to add or drop users, allow users with applicable system privileges to add or drop SAP Sybase IQ user accounts. When SAP Sybase IQ User Administration is enabled, these SAP Sybase IQ user accounts allow control user connections and password expirations.

Although SQL Anywhere and SAP Sybase IQ allow Adaptive Server Enterprise system procedures for managing users and groups, the exact syntax and function of these procedures differs in some cases.