Data dictionary table for Oracle users, roles, and permissions (System and object permissions), and oracle dictionary

Data dictionary table for Oracle users, roles, and permissions (System and object permissions), and oracle dictionary

1. Three dictionary tables

1.1 users

Select * from dba_users;

Select * from all_users;

Select * from user_users;

1.2 roles

Select * from dba_roles;

1.3 Permissions

System permissions and object permissions:

Select * from system_privilege_map;

Select * from table_privilege_map;

2. dictionary tables with relationships between the three elements

The table names and suffixes of these relational dictionary tables contain "_ privs"

2.1 users and Roles

User Roles:

Select * from dba_role_privs;

Select * from user_role_privs;

Select * from role_role_privs;

Role_role_privs and role_role_privs are both subsets of dba_role_privs.

The grantee field of dba_role_privs includes the user name and role name.

The username field of user_role_privs includes the username of the Operating User.

The role_role_privs role field is only the role name.

Note: No role_all_privs. Why didn't you understand it.

2.2 users and permissions

System permissions of users:

Select * from dba_sys_privs;

Select * from user_sys_privs;

NOTE: If all_sys_privs is not available, why not understand it.

Object permissions of users:

Select * from dba_tab_privs;

Select * from all_tab_privs;

Select * from user_tab_privs;

Select * from dba_col_privs;

Select * from all_col_privs;

Select * from user_col_privs;

2.3 roles and permissions

Query the granted object permissions (usually by the owner himself)

Select * from role_sys_privs;

Select * from role_tab_privs;

3 others

Query the granted object permissions (usually by the owner himself)

Select * from user_tab_privs_made;

Select * from all_tab_privs_made;

Object permissions of users

Select * from user_tab_privs_recd;

Select * from all_tab_privs_recd;

Object permissions of columns allocated by the user

Select * from user_col_privs_made;

Select * from all_col_privs_made;

Object permissions of columns owned by users

Select * from user_col_privs_recd;

Select * from all_col_privs_recd;