9. Oracle permission and role management

Manage permissions and Roles

How Does Oracle manage permissions and roles? What is the difference between permissions and roles?

When a user is created, the user does not have any permissions and cannot perform any operations. If you want to perform a specific database operation, you must grant the system permission to it. If you want to access objects in other schemes, you must grant the object permission to it.

Roles can be used to simplify permission management.

1. Category

Permission: Permission refers to the right to execute a specific type of SQL command or access other solution objects, including system permission and object permission 2:

1) What is system permission? System permissions include logging on to the database, creating databases, creating tables, creating stored procedures, and creating indexes. (There are more than 140 permissions) 2) What are system permissions? Select * From system_privilege_map order by name; 3) How to grant system permissions to users?

1) What is object permission? Object permission: the user's permission to access/operate Data Objects of other users; (about 25) For example, the user's permission to access tables and views of other users; 2) What are the object permissions? Select * from 3) How to grant Objects Permissions to users?

I. System Permissions

1) Introduction to system Permissions

System permission refers to the right to execute specific SQL commands. It is used to control one or more database operations that users can perform. For example, if you have the create table permission, you can create a table in the solution. If you have the create any table permission, you can create a table in any solution. Oracle provides more than 100 system permissions.

Common system permissions include:

Create session: connect to the database Create Table: Create a table Create view: Create View Create procedure: creation process, function, package Create trigger: Create a trigger Create cluster: Create Cluster Create public synonym: Synonym

 

2) display system Permissions

Oracle provides more than 140 system permissions. The higher the Oracle version, the more system permissions it provides. You can query the data dictionary view system_privilege_map to display all system permissions.

Select * From system_privilege_map order by name;

 

3) grant system Permissions

Generally, the DBA grants the system permission. If other users grant the system permission, the user must have the system permission to grant any privilege. When granting permissions, You can include the with admin option. In this way, the authorized user or role can grant the system permission to other users or roles.

For example:

A) create two users, Ken and Tom. They do not have any permissions in the initial stage. If they log on, an error is reported.

Create user Ken identified by Ken;

Create user Tom identified by m123;

B) authorize user Ken

①Grant create session, create table to Ken with admin option;

②Grant create view to Ken;

C) authorize the user Tom

Grant create session, create table to Tom with admin option;

 

4) Revoke system Permissions

Generally, DBA is responsible for revoking system permissions. If other users revoke system permissions, this user must have the corresponding system permissions and the option (with admin option) to authorize the system permissions, and revoke the system permissions with the revoke command.

After revoking system permissions, the user cannot perform the corresponding operations. But please note that the system permission cascading recovery problem?

System ----------------------- à Ken --------------------------- à Tom

(Create session) (create sessioin)

Use System to perform the following operations: revoke create Session from Ken;

Think about it. Can Tom log on? -- Yes. The system permission is not cascaded revocation.

 

Ii. Object Permissions

1) object permission Introduction

Object permission refers to the permission to access objects in other schemes. Users can directly access objects in their schemes. If they want to access objects in other schemes, they must have the object permission.

For example, the Smith user wants to access the Scott. EMP table (Scott solution, EMP table)

You must have the object permission on the EMP table.

Common Object permissions include:

Alter: Modify Delete: Delete Select: Query Insert: add Update: Modify Index: Index Reference: Reference Execute: Execute

 

2) Display object permissions

The data dictionary view dba_tab_privs can be used to display the object permissions of users or roles.

Conn system/manager as sysdba;

Select distinct privilege from dba_tab_privs;

Select grantor, owner, table_name, privilege from dba_tabl_privs where grantee = 'blank ';

 

3) grant object permissions

Before Oracle9i, the object permission is granted by the object owner. If other users perform operations, the user must have the (with grant option) Permission, starting from Oracle9i, DBA users (sys, system) can grant objects on any object to other users. Grant object permissions using the grant command.

Object permissions can be granted to users, roles, and public. When granting permissions, if the with grant option is included, you can grant this permission to other users. Note that the with grant option cannot be granted to the role.

