Returns all roles granted to the specified system privilege, system role, user-defined role, or user name, or displays the entire hierarchy tree of roles.
sp_displayroles( user_role_name , display_mode , grant_type )
user_role_name Use this CHAR(128) parameter to specify the name of a system privilege, system role, user-defined role, or user name. If it is not specified or is NULL, then the current user is used by default.
display_mode Use this VARCHAR(30) parameter to specify whether to return parent-level or child-level hierarchy, relative to user_role_name. If display_mode is not specified or is NULL, then only explicitly granted roles and privileges are returned (no inherited roles or privileges). Possible values for display_mode include the following:
expand_up Shows the system roles granted to user_role_name in the parent hierarchy tree for user_role_name.
expand_down Shows the system roles and privileges granted to user_role_name, including the role hierarchy tree for the child levels of user_role_name.
grant_type Use this VARCHAR(30) parameter to control the grant type returned. If it is not specified, then ALL is used by default. Possible values for grant_type including the following:
NO_ADMIN Shows all roles and system privileges granted to user_role_name with the WITH NO ADMIN OPTION or WITH ADMIN OPTION clause.
ADMIN Returns all roles and system privileges granted to user_role_name with the WITH ADMIN OPTION or WITH ADMIN ONLY OPTION clause.
ALL Shows all roles/system privileges granted to user_role_name.
|Column name||Data type||Description|
|role_name||CHAR(128)||The role or system privilege granted to user_role_name.|
|parent_role_name||CHAR(128)||The role names for the parents of user_role_name.|
|grant_type||CHAR(10)||Information about whether user_role_name has administrative rights. Possible values: NO ADMIN, ADMIN, or ADMIN ONLY.|
With expand_down mode: Level is 1 for directly granted roles, 2 for the next level below, and so on.
With expand_up mode: Level is 0 for the roles to which user_role_name has been granted, -1 for the next hierarchy above, and so on.
For system privileges, the result shows the system privilege name instead of the system privilege role name. With expand_down mode, the parent_role_name is NULL for level 1 (directly granted roles). With the default mode, the role_level column is 1 and parent_role_name is NULL, since with default mode only the directly granted roles are displayed.
If this procedure is used for a user with mode expand_up, then no results are returned since a user resides at the top level in any role hierarchy. Similarly, if this procedure is used for an immutable system privilege, with mode expand_down, then no results are returned because an immutable system privilege resides at the bottom level in any role hierarchy. The default mode is to display only the directly granted roles/system privileges.
SQL/2008 Vendor extension.
No privileges are required to execute this procedure on yourself. However, to return the system privileges or roles for another user ID or a role, you must have the MANAGE ROLES system privilege.
The following statement returns all roles granted to the user issuing the command.
This examples returns the list of system privileges granted to the SYS_SPATIAL_ADMIN_ROLE system role:
CALL sp_displayroles( 'SYS_SPATIAL_ADMIN_ROLE' );
|MANAGE ANY SPATIAL OBJECT||(NULL)||NO ADMIN||1|
This examples returns the list of system privileges granted to the SYS_SPATIAL_ADMIN_ROLE, including all roles above it in the hierarchy of roles:
CALL sp_displayroles( 'SYS_SPATIAL_ADMIN_ROLE', 'expand_up');
|MANAGE ROLES||SYS_REPLICATION_ADMIN_ROLE||NO ADMIN||-1|
|SYS_SPATIAL_ADMIN_ROLE||MANAGE ROLES||ADMIN ONLY||0|
The following statement returns all system privileges granted to the user User1:
CALL sp_displayroles( 'User1' );
This example returns the list of system privileges pertaining to views:
SELECT sys_priv_name FROM sp_sys_priv_role_info() WHERE sys_priv_name LIKE '%VIEW%'
Discuss this page in DocCommentXchange.
|Copyright © 2014, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0|