Oracle User role Permissions related views

Overview of common related views

Dba_sys_privs: Querying system permissions owned by a user

User_sys_privs: system permissions owned by the current user

Session_privs: All permissions owned by the current user

Role_sys_privs: system permissions owned by a role ( to log in as SYS user )

Role_role_privs: role assigned to the current role

Session_roles: The role that the current user is activating

User_role_privs: The role that the current user is granted

View of access rights for tables :

Table_privileges

All_tab_privs

Role_tab_privs: permissions on related tables that a role is given

examples of using related views

1. Querying permissions owned by the current user

Select * from Session_privs;

2. Query the system permissions that a user has been given .

There can be many ways

Select * from User_sys_privs;

or :

Select * from Dba_sys_privs where grantee= ' XXX ' ( requires user to have DBA role )

3. Query the role that the current user is granted :

1). Select * from Session_roles ORDER by ROLE

Description : This query returns all roles that the current user has been granted , including nested licensing roles . For example , a DBA role is granted to a user , the DBA role

roles that have been granted ( such as exp_full_database and imp_full_database) will be queried, too.

2). Select * from User_role_privs

4. querying the system permissions assigned to a role

Select Privilege from Role_sys_privs where Role=&role

Input role= ' CONNECT '

Output:

PRIVILEGE

--------------------

ALTER SESSION

CREATE CLUSTER

CREATE DATABASE LINK

CREATE SEQUENCE

CREATE SESSION

CREATE synonym

CREATE TABLE

CREATE VIEW

5. Query the role that the current role is granted

Select Granted_role from Role_role_privs where role=&role

Input role= ' DBA '

Output:

Granted_role

----------------------

Delete_catalog_role

Execute_catalog_role

Exp_full_database

Imp_full_database

Plustrace

Select_catalog_role

Description: Plustrace This role is used to execute SQL AUTO trace, which can be generated by executing a $ORACLE _home/sqlplus/admin/plustrce.sql.

Oracle User role Permissions related views