Oracle roles, permissions

Oracle Role Management　
First, what is the role
Role: A role is a set of permissions that assigns a role to a user who has all the permissions in the role.
Second, the system pre-defined role 　　

Predefined roles are some of the most commonly used roles that the system automatically creates after the database is installed.

The following is a brief introduction to these predetermined roles. The permissions that the role contains can be queried using the following statement:
Sql>select * from Role_sys_privs where role= ' role name ';
1. CONNECT, RESOURCE, DBA
These predefined roles are primarily for backwards compatibility. It is mainly used for database management. Oracle recommends that users design their own database management and security rights planning instead of simply using these predefined roles. In future releases, these roles may not be used as predefined roles.
2. Delete_catalog_role, Execute_catalog_role, Select_catalog_role
These roles are primarily used to access data dictionary views and packages.
3. Exp_full_database, imp_full_database These two roles are used for the data Import Export tool.
4. Aq_user_role, Aq_administrator_role aq:advanced Query. These two roles are used for Oracle advanced query functionality.
5. Snmpagent for Oracle Enterprise Manager and Intelligent Agent
6. Recovery_catalog_owner is used to create a user with a recovery library.

For information on recovering a library, refer to the Oracle documentation oracle9i user-managed Backup and Recovery Guide

7. Hs_admin_role A DBA using Oracle ' s heterogeneous services feature needs this ROLE to access appropriate tables in the data Dictionary.
Iii. Management Role
1. Build a role

sql>create role Role1;
2. Authorization to the role

Sql>grant Create any table,create procedure to role1;
3. Grant the role to the user
Sql>grant Role1 to User1;
4. View the permissions that the role contains

Sql>select * from Role_sys_privs;
5. Create a password with a role (password must be provided when a role with password is in effect)

Sql>create role Role1 identified by Password1;
6. Modify the role: password is required

Sql>alter role Role1 not identified; Sql>alter role Role1 identified by Password1;
7. Set the role for the current user to take effect

(Note: What is the concept of role effectiveness?) Assuming that user A has b1,b2,b3 three roles, then if B1 does not take effect, the permissions contained in the B1 are not owned by a, only the role is valid, the role's permissions are applied to the user, the maximum number of valid roles is set by the parameter Max_enabled_roles, and after the user logs on, Oracle assigns all permissions that are directly assigned to the user and permissions in the user's default role to the user.
Sql>set role role1;//make role1 effective
Sql>set role role,role2;//make role1,role2 effective
Sql>set role Role1 identif The IED by password1;//takes effect with the password role1
sql>set role all;//all roles with that user
Sql>set role none;//set all role invalidation
SQL >set role all except role1;//except role1 for all other roles of that user.
Sql>select * from session_roles;//View the roles in effect for the current user.
8. Modify the specified user, set their default roles
Sql>alter user user1 default role role1;
Sql>alter User User1 default role all except Role1;
See Oracle Reference Documentation
9. Remove roles
Sql>drop role role1; After the
role is deleted, the user who originally owns the role will no longer have the role, and the corresponding permissions will not be available.
Description:
1) cannot use the WITH GRANT option to grant object permissions to the role
2) You can use the WITH ADMIN option to grant system permissions to the role, and not cascade when canceling

Oracle built-in role Connect and resource permissions
First give the user the Connect role and the resource role with a command:
Grant Connect,resource to user;
User-included permissions after successful run:
Connect role:--is the typical right to grant the end user, the most basic alter session--Modify the conversation
Create CLUSTER--Build a cluster
Create DATABASE link
Create SEQUENCE-Create sequence
Create session--Set up sessions
Create synonym--create synonyms
Create view--build views
RESOURCE role:--is granted to developers
Create CLUSTER--Build a cluster
Create PROCEDURE--build process
Create SEQUENCE-Create sequence
CREATE table--Build tables
Create TRIGGER--Create a trigger
Create type--build types
Can be found from dba_sys_privs (note that this must be logged in as a DBA role):

Select Grantee,privilege from Dba_sys_privs where grantee= ' RESOURCE ' order by privilege;

GRANTEE PRIVILEGE

------------ ----------------------

