Oracle users, permissions, role management

Oracle Database users manage Oracle permissions 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
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; 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, that is, a can recover 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.

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

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

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

6. Entity Rights recovery:
User01:
Sql>revoke Select, update on the product from User02; All the permissions passed will be lost.

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; Deletes the user and all of his or her established entities *1. Users currently connected are not allowed to delete.

V. Monitoring users:

1, query the 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 user SQL statements:
Sql> Select User_name, sql_text from V$open_cursor; Sql> ALTER Session SET
Nls_language= ' Simplified Chinese '
Nls_territory= ' in '
Nls_currency= ' RMB '
Nls_iso_currency= ' in '
Nls_numeric_characters= '., '
Nls_calendar= ' Gregorian '
nls_date_format= ' Yyyy-mm-dd dy '
Nls_date_language= ' Simplified Chinese '
Nls_sort= ' BINARY '
Time_zone= ' +08:00 '
nls_dual_currency = ' RMB '
Nls_time_format = ' HH.MI. Ssxff AM '
Nls_timestamp_format = ' Dd-mon-rr hh.mi. Ssxff AM '
Nls_time_tz_format = ' HH.MI. Ssxff AM Tzh:tzm '
Nls_timestamp_tz_format = ' Dd-mon-rr hh.mi. Ssxff AM Tzh:tzm '

First, Oracle Rights Management
Sql> Grant Connect, resource, dba to ACC01; Sql> revoke Connect, resource from ACC01; Second, Oracle role management sql> Create roles <role_name>
identified by Password/not identified;
sql> Alter Role <role_name> ... Sql> Grant <privs> to <role_name>; Sql> Grant <role_name> to <user_name> sql> Set role <role_name>
All Except <role_name2> /None