Oracle Administrative permissions and Roles

Introduced

This section focuses on how to manage permissions and roles in Oracle, and what the difference between permissions and roles is.

When a user is just established, the user does not have any permissions and cannot perform any action. If you want to perform a specific database operation, you must grant it permissions to the system, and if the user wants to access objects from other scenarios, you must grant them permissions to the object, and in order to simplify the administration of permissions, you can use roles.

Permissions:

Permissions are the rights to execute a specific type of SQL command or to access other schema objects, including system permissions and two diagonal permissions .

Introduction to System Permissions

System permissions are the right to execute a specific type of SQL command, which controls one or a set of database operations that a user can perform. For example, when a user has CREATE TABLE permissions, a table can be built in its schema, and a table can be built in any scenario when the user has the Create any table permission. Oracle provides over 160 system permissions. commonly used are:

Create session connection database;

Create view build views;

Create PROCEDURE build process, function, package;

Create cluster build cluster;

CREATE table build tables;

Create public synonym to build synonyms;

Create trigger trigger;

Show system permissions

Oracle provides 166 system permissions, and the higher the Oracle version, the more system permissions are available, and we can query the data dictionary view System_privilege_map, which shows all system permissions .

Basic syntax:

SELECT * from System_privilege_map order by name;

208 system permissions are available in the ORACLE11GR2.

Granting system permissions

In general, the grant of system permissions is done by a DBA, and if you use other users to grant system permissions, you require that the user must have system permissions with the grant any privilege to grant system permissions with the WITH admin option option , so that the user or role that is granted permission can also grant the system permission to other users or roles . To get you started quickly, let's illustrate:

1, create two users ken,tom initial stage they do not have any permissions, if the login will be the wrong information.

1.1, create two users, and specify a password.

Sql>create user Ken identified by Ken;

Sql>create user Tom identified by Tom;

2, to the user Ken authorized

2.1. When granting create session and CREATE table permissions with the WITH admin option

Authorization basic Syntax:

Grant permission name to user name;

Sql>grant create session to Ken with admin option;

Sql>grant CREATE table to Ken with admin option;

2.2. Grant create view without the with admin option

Sql>grant CREATE view to Ken;

3, to the user Tom authorized

We can grant Tom permission through Ken, because with admin option is added. Of course, we can authorize Tom through the DBA, and we'll use Ken to give Tom authorization:

Sql>grant create session to Tom with admin option;

Sql>grant CREATE table to tom with admin option;

Sql>grant CREATE view to tom;//error, Ken does not have permission to create view for Tom because Ken is not authorized by the DBA with the WITH admin option parameter.

Reclaim System permissions

In general, the Recovery System Authority is done by the DBA , and if other users are to reclaim system privileges, the user must have the appropriate system permissions and the option to delegate system permissions (with admin option). The recycle system permissions are done using revoke, and when the system permissions are reclaimed, the user cannot perform the appropriate action, but notice that the system permissions cascade back up the issue? (Do not Cascade collection permissions)

System==>>ken==>>tom

(Create session) (Create session) (Create session)

Perform the following actions with system:

Revoke create session from Ken; Do you think Tom can still log in? Can log in.

Basic syntax for recycling system permissions:

Revoke System permission name from user name;

Special note: The collection of system permissions is not a cascade collection.

Introduction to Object Permissions

The right to access other schema objects, the user can directly access the object of their own schema, but if you want to access objects of other scenarios, you must have permissions on the object, such as the Smith user to access the Scott.emp table (Scott: scheme, EMP: Table)

You must have permission on the Scott.emp table for the object. The common permissions are:

Alter Modify, delete delete, select query, insert Add, update Modify, index index, references reference, execute execute.

View all object permissions provided by Oracle (DBA users can view only)

Select distinct privilege from Dba_tab_privs;

Important Query Documents

View the current user's table (own table)

Select table_name from User_tables;

Querying all system permissions in Oracle, typically DBA

SELECT * from System_privilege_map order by name;

Querying all roles in Oracle, typically DBA

SELECT * from Dba_roles;

Querying all object permissions in Oracle, typically DBA

Select distinct privilege from Dba_tab_privs;

Querying the table space of a database

Select Tablespace_name from Dba_tablespaces;

Querying what system permissions the current user has

SELECT * from User_sys_privs;

Query the current user on someone else's table with what kind of object permissions

SELECT * from User_tab_privs; (View permissions on a table)

