Oracle User management and role management

Original Bo: http://liwx.iteye.com/blog/1182251

First, create the user profile file

1 SQL>Create profile student limit  //  student as resource file name 2 Failed_login_attempts  3  // Specify number of logon failures for locked users 35  // specified user is locked for 4  days // Specify the number of days the password is available

Second, create the user
Sql> Create User username
Identified by password
Default tablespace tablespace
Temporary tablespace tablespace
Profile Profiles
Quota integer/unlimited on tablespace;

Cases:

1Sql> Create UserACC012Identified byAcc01//If your password is a number, enclose it in double quotation marks.3  defaulttablespace Account4  TemporaryTablespaceTemp5Profiledefault6Quota 50m onAccount ;7Sql> GrantConnect, Resource toACC01;

[*] Query user default tablespace, temp table space
Sql> Select Username, Default_tablespace, temporary_tablespace from Dba_users;

[*] Query system resource file name:
Sql> select * from Dba_profiles;

The resource file resembles a table and is saved in the database once it is created.

1 SQL>Create profile common limit2  53  5 ; 4 5 SQL>Alteruser acc01 profile common;

Third, modify the user:
sql> Alter User Name
Identified password
Default tablespace tablespace
Temporary tablespace tablespace
Profile Profiles
Quota integer/unlimited on tablespace;

1, change the password word:
Sql>alter user acc01 identified by "12345";

2, modify the user default table space:
sql> Alter User acc01 default tablespace users;

3. Modify the user temp table space
sql> Alter user acc01 temporary tablespace temp_data;

4, forcing users to change the password word:
sql> Alter user acc01 password expire;

5. Lock the user
sql> Alter user acc01 account lock; Locking
sql> Alter user acc01 account unlock; Unlock

Iv. Deleting users
Sql>drop user username; The user has not built any entities
Sql> drop user username CASCADE; Delete the user and all the entities they have built

* *. The user who is currently connected must not be deleted.

V. monitoring users :
1. Query 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 the user SQL statement:
Sql> Select User_name, sql_text from V$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 that 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 used

The following statement queries:

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 permission plans, rather than simply

These predefined roles are used for single use. 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.

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

Description

1) cannot use with GRANT option to grant object permissions to a role

2) You can use the with ADMIN OPTION to grant system permissions to the role, not cascade when canceling

Oracle User management and role management