Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - Database Administration » User and database security » User security (roles and privileges)

 

Groups

A group is a set of users that possess a set of roles and privileges common to all other users in the group.

Groups are an efficient way to maintain roles and privileges for a set of users.

Membership in a group that owns objects also means that users do not need to qualify the object names when performing operations such as executing a procedure owned by the group, or querying a table owned by the group. For example, if a table named MyData is owned by a group called PersonnelData that a user ID M_Haneef is a member of, then M_Haneef can reference the table as MyData in queries, instead of PersonnelData.MyData. If a user owns a table that has the same name as a table owned by a group, the database server uses the table owned by the user, not the one owned by the group. If a user belongs to more than one group that has a table with the same name, the user must qualify the table name.

In SQL Anywhere, a group is not a database object. Instead, groups are achieved using user-defined roles, by one of two approaches:

  • User-extended role approach to creating groups   User-extended roles are user-defined roles created by extending an existing user ID to become a role. In this approach to group creation, the user ID is created and then granted all roles and privileges required by the group. The user ID is converted to a user-extended role, and then granted to the users who need it. These steps can occur in different order, of course.

    This approach is common when creating groups for replication purposes because the user that has been extended to a role also has login capabilities.

    For users of previous versions of SQL Anywhere, this approach is identical to the deprecated GRANT statement approach of granting GROUP to a user, and then granting MEMBERSHIP IN GROUP to other users.

    To drop a group created using this approach, you convert the user-extended role back to a regular user (DROP ROLE FROM USER userid). Ownership of objects remains with the user that is being converted back to a regular user.

    When the user-extended role approach is used, groups and their member information can be queried from the SYSROLEGRANTS consolidated view, or the SYSGROUPS compatibility view.

  • Standalone role approach to creating groups   Standalone roles are user-defined roles that are not associated with any user ID. In this approach to group creation, the role is created and then granted all roles and privileges required by a defined set of users. The role is then granted to the users who need it.

    To drop a group created using the standalone approach, you drop the role. Optionally, you can drop the objects owned by the role at the same time you drop the role.

    When the standalone role approach is used, groups and their member information can not be queried from the SYSGROUPS compatibility view. Instead, the information can be queried from the SYSROLEGRANTS consolidated view.

For both approaches, administration of the privileges for group members is performed at the user-defined role level, instead of at the level of each user ID.

 Examples of the two approaches
 See also