1. View All Users:
SELECT * from Dba_users;
SELECT * from All_users;
SELECT * from User_users;
2. View user or role system permissions (System permissions that are directly assigned to a user or role):
SELECT * from Dba_sys_privs;
SELECT * from User_sys_privs;
3. View the permissions that are included in the role (can only view the roles owned by the logged-on user)
Sql>select * from Role_sys_privs;
4. View User Object permissions:
SELECT * from Dba_tab_privs;
SELECT * from All_tab_privs;
SELECT * from User_tab_privs;
5. View all roles:
SELECT * from Dba_roles;
6. View the roles owned by the user or role:
SELECT * from Dba_role_privs;
SELECT * from User_role_privs;
7. See which users have SYSDBA or Sysoper system permissions (requires appropriate permissions when querying)
Sql> SELECT * from Dba_role_privs where grantee= ' Cx_zj_ros '; -------------the roles owned by the user
GRANTEE granted_role ADM DEF
---------- --------------- ----- ----
Cx_zj_ros Zhro NO YES
Sql> SELECT * from Dba_sys_privs WHERE grantee= ' Zhro '; --------Here are the permissions that are queried by the user and the custom role
GRANTEE PRIVILEGE ADM
-------- ------------ -------------------
Zhro CREATE SEQUENCE NO
Zhro CREATE SESSION NO
Zhro CREATE TABLE NO
Zhro UNLIMITED tablespace NO
5 rows selected.
------------here Unlimited tablespace permissions are actually not granted through the role of the
Sql> SELECT * from Role_sys_privs where role= ' CONNECT ';
-------Here is the permissions that the system role has
ROLE PRIVILEGE ADM
--------- ---------------- ----------
CONNECT CREATE SESSION NO
Sql> SELECT * from Dba_sys_privs WHERE grantee= ' ilog_rts ';
GRANTEE PRIVILEGE ADM
--------- --------------------- -------------
Ilog_rts UNLIMITED tablespace NO
Sql> SELECT * from Dba_role_privs where grantee= ' ilog_rts ';
GRANTEE granted_role ADM DEF
---------- ------------- --- ---
Ilog_rts CONNECT NO YES
Ilog_rts RESOURCE NO YES
SELECT * FROM V$pwfile_users
TABLE_NAME COMMENTS
----------------- -------------------------------------
Dba_connect_role_grantees information regarding which users are granted CONNECT
Dba_roles all ROLES which exist in the database
Dba_role_privs Roles granted to users and Roles
Dba_scheduler_job_roles all SCHEDULER jobs on the database by database role
User_role_privs Roles granted to current user
Role_role_privs Roles which is granted to Roles
Role_sys_privs System privileges granted to roles
Role_tab_privs Table privileges granted to roles
Session_roles ROLES which the user currently has enabled.
TABLE_NAME COMMENTS
------------------- -------------------------------------
Dba_aq_agent_privs
Dba_col_privs all grants on columns in the database
Dba_role_privs Roles granted to users and Roles
Dba_rsrc_consumer_group_privs Switch privileges for CONSUMER groups
Dba_rsrc_manager_system_privs SYSTEM privileges for the resource MANAGER
Dba_sys_privs System privileges granted to users and roles
Dba_tab_privs all grants on objects in the database
User_col_privs Grants on columns for which the user is the owner, grantor or grantee
User_col_privs_made all grants on columns of objects owned by the user
USER_COL_PRIVS_RECD Grants on columns for which the user is the grantee
User_role_privs Roles granted to current user
User_rsrc_consumer_group_privs Switch privileges for CONSUMER groups for the user
User_rsrc_manager_system_privs SYSTEM privileges for the resource MANAGER for the user
User_sys_privs System privileges granted to current user
User_tab_privs Grants on objects for which the user is
The owner, grantor or grantee
User_tab_privs_made all grants on objects owned by the user
USER_TAB_PRIVS_RECD Grants on objects for which the user is the grantee
All_col_privs Grants on columns for which the user is
The grantor, grantee, owner,or An-enabled role or public is the grantee
All_col_privs_made Grants on columns for which the user is owner or grantor
ALL_COL_PRIVS_RECD Grants on columns for which the user, public or enabled role is the grantee
All_tab_privs Grants on objects for which the user is the grantor, grantee,
Owner,or An-enabled role or public is the grantee
All_tab_privs_made user ' s grants and grants on User ' s objects
ALL_TAB_PRIVS_RECD Grants on objects for which the user, public or enabled role is the grantee
Role_role_privs Roles which is granted to Roles
Role_sys_privs System privileges granted to roles
Role_tab_privs Table privileges granted to roles
Session_privs privileges which the user currently Hasset
Gv$enabledprivs synonym for Gv_$enabledprivs
V$enabledprivs synonym for V_$enabledprivs
Set Linesize 120
Col username for A20
Col Account_status for A30
Col CREATED for A30
Set pagesize 600
Col Default_tablespace for A30
Select Username,account_status,created,default_tablespace from Dba_users order by Created,account_status;
Col GRANTEE for A30
Col Granted_role for A30
Col Admin_option for A20
Col Default_role for A20
-------------Here are the roles owned by the user role
SELECT * FROM Dba_role_privs where grantee in (select username from dba_users where username not in (' SYS ', ' SYSTEM ') and A ccount_status= ' OPEN ') Order by Grantee,granted_role;
-------------Here are the permissions that are queried by the user and the custom role
SELECT DISTINCT grantee,privilege,admin_option from (select Grantee,privilege,admin_option from Dba_sys_privs WHERE GRANTEE in (select Granted_role from Dba_role_privs where GRANTEE in (select username from dba_users where username (' SYS ', ' SYSTEM ') and account_status= ' OPEN ')) union select Grantee,privilege,admin_option from Dba_sys_privs WHERE GRANTEE in (SELECT Usern Ame from Dba_users where username (' SYS ', ' SYSTEM ') and account_status= ' OPEN ')] ORDER by Grantee,privilege;
