Oracle User and Rights Management

• Permissions classification

1. System permissions: Permissions for database operations, including but not limited to:

CREATE TABLE

CREATE VIEW

CREATE SESSION

CREATE SEQUENCE

CREATE PROCEDURE

......

2, object permissions: For user-created tables, view access, modify the ability to manipulate permissions, including but not limited to:

SELECT

UPDATE

INSERT

DELETE

......

• Permission Assignment and Recycling

1. permission assignment:

GRANT permissions to user / role

Example 1: assigning access rights to the employee table under the HR user to the user SCOTT

GRANT SELECT on Hr.employee to Scott;

Example 2: Assigning a role connect to a user SCOTT

GRANT CONNECT to Scott

2. Permission recovery:

REVOKE permissions from user / role

Example 1: Recovering user Scott 's access rights to the HR User Employee table

REVOKE Hr.employee from Scott;

Example 2: Recovering user Scott 's CONNECT role

REVOKE CONNECT from Scott;

• Permission Inheritance and Recycling

1.Scenario Simulation:Oracle Headquarters has administrative rights to Oracle products,Oracle headquarters gives Oracle China Management in China, if Oracle China needs two times to assign this permission to use the WITH ADMIN option(System rights Inheritance)/with grant Option when the authority is granted at the headquarters (Object permission inheritance)

Example: SYS gives the user Scott the right to create tables, and Scott can give other users permission to create tables

Sol>conn SYS

GRANT CREATE TABLE to Scott Withadmin OPTION;

Description

CREATE TABLE under System permissions, use keywords With ADMIN OPTION granting permission Inheritance ;

at this point, SCOTT users can assign permissions to create tables to any other user

Sql>conn SCOTT

GRANT CREATE TABLE totest;

if the inheritance of object permissions is required, use the Withgrant OPTION

Example: Assign access to the employee table under the HR user to the user Scott,who can inherit

Sql>conn HR

Grant SELECT on Hr.employee to Scott with GRANT OPTION;

CONN Scott

GRANT SELECT onhr.employee to test; --scott Users assign permissions two times to test users;

2, inherit the rights to reclaim

when permissions are reclaimed, they are not recycled for system permissions Child permissions assigned with ADMIN OPTION;

object permissions are cascade recycled, such as user Scott user access to the Employe table under the HR user ,and The Scott user assigns access rights two times to the new user Test, the system automatically reclaims the access rights assigned by Scott Two while recovering Scott 's access rights ;

• Common role permissions and allocation principles

1. Common Roles

DBA: Has full privileges, is the highest system privilege, and only the DBA can create the database structure.

RESOURCE: You can create only entities, not to create database structures.

CONNECT: You can only log in to Oracle, not create entities, and you cannot create a database structure.

2, the role of the rights allocation principle:

for normal users: Grant Connect, resource permissions.
for DBA Admin User: Grant connect,resource, dba authority.

• Common Permissions Query view

--1 , show the system permissions that the role has

SELECT * from Role_sys_privs;

--2 , roles have permissions on table columns

SELECT * from Role_tab_privs;

--3 , querying the role that the user belongs to

SELECT * from User_role_privs;

--4 , displays the object permissions that have been granted to other users, the user is the currently logged on user

SELECT * from User_tab_privs_made

--5 , displays the object permissions that have been granted to other users, and the user is the person being granted

SELECT * from USER_TAB_PRIVS_RECD

--6 , display the permissions granted on the column, the user is the currently logged on user

SELECT * from User_col_privs_made

--7 , displays the permissions granted on the column, and the user is the person being granted

SELECT * from USER_COL_PRIVS_RECD

--8 , displays the system permissions owned by the currently logged on user

SELECT * from User_sys_privs

• User Management

1. Create user test, password test

CREATE USER test identified by test;

2, Modify the user test password is 123456

ALTER USER test identified by 123456;

3. unlock user SCOTT

ALTER USER Scott account UNLOCK;

• Create a database connection

CREATE [public] DATABASE link Connection name

CONNECT user name identified by login password

USING DB Instance Name

This article is from the "Oralce Learning path" blog, make sure to keep this source http://dushuai.blog.51cto.com/9461011/1577260

Oracle User and Rights Management