Oracle user permissions and role management

Oracle permission settings

I. Permission classification:

System permission: The system specifies the user's permission to use the database. (System permissions are for users ).

Object permission: the access permission of a certain user to tables or views of other users. (For tables or views ).

Ii. system permission management:

1. system permission classification:

DBA: it has all the privileges and is the highest system permission. only DBA can create a database structure.

RESOURCE: users with Resource permissions can only create entities, but cannot create database structures.

CONNECT: users with the Connect permission can only log on to Oracle, create entities, and create database structures.

For normal users: grant connect and resource permissions.

For DBA management users: grant the connect, resource, and dba permissions.

2. system permission authorization command:

[System permissions can only be granted by DBA users: sys and system (only these two users are allowed at the beginning)]

Authorization command: SQL> grant connect, resource, dba to username 1 [, username 2]...;

[Normal users can have the same user permissions as system through authorization, but they can never have the same permissions as sys users. system users can also be revoked.]

Example:
Copy codeThe Code is as follows:
SQL> connect system/manager

SQL> Create user user50 identified by user50;

SQL> grant connect, resource to user50;

Query the permissions of a user:
Copy codeThe Code is as follows:
SQL> select * from user_sys_PRivs; view all permissions of the current user

SQL> select * from user_tab_privs; view the table permissions of the users used

Delete A user: SQL> drop user Username cascade; // Add cascade to delete all the users and their created items.

3. pass system permissions:

If the with admin option is added, the obtained permissions can be passed.
Copy codeThe Code is as follows:
SQL> grant connect, resorce to user50 with admin option; // You can pass the obtained permissions.

4. Revoke system permissions: only DBA users can revoke system permissions.

Command: SQL> Revoke connect, resource from user50;

Note:

1) if you use the with admin option to grant system permissions to a user, for all users granted the same permissions by this user, canceling the user's system permissions does not cascade the same permissions of these users.

2) The system permissions are continuously connected, that is, A grants B permissions, and B grants C permissions. If A revokes B permissions, C permissions are not affected. system permissions can be revoked across users, that is, A can directly revoke the permissions of user C.

Iii. Object permission management

1. Object permission classification: select, update, insert, alter, index, delete, all // all includes all Permissions

Execute // execute the Stored Procedure permission

User01:
Copy codeThe Code is as follows:
SQL> grant select, update, insert on product to user02;

SQL> grant all on product to user02;

User02:
Copy codeThe Code is as follows:
SQL> select * from user01.product;

// At this time, user02 queries user_tables, excluding the table user01.product. However, if all_tables is checked, it can be found because it can be accessed.

 

2. Grant table operation permissions to all users:
Copy codeThe Code is as follows:
SQL> grant all on product to public; public indicates all users. The all permission here does not include drop.

[Object permission data dictionary]:
Copy codeThe Code is as follows:
SQL> select owner, table_name from all_tables; // tables that can be queried by the user

SQL> select table_name from user_tables; // table created by the user

SQL> select grantor, table_schema, table_name, privilege from all_tab_privs; // authorized table (authorized)

SQL> select grantee, owner, table_name, privilege from user_tab_privs; // The table that grants permissions (granted permissions)

3. DBA users can operate any base table of all users (no authorization required, including deletion ):

DBA User:
Copy codeThe Code is as follows:
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. Object permission transfer (with grant option ):

User01:
Copy codeThe Code is as follows:
SQL> grant select, update on product to user02 with grant option; // get the permission and pass it.

5. Revoke object permissions:

User01:
Copy codeThe Code is as follows:
SQL> Revoke select, update on product from user02; // all permissions passed will be lost.

Description

1) if you cancel the object permissions of a user, the same permissions of the users who use the with grant option to GRANT permissions are also revoked, that is to say, it is Cascade when authorization is canceled.

Oracle user management

1. Create a user's Profile file

SQL> create profile student limit // student is the resource file name

FAILED_LOGIN_ATTEMPTS 3 // specify the number of Logon failures for the user to be locked

PASSWORD_LOCK_TIME 5 // specify the number of days the user is locked

PASSWORD_LIFE_TIME 30 // specifies the password availability days

2. Create a user
Copy codeThe Code is as follows:
SQL> Create User username

Identified by password

Default Tablespace tablespace

Temporary Tablespace tablespace

Profile profile

Quota integer/unlimited on tablespace;

 

Example:
Copy codeThe Code is as follows:
SQL> Create user acc01
Identified by acc01 // if the password is a number, enclose it in double quotation marks.
Defaulttablespaceaccount
Temporarytablespacetemp
Profiledefault
Quota50monaccount;
SQL> grantconnect, resourcetoacc01;

[*] Query the user's default tablespace and temporary tablespace
Copy codeThe Code is as follows:
SQL> selectusername, default_tablespace, temporary_tablespacefromdba_users;

[*] Query system resource file name:
Copy codeThe Code is as follows:
SQL> select * fromdba_profiles;

Resource files are similar to tables, which are saved in the database once created.
Copy codeThe Code is as follows:
SQL> selectusername, profile, default_tablespace, temporary_tablespacefromdba_users;
SQL> createprofilecommonlimit
Failed_login_attempts5
Idle_time5;
SQL> Alteruseracc01profilecommon;

