Role and permission granting in Oracle

Oracle provides three standard roles (role) for compatibility with earlier versions: connect, resource, and dba.

1. connect role (connection role)

Temporary users, especially those who do not need to create tables, usually only connect them. Connect is a simple permission for using oracle. This permission can be meaningful only when you have access to tables of other users, including select, insert, update, and delete. Users with connect role can also create tables, views, sequences, clusters, synonyms (synonym), and sessions) and link with other databases ).

Query the connect permission of the current version

Select grantee, privilege from dba_sys_privs
Where grantee = 'connect ';

2. resource role (resource role)

Resource role can be granted to more reliable and formal database users. Resource provides users with additional permissions to create their own tables, sequences, procedures, triggers, indexes, and clusters ).

Select grantee, privilege from dba_sys_privs
Where grantee = 'resource ';

3. dba role (database administrator role)

Dba role has all system permissions, including unlimited space limits and the ability to grant various permissions to other users. System is owned by dba users. The following describes some typical permissions frequently used by DBAs.

Select grantee, privilege from dba_sys_privs
Where grantee = 'dba ';

(1) grant (authorization) command

The following command is used to grant permissions to user01:

Grant connect, resource to user01;

(2) revoke (UNDO) Permission

The granted permissions can be revoked. For example, to revoke the authorization in (1), run the following command:

Revoke connect, resource from user01;

A user with a dba role can revoke the connect, resource, and dba permissions of any other users or even other DBAs. Of course, this is very dangerous. Therefore, unless necessary, dba permissions should not be granted to general users who are not very important. Revoking all permissions of a user does not mean that the user is deleted from oracle, nor does it damage any table created by the user; it simply disallow access to these tables. Other users who want to access these tables can access these tables as before.

Ii. Create a role

In addition to the three system roles-connect, resource, and dba, you can also create your own role in oracle. A role created by a user can be composed of a table or system permission or a combination of the two. To create a role, you must have the create role system permission. The following is an example of the create role command:

Create role student;

This command creates a role named student.

Once a role is created, the user can authorize it. The syntax of the grant command authorized to role is the same as that for the user. When authorizing a role, you must use the role name in the to clause of the grant command, as shown below:

Grant select on class to student;

Now, all users with the student role have the select permission on the class table.

Iii. delete a role

To delete a role, run the drop role command as follows:

Drop role student;

The specified role and related permissions will be deleted from the database.

Oracle collection User Permissions

Oracle permission Summary

Oracle queries all tables with permissions of the specified user

Several misunderstandings in Oracle user and role permission management

Oracle Data Guard Role Switching

Create a user, role, authorization, and tablespace in Oracle