Users, groups, and permissions

There are some differences between the Adaptive Server Enterprise and SQL Anywhere and Sybase 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.

SQL Anywhere and Sybase IQ users do not require a server login ID. All SQL Anywhere and Sybase IQ users receive a user ID and password for a 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.

• SQL Anywhere and Sybase 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

• SQL Anywhere and Sybase 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 Sybase 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.

• SQL Anywhere and Sybase 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.

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

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