3. modify a user:
Copy codeThe Code is as follows:
SQL> AlterUser User Name

Identified Password

DefaultTablespacetablespace

TemporaryTablespacetablespace

Profileprofile

Quotainteger/unlimitedontablespace;

1. Modify the password:
Copy codeThe Code is as follows:
SQL & gt; Alteruseracc01identifiedby "12345 ";

2. Modify the user's default tablespace:
Copy codeThe Code is as follows:
SQL> Alteruseracc01defaulttablespaceusers;

3. Modify the user's temporary tablespace
Copy codeThe Code is as follows:
SQL> Alteruseracc01temporarytablespacetemp_data;

4. Force the user to change the password:
Copy codeThe Code is as follows:
SQL> Alteruseracc01passwordexpire;

5. Lock the user
Copy codeThe Code is as follows:
SQL> Alteruseracc01accountlock; // lock
SQL> Alteruseracc01accountunlock; // unlock

4. delete a user
Copy codeThe Code is as follows:
SQL> dropuser username; // The user has not created any entity
SQL> dropuser username CASCADE; // delete all users and their created entities

* 1. The user currently being connected cannot be deleted.

5. Monitor users:

1. query user session information:
Copy codeThe Code is as follows:
SQL> selectusername, sid, serial #, machinefromv $ session;

2. delete user session information:
Copy codeThe Code is as follows:
SQL> Altersystemkillsession 'sid, serial #';

3. query user SQL statements:
Copy codeThe Code is as follows:
SQL> selectuser_name, SQL _textfromv $ open_cursor;

Oracle role management

I. Roles

Role. A role is a set of permissions. If a role is assigned to a user, the user has all permissions in the role.

Ii. system pre-defined roles

Predefined roles are common roles automatically created by the system after the database is installed. The following describes the predefined roles. You can use the following statement to query the permissions contained in a role:
Copy codeThe Code is as follows:
SQL> select * fromsys_role_privswhererole = 'Role name ';

1. CONNECT, RESOURCE, DBA

These predefined roles are primarily intended for backward compatibility. It is mainly used for database management. Oracle recommends that you design your own database management and security permission planning, instead of simply using these pre-roles. In future versions, these roles may not be pre-defined.

2. DELETE_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, SELECT_CATALOG_ROLE

These roles are mainly used to access data dictionary views and packages.

3. EXP_FULL_DATABASE, IMP_FULL_DATABASE

These two roles are used for data import and export tools.

4. AQ_USER_ROLE, AQ_ADMINISTRATOR_ROLE

AQ: AdvancedQuery. These two roles are used for advanced oracle query.

5. SNMPAGENT

Used for oracleenterprisemanager and IntelligentAgent

6. RECOVERY_CATALOG_OWNER

Creates a user with a recovery database. For information on restoring the database, see the oracle document Oracle9iUser-ManagedBackupandRecoveryGuide

7. HS_ADMIN_ROLE

ADBAusingOracle 'sheterogeneousservicesfeatureneedsthisroletoaccessappropriatetablesinthedatadictionary.

3. Manage Roles

1. Create a role
Copy codeThe Code is as follows:
SQL> createrolerole1;

2. Authorize the role
Copy codeThe Code is as follows:
SQL> grantcreateanytable, createproceduretorole1;

3. Grant a role to the user
Copy codeThe Code is as follows:
SQL> grantrole1touser1;

4. view the permissions contained in the role
Copy codeThe Code is as follows:
SQL> select * fromrole_sys_privs;

5. Create a role with a password (a password must be provided when a role with a password takes effect)
Copy codeThe Code is as follows:
SQL> createrolerole1identifiedbypassword1;

6. Modify role: Password required
Copy codeThe Code is as follows:
SQL> alterrolerole1notidentified;

SQL> alterrolerole1identifiedbypassword1;

7. Set the role to take effect for the current user

(Note: What is the concept of role effectiveness? Assume that user a has three roles: b1, b2, and b3. If b1 does not take effect, the permissions contained in b1 are not owned by user a, and only the role takes effect, the permissions in the role apply to the user. The maximum number of valid roles is set by the MAX_ENABLED_ROLES parameter. After the user logs on, oracle grants all permissions directly granted to the user and permissions granted to the user in the user's default role .)
Copy codeThe Code is as follows:
SQL> setrolerole1; // enable role1

SQL> setrolerole, role2; // make role1 and role2 take effect

SQL> setrolerole1identifiedbypassword1; // use role1 with a password

SQL> setroleall; // all roles used by this user take effect

SQL> setrolenone; // set that all roles are invalid.

SQL> setroleallexceptrole1; // all roles except role1 take effect.

SQL> select * fromSESSION_ROLES; // you can view the roles that take effect for the current user.

8. Modify the specified user and set its default role.
Copy codeThe Code is as follows:
SQL> alteruseruser1defaultrolerole1;
SQL> alteruseruser1defaultroleallexceptrole1;

9. delete a role
Copy codeThe Code is as follows:
SQL> droprolerole1;

After a role is deleted, the user who previously used this role no longer has this role, and the corresponding permissions are lost.

Note:

1) You cannot use WITHGRANTOPTION to grant object permissions to a role.

2) You can use WITHADMINOPTION to grant the role system permissions.