Oracle user, authorization, role management

Creating and deleting users is a common operation in Oracle User management, but this implies knowledge of the system permissions and object permissions of the Oracle database system. Mastering the authorized operations and principles of Oracle users can effectively improve our productivity. The permission system of Oracle database is divided into System permission and object permission. System permissions (Database system Privilege) allow the user to execute a specific set of commands. For example, the CREATE TABLE permission allows a user to create a table, and the grant any PRIVILEGE permission allows the user to grant any system permissions. Object permissions (Database object Privilege) enable users to perform certain operations on individual objects. For example, delete permission allows a user to delete rows of a table or view, and select permissions allow the user to query information from a table, view, sequence (sequences), or snapshot (snapshots) through select.

Each Oracle user has a name and password, and has some tables, views, and other resources created by it. An Oracle role is a set of permissions (privilege) (or the type of access each user needs depending on its state and criteria). The user can grant or assign the specified permissions to a role, and then assign the role to the appropriate user. A user can also authorize other users directly.

Oracle Permissions Settings

First, the Classification of rights:
System permissions: The system specifies the user's permission to use the database. (System permissions are for users).

Entity permissions: A permission user's access to other users ' tables or views. (For a table or view).

Second, System Rights Management:
1, System Permissions classification:
DBA: Has full privileges, is the highest system privilege, and only the DBA can create the database structure.

RESOURCE: A user with RESOURCE permission can only create entities and cannot create a database structure.

Connect: A user with connect permission can only log on to Oracle, not create an entity, and cannot create a database structure.

For normal users: Grant Connect, resource permissions.
For DBA administration users: Grant Connect,resource, dba authority.


2, System Authority authorization command:
[System permissions can only be granted by the DBA User: sys, system (only two users at the beginning)]
Authorization Command:sql> Grant Connect, resource, DBA to username 1 [, user name 2] ...;

[An ordinary user with authorization can have the same user rights as the system, but never the same permissions as the SYS user, and the permissions of the system user can also be recycled.] ]

Cases:
Sql> Connect System/manager
sql> Create User User50 identified by USER50;
Sql> Grant Connect, resource to User50;

Query where the user has permissions:
Sql> select * from Dba_role_privs;
Sql> select * from Dba_sys_privs;
Sql> select * from Role_sys_privs;

Delete User:sql> drop the user username cascade; Plus cascade the user with everything they created.


3. System Permission Pass:
Add the WITH ADMIN option option, then the resulting permissions can be passed.

sql> Grant Connect, Resorce to user50 with admin option; You can pass the permissions you have received.


4, System rights recovery: System permissions can only be collected by DBA users
Command:sql> Revoke Connect, resource from User50;


Description

1) If you use the WITH ADMIN option to grant system permissions to a user, canceling the user's system permissions does not cascade the same permissions for all users who are granted the same permissions by the user.

2) There is no cascade of system permissions, that is, a grants B permission, b grants C permissions, if a withdraws the permissions of B, the permissions of C are not affected, and the system permissions can be reclaimed across users, that is, a can directly reclaim the rights of C users.


Third, Entity Rights Management
1. Entity Permissions Classification: Select, UPDATE, INSERT, ALTER, INDEX, delete, all//all including all permissions
Execute//execute Stored procedure permissions

User01:
Sql> Grant SELECT, UPDATE, insert on product to User02;
Sql> grant all on product to User02;

User02:
Sql> select * from User01.product;

At this time user02 check user_tables, not including user01.product this table, but if check all_tables can be found, because he can access.


2. Grant the operation permissions of the table to all users:
Sql> grant all on product to public; Public means all users, and the all permission here does not include drop.

[Entity permission data Dictionary]:
Sql> Select owner, table_name from All_tables; Tables that users can query
Sql> Select table_name from User_tables; User-Created Tables
Sql> Select Grantor, TABLE_SCHEMA, table_name, privilege from All_tab_privs; Tables (authorized) to which access is granted
Sql> Select grantee, owner, TABLE_NAME, privilege from User_tab_privs; Grant permission to the table (granted permission)



3. DBA user can operate any base table of all users (without authorization, including deletion):
DBA User:
sql> Create Table Stud02.product (
ID Number (10),
Name VARCHAR2 (20));
sql> drop table stud02.emp;

Sql> CREATE TABLE Stud02.employee
As
SELECT * from Scott.emp;

4. Entity permission delivery (with GRANT OPTION):
User01:

Sql> Grant SELECT, update on product to USER02 with GRANT option; User02 is granted permission and can be passed.



5. Entity Permission Recycling:
User01:
Sql>revoke Select, update on product from User02; The passed permissions are all lost.


Description

1) If you cancel the object permissions for a user, the same permissions are also canceled for users who grant permissions with GRANT option, which means that they are cascaded when the authorization is canceled.



Oracle User Management

First, create the user profile file
Sql> Create profile Student limit//Student as resource file name
Failed_login_attempts 3//Specifies the number of logon failures for locked users
Password_lock_time 5//Specify the number of days the user is locked
Password_life_time 30//Specify the number of days the password is available


Second, create the user
Sql> Create User username
Identified by password
Default tablespace tablespace
Temporary tablespace tablespace
Profile Profiles
Quota integer/unlimited on tablespace;

