Oracle Learning Rights Management

After creating the user, the user can not do anything, the login database will be reported Ora-01045:user Xiaoming lacks CREATE SESSION privilege; Logon denied error. At this point, we should give the user authorization. First, learn about Oracle's rights management.

I. Overview of permissions

Oracle databases are security-managed with permissions, and permissions are an important part of Oracle's security mechanisms. These permissions can be divided into two categories: System permissions and Object permissions.

System permissions: System permissions are the system-level control of database access and use of the mechanism, that is, the ability to execute a statement SQL statement, for example, start, stop the database, modify the database parameters, connect to the database and create, delete, change schema objects (such as tables, views, indexes, procedures, etc.).

Object permissions: Object permissions are the mechanisms that control the access and use of databases at the object level, that is, the ability to access other user-mode objects. Object permissions are the mutual access permissions for schema objects, such as tables and views, between users. For example, the user xiaoming to query the information of the EMP table in Scott, which requires the authorization of the Scott user.

Second, System Rights management

Oracle's system permissions generally need to be granted to database administrators and application developers, who can grant system permissions to other users, or they can reclaim system permissions from the granted user. Can be divided into a variety of different types

2.1 Classification of system permissions

1. Database maintenance permissions. For the database administrator, you need to create a table space, modify the database structure, create users, modify user permissions, and so on database maintenance permissions.

2. Database schema object permissions. For database developers, just understand the permissions to manipulate database objects, such as creating tables, creating views, and so on

3. Any permission, and one of the privileges in system permissions is any, with any permission that can be manipulated in any user mode.

2.2 Grant of System permissions

Syntax format:

Grant  to {public| Role_name| user_name [.. n]

       [withadmin option]

Where System_privilege is the name of the system privilege, public is the common user group in Oracle, and if the permissions are granted publicly, it means that all users in the database will have that permission, and Role_name is the name of the role to grant permissions. User_name is the name of the user to grant permissions to.

If you use the WITH ADMIN option option, authorized users can also pass these system permissions to other users and roles.

Example one: Granting a user xiaoming permission to connect to a database

The user does not have any permissions after creating the user Xiaoming, and the login database will be error

Using the System connection database, execute the statement:

Grant Create  to Xiaoming

You can then use the Xiaoming user to log in to the database.

Example two: Grant the user xiaoming permissions to create tables and views in any user mode. and allow users to grant these permissions to other users xiaoming

Grant Create  any table,createanyview

       to xiaoming        with option

2.3 Recovery of System permissions

A database administrator or a user with permissions granted to his or her users can use the REVOKE statement to retract the system permissions that have been given.

Syntax format:

Revoke  from {public| Role_name| user_name [.. n]}

For example, using the system user login, the following statement can recover user Xiaoming's Create session permissions

Revoke Create  from Xiaoming;

When a user's system permissions are reclaimed, the corresponding delivery permission is also retracted, but the user who has passed and gets permission is not affected. For example: After the user xiaoming permissions are reclaimed, the create session granted to the user by Xiaoming Xiaohong is not recycled.

Iii. Object Permissions 3.1 Classification of object permissions

Oracle scenarios have 9 permissions on objects: Select, Update, delete, insert, execute (Execution type, function, package, and procedure), read (read data in data dictionary), index, peferences (Generate foreign key), Alter.

3.2 Granting of object permissions

Syntax format:

Grant{Object_privilege| All[Privilege][column (.. N)]}          on [schema.]object_name  to{ Public|Role_name|user_name[.. N]}         [With grant OPTION];

Where Object_privilege is the name of the object permission, and the keyword all represents the object permissions granted to the object. You can also use column to specify permissions on a column of a table, which is used to specify the object on which the permission resides, with the GRANT option option to specify that the user can grant these permissions to other users.

Example three: Grant the query of the Scott User's EMP table to Xiaoming and allow the user to grant this permission to other users xiaoming

To log on using the system user, execute the statement:

Grant Select  on  to  with Grant option

3.3 Recall of object permissions

Syntax format:

Revoke{Object_privilege| All[Privilege][column (.. N)]}          on [schema.]object_name  to{ Public|Role_name|user_name[.. N]}         [Cascade constrainsts];

Where the Cascade constrainsts option indicates that when the object is retracted, the referential integrity constraints defined on the object are also deleted with the References object permissions.

Example four: Recover user Xiao Ming inquires Scott's EMP table

Using System login, execute the statement:

Revoke Select  on  from Xiaoming

Note: After the object permission is reclaimed, the corresponding delivery permission is also reclaimed, and the permissions of the user who has passed and obtained permissions are also recycled. For example, the user xiaoming the right to query the EMP table to Xiaohong, and when Xiaoming's permissions are reclaimed, the permissions of the red are not there.

To give the query permission to Xiaohong:

Reclaim xiaoming Query permissions, xiaohong query permissions are also recycled:

Reference: Oracle Use Tutorial (third edition) Zheng Achi Editor

Oracle Learning Rights Management