Provides you with an in-depth understanding of oracle permission management

Oracle permission management also gives the corresponding permissions after creating the database role, which is described in detail in the following section. If you are interested in oracle permission management, take a look.

I. Create a role
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. Role authorization

1. Grant the role system permissions and Roles
To authorize a role, you must have the following permissions:
If you want to GRANT system permissions, you must be granted this permission, including Admin Option or grant any privilege system permissions.
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 oracle permission management right 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
To grant an object permission to a user, you must meet the following conditions:
You have the specified object
You have the grant any object privelege system permission.
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 have the following 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 users the insert, modify, and delete permissions on Individual columns:
Grant insert (acct_no) on accounts to scott;

3. Revoke role Permissions

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

2. Revoke Object permissions
To revoke Object permissions, the following conditions must be met:
This permission is granted to users or roles by you.
You have the grant any object privilege permission.
Note: if an Object has been recycled, the 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 ckerk identified by bicentenrix; if the user has a password, you must use identified by to specify
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

How to store images in oracle

Implementation of oracle tree Query

Implementation of oracle query current time

Learn more about Oracle hierarchical Query

Provides you with an in-depth understanding of Oracle temporary tables