Oracle user Rights, role management detailed _oracle

Oracle permission settings

I. Classification of authority:

System permissions: The system requires users to use the database permissions. (System permissions are for users).

Entity permissions: The access rights of a user to a table or view of another user. (For a table or view).

Second, the System Authority management:

1. Classification of System privileges:

DBA: With full privileges, the system has the highest privileges, and only DBAs can create the database structure.

RESOURCE: Users with RESOURCE permissions can create entities only and cannot create database structures.

Connect: Users who have connect permissions can only log on to Oracle and cannot create entities and cannot create database structures.

For ordinary users: Grant Connect, resource permissions.

For DBA administration User: Grant Connect,resource, dba authority.

2, the System Authority authorization order:

[System permissions can only be granted by DBA User: sys, system (only two users in the beginning)]

Authorization Command:sql> Grant Connect, resource, dba to User name 1 [, username 2] ...;

[Normal users can have the same user rights as the system through authorization, but they will never reach the same permissions as the SYS user, and the system user's permissions can be reclaimed. ]

Cases:

Copy Code code as follows:

Sql> Connect System/manager

sql> Create User User50 identified by USER50;

Sql> Grant Connect, resource to User50;

Query where the user has permissions:

Copy Code code as follows:

Sql> select * from User_sys_privs; View all permissions for the current user

Sql> select * from User_tab_privs; View permissions for a table used by the user

Delete user:sql> drop user username cascade; Plus cascade deletes the user, along with what it creates.

3, System Authority Transfer:

The WITH ADMIN option option is added, the resulting permission can be passed.

Copy Code code as follows:

sql> Grant Connect, Resorce to user50 with admin option; Can pass the granted permission.

4, System rights Recycling: System permissions can only be reclaimed by 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, the cancellation of the user's system permissions for all users who have been granted the same permission by this user does not cascade the same permissions for those users.

2 system permissions are not cascaded, that is, a grant B permissions, b grant C permissions, if a to recover the rights of B, C's permissions are not affected; System permissions can be reclaimed across users, i.e. a can reclaim the rights of C users directly.

Third, Entity Rights Management

1. Entity Permissions Categories: Select, UPDATE, INSERT, ALTER, INDEX, delete, all//all including all permissions

Execute//execute Stored procedure permissions

User01:

Copy Code code as follows:

Sql> Grant SELECT, UPDATE, insert on product to User02;

Sql> grant all in product to User02;

User02:

Copy Code code as follows:

Sql> select * from User01.product;

At this time user02 check user_tables, does not include user01.product this table, but if check all_tables can be found, because he can visit.

2. Grant the operation rights of the table to all users:

Copy Code code as follows:

Sql> grant all in product to public; Public represents all users, and the all permission here does not include drop.

[Entity Rights data Dictionary]:

Copy Code code as follows:

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; A table (authorized) that is entitled to access.

Sql> Select grantee, owner, TABLE_NAME, privilege from User_tab_privs; Table granting permissions (permissions granted)

3. DBA users can operate any base table (without authorization, including deletion) for all users:

DBA User:

Copy Code code as follows:

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 transfer (with GRANT OPTION):

User01:

Copy Code code as follows:

Sql> Grant SELECT, update on product to USER02 with GRANT option; User02 get permission and can be passed.

5. Entity Rights recovery:

User01:

Copy Code code as follows:

Sql>revoke Select, update on the product from User02; All the permissions passed will be lost.

Description

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

Oracle User Management

First, create the user profile file

Sql> Create profile Student limit//Student for 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 a user is locked

Password_life_time 30//Specify Password available days

Second, create users

Copy Code code as follows:

Sql> Create User username

Identified by password

Default tablespace tablespace

Temporary tablespace tablespace

Profile profile

Quota integer/unlimited on tablespace;

Cases:

Copy Code code as follows:

sql> Create User Acc01
identified by ACC01//If the password is a number, enclose it in double quotes
Defaulttablespaceaccount
Temporarytablespacetemp
Profiledefault
Quota50monaccount;
sql>grantconnect,resourcetoacc01;

[*] Query user default table space, temporary tablespace

Copy Code code as follows:

sql>selectusername,default_tablespace,temporary_tablespacefromdba_users;

[*] Query system resource file name:

Copy Code code as follows:

sql>select*fromdba_profiles;

Resource files are similar to tables and are saved in the database once they are created.

Copy Code code as follows:

sql>selectusername,profile,default_tablespace,temporary_tablespacefromdba_users;
Sql>createprofilecommonlimit
Failed_login_attempts5
Idle_time5;
sql>alteruseracc01profilecommon;

Third, modify the user:

Copy Code code as follows:

Sql>alteruser User Name

Identified password

Defaulttablespacetablespace

Temporarytablespacetablespace

Profileprofile

Quotainteger/unlimitedontablespace;

1, change password Word:

Copy Code code as follows:

Sql>alteruseracc01identifiedby "12345";

2. Modify user Default table space:

Copy Code code as follows:

sql>alteruseracc01defaulttablespaceusers;

3, modify user temporary table space

Copy Code code as follows:

sql>alteruseracc01temporarytablespacetemp_data;

4. Force user to change password Word:

Copy Code code as follows:

sql>alteruseracc01passwordexpire;

5, the user to add locks

Copy Code code as follows:

sql>alteruseracc01accountlock;//Plus Lock
sql>alteruseracc01accountunlock;//unlock

Four, delete users

Copy Code code as follows:

Sql>dropuser user name;//user not building any entities
Sql>dropuser User name cascade;//Delete all users and their established entities

*1. Users currently connected are not allowed to delete.

V. Monitoring users:

1, query the user session information:

Copy Code code as follows:

sql>selectusername,sid,serial#,machinefromv$session;

2. Delete User session information:

Copy Code code as follows:

Sql>altersystemkillsession ' sid,serial# ';

3, query user SQL statements:

Copy Code code as follows:

sql>selectuser_name,sql_textfromv$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 the role.

Second, the system predefined role

A predefined role is a common role that the system automatically creates after the database is installed. The following is a brief introduction to these scheduled roles. The permissions that the role contains can be queried with the following statement:

Copy Code code as follows:

sql>select*fromsys_role_privswhererole= ' role name ';

1. Connect,resource,dba

These predefined roles are primarily for backward compatibility. It is mainly used for database management. Oracle recommends that users design their own database management and secure permission plans instead of simply using these predefined roles. These roles may not be predefined roles in future releases.

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

The two roles are used for the data Import Export tool.

4. Aq_user_role,aq_administrator_role

Aq:advancedquery. These two roles are used for Oracle advanced query functionality.

5. Snmpagent

For Oracleenterprisemanager and Intelligentagent

6. Recovery_catalog_owner

Used to create a user with a recovery library. For information about the recovery library, refer to the Oracle documentation "Oracle9iuser-managedbackupandrecoveryguide"

7. Hs_admin_role

Adbausingoracle ' Sheterogeneousservicesfeatureneedsthisroletoaccessappropriatetablesinthedatadictionary.

III. Management Role

1. Build a role

Copy Code code as follows:

sql>createrolerole1;

2. Delegating to Roles

Copy Code code as follows:

sql>grantcreateanytable,createproceduretorole1;

3. Grant the role to the user

Copy Code code as follows:

sql>grantrole1touser1;

4. View the permissions that the role contains

Copy Code code as follows:

sql>select*fromrole_sys_privs;

5. Create a password with a role (must provide a password when a role with a password is in effect)

Copy Code code as follows:

sql>createrolerole1identifiedbypassword1;

6. Modify the role: Do you need a password

Copy Code code as follows:

sql>alterrolerole1notidentified;

sql>alterrolerole1identifiedbypassword1;

7. Set the role that the current user will be in effect

(Note: What is the concept of the role's entry into force?) Assuming that user A has b1,b2,b3 three roles, then if B1 is not in effect, then the rights contained in B1 are not owned by a, only the role is in effect, the role of the roles in the user, the maximum number of effective roles by the parameter max_enabled_roles set; After the user logs on, Oracle assigns all permissions that are assigned directly to the user and permissions in the user's default role to the user. ）

Copy Code code as follows:

sql>setrolerole1;//make Role1 effective

sql>setrolerole,role2;//make Role1,role2 effective

sql>setrolerole1identifiedbypassword1;//use Role1 with password to take effect

sql>setroleall;//use all roles of the user in effect

sql>setrolenone;//Set all role failures

sql>setroleallexceptrole1;//all other roles of the user except Role1 are in effect.

sql>select*fromsession_roles;//view the active role of the current user.

8. Modify the specified user to set its default role

Copy Code code as follows:

sql>alteruseruser1defaultrolerole1;
sql>alteruseruser1defaultroleallexceptrole1;

9. Delete Roles

Copy Code code as follows:

sql>droprolerole1;

After the role is removed, the user who originally owns the role no longer has the role, and the corresponding permissions are gone.

Description

1 cannot use Withgrantoption to grant object permissions to the role

2 You can use Withadminoption to grant system privileges to the role, not cascade when canceled