Oracle user, permission, role management

Welcome to the Oracle community forum and interact with 2 million technical staff. 3. Modify the user: SQLAlterUser username Identified password DefaultTablespacetablespaceTemporaryTablespacetablespaceProfileprofileQuotaintegerunlimitedontablespace; 1. Modify the password

Welcome to the Oracle community forum and interact with the 2 million technical staff> go to step 3. Modify the user: SQL Alter User Username Identified password Default Tablespace tablespace Temporary Tablespace Profile profile Quota integer/unlimited on tablespace; 1. Modify the password

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

3. modify a user:

SQL> Alter User Name

Identified Password

Default Tablespace tablespace

Temporary Tablespace tablespace

Profile profile

Quota integer/unlimited on tablespace;

1. Modify the password:

SQL> Alter user acc01 identified by "12345 ";

2. Modify the user's default tablespace:

SQL> Alter user acc01 default tablespace users;

3. Modify the user's temporary tablespace

SQL> Alter user acc01 temporary tablespace temp_data;

4. Force the user to change the password:

SQL> Alter user acc01 password expire;

5. Lock the user

SQL> Alter user acc01 account lock; // lock

SQL> Alter user acc01 account unlock; // unlock

4. delete a user

SQL> drop user Username; // The user has not created any entity

SQL> drop user 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:

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;

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:

SQL> select * from role_sys_privs where role = '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: Advanced Query. These two roles are used for advanced oracle query.

5. SNMPAGENT

For oracle enterprise manager and Intelligent Agent

6. RECOVERY_CATALOG_OWNER

Creates a user with a recovery database. For information on database restoration, see oracle document 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.

3. Manage Roles

1. Create a role

SQL> create role role1;

2. Authorize the role

SQL> grant create any table, create procedure to role1;

3. Grant a role to the user

SQL> grant role1 to user1;

4. view the permissions contained in the role

SQL> select * from role_sys_privs;

5. Create a role with a password (a password must be provided when a role with a password takes effect)

SQL> create role role1 identified by password1;

6. Modify role: Password required

SQL> alter role role1 not identified;

SQL> alter role role1 identified by password1;

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

SQL> set role role1; // enable role1

SQL> set role, role2; // make role1 and role2 take effect

SQL> set role role1 identified by password1; // use role1 with a password

SQL> set role all; // all roles used by the user take effect.

SQL> set role none; // you can specify that all roles are invalid.

SQL> set role all roles t role1; // all roles except role1 take effect.

SQL> select * from SESSION_ROLES; // view the roles that take effect for the current user.

8. Modify the specified user and set its default role.

SQL> alter user user1 default role role1;

SQL> alter user user1 default role all role t role1;

For more information, see oracle reference documentation.

9. delete a role

SQL> drop role role1;

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) The with grant option cannot be used to GRANT object permissions to the role.

2) You can use with admin option to grant the role system permissions.

[1] [2]