Oracle predefined roles and custom roles

Oracle pre-roles and custom roles, query all oracle roles, and introduce common pre-roles such as connect, resource, dba, and dba.

Oracle pre-roles and custom roles, query all oracle roles, and introduce common pre-roles such as connect, resource, dba, and dba.

Oracle predefined roles and custom roles

[Date: 2012-08-06] Source: Linux community Author: takeme [Font:]

1. query all Oracle roles

Select * from dba_roles;

2. Describes frequently used pre-defined roles, such as connect, resource, and dba.

1. connect has most of the permissions of General developers. In most cases, it is enough to assign the connect and resource roles to a user.

The connect role has the following permissions:

Alter session

Create cluster

Create database link

Create session

Create table

Create view

Create sequence

2. The resources role uses other permissions selected by developers, such as creating stored procedures, triggers, and resources. The resource role implies the unlimited tablespace system permission and unlimited tablespace permissions.

Create cluster

Create indextype

Create table

Create type

Create proecdure

Create trigger

3. the dba role has all system permissions and the with admin option. The default dba users are sys and system. They can grant any system permissions to other users, however, the dba role does not have the privileges of sysdba and sysoper (starting and disabling databases)

3. Example: grant the system role of connect resource dba

4. revoke a role

5. Custom roles-- Generally, it is created by dba, and other users need the system permission to create a role (you can specify the authentication method when creating a role (no verification, database verification, etc)

6.Grant system permissions to custom roles

7.Grant object permissions to custom roles

8. Grant user-defined roles

Grant role name to user name [with admin option]

9. delete a user's role

Revoke role name from user name

10. delete a custom role

11. ViewRoles owned by the current user

Select * from user_role_privs