Oracle roles, permissions, and users[Repost 2010-1-25 10:29:45]size: big medium small
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 sessions
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 SEQUENCE
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 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
Used to create a user who owns the 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.
II. 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 identified by password1;//using ROLE1 with password in effect
Sql>set role all;//takes effect with all roles of the user
Sql>set role none;//set all roles Invalid
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 role
Sql>alter user User1 default role role1;
Sql>alter user User1 default role all except Role1;
See Oracle Reference documentation
9. Deleting a role
Sql>drop role Role1;
After the role is deleted, the user who owns the role will no longer have the role, and the corresponding permissions will not be available.
============================================================
First, the Classification of rights:
System permissions: The system specifies the user's permission to use the database. (System permissions are for users).
Entity permissions: A permission user's access to other users ' tables or views. (For a table or view).
Second, System Rights Management:
1, System Permissions classification:
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 Authority authorization command:
[System permissions can only be granted by the DBA User: sys, system (only two users at the beginning)]
Authorization Command:sql> Grant Connect, resource, DBA to username 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.] ]
Cases:
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 the user username cascade; Plus cascade the user with everything they created.
3. System Permission Pass:
Add the WITH ADMIN option option, then the resulting permissions can be passed.
sql> Grant Connect, Resorce to user50 with admin option; You can pass the permissions you have received.
4, System rights recovery: System permissions can only be collected by DBA users
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) There is no cascade of system permissions, that is, a grants B permission, b grants C permissions, if a withdraws the permissions of B, the permissions of C are not affected, and the system permissions can be reclaimed across users, that is, a can directly reclaim the rights of C users.
Third, Entity Rights Management
1. Entity Permissions Classification: Select, UPDATE, INSERT, ALTER, INDEX, delete, all//all including 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 check user_tables, not including user01.product this table, but if check all_tables can be found, because he can access.
2. Grant the operation permissions of the table to all users:
Sql> grant all on product to public; Public means all users, and the all permission here does not include drop.
[Entity permission data Dictionary]:
Sql> Select owner, table_name from All_tables; Tables that users can query
Sql> Select table_name from User_tables; User-Created Tables
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 (10),
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.
Description
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 as resource file name
Failed_login_attempts 3//Specifies the number of logon 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 Profiles
Quota integer/unlimited on tablespace;
Cases:
sql> Create User Acc01
identified by ACC01//If your password is a number, enclose it in double quotation marks.
Default Tablespace Account
Temporary Tablespace Temp
Profile default
Quota 50m on 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 Name
Identified password
Default tablespace tablespace
Temporary tablespace tablespace
Profile Profiles
Quota integer/unlimited on tablespace;
1, change the password word:
Sql>alter user acc01 identified by "12345";
2, modify the user default table space:
sql> Alter User acc01 default tablespace users;
3. Modify the user temp 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 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 that 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
Used to create a user who owns the 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 identified by password1;//using ROLE1 with password in effect
Sql>set role all;//takes effect with all roles of the user
Sql>set role none;//set all roles Invalid
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 role
Sql>alter user User1 default role role1;
Sql>alter user User1 default role all except Role1;
See Oracle Reference documentation
9. Deleting a role
Sql>drop role Role1;
After the role is deleted, the user who owns the role will no longer have the role, and the corresponding permissions will not be available.
Description
1) cannot use with GRANT option to grant object permissions to a role
2) You can use the with ADMIN OPTION to grant system permissions to the role, not cascade when canceling
