Oracle Administrative permissions and Roles (learning notes)

This article focuses on managing permissions and roles in the Oracle database.

Oracle permissions are divided into system permissions and object permissions .

First, System permissions:

System permissions are permissions that execute a specific SQL command that controls the user's ability to perform one or a set of database operations, such as when a user has CREATE TABLE permissions and can create tables under its schema, and can build tables under other user scenarios when owning the create any table.

Oracle provides more than 100 system permissions, often with:

Create Session Connection Database

CREATE TABLE Build Tables

Create View build views

Create TRIGGER Build Trigger

CREATE procedure to build stored procedures

Show system permissions: You can query the data dictionary view System_privilege_map to display all system permissions.

Sql> SELECT * from System_privilege_map order by name;

1. Grant system permissions:

In general, the grant of system permissions is done by the DBA, and if done by another user, the user must have the grant any privilege permission. When granting system permissions, you can bring the WITH ADMIN option option so that users who are granted permissions or roles can grant this permission or role to others.

Sql> Grant Privilege_name to user_name [with admin option];

2. Reclaim System permissions

In general, the recovery permission is completed by the DBA, and if other users complete the Reclaim system permission, the user is required to have the system permissions and the option to delegate system permissions (with admin option). The Reclaim system permissions are completed with the revoke command. However, notice the cascading reclamation issues for permissions, for example, System grants User a permission with admin option, User a grants permission to User B, but system reclaims user A's permissions, does User B still have permissions? I've come to the conclusion that User B still has permissions, which means that Oracle permission reclamation is not cascading .

Sql> revoke privilege_name from user_name;

second, object permissions

Refers to the right to access other programme objects. Users have direct access to objects in their own scenarios, and if they want to access objects in other user scenarios, they must have object permissions. For example, if User A wants to access the X table on user B, you must have permissions on the object on X.

Commonly used are:

Alter Modify

Delete Deletes

Select query

Insert Insertion

Update modification

Index indexes

References references

Execute execution

All permissions

Display object permissions, which can be displayed by using the data dictionary view to display the object permissions that the user or role has, and the view is Dba_tab_privs

Sql> SELECT * from Dba_tab_privs where grantee= ' user_name ';

1. Grant object permissions

prior to the Oracle 9i release, the grant of object permissions was done by the owner of the object, or by a user with GRANT option, after Oracle 9i, the DBA user could grant object permissions to any user. Grant object permissions are done with the grant command.

Sql> Grant privilege on user_name1.table_name[(FIELD1,FIELD2)] to user_name2;

2. Reclaim object Permissions

after Oracle 9i, the object owner or DBA user completes the object permission collection. note, however, that the collection of object permissions is cascading .

Third, the role

A role is a collection of related permission commands that are used to simplify the management of permissions. Roles are divided into pre-defined and custom-defined categories.

Predefined is the role defined by Oracle, and customization is the role that you establish based on your management needs.

1. Common Predefined role Descriptions

(1) Connect

Permissions: Alter session,create cluster,create database link,create session,create table,create view,create sequence

(2) Resource

Permissions: Create Cluster,create indextype,create table,create sequence,create type,create trigger,create procedure

(3) DBA

The DBA role has all of the system permissions and the WITH ADMIN option option, the default user is SYS and system, they can grant any user privileges to the systems, but the DBA does not have the privilege of SYSDBA and Sysoper (starting and shutting down the database).

2. Custom Roles

The role is typically created by the DBA and requires the Create role permission if another user creates it. You can specify the authentication method (no validation, database validation, etc.) when establishing a role

(1) Role creation (not verified)

If the role is a public role, it can be created in a way that is not validated.  

sql> Create role Role_name not identified;

(2) Role Creation (database validation)

In this way the role is established, and the role name and password are stored in the database. The password must be provided when the role is activated, and a password is required to establish the role.

Sql> Create role Role_name identified by password;

3. Role Authorization

When a role has just been created without any permissions, in order for the role to complete a specific task, you need to grant it certain system permissions and object permissions. There is little difference between a role authorization and a user's authorization, but note that the unlimited Tablespace and object permissions of the system permissions cannot be granted to the role with GRANT option.

SQL >grant privilege_name to Role_name;

4. Assigning Roles to users

The General allocation role is done by the DBA, and if the normal user assigns a role, the system permissions of the grant any role are required. Assign roles with the grant command.

Sql> Grant Role_name to user_name [with admin option];

5. Deleting a role

The General allocation role is done by the DBA, and if a normal user assigns a role, the system permission for the drop any role is required. Note that when a role is deleted, users who have this role will no longer have the corresponding permissions.

sql> drop role Role_name;

6. Show role Information

Show All role information:

Sql> select * from Dba_roles;

Displays the system permissions that the role has:

Sql> Select Privilege,admin_option from Role_sys_privs where role= ' role_name ';

Displays the roles and default roles that the user has:

Sql> Select Granted_role,default_role from Dba_role_privs where grantee= ' user_name ';

This article is from the "Flying Fish Technology" blog, please be sure to keep this source http://flyingfish.blog.51cto.com/9580339/1583151

Oracle Administrative permissions and Roles (learning notes)