Shulou(Shulou.com)06/01 Report--

A few days ago, I was asked some questions about permissions and roles. Today, I took the time to summarize how to view the permissions or roles of Oracle users. In the previous blog post, I wrote two blog posts about the differences and relations of SYS,SYSTEM,DBA,SYSDBA,SYSOPER and the case study of defined rights and caller permissions in Oracle. Readers can refer to and read them! Oracle things feel very miscellaneous, spare time to sum up more is also a comb of knowledge and an improvement of their own, if there are any mistakes or inadequacies in the article, please point out, thank you!

First, view users

1. View all users:

Select * from dba_users

Select * from all_users

Select * from user_users; / / View current user

Second, view roles

1. All roles activated by the current user

Select * from session_roles

two。 The role granted to the current user

Select * from user_role_privs

3. Roles granted to all users

Select * from dba_role_privs

4. View the roles owned by a user

Select * from dba_role_privs where grantee=' username'

5. View the permissions that a role has

Select * from dba_sys_privs where grantee='CONNECT'

6. View all roles

Select * from dba_roles

Third, view permissions

1. Basic permissions query:

Select * from session_privs;-all permissions that the current user has

Select * system permissions of the current user of from user_sys_privs;--

Select * object permissions of the current user of from user_tab_privs;--

Select * from dba_sys_privs;-- query the system permissions owned by a user

Select * from role_sys_privs;-- view the permissions contained in roles (you can only view roles owned by logged in users)

two。 View the user's system permissions (system permissions assigned directly to the user or role) select * from dba_sys_privs

Select * from user_sys_privs

two。 View the user's object permissions:

Select * from dba_tab_privs

Select * from all_tab_privs

Select * from user_tab_privs

3. Check which users have sysdba or sysoper system permissions (appropriate permissions are required for query)

Select * from v$pwfile_users

Expansion

1. The following statement can view the system permissions provided by Oracle

Select name from sys.system_privilege_map

2. View all system permissions of a user (including system permissions of roles)

Select privilege from dba_sys_privs where grantee='SCOTT'

Union

Select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='SCOTT')

3. Query all the data dictionary views that the current user can access.

Select * from dict where comments like'% grant%'

4. Display the full name of the current database

Select * from global_name

Question1: how to query the permissions included in a role?

a. System permissions contained in a role

Select * from dba_sys_privs where grantee=' role name'

Select * from dba_sya_privs where grantee='COONNECT'; connect should be capitalized

You can also view it like this:

Select * from role_sys_privs where role=' role name'

b. Object permissions contained in a role

Select * from dba_tab_privs where grantee=' role name'

How many roles does 2:Oracle have?

Select * from dba_roles

Question 3: how do I view a user and what role does he or she have?

Select * from dba_role_privs where grantee=' username'

Question 4: see which users have the role of DBA

Select grantee from dba_role_privs where granted_role='DBA'

Author: SEian.G (hard practice changes in 72, but it is difficult to laugh at 81)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.