Users, groups, and permissions

There are some differences between the Adaptive Server Enterprise and Adaptive Server Anywhere and IQ models of users and 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.

In Adaptive Server Anywhere and IQ, users connect directly to a database and do not require a server login ID. Instead, each user receives a user ID and password on a database so they can use that database.

All three products support user groups, so you can grant permissions to many users at one time. However, there are differences in the specifics of groups:

• Adaptive Server Enterprise allows each user to be a member of only one group.

• Adaptive Server Anywhere and IQ allow users to be members of multiple groups, and group hierarchies are allowed.

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

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.

  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 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

• Adaptive Server Anywhere and IQ require a user to have RESOURCE authority to create database objects. (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 IQ does not permit WITH GRANT OPTION to be used on a GRANT EXECUTE statement.

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

• Adaptive Server Anywhere and IQ employ DBA permissions to allow a user full authority within a database.

• The System Administrator in Adaptive Server Enterprise enjoys this permission for all databases on a server. However, DBA authority on a Sybase IQ database is different from the permissions of an Adaptive Server Enterprise Database Owner, who must use the Adaptive Server Enterprise SETUSER statement to gain permissions on objects owned by other users.

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

Adaptive Server Anywhere and IQ add users in a single step.

IQ User Administration stored procedures, while not required to add or drop users, allow DBAs to add or drop IQ user accounts. When IQ User Administration is enabled, these accounts let DBAs control user connections and password expirations.