For example:

A) to operate the Scott. EMP table as a monkey user, the corresponding object permissions must be granted.

① What should I do if I want monkey to query Scott. EMP table data?

Grant select on EMP to monkey;

② What should I do if I want monkey to modify the data in the Scott. EMP table?

Grant update on EMP to monkey;

③ What should I do if I want monkey to delete the Scott. EMP table data?

Grant delete on EMP to monkey;

④ Is there a simpler way to grant all permissions to monkey at one time?

Grant all on EMP to monkey;

 

B) Can I control the monkey access permission more precisely? (Grant column Permissions)

① How can I modify the Sal field of the Scott. EMP table for monkey?

Grant update on EMP (SAL) to monkey;

② What should I do if I want monkey to query the ename and Sal fields of the Scott. EMP table?

Grant select on EMP (ename, Sal) to monkey;

 

4) grant alter permission

If the black user wants to modify the Scott. EMP table structure, the alter object permission must be granted.

SQL> conn Scott/triger;

SQL> grant alter on EMP to black;

Of course, you can also use sys and system to complete this task.

 

5) grant execute permission

If you want to execute procedures, functions, and packages for other schemes, you must have the execute permission.

For example, you can grant the execute permission to allow the Ken to execute the package dbms_transaction.

SQL> conn system/manager;

SQL> grant execute on dbms_transaction to Ken;

 

6) grant the index permission

If you want to create an index on a table in another solution, you must have the index object permission.

For example, to allow black to create an index on the Scott. EMP table, grant it the object permission of the index.

SQL> conn Scott/triger;

SQL> grant index on EMP to black;

 

7) use the with admin option and use the with grant option for object permissions

This option is used to grant object permissions. At that time, this option can only be granted to users and cannot be granted to roles.

SQL> conn Scott/triger;

SQL> grant select on EMP to black with admin option;

SQL> conn black/m123;

SQL> grant select on Scott. EMP to Jones; -- Transfer

 

8) Revoke object permissions

After Oracle9i, revoking object permissions can be completed by the object owner, or by DBA users (sys, system.

Note: After the object permission is revoked, the user cannot execute the corresponding SQL command. However, do you need to note that the object permission will be cascaded for revocation?

For example:

Scott ----------------------------- à black ---------------- à Jones

(Select on EMP)

SQL> conn Scott/triger;

SQL> revoke select on EMP from black;

Think about it: Can Jones still query the data in the Scott. EMP table?

-No, the object permission is cascading deletion.

 

1. Solution

When you create a user, the system automatically creates a solution that corresponds to the user name and has the same name as the user name.

The solution stores various data objects.

1. Data Object

Data Objects: tables, stored procedures, triggers, views, sequences, synonyms, etc.

1. Role

Roles are used to simplify permission management.

Oracle provides a total of 25 predefined roles:

Select * From dba_roles;

In Oracle, It is very tiring to assign too many permissions one by one. Therefore, a set of built-in basic permissions is proposed, which is called a role;

For example, connect is a role that contains permissions;

Roles include custom roles and predefined roles.

Predefined roles: built-in

Custom roles: User-defined roles

Authorization example:GrantConnect to Xiaoming; -- authorization successful

Common roles:

Connect: database connection permission

DBA: the permission is high and cannot be granted easily

Resource: You can create tables in any tablespace.

 

Iii. Roles

1. Roles

A role is a set of commands for related permissions. The main purpose of a role is to simplify permission management.

Roles are divided into predefined roles and custom roles. (Lenovo: Package and buffet)

Assume that users A, B, and C have permissions for them.

①Connect to the database,

②Select, insert, update on Scott. EMP

If you use direct authorization, You need to perform 12 operations, which is too troublesome!

Simplified operation:

First, create session, select on Scott. EMP, insert on Scott. EMP, update on Scott. EMP grants the role, and then grants the role to three users, A, B, and C.

 

1. Predefined role

 

 

 

 

 

If the role is deleted, can the user log on or select the role?