Oracle Query User Rights role (DBA_SYS_PRIVS) ____oracle

Oracle Query user Rights 2009-06-22 16:54

Data dictionary

1, Dynamic Data dictionary is starting with the V$XXX data dictionary, in the database about 150, these data dictionaries reflect the dynamic state of the database, the query at different times will get different results.

2, the DBA data dictionary is expressed in dba_xxx, the data dictionary stores the database structure, query DBA data dictionary can reflect the database structure settings, management of disk space and table space, transaction and fallback section, User and table space and other information.

3, the user data dictionary is expressed in user_xxx, these data dictionaries reflect the entity information created by the user. For example, User_tables, user_views, database administrators have the right to operate all the entities of all users, you can query such a data dictionary, understand the user created entity status, if necessary, users can create an incorrect entity deleted.

4, the All_xxx class data dictionary, represents the entity which the user creates and the user has the right to be able to access the entity.

Select privilege from Dba_sys_privs where grantee= ' USERNAME '

Turn Oracle Query user Rights

--Determine the permissions of the role
SELECT * from Role_tab_privs;               contains the object permissions granted to the role
SELECT * from Role_role_privs;              contains roles that are granted to another role
SELECT * from Role_sys_privs;               contains system permissions granted to the role

--Determine the permissions granted by the user account
Select * from Dba_tab_privs;   Object permissions granted directly to the user account
select * from Dba_role_privs; Grant user account role
SELECT * from Dba_sys_privs;   Grant user account system Permissions

View Current user rights:
sql> select * from Session_privs;

Privilege
----------------------------------------
Create session
Create TABLE
Create CLUSTER
Create synonym
Create VIEW
Create SEQUENCE
Create PROCEDURE
Create TRIGGER

8 rows selected.

Oracle 's own data dictionary design I personally feel very reasonable, because dba_xxx, all_xxx,user_xxx let a person look probably know what this view is for. This article briefly summarizes the views associated with permissions and roles. I. Overview

The views associated with permissions and roles are probably the following:

Dba_sys_privs: Querying the system permissions that a user has

User_sys_privs: System permissions for the current user

Session_privs: All permissions available to the current user

Role_sys_privs: System permissions owned by a role

Note: To log in to the SYS user to query this view, otherwise return empty.

Role_role_privs: Roles assigned by the current role

Session_roles: The role that the current user is activated

User_role_privs: The role that the current user is granted

There is also a view of access rights for the table:

Table_privileges

All_tab_privs

Role_tab_privs: Permissions for related tables assigned by a role

... two. Examples 1. Query the permissions that the current user has

Select * from Session_privs; 2. Query the system permissions given by a user.

There can be many ways

Select * from User_sys_privs;

Or: SELECT * from Dba_sys_privs where grantee= ' XXX '

(Requires the current user to have a DBA role) 3. Query the role that the current user is granted:

1. Select * FROM Session_roles

Description: This query returns all the roles that the current user has been granted, including

The role of the nested authorization. For example, a DBA role is granted to a user, and the DBA role

Roles already granted (e.g. Exp_full_database and imp_full_database)

will also be queried.

2. Select * from User_role_privs 4. Query for system permissions given by 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 for roles granted by the current role

Select granted_role from Role_role_privs where role=&role

Enter 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 by executing

$ORACLE _home/sqlplus/admin/plustrce.sql can generate this role.

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

How to view Oracle user rights.

The types of Oracle data dictionary views are: User,all and DBA.

User_*: Information about the object that the user has, the object information that the user has created

All_*: Information about objects that a user can access, that is, the information that the user creates, plus

Other user-created objects but the information that the user is authorized to access

Dba_*: Information about objects in the entire database

(Here the * can be tables,indexes,objects,users and so on.) ）

1. View All Users

SELECT * from Dba_user;

SELECT * from All_users;

SELECT * from User_users;

2, view the user system permissions

SELECT * from Dba_sys_privs;

SELECT * from All_sys_privs;

SELECT * from User_sys_privs;

3. View User Object permissions

SELECT * from Dba_tab_privs;

SELECT * from All_tab_privs;

SELECT * from User_tab_privs;

4. View all roles

SELECT * from Dba_roles;

5. View the roles that users have

SELECT * from Dba_role_privs;

SELECT * from User_role_privs;

6, view the current user's default table space

Select Username,default_tablespace from User_users;

7, view the specific permissions of a role

such as Grant Connect,resource,create session,create view to TEST;

8. View RESOURCE with those permissions with select * from Dba_sys_privs WHERE grantee= ' RESOURCE ';