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) » Tutorial: Granting roles and privileges (SQL)

 

Lesson 5: View the roles and privileges for UserB (SQL)

You can view the roles, system privileges, and object-level privileges for a user, including those the user inherits through membership in other roles.

Prerequisites

This lesson assumes that you have completed all previous lessons in this tutorial. See Lesson 4: Convert UserA to a user-extended role and grant that role to UserB (SQL).

 Task
  1. In Interactive SQL, execute the following statement to view the roles and privileges for UserB, including administrative rights:

    CALL sp_displayroles ( 'UserB', 'expand_down' );
    role_name parent_role_name grant_type role_level
    UserA (NULL) NO ADMIN 1
    PUBLIC (NULL) NO ADMIN 1
    dbo PUBLIC NO ADMIN 2
    PUBLIC UserA NO ADMIN 2
    DebugAndFix UserA ADMIN 2
    BACKUP DATABASE UserA NO ADMIN 2
    VALIDATE ANY OBJECT UserA NO ADMIN 2
    ALTER ANY OBJECT DebugAndFix NO ADMIN 3
    DEBUG ANY PROCEDURE DebugAndFix NO ADMIN 3
    dbo PUBLIC NO ADMIN 3

    Consider the role_level value as a level in a hierarchy of inheritance, where UserB is level 1:

    • From rows with role_level 1, we learn that the UserA role was granted directly to UserB. We also learn that the PUBLIC role was granted to UserB directly. The PUBLIC role is automatically granted to new users.

    • From rows with role_level 2, we learn that UserB inherits the dbo and PUBLIC role from UserA. UserB also inherits the DebugAndFix role that was granted to UserA, including the administrative rights on the role. Finally, UserB inherits the BACKUP DATABASE and VALIDATE ANY OBJECT system privileges that were granted to UserA.

    • From rows with role_level 3, we learn that UserB inherits the ALTER ANY OBJECT and DEBUG ANY PROCEDURE system privileges from UserA, who inherited them from the DebugAndFix role. UserB also inherits dbo again, this time from the PUBLIC role, which was automatically granted to the DebugAndFix role when it was created.

  2. Execute the following statement to view the object-level privileges that UserB has.

    CALL sp_objectpermission ( 'UserB' );
    grantor grantee object_name owner object_type column_name permission grantable
    ... ... ... ... ... ... ... ...
    DBA UserA Employees GROUPO TABLE (NULL) SELECT Y
    DBA UserA Employees GROUPO TABLE (NULL) UPDATE Y
    SYS PUBLIC spt_collation_map dbo TABLE (NULL) SELECT N
    ... ... ... ... ... ... ... ...

    The result set is quite long because users inherit many things by being a member of the PUBLIC role. The rows of interest, however, are the second and third rows in the table above because they reflect that UserB inherits SELECT and UPDATE privileges on the Employees table. We also learn that these two privileges were granted by the user DBA.

  3. Administrative rights on object-level privileges are not inheritable, so even though DBA granted UserA the right to grant SELECT and UPDATE privileges on the Employees table, UserB does not inherit those administrative rights. The Y in the grantable column for these rows indicates that UserA has administrative rights for the privileges.

Results

You have verified the roles and privileges granted to, and inherited by, UserB.

 See also