One, Oracle built-in role Connect and resource permissions
Grant Connect,resource to user;
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;
=================================================
First, what is the role?
The permissions and the user are described 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.
The following is a two-part description of the Oracle 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.
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 that B1 contains are not owned by a.
Only the role in effect, the role of the permissions within the user, the maximum number of effective role is set by the parameter max_enabled_roles;
After the user logs on, Oracle assigns all permissions that are directly assigned to the user and the 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.] ]
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 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 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.
