Grant, view, and manage user permissions in the Oracle System (2)

In Oracle databases, user permissions are divided into two types (here we do not discuss the permissions of dba or role, but only the permissions of common users ), system Privilege System permission and User Table Privilege User data Table permission.

1. First, create a user. The following commands can be used to create a user, provided that you must Log On As a dba (if you are not a DBA, do not check it ):

Create user DB_USER identified by DB_USER_PW 'create user DB_USER with the password DB_USER_PW

Grant create session to DB_USER permission to create a session for the user

Grant resource to DB_USER

2. after a user is created, Scheme of the user is automatically generated in the Oracle database system (it can be understood as all tables belonging to the user, view .... object set ).

This user can grant access permissions to these objects to other system users.

3. After the user logs on with sqlplus, the following command shows the user's permissions (this part is taken from the CNOUG website ):

This user's permission to read other user objects:
Select * from user_tab_privs;
System permissions of the User:
Select * from user_sys_privs;

ORACLE Database User and permission management

ORACLE is a multi-user system that allows many users to share system resources. To ensure the security of the database system, the database management system is configured with a sound security mechanism.

2. 1 ORACLE Database Security Policy

Establish system-level security assurance
System-level privileges are implemented by granting users system-level rights. System-level rights (SYSTEM privileges) include creating tablespaces, creating users, modifying users' rights, and deleting users. System privileges can be granted to users or recycled at any time. The ORACLE system has over 80 privileges.

Establish object-level security assurance
Object-level privileges are implemented by granting users the right to operate (query, add, delete, and modify) Specific tables, views, and sequences in the database.

Establish user-level security assurance
User-level security assurance is implemented through user passwords and role mechanisms (a set of rights. The role mechanism is introduced to simplify user authorization and management. The practice is to group users by their functions, create roles for each user, and assign roles to users. users with the same roles have the same privileges.

2.2 user management

The content of ORACLE user management mainly includes User Creation, modification and deletion.

User Creation
SQL> CREATE USER jxzy

> Identified by jxzy_password

> Default tablespace system

> QUATA 5 m on system; // maximum space limit for users

User Modification
SQL> CREATE USER jxzy

> Identified by jxzy_pw

> QUATA 10 m on system;

Delete users and their created objects
SQL> DROP USER jxzy CASCADE; // Delete the created object at the same time

2.3 system privilege management and control

ORACLE provides over 80 SYSTEM privileges, each of which allows you to perform one or more database operations.

Grant System privileges
SQL> GRANT CREATE USER, ALTER USER, DROP USER

> TO jxzy_new

> With admin option;

Revoke SYSTEM privileges
SQL> REVOKE CREATE USER, ALTER USER, DROP USER

> FROM jxzy_new

// But no Cascade recovery function

Displays System privileges granted (SYSTEM privileges of a user)
SQL> SELECT * FROM sys. dba_sys_privs

2.4 Object Privilege Management and Control

ORACLE Object Privilege refers to the user's right to perform special operations on the specified table. These special operations include addition, deletion, modification, viewing, execution (stored procedure), reference (other table fields as foreign keys), and indexing.

Grant object privileges
SQL> GRANT SELECT, INSERT (office_num, office_name ),

> UPDATE (desc) ON office_organization

> TO new_adminidtrator

> With grant option;

// Cascade authorization

SQL> GRANT ALL ON office_organization

> TO new_administrator

Revoke object privileges
SQL> REVOKE UPDATE ON office_orgaization

> FROM new_administrator

// Cascade recovery

SQL> REVOKE ALL ON office_organization

> FROM new_administrator

Show all granted object privileges
SQL> SELECT * FROM sys. dba_tab_privs

2.5 role management

An ORACLE role is a named privileged group (including system and object privileges). ORACLE uses it to simplify Privilege Management and grant it to users or other roles.

The ORACLE Database System pre-defines five roles: CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE, and IMP_FULL_DATABASE. CONNECT has the privilege to create tables, views, sequences, and other resources. RESOURCE has the privilege to create processes, triggers, tables, sequences, and other resources. DBA has all system privileges; EXP_FULL_DATABASE and IMP_FULL_DATABASE have the privilege of detaching and loading databases.

By querying sys. dba_sys_privs, you can understand the permissions of each role.

Grant User Roles
SQL> GRANT DBA TO new_administractor

> With grant option;