Five Aspects of Oracle user permissions

Oracle users are divided into system and object permissions based on the granted permissions. This section describes how to create a role, grant permissions to the role, and revoke permissions from the role. Next we will analyze them one by one.

1. Create an Oracle user

Create role clerk identified by bicentenrix;
The preceding command creates a role named clerk with the password of bicentenrix. When the role is granted to a user, the password must be used for verification. If the identified clause is not specified, the database uses not identified. In this case, role activation does not require authentication.

Ii. Oracle user permissions

1. Grant the role system permissions and Roles
To authorize a role, you must have the following permissions:
1) To GRANT system permissions, you must be granted this permission, including Admin Option or grant any privilege system permissions.
2) if a role is granted, you must be granted the Admin Option or have the grant any roel system role. Grant create session to jward;

A) grant Admin Option
Grant new_dba to michal with admin option;
If a user or role is authorized to specify the with admin option clause at the same time, the following effects are available:
I. This user or role can grant this permission to any other user or revoke this permission from another user.
Ii. This user or role can pass the management right of this permission to any user.
Iii. Users granted a role can modify or drop the role.

B) create users while granting permissions
Oracle allows you to create a new user at the same time as grant. If you specify a password through an identified by clause and the user name/password does not exist in the database, a new user will be created.

C) Grant connect to ssmith identified by p1q2r3;

2. Grant the object role
If you want to GRANT the permission of an object to a user, you must meet the following conditions: You have the specified object; you have the permission to grant any object privelege system; you are granted the permission to access this Object, including with grant option.
Grant select, insert, delete om emp to July, tsmith;
Grant all on emp to July;
A) Grant Option
If you specify with grant option, the authorized user will obtain the following Oracle user permissions:
I. Authorized users can grant this permission to any user.
Ii. If the authorized user has the Create view or Create any view system permission, the user can Create a view on the table and grant access permissions to other users.

3. Authorize On Columns
You can Grant the permission to insert, modify, and delete a user on a separate column: Grant insert (acct_no) on accounts to scott;

Iii. Revoke Oracle user permissions

1. Revoke system Permissions
Any admin option or user with the specified system permission can Revoke the specified permission Revoke create table from tsmith from any user;
Note thatAdmin option cannot be revoked separately as a permission.

2. Revoke Object permissions
In order to revoke the Object permission, you must meet the following conditions: this permission is granted to the user or role before, and you have the grant any object privilege permission.

Note:: A user with a grant option. If the Object is recycled, the corresponding permissions of all users authorized by the Object will be revoked.
Revoke all on dept from human_resource;

Iv. Effective time of authorization and Revocation

Authorization and revocation of system or Object permissions take effect immediately. Granting a role to a user or revoking a role from the user will take effect the next time you create a session, unless specified by using Set. In the session, you can use set role to change the role: Set role ckeridentified by bicentenstn; if the user has a password, you must use identified by to specify to disable all roles Set role none.

5. Specify the user's default permissions

When a user logs in, oracle automatically grants permissions in the user's default role to the user. Alter user jane default role payclerk, pettycash

1. Seven methods for viewing Oracle user permissions
2. Explain how to use the Oracle user permission View
3. Introduction to Oracle user permissions and role management
4. Create and assign Oracle user permissions
5. An analysis of the management method of the Oracle user permission table