User rights and roles for Oracle

User rights and role system permissions for Oracle

definition: refers to the right of a particular type of SQL command.

Common ones are:

Create Session Connection Database

CREATE TABLE Build Tables

CREATE VIEW Building View

Create public synonym synonyms

Create PROCEDURE build process, function, package

Create TRIGGER Build Trigger

Create cluster build Cluster

How to use Select to query what system permissions are available

SELECT * from System_privilege_map order by name;

Case:

1. Create two users and specify a password

Connect SYSTEM/ORCL;

Create user Ken identified by Ken;

Create user Tom identified by Tom;

2. Grant the session to Ken, build the table, and build the View permissions

Grant create session to Ken with admin option "with admin option"

Grant CREATE table to Ken with admin option "with admin option"

Grant CREATE view to Ken "No with admin option"

3. use Ken to give Tom the same permissions

Connect Ken/ken;

Grant create session to Tom with admin option
Grant CREATE table to Tom with admin option

Grant CREATE view to Tom " Error!!!!! "

------------------------------

reasons for the error:

Flow of permissions:

System = "Ken =" Tom

How can I give others permission, except that system has super privilege, other users want to inherit to give others

permissions, you must add the WITH ADMIN option when the system grants permissions to have permission

Ability, as shown above when the system gives Ken permission, only the CREATE view is not joined with Adminoption, so he can not give others permission, so error.

Reclaim system permissions

Reclaim Ken [Create session permissions] using System

Basic syntax

Revoke permission name from user name

Cases:

Revoke create session from Ken;

Problem:

Whether or not the permission of Tom, who was once granted permission by Ken, is also recycled after Ken's permissions are recycled.

Answer: No, only the specified permissions are recycled

Object permissions

definition : The right to access other program objects

Oracle provides us with 17 object permissions, which can be viewed by the following instructions (DBA role)

Select distinct privilege from Dba_tab_privs;

Basic syntax:

Grant object permission on scheme. Data object to user [with GRANT OPTION]

Grant object permission on scheme. Data object to role ( role cannot have rights to grant permissions )

Case:

1.monkey to manipulate the Scott.emp table, the user must give the corresponding object permission

(1) Hope Monkey can query the data in the Scott.emp table?

Using Scott or System/sys user actions

Grant Select on Scott.emp to Monkey;

(2) Want Monkey to be able to modify the data in the Scott.emp table?

Grant update on scott.emp to monkey;

(3) Want Monkey to delete the data in the Scott.emp table?

Grant Delete on scott.emp to monkey;

(4) Is there a simple way to give all rights at once?

Grant all on scott.emp to monkey;

2. Grant Moneky users The ability to modify the structure of the Scott.emp table

Grant alter on scott.emp to Monkey;

3. Grant Execute permission (concept)

Execute permission is required if the user wants to execute packages/procedures/functions for other scenarios.

4. Grant Monkey users the ability to index in the Scott.emp table?

Connect Scott/tiger;

Grant index on scott.emp to monkey;

Reclaim Object permissions:

Basic syntax:

Revoke object permission on scheme. Data object from user

Object permissions are cascading recycles: Because an object is referenced, the reference is lost once the object disappears.

Scott =>>>>> Blake =>>>>> Jones

System Operation:

Create user Blake identified by Blake;

Create user Jones identified by Jones;

Grant create session to Blake;

Grant create session to Jones;

Using Scott Login, grant Blake the ability to query the EMP table and

Then use Blake to grant Jones permission and then query the EMP table

Scott Operations

Grant SELECT on the EMP to Blake;

Blake operation

Grant SELECT on Scott.emp to Jones;

At this point, both tables can query the EMP table

Attention:

Scott Operations

Revoke select on the EMP from Blake; --Reclaim permissions

Neither table can query the EMP table at this time

Role

definition: a role is a set of permissions that is designed to simplify the management of permissions and thus achieve a simple management of the user.

categories of roles :

(1 ) pre-defined roles:

Oracle provides 33 predefined roles, commonly used (connect,dba,resource);

How do I know what permissions a role has?

SELECT * FROM dba_sys_privs where grantee = ' DBA ';

How do I know what permissions a user has?

SELECT * FROM dba_role_privs where grantee = ' username ';

Note: Character names must be capitalized

(2 ) Custom roles:

Basic Syntax:

without validation:

Create role name not identified;

With validation:

Create role name identified by password;
Case:

To create a role:

Create role Myrole not identified;

Assign a role right:

System permissions:

Grant create session to Myrole;

object Permissions (scott.emp) :

Grant SELECT on Scott.emp to Myrole;

Grant insert on scott.emp to Myrole;

Grant update on scott.emp to Myrole;

To give the user a role:

Create user Jerry identified by Jerry;

Grant Myrole to Jerry;

Recycling roles:

Drop role Mylore;

scope: a role can contain either a system role or a custom role.

User rights and roles for Oracle