Cases:
sql> Create User Acc01
identified by ACC01//If your password is a number, enclose it in double quotation marks.
Default Tablespace Account
Temporary Tablespace Temp
Profile default
Quota 50m on account;
Sql> Grant Connect, resource to ACC01;

[*] Query user default tablespace, temp table space
Sql> Select Username, Default_tablespace, temporary_tablespace from Dba_users;

[*] Query system resource file name:
Sql> select * from Dba_profiles;
The resource file resembles a table and is saved in the database once it is created.
Sql> Select Username, profile, Default_tablespace, temporary_tablespace from Dba_users;

Sql> Create profile common limit
Failed_login_attempts 5
Idle_time 5;

sql> Alter user Acc01 profile Common;



Third, modify the user:
sql> Alter User Name
Identified password
Default tablespace tablespace
Temporary tablespace tablespace
Profile Profiles
Quota integer/unlimited on tablespace;

1, change the password word:
Sql>alter user acc01 identified by "12345";

2, modify the user default table space:
sql> Alter User acc01 default tablespace users;

3. Modify the user temp table space
sql> Alter user acc01 temporary tablespace temp_data;

4, forcing users to change the password word:
sql> Alter user acc01 password expire;

5. Lock the user
sql> Alter user acc01 account lock; Locking
sql> Alter user acc01 account unlock; Unlock



Iv. Deleting users
Sql>drop user username; The user has not built any entities
Sql> drop user username CASCADE; Delete the user and all the entities they have built

* *. The user who is currently connected must not be deleted.


V. Monitoring users:
1. Query user session information:
Sql> Select username, sid, serial#, machine from v$session;

2. Delete User session information:
Sql> Alter system kill session ' Sid, Serial# ';

3. Query the user SQL statement:
Sql> Select User_name, sql_text from V$open_cursor;


Oracle Role Management

First, what is the role
Role. A role is a set of permissions that assigns a role to a user who has all the permissions in that role.

Second, the system pre-defined role
Predefined roles are some of the most commonly used roles that the system automatically creates after the database is installed. The following is a brief introduction to these predetermined roles. The permissions that the role contains can be queried using the following statement:
Sql>select * from Role_sys_privs where role= ' role name ';

1. CONNECT, RESOURCE, DBA
These predefined roles are primarily for backwards compatibility. It is mainly used for database management. Oracle recommends that users design their own database management and security rights planning instead of simply using these predefined roles. In future releases, these roles may not be used as predefined roles.



2. Delete_catalog_role, Execute_catalog_role, Select_catalog_role
These roles are primarily used to access data dictionary views and packages.



3. Exp_full_database, Imp_full_database
These two roles are used for the data Import Export tool.



4. Aq_user_role, Aq_administrator_role
Aq:advanced Query. These two roles are used for Oracle advanced query functionality.



5. Snmpagent
For Oracle Enterprise Manager and Intelligent Agent



6. Recovery_catalog_owner
Used to create a user who owns the recovery library. For information on recovering a library, refer to the Oracle documentation oracle9i user-managed Backup and Recovery Guide



7. Hs_admin_role
A DBA using Oracle ' s heterogeneous services feature needs this role to access appropriate tables in the data dictionary.



III. Management Role
1. Build a role
sql>create role Role1;


2. Authorization to the role
Sql>grant Create any table,create procedure to role1;


3. Grant the role to the user
Sql>grant Role1 to User1;


4. View the permissions that the role contains
Sql>select * from Role_sys_privs;


5. Create a password with a role (password must be provided when a role with password is in effect)
Sql>create role Role1 identified by Password1;


6. Modify the role: password is required
Sql>alter role Role1 not identified;
Sql>alter role Role1 identified by Password1;


7. Set the role for the current user to take effect
(Note: What is the concept of role effectiveness?) Assuming that user A has b1,b2,b3 three roles, then if B1 does not take effect, the permissions contained in the B1 are not owned by a, only the role is valid, the role's permissions are applied to the user, the maximum number of valid roles is set by the parameter Max_enabled_roles, and after the user logs on, Oracle assigns all permissions that are directly assigned to the user and permissions in the user's default role to the user. ）
Sql>set role role1;//make role1 effective
Sql>set role role,role2;//make role1,role2 effective
Sql>set role Role1 identified by password1;//using ROLE1 with password in effect
Sql>set role all;//takes effect with all roles of the user
Sql>set role none;//set all roles Invalid
Sql>set role all except role1;//except role1 for all other roles of that user.
Sql>select * from session_roles;//View the roles in effect for the current user.


8. Modify the specified user, set their default role
Sql>alter user User1 default role role1;
Sql>alter user User1 default role all except Role1;
See Oracle Reference documentation


9. Deleting a role
Sql>drop role Role1;
After the role is deleted, the user who owns the role will no longer have the role, and the corresponding permissions will not be available.



Description

1) cannot use with GRANT option to grant object permissions to a role

2) You can use the with ADMIN OPTION to grant system permissions to the role, not cascade when canceling

Querying user Permissions

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 '

Turn
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 ';

Oracle user, authorization, role management