Oracle permissions (Grant, REVOKE)

Database version: 11GR2

First, Introduction

In Oracle, there is no concept of databases in other database systems, and objects are created under the user. The current user has all the permissions for all objects under the current user, regardless of whether the object is created by the current user. Give a simple example create a user to grant the user connection permissions, and then create a table under that user with an administrator user who can delete the tables that the administrator created under that user.

Second, the Authority

Permissions can be divided into three main categories: System permissions, Roles, object permissions. A role is a grouping of a class of permissions.

1. System permissions

System permissions are actually the permissions that the user has under the current user schema. There are 200 system privileges in 11g, such as Create table,unlimited tablespace.

To query the system permissions that the current user has:

From User_sys_privs;

2. Roles

A role is actually a group of permissions, so assigning a role to a user is actually assigning permissions to the user. There are three more commonly used roles in Oracle. For generally not very strict systems can be granted to the development of the user Connect, resource role permissions can be.

DBA: The role has all the permissions of the database.

Connect: The role has permissions to connect to the database, as well as the permissions of the create session.

RESOURCE: This role is an application development role with the following permissions

To query the roles owned by the current user:

From User_role_privs;

3. Object permissions

Object permissions refer to the permissions of other objects that own the user. Permissions for other user objects include: select,delete,update,alter,insert,index,references,flashback,debug,query rewrite,on COMMIT REFRESH; Note the permissions for other user objects do not have drop permissions.

To query object permissions owned by the current user:

From User_tab_privs;

The current user chenmh has all the permissions for the student table under user Zhang.

Third, Grant authorization

1. Grant user connect, Resource role permissions. Generally creating application development users can grant permissions to both roles.

to Zhang;

2. Grant the user CHENMH Select,delete,update,insert permission to the person table under the user Zhang, and grant specific object permissions is a scheme for strict control of permissions.

SELECT,DELETE, tochenmh; 

3. Grant user CHENMH All rights to the person table under the user Zhang

to CHENMH;

Grant Permission graph:

Iv. Revoke recovery rights

1. Reclaim Role Permissions

From CHENMH;

2. Reclaim System permissions

From CHENMH;

3. Reclaim User object permissions, reclaim all permissions of the person table under the Zhang user, if the individual granted permission requires a single retract

From CHENMH;

Revoke chart:

Five, batch operation

1. Batch grant permission, grant the user chenmh to have all tables under the Zhang additions and deletions to change permissions. Production batch Execution SQL

SELECT ' GRANT select,delete,update,insert on ' | | owner| | '. ' | | table_name| | ' to CHENMH; ' From Dba_tables WHERE owner= ' ZHANG ';

You can query the Dba_objects table if you want to query other objects.

2. Batch Reclaim system privileges, recover all system privileges of user CHENMH

SELECT ' REVOKE ' | | privilege| | ' from CHENMH; '  From Dba_sys_privs WHERE grantee= ' chenmh ';

Copy the stitched SQL out to execute the query.

3. Bulk recall of Role permissions

SELECT ' REVOKE ' | | granted_role| | ' from CHENMH; ' From Dba_role_privs WHERE grantee= ' chenmh ';

4. Bulk recover User object permissions, recover user chenmh under the schema Zhang All rights

SELECT ' REVOKE ' | | privilege| | ' On ' | | owner| | '. ' | | table_name| | ' from CHENMH; ' From Dba_tab_privs WHERE grantee= ' chenmh ' and owner= ' the ' ZHANG ' ORDER by Table_name,privilege;

Note: View permissions for Oracle users are special and need to be granted separately to the resource role to not have permission to create views. can perform

Grant CREATE view to CHENMH;

Oracle permissions (Grant, REVOKE)