Oracle user permissions, profile and role management

In Oracle databases, users and roles are closely related to database permissions. Correct understanding of user and role management is very important for database installation.

In Oracle databases, users and roles are closely related to database permissions. Correct understanding of user and role management is very important for database installation.

In Oracle databases, users and roles are closely related to database permissions. Correct understanding of user and role management is very important for database installation, this article mainly summarizes the user and role management operations that are frequently used in the work process:

I. Basic user management operations:
1. Create a user and specify both the password and the default tablespace.
Create user test identified by test default tablespace users;
2. System permissions that allow users to log on
Grant connect to test;
3. Revoke User Permissions
Revoke connect from test;
4. delete a user
Drop user test cascade;
5. Change the User Password
Alter user test identified by newpasswd;
6. Modify the user's default tablespace (Note that if there is no quota on the tablespace, You need to assign a quota to the user)
Alter user test default tablespace t1;
SQL> connect test/test@1.1.1.12/orcl
Connected.
SQL> create table tb1 (id int );
Create table tb1 (id int)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 't1'
Allocate space to the user on table t1:
Alter user test quota 10 m on t1;
SQL> connect test/test@1.1.1.12/orcl
Connected.
SQL> create table tb1 (id int );
Table created.

2. Use the profile file to Implement User Password restrictions and resource restrictions;
Proflie is a set of names for password restrictions and resource restrictions. when an oracle database is created, oracle automatically creates a PROFILE named DEFAULT. The initialized DEFAULT has no password or resource restrictions. use PROFILE has the following main items.
A. if you set only some passwords or resource restriction options when creating a PROFILE, other options will automatically use the DEFAULT value (corresponding options of DEFAULT)
B. If the PROFILE option is not specified when a user is created, oracle automatically assigns the DEFAULT option to the corresponding database user.
C. One user can only allocate one PROFILE. To manage the user's passwords and resources at the same time, you must specify both the password and resource options when creating the PROFILE.
D. When you use PROFILE to manage passwords, the password management option is always in the active state. However, if you use PROFILE to manage resources, you must activate resource restrictions.
1. view the existing profile file
Select distinct profile from dba_profiles;
View the resource limits in the specified profile:
Select resource_name, limit from dba_profiles where profile = 'default ';
2. Modify the resource options in the current profile file:
Alter profile default limit FAILED_LOGIN_ATTEMPTS 1 PASSWORD_LOCK_TIME 3;
3. Create a new profile file:
Create profile lock_accout LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 10;
4. Let the user use the new profile file:
Alter user test profile lock_accout;
5. view the profile file currently used by the user
Select username, profile from dba_users;
6. Use the profile file to restrict the use of resources;
Resource restrictions must be activated first:
Alter system set resource_limit = TRUE scope = memory;
Modify resource restrictions:
Alter profile lock_accout limit cpu_per_session 5000;
7. Delete profile
Drop profile lock-accout;
8. Delete the profile and change the user profile that uses the current profile to default.
Drop profile lock_accout cascade;
9. The following lists all profile-related parameters for reference:
FAILED_LOGIN_ATTEMPTS: used to specify the maximum number of failed attempts to contact log-on.
PASSWORD_LOCK_TIME: used to specify the number of days the account is locked.
PASSWORD_LIFE_TIME: used to specify the password Validity Period
PASSWORD_GRACE_TIME: used to specify the password grace period.
PASSWORD_REUSE_TIME: used to specify the password reusable time.
PASSWORD_REUSE_MAX; used to specify the number of times the password needs to be changed before the password is reused.
PASSWORD_VERIFY_FUNCTION; Whether to verify the password (verify to change the value to VERIFY_FUNCTION)
CPU_PER_SESSION: used to specify the maximum CPU time that each session can occupy.
LOGICAL_READS_PER_SESSON: used to specify the maximum number of logical reads of a session.
PRIVATE_SGA: used to specify the maximum total private space that a session can allocate in the Shared Pool. Note that this option only applies to the Shared Server mode.
COMPOSITE_LIMIT: used to specify the total resource consumption of a session (unit: Service Unit ).
CPU_PER_CALL: limits the maximum CPU time (in 1% seconds) that can be used for each call (resolution, execution, or data extraction)
LOGICAL_READS_PER_CALL: used to limit the maximum number of logical I/O times of each call.
SESSIONS_PER_USER: used to specify the maximum number of concurrent sessions for each user.
CONNECT_TIME: used to specify the maximum connection time of a session.
IDLE_TIME: used to specify the maximum idle time of a session.