Introduction to Oracle 11g user permission query, oracle11g

Introduction to Oracle 11g user permission query, oracle11g

/** (Oracle 11g) User Permissions **/

-- Role and system Permissions

Select a. grantee,

A. granted_role,

To_char (replace (wm_concat (B. privilege), '|') privilege,

A. admin_option,

A. default_role

From dba_role_privs

Left join role_sys_privs B

On a. GRANTED_ROLE = B. ROLE

Where a. grantee = 'sbni'

Group by a. grantee, a. granted_role, a. admin_option, a. default_role

Union all

Select grantee, ''granted_role, privilege, admin_option, ''default_role

From dba_sys_privs

Where grantee = 'sbni'; -- Object permission

Select GRANTEE,

OWNER,

TABLE_NAME,

GRANTOR,

To_char (wm_concat (PRIVILEGE )),

GRANTABLE,

HIERARCHY

From dba_tab_privs

Where grantee = 'sbni'

Group by GRANTEE, OWNER, TABLE_NAME, GRANTOR, GRANTABLE, HIERARCHY; -- Summary: Role permissions, system permissions, User Permissions

Select a. grantee,

A. granted_role as "granted_role ---- table_name ",

To_char (replace (wm_concat (B. privilege), '|') privilege,

'Admin _ option: '| a. admin_option as "option ",

'Default _ role: '| a. default_role as "default_role ---- hierarchy"

From dba_role_privs

Left join role_sys_privs B

On a. granted_role = B. role

Where a. grantee = 'sbni'

Group by a. grantee, a. granted_role, a. admin_option, a. default_role

Union all

Select grantee,

''Granted_role,

Privilege,

'Admin _ option: '| admin_option as "option ",

''Ulult_role

From dba_sys_privs

Where grantee = 'sbni'

Union all

Select grantee,

Owner | '.' | table_name as "table_name ",

-- Grantor,

To_char (wm_concat (privilege) as "privilege ",

'Grant _ option: '| grantable as "option ",

'Hierarchy: '| hierarchy

From dba_tab_privs

Where grantee = 'sbni'

Group by grantee, owner, table_name, grantor, grantable, hierarchy;