RESOURCE Create CLUSTER RESOURCE Create Indextype RESOURCE Create OPERATOR RESOURCE Create PROCEDURE RESOURCE Create Seque NCE RESOURCE Create TABLE RESOURCE Create TRIGGER RESOURCE Create TYPE =================================================
first, what is the role? 　
I explained the permissions and the user in the previous space. Slowly in use you will find a problem: If you have a group of people, their required permissions are the same, when they manage their permissions is very inconvenient. Because you want to manage the permissions of each user in this group.
There is a good solution to this: the role.
A role is a set of permissions that assigns a role to a user who has all the permissions in that role. So the above problem is very good to deal with, as long as the first time to assign a role to this group of users, then as long as the role of the management is OK.
The above is a typical use of the character. In fact, just understand: a role is a set of permissions. Here are two departments to illustrate Oracle roles
second, the system pre-defined role 　　
Predefined roles are some of the most commonly used roles that the system automatically creates after the database is installed. The following is a brief introduction to these predetermined roles. The permissions that the role contains can be queried using the following statement:
Sql>select * from Role_sys_privs where role= ' role name ';
1. CONNECT, RESOURCE, DBA these pre-defined roles are primarily for backwards compatibility. It is mainly used for database management. Oracle recommends that users design their own database management and security rights planning instead of simply using these predefined roles. In future releases, these roles may not be used as predefined roles.
2. Delete_catalog_role, Execute_catalog_role, select_catalog_role these roles are primarily used to access data dictionary views and packages.
3. Exp_full_database, imp_full_database These two roles are used for the data Import Export tool. 4. Aq_user_role, Aq_administrator_role aq:advanced Query. These two roles are used for Oracle advanced query functionality. 5. Snmpagent for Oracle Enterprise Manager and Intelligent Agent 6. Recovery_catalog_owner is used to create a user with a recovery library. For information about the recovery library, refer to the Oracle documentation oracle9i user-managed Backup and Recovery Guide 7. Hs_admin_role A DBA using Oracle ' s heterogeneous services feature needs this ROLE to access appropriate tables in the data Dictionary. II. Management Role

1. Build a character sql>create role role1;
2. Authorize the role sql>grant create any table,create procedure to role1;
3. Grant the role to the user sql>grant role1 to user1;
4. View the permissions that the role contains
Sql>select * from Role_sys_privs;
5. Create a password with a role (password must be provided when a role with password is in effect)
Sql>create role Role1 identified by Password1;
6. Modify the role: password is required
Sql>alter role Role1 not identified;
Sql>alter role Role1 identified by Password1;
7. Set the role to be active for the current user (
Note: What is the concept of role effectiveness? Assuming that user A has b1,b2,b3 three roles, then if B1 does not take effect, the permissions contained in the B1 are not owned by a, only the role is valid, the role's permissions are applied to the user, the maximum number of valid roles is set by the parameter Max_enabled_roles, and after the user logs on, Oracle assigns all permissions that are directly assigned to the user and permissions in the user's default role to the user. ）
Sql>set role role1;//make role1 effective

Sql>set role role,role2;//make role1,role2 effective

Sql>set role Role1 identified by password1;//using ROLE1 with password in effect

Sql>set role all;//takes effect for all roles of the user
Sql>set role none;//set all roles invalidated
Sql>set role all except role1;//except Role1 All other roles for that user. Sql>select * from session_roles;//View the roles in effect for the current user.
8. Modify the specified user, set its default role sql>alter user user1 default roles role1; Sql>alter user user1 default role all except Role1; Oracle Reference Documentation 9. Remove Roles sql>drop role role1; After the
role is deleted, the user who originally owns the role will no longer have the role, and the corresponding permissions will not be available. ============================================================
I. Permissions classification :
System permissions: The system specifies that users have permission to use the database. (System permissions are for users).
Entity permissions: A permission user's access to another user's table or view. (For a table or view).
second, system Rights Management:
1, System permissions Category:

DBA: Has full privileges, is the highest system privilege, and only the DBA can create the database structure.
RESOURCE: A user with RESOURCE permission can only create entities and cannot create a database structure. Connect: A user with connect permission can only log on to Oracle, not create an entity, and cannot create a database structure. For normal users: Grant Connect, resource permissions. For DBA administration users: Grant Connect,resource, dba authority.
2, System Rights authorization command:
[System permissions can only be granted by DBA User: sys, system (only two users at the beginning)]
Authorization command:sql> Grant Connect, Resource, dba to User name 1 [, user name 2] ...; [An ordinary user with authorization can have the same user rights as the system, but never the same permissions as the SYS user, and the permissions of the system user can also be recycled.] ]
Example: sql> connect system/manager
sql> Create user user50 identified by USER50;
sql> Grant Connect, resource to User50;
Query where the user has permissions:
sql> select * from Dba_role_privs;
Sql> SELECT * from Dba_sys_privs;
Sql> SELECT * from Role_sys_privs;
Delete User:sql> drop username cascade;//Add cascade to delete the user along with everything they created
3, System rights pass:

