Oracle's user, authority, and role management

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:

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 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.

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.

More Wonderful content: http://www.bianceng.cn/database/Oracle/

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:

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

Sql> grant all in product to User02;

User02:

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:

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

[Entity Rights 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; 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:

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

User01:

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

5. Entity Rights recovery:

User01:

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

Sql> Create User username

Identified by password

Default tablespace tablespace

Temporary tablespace tablespace

Profile profile

Quota integer/unlimited on tablespace;

Cases:

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 on account;

Sql> Grant Connect, resource to ACC01;

Query user default table space, temporary tablespace

Sql> Select Username, Default_tablespace, temporary_tablespace from Dba_users;

Query system resource File name:

Sql> select * from Dba_profiles;

Resource files are similar to tables and are saved in the database once they are 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 profile

Quota integer/unlimited on tablespace;

1, change password Word:

Sql>alter user acc01 identified by "12345";

2. Modify user Default table space:

sql> Alter User acc01 default tablespace users;

3, modify user temporary table space

sql> Alter user acc01 temporary tablespace temp_data;

4. Force user to change password Word:

sql> Alter user acc01 password expire;

5, the user to add locks

sql> Alter user acc01 account lock; Lock

sql> Alter user acc01 account unlock; Unlock

Four, delete users

Sql>drop user username; User does not build any entities

Sql> drop user username CASCADE; Remove all users and their established entities

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

V. Monitoring users:

1, query the user session information: