Some concepts about users and roles in Oracle.

Each user in Oracle corresponds to a separate scheme (schema), with the same name as the user name, with many elements such as data objects, tables, views, triggers, stored procedures, and so on.

The role of the management database in Oracle is Sys,system, and the maintenance of the database is done by the database itself and cannot be changed by any user.

SYS: the base tables and views of all Oracle data dictionaries are stored in the SYS user,

SYS has dba,sysdba,sysoper roles or permissions and is the highest-privileged user.

SYS user must be logged in as SYSDBA or as Sysoper user and cannot be logged in as normal.

System: for storing sub-level internal data, with DBA,SYSDBA role

System is logged on as a normal user

System Administrator's permissions:

Sysdba>sysoper>dba

The role has permissions, assigns the role to the user, and the user has the appropriate permissions, and the user and role may be 1:1 or 1:m.

SELECT * from Dba_roles; You can query how many roles Oracle contains

Permissions:

Permissions are divided into object permissions and system permissions

Object permissions correspond to data objects, system permissions correspond to database

Data dictionary

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

2. DBA data dictionary is expressed in dba_xxx, the data dictionary stores the database structure, querying the DBA data dictionary can reflect the database structure settings, management disk space and table space, transaction and fallback segment, 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, the database administrator has the ability to operate all the entities of the entire user, you can query such data dictionary, understand the user created entity condition, if necessary, you can delete the incorrect entity created by the user.

4, All_xxx class data dictionary, represents the entity created by the user and the entity that the user has permission to access.

Select privilege from Dba_sys_privs where grantee= ' USERNAME '

Oracle Query User Rights

--Determine permissions for roles
SELECT * from Role_tab_privs; Contains the object permissions granted to the role
SELECT * from Role_role_privs; Contains a role that is granted to another role
SELECT * from Role_sys_privs; Contains the system permissions granted to a role

--Determine the permissions granted by the user account
SELECT * from Dba_tab_privs; Grant object permissions directly to a user account
SELECT * from Dba_role_privs; The role granted to the user account
SELECT * from Dba_sys_privs; Granting system permissions to user accounts

To view the 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 think it is very reasonable, because dba_xxx, all_xxx,user_xxx let a person look around to know what this view is for. This article briefly summarizes the views related to permissions, roles.

I. Overview

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

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

Note: To query this view with SYS user login, otherwise return NULL.

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

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

Table_privileges

All_tab_privs

Role_tab_privs: Permissions on related tables that a role is given

...

Two. Examples

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 current 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 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 that have been granted (such as Exp_full_database and Imp_full_database)

will also be queried.

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

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

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

How do I view Oracle user rights?

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

User_*: Information about the object that the user owns, that is, the object information created by the user himself

All_*: Information about the objects that the user can access, that is, information about the objects created by the user, plus

Other user-created objects but the information that the user has access to

Dba_*: Information about an object 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 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 owned by the user

SELECT * from Dba_role_privs;

SELECT * from User_role_privs;

6. View the default tablespace for the current user

Select Username,default_tablespace from User_users;

7. View specific permissions for 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 ';

Some concepts about users and roles in Oracle.