You can view the roles, system privileges, and object-level privileges for a user, including those the user inherits through membership in other roles.
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).
In Interactive SQL, execute the following statement to view the roles and privileges for UserB, including administrative rights:
CALL sp_displayroles ( 'UserB', 'expand_down' );
|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|
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.
Execute the following statement to view the object-level privileges that UserB has.
CALL sp_objectpermission ( 'UserB' );
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.
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.
Discuss this page in DocCommentXchange.
|Copyright © 2014, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0|