SELECT * from User_col_privs; (View permissions on a table's columns)

Query what role a user has

SELECT * from Dba_role_privs where grantee= ' username ';

See which system permissions are included in a role.

SELECT * from Dba_sys_privs where grantee= ' DBA ';

Or is:

SELECT * from Role_sys_privs where role= ' DBA ';

To view the object permissions that a role includes

SELECT * from Dba_tab_privs where grantee= ' role name ';

When a role has any system or object permissions, it can also be viewed directly through the PL/SQL developer tool.

Display error messages for execution

After executing the statement, execute the following statement

Show error;

Show an Oracle operation

Set timing on;

Authorization Object Permissions

Before Oracle9i, the grant of object permissions is done by the owner of the object and, if manipulated by other users, requires the user to have the corresponding (with GRANT OPTION) permission, starting with Oracle9i, SYS, The system can grant object permissions on any object to other users, and granting object permissions is done with the grant command .

Authorization basic Syntax:

Grant object permissions on database object to user name [with GRANT option] [, role name]

Special Note: You can assign permissions directly to users or roles. The [WITH GRANT OPTION] option can only be granted to a user and cannot be granted a role.

Let's look at a few cases:

1, monkey the user to manipulate the Scott.emp table, the corresponding object permissions must be granted.

(1), Hope Monkey can query scott.emp table data, how to operate?

Sql>grant Select on Scott.emp to Monkey;

(2), Hope Monkey can modify the table data of scott.emp, how to operate?

Sql>grant update on scott.emp to monkey;

(3), Hope Monkey can delete scott.emp table data, how to operate?

Sql>grant Delete on scott.emp to monkey;

(4), is there a simpler way to assign all permissions to monkey at once?

Sql>grant all on scott.emp to monkey;

2. Authorization ALTER permission

If the black user modifies the structure of the SCOTT.EMP table, the Alter object permission must be granted

Sql>grant alter on scott.emp to black;

3. Grant Execute permission

Execute permission is required if the user wants to execute packages/procedures/functions for other scenarios. For example, to allow Ken to execute Package dbms_transaction, execute permissions can be granted.

Sql>grant execute on Dbms_transaction to Ken;

4. Grant the index permission

If you want to index on a table in another scenario, you must have the Index object permission, such as the object permission for Black to index on scott.emp

Sql>grant index on scott.emp to Blake;

5. Using the WITH GRANT option

This option is used to delegate object permissions, but this option can only be granted to the user and not to the role.

Example: Grant to Jones by Blake Select permission

The DBA gives Blake the SELECT permission first

Sql>conn SYSTEM/ORCL;

Sql>grant Select on Scott.emp to Blake with GRANT option;

Sql>conn BLAKE/ORCL;

Sql>grant Select on Scott.emp to Jones;

Reclaim Object permissions

In Oracle9i, the permission to retract an object can be done by the owner of the object, or by the DBA User (Sys,system).

The point here is that the user will not be able to execute the corresponding SQL command after the object permission is retracted, but be aware that the object's permissions will be cascaded back up? (Cascade Recycle)

Please see the case:

Scott========>>blake========>>jones

Select on EMP Select on EMP Select on EMP

Basic syntax for object permission reclamation:

Revoke object permissions on the database object from user name [, role name];

Special NOTE: Permission recycling for objects is a cascading collection.

1. Switching system users

Sql>conn SYSTEM/ORCL;

2. Build Blake and Jones users

Sql>create user Blake identified by Blake;

Sql>create user Jones identified by Jones;

3. Assign system privileges to Blake and Jones users (login rights)

Sql>grant create session to Blake with admin option;

Sql>grant create session to Jones;

4. Switch Scott user to Blake Assignment permissions (View permissions)

Sql>conn Scott/tiger;

Sql>grant Select on EMP to Blake with GRANT option;

5. Switch Blake user to Jones assignment permissions (View permissions)

Sql>conn Blake/blake;

Sql>grant Select on Scott.emp to Jones;

6. Switch Scott user to reclaim Blake object permissions

Sql>conn Scott/tiger;

Sql>revoke Select on the EMP from Blake;

7. Switch Blake user test to see if object permissions are still available.

Sql>conn Blake/blake;

Sql>select * from scott.emp;//error, Blake no query permission.

8. Switch Jones user test to see if object permissions are still available.

Sql>conn Jones/jones;

Sql>select * from scott.emp;//error, Jones no query permission.

Managing permissions and roles--roles

Introduced

A role is a collection of commands related to permissions, and the primary purpose of using roles is to simplify the management of permissions.

Take a look at the question: Assume that you have a user of one-and-one to give them permission.

1. Connect to the database

2, on the Scott.emp table select,insert,update

If you take a direct authorization operation, you need to authorize 12 times.

Roles are divided into pre-defined roles and custom roles.

Pre-defined roles

Predefined roles refer to the roles provided by Oracle, each of which is used to perform specific administrative tasks, and we describe common predefined roles connect,resource,dba

Special note: A role can contain system permissions or object permissions.

To see what permissions a role has, you can view it through the following statements:

SELECT * from Dba_sys_privs where grantee= ' DBA ';

Note: The name of the role is capitalized when queried (DBA, CONNECT, RESOURCE), lowercase cannot be queried

You can query all predefined roles by using the system login:

SELECT * from Dba_roles;

How to know what role a user has:

SELECT * from Dba_role_privs where grantee= ' user name ';

Basic syntax for assigning permissions to users through roles:

Grant role name [, role name 2,...] to user name;

1. Connect role

The Connect role has most of the permissions that a general application developer needs, as long as the connect and resource roles are granted to the user, what system permissions does the connect role have?

The Connect role has:

Create session creating Connection permissions

2. Resource role

The resource role has other permissions that the application developer needs, such as setting up stored procedures, triggers, and so on. It is important to note that the resource role implies unlimited tablespace system permissions.

The resource role has:

Create trigger creating a trigger

Create sequence creating a sequence

Create Type Permissions

CREATE PROCEDURE creation Process

Create cluster creating a cluster

Create operator operator

Create Indextype Creating an index type

CREATE table Creating tables

3. DBA role

The DBA role has all of the system permissions, and with the admin option option, the default DBA user is SYS and the system they can grant any systems permission to other users, but be aware that the dba Role does not have privileges of SYSDBA and Sysoper (start and close database)

Case:

Create a user and assign to the Connect role and the resource role

Sql>create user Tempuser identified by Tempuser;

Sql>grant Connect,resource to Tempuser;

Create a user jack and set it as a user with a DBA role

Sql>create user Jack identified by Jack;

Sql>grant DBA to Jack;

Custom roles

As the name implies is your own definition of the role, according to their own needs to define, usually DBA to establish, if the use of other users to establish, you need to have the create role system permissions. You can specify the authentication method (no validation, database validation, etc.) when establishing a role

1. Build a role (not verified)

If the role is a public role, the role can be established in a non-verifiable manner.

The build role does not validate the basic syntax:

Create role role name not identified;

2. Establish a role (database validation)

In this way, the role name and password are stored in the database. When the role is activated, a password is required. When this role is established, it is required to provide a password.

The basic syntax for establishing a role requires database validation:

Create role role name identified by password;

Role authorization

When a role is established, the role does not have any permissions, and in order for the role to complete a specific task, it must be granted the appropriate system and object permissions.

(i) Authorization to the role

There is not much difference between granting permissions to a role and authorizing a user, but note that the unlimited tablespace and object permissions of the system permissions cannot be granted to the role with the GRANT OPTION option .

Basic syntax for role authorization:

Grant object permissions on the database object to the custom role name;

Case:

Complete the Create session,select on Scott.emp,insert to scott.emp,update on Scott.emp grant role, and then grant the role to the A,b,c user.

1. Create a custom role using the system user

Sql>conn SYSTEM/ORCL;

Sql>create role Crud_scott not identified;

2. Crud_scott Authorization for custom roles

Sql>grant create session to Crud_scott;

Sql>grant Select on Scott.emp to Crud_scott;

Sql>grant insert on scott.emp to Crud_scott;

Sql>grant update on scott.emp to Crud_scott;

3, authorized by the role Crud_scott to the user

Sql>grant Crud_scott to A;

Sql>grant Crud_scott to B;

Sql>grant Crud_scott to C;

(ii) Assigning roles to a user

The General allocation role is done by the DBA, and if you want to assign a role as a different user, you are required to have system permissions for the grant any role.

To authorize user basic syntax by role name:

Grant role name to user name [with admin option];

If the user is assigned permissions with the WITH ADMIN option option, the authorized user can continue to grant this permission to other users.

Remove a role

Use the drop role, which is typically performed by a DBA, and requires that the user have drop any role system permissions , as with other users

Delete role Basic syntax:

Drop Role role name;

Show role Information

1. Show All characters

SELECT * from Dba_roles;

2. Display the system permissions that the role has

Select Privilege,admin_option from Role_sys_privs where role= ' role name ';

3. Display the object permissions that the role has

Query the data dictionary view Dba_tab_privs to view the object permissions or the permissions of a column that the role has.

SELECT * from Dba_tab_privs where grantee= ' role name ';

4. Display the role that the user has, and the default role

When connecting to a database as a user, Oracle automatically activates the default role by querying the database dictionary view Dba_role_privs to display all the roles that a user has and the current default role

Select Granted_role,default_role from Dba_role_privs where grantee= ' role name ';

Granular access control (only to understand, not detailed)

means that users can use functions and policies to achieve more granular case access control. If granular access control is used, Oracle automatically appends a predicate (WHERE clause) to the SQL statement and executes a new SQL statement when the client issues the SQL statement (Select,insert,update,delete). This control allows different database users to return different data information when accessing the same table. The use of functions or policies is to better protect the security of the data, to provide different levels of security for users of various permissions, can effectively protect information security.

Oracle Administrative permissions and Roles