The WITH ADMIN option option is added, the resulting permissions can be passed.
sql> Grant Connect, Resorce to user50 with admin option;//can pass the permissions granted. 4, System rights recovery: System permissions can only be reclaimed by the DBA User
Command:sql> Revoke Connect, resource from User50;
Description:
1) If you use the WITH ADMIN option to grant system permissions to a user, canceling the user's system permissions does not cascade the same permissions for all users who are granted the same permissions by the user.
2) System permissions are not cascaded, that is, a grant B permissions, b grants C permissions, if a to recover the permissions of B, C's permissions are not affected, system permissions can be recycled across users, that is, a can directly recover the rights of C users.
Third, entity Rights Management
1, Entity permissions classification:
Select, UPDATE, INSERT, ALTER, INDEX, DELETE, all//all include all permissions execute// Execute stored Procedure permissions
user01:sql> Grant SELECT, UPDATE, insert on product to User02;
Sql> Grant all on product to User02;
User02:sql> SELECT * from user01.product;//At this time User02 user_tables, not including user01.product this table, but if the all_tables can be found, Because he can access it.
2. Grant the operation permissions of the table to the entire user:
Sql> grant all on product to public;//public means all users, here the all permission does not include drop.
[Entity permission data dictionary]:
sql> Select owner, table_name from All_tables;//The table that the user can query
sql> SELECT table_name from use R_tables; User-created table

Sql> Select Grantor, TABLE_SCHEMA, table_name, privilege from All_tab_privs; Tables (authorized) to which access is granted
Sql> Select grantee, owner, TABLE_NAME, privilege from User_tab_privs; Grant permission to the table (granted permission)
3. DBA user can operate any base table of all users (without authorization, including deletion):

DBA User: Sql> Create table stud02.product (ID number, name VARCHAR2 (20)); sql> drop table stud02.emp; Sql> CREATE TABLE Stud02.employee as SELECT * from Scott.emp;
4. Entity permission delivery (with GRANT OPTION):

User01:sql> Grant SELECT, update on product to USER02 with GRANT option; User02 is granted permission and can be passed.
5. Entity Permission Recycling:

User01:sql>revoke Select, update on product from User02; The passed permissions are all lost.

Note 1) If you cancel the object permissions for a user, the same permissions are also canceled for users who grant permissions with GRANT option, which means that they are cascaded when the authorization is canceled.

Oracle User Management
First, create the user profile file
Sql> Create profile Student limit//student is the resource file name failed_login_attempts
3//Specify the number of login failures for locked users password_lock_time 5//Specify the number of days the user is locked Password_life_time 30//Specify the number of days the password is available
Second, create the user
Sql> Create User username identified by password Default tablespace tablespace temporary tablespace tablespace profile Profile Quota integer/unlimited on tablespace;
Example: sql> Create user acc01 identified by ACC01//If the password is a number, enclose it in double quotes default tablespace account temporary Tablespace Temp Profile default quota 50m in account;
Sql> Grant Connect, resource to ACC01; Querying user default tablespace, temp table space
Sql> Select Username, Default_tablespace, temporary_tablespace from Dba_users; Query system resource File name:
Sql> select * from Dba_profiles; The resource file resembles a table and is saved in the database once it is created. Sql> Select Username, profile, Default_tablespace, temporary_tablespace from Dba_users;
Sql> Create profile common limit failed_login_attempts 5 idle_time 5; sql> Alter user Acc01 profile Common;
Third, modify the user:
Sql> Alter user username identified password Default tablespace tablespace temporary tablespace tablespace profile Profiles Quota I Nteger/unlimited on tablespace;
1, change the password word: Sql>alter user acc01 identified by "12345";
2, modify the user default table space: sql> Alter users acc01 default tablespace users;
3, modify the user temporary table space sql> Alter user acc01 temporary tablespace temp_data;
4, forcing users to change the password Word: sql> Alter user acc01 password expire;
5. Lock the user
sql> Alter user acc01 account lock; Locking
sql> Alter user acc01 account unlock; Unlock
Iv. Deleting users
Sql>drop user username; The user has not built any entities
Sql> drop user username CASCADE; Delete the user and all the entities they have built
* *. The user who is currently connected must not be deleted.
v. Monitoring users：
1. Query user session information:
Sql> Select username, sid, serial#, machine from v$session;
2. Delete User session information:
Sql> Alter system kill session ' Sid, Serial# ';
3. Query the user SQL statement:
Sql> Select User_name, sql_text from V$open_cursor;

Oracle roles, permissions