Oracle predefined roles and custom roles

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.

1. The connect role has the following permissions:
2. Alter session
3. Create cluster
4. Create database link
5. Create session
6. Create table
7. Create view
8. Create sequence

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

1. Create cluster
2. Create indextype
3. Create table
4. Create type
5. Create proecdure
6. 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

1. Grant connect to luob [with admin option]// Whether the connection permission is inherited
2. Grant resource, dba to luob// Grant resources and dba roles

4. revoke a role

Revoke connect, resource, dba from luob// Reclaim the dba role of the connection Resource

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)

1. Create role name not identified;// Verification not required
2. Alter role myrole identified by m123;// Change the role defined above to a role that requires database Verification
3. Create role name identified by r111;// Define the role myrole to be verified

6.Grant system permissions to custom roles

1. Grant create session to role name [with admin option]// Grant the connection permission to the role

7.Grant object permissions to custom roles

1. Grant select on scott. emp from role name -- all are system user logon
2. Grant update on scott. emp from role name -- no solution name if scott is used
3. Grant delete on scott. emp from role name
4. Grant all on emp from role

8. Grant user-defined roles

1. Grant role name to user name [with admin option]

9. delete a user's role

1. Revoke role name from user name

10. delete a custom role

1. Drop role name// If the role is deleted, No permissions are granted to the role owner.

11. ViewRoles owned by the current user

1. Select * from user_role_privs