14th oracle users

1. User concepts
The user, that is, the user, is generally the "person" who accesses the Oracle database ". In Oracle, users can control various security parameters to maintain database security. These concepts include schema), permissions, roles, storage settings, space limits, access resource restrictions, database audit, and so on. Each user has a password, and the correct user/password can be used to log on to the database for data access.

Ii. User's default tablespace
The tablespace is the maximum logical unit for information storage. When you connect to the database for data storage, if you do not specify the destination tablespace for data storage, the data is stored in the user's default tablespace. For example, create table mytable (ID varchar2 (20), name varchar2 (100); this statement creates a table mytable and stores it in the default tablespace of the current user, to specify a tablespace, create table mytable (ID varchar2 (20), name varchar2 (100) tablespace tbs1 ;. You can specify the default tablespace when creating a user, or use the aler user command to specify the tablespace. For more information about the syntax, see the following section.

Iii. User temporary tablespace
Temporary tablespace is mainly used for sorting order by statements and other intermediate operations. Before Oracle9i, you can specify that users use different temporary tablespaces. From 9i, temporary tablespaces are generic and all users use temp as temporary tablespaces.

Iv. user resource files
User resource files are used to restrict the access to user resources, including: CPU usage time limit, memory logical read limit, session data limit that each user can connect to at the same time, the space and time limit of one session, the duration limit of one session, dedicated for each session SGA space limit.

V. User tablespace quota
The tablespace storage limit is the total number of buckets that a user can use in a tablespace. You can specify the quota parameter when creating or modifying a user. If you exceed this limit when storing data in a tablespace, an error occurs. Error message: 'ora-01536: space quota exceeded for tablespace tablespacename ..'. You can query the dba_ts_quotas dictionary to view the tablespace quota information.

6. create user resource files
The syntax for creating a user resource file is as follows:
Create profile filename limit
Session_per_user integer
Cpu_per_session integer
User_per_call integer
Connect_time integer
......
Base:
Session_per_user: the maximum number of sessions that a user can connect to simultaneously;
Cpu_per_session: The total CPU time that a user can use during a database meeting. The unit is 1% seconds;
User_per_call: The total CPU time available for one SQL call. The unit is 1% seconds;
Logical_reads_per_session: the maximum number of database blocks that can be read during a database session;
Logical_reads_per_call: the maximum number of database blocks that can be read by one SQL call;
Idle_time: the idle time limit after the user connects to the database. The unit is minutes. If the idle time exceeds this value, the connection is disconnected;
Connect_time: Total time limit for one connection. The unit is minutes. When the connection time exceeds this value, the connection is disconnected;
Private_sga: the size of your SGA zone, in the unit of database blocks. The default value is unlimited;
Composite_limit: This is a combined resource item consisting of the preceding limit parameters. For example, assume that the resource settings are as follows:
Idle_time 20
Connect_time 120
CPU _ per_call 750
Com posite_limt 800
When the session space exceeds 20 minutes, or the connection time exceeds 120 minutes, or an SQL statement takes more than 7.5 seconds, or the total number of these resource limits exceeds 800, the system automatically terminates the session.
Failed_login_attempts: the maximum number of Logon failures caused by failed user name/password verification. If this limit is exceeded, the account is locked;
Password_life_time: Password validity period, in days. If logon is denied, you must reset the password. The default value is unlimited;
Password_reuse_time: the number of days after which a failed password can be reused. The default value is unlimited;
Password_reuse_max: number of times a password can be reused;
Password_lock_time: When the logon Failure reaches failed_login_attemps, the account is locked. this parameter is used to set the number of days locked;
The following is an example:
1) create a user resource file
Create profile tax_users limit
Session_per_user 3
Cpu_per_session Unlimited
Connect_time 30
Logical_reads_per_session default
Logical_reads_per_call 1000
Private_sga 15 K
Com posite_limit 500000
Password_life_time 90
2) query user resource file information
SQL> select * From dba_profile where profile = 'tax _ users'
3) Specify the user resource file to the user
SQL> alter user US1 profile tax_users

7. create, modify, and delete users
1. Create a user
For detailed Syntax of creating a user, please query the official Oracle parameter documentation. Here we will introduce the typical syntax. Syntax:
Create User Username
Identified by password
Default tablespace
Temporary tablespace
Profile
Quota integer | unlimited on tablespace
The meanings of the options are as follows:
Identified by password: User Password;
Default tablespace: default tablespace;
Temporary tablespace: Temporary tablespace;
Profile profile | default: user resource file;
Quota integer [k | M] | unlimited on tablespace: the user's space usage quota on the tablespace. You can specify the quota of multiple tablespaces.
Example:
Create user US1 identitied by ABC123
Default tablespace user01
Temporary tablespace temp
Profile default
Quota 1000 m on user01;

2. modify a user
The syntax for modifying a user is similar to the syntax for creating a user. The main reason is that "create user" is changed to "alter user". For more information, see the Oracle document.

3. delete a user
Deleting a user deletes the schema object created by the user and the user from the database. As follows:
SQL> drop user US1;
If US1 contains a schema object, the execution fails without the preceding statement. You must add the keyword "cascade" to delete it, which means that the object is deleted together, as shown below:
SQL> drop user US1 cascade;

 

1. User concepts
The user, that is, the user, is generally the "person" who accesses the Oracle database ". In Oracle, users can control various security parameters to maintain database security. These concepts include schema), permissions, roles, storage settings, space limits, access resource restrictions, database audit, and so on. Each user has a password, and the correct user/password can be used to log on to the database for data access.

Ii. User's default tablespace
The tablespace is the maximum logical unit for information storage. When you connect to the database for data storage, if you do not specify the destination tablespace for data storage, the data is stored in the user's default tablespace. For example, create table mytable (ID varchar2 (20), name varchar2 (100); this statement creates a table mytable and stores it in the default tablespace of the current user, to specify a tablespace, create table mytable (ID varchar2 (20), name varchar2 (100) tablespace tbs1 ;. You can specify the default tablespace when creating a user, or use the aler user command to specify the tablespace. For more information about the syntax, see the following section.

Iii. User temporary tablespace
Temporary tablespace is mainly used for sorting order by statements and other intermediate operations. Before Oracle9i, you can specify that users use different temporary tablespaces. From 9i, temporary tablespaces are generic and all users use temp as temporary tablespaces.

Iv. user resource files
User resource files are used to restrict the access to user resources, including: CPU usage time limit, memory logical read limit, session data limit that each user can connect to at the same time, the space and time limit of one session, the duration limit of one session, dedicated for each session SGA space limit.

V. User tablespace quota
The tablespace storage limit is the total number of buckets that a user can use in a tablespace. You can specify the quota parameter when creating or modifying a user. If you exceed this limit when storing data in a tablespace, an error occurs. Error message: 'ora-01536: space quota exceeded for tablespace tablespacename ..'. You can query the dba_ts_quotas dictionary to view the tablespace quota information.

6. create user resource files
The syntax for creating a user resource file is as follows:
Create profile filename limit
Session_per_user integer
Cpu_per_session integer
User_per_call integer
Connect_time integer
......
Base:
Session_per_user: the maximum number of sessions that a user can connect to simultaneously;
Cpu_per_session: The total CPU time that a user can use during a database meeting. The unit is 1% seconds;
User_per_call: The total CPU time available for one SQL call. The unit is 1% seconds;
Logical_reads_per_session: the maximum number of database blocks that can be read during a database session;
Logical_reads_per_call: the maximum number of database blocks that can be read by one SQL call;
Idle_time: the idle time limit after the user connects to the database. The unit is minutes. If the idle time exceeds this value, the connection is disconnected;
Connect_time: Total time limit for one connection. The unit is minutes. When the connection time exceeds this value, the connection is disconnected;
Private_sga: the size of your SGA zone, in the unit of database blocks. The default value is unlimited;
Composite_limit: This is a combined resource item consisting of the preceding limit parameters. For example, assume that the resource settings are as follows:
Idle_time 20
Connect_time 120
CPU _ per_call 750
Com posite_limt 800
When the session space exceeds 20 minutes, or the connection time exceeds 120 minutes, or an SQL statement takes more than 7.5 seconds, or the total number of these resource limits exceeds 800, the system automatically terminates the session.
Failed_login_attempts: the maximum number of Logon failures caused by failed user name/password verification. If this limit is exceeded, the account is locked;
Password_life_time: Password validity period, in days. If logon is denied, you must reset the password. The default value is unlimited;
Password_reuse_time: the number of days after which a failed password can be reused. The default value is unlimited;
Password_reuse_max: number of times a password can be reused;
Password_lock_time: When the logon Failure reaches failed_login_attemps, the account is locked. this parameter is used to set the number of days locked;
The following is an example:
1) create a user resource file
Create profile tax_users limit
Session_per_user 3
Cpu_per_session Unlimited
Connect_time 30
Logical_reads_per_session default
Logical_reads_per_call 1000
Private_sga 15 K
Com posite_limit 500000
Password_life_time 90
2) query user resource file information
SQL> select * From dba_profile where profile = 'tax _ users'
3) Specify the user resource file to the user
SQL> alter user US1 profile tax_users

7. create, modify, and delete users
1. Create a user
For detailed Syntax of creating a user, please query the official Oracle parameter documentation. Here we will introduce the typical syntax. Syntax:
Create User Username
Identified by password
Default tablespace
Temporary tablespace
Profile
Quota integer | unlimited on tablespace
The meanings of the options are as follows:
Identified by password: User Password;
Default tablespace: default tablespace;
Temporary tablespace: Temporary tablespace;
Profile profile | default: user resource file;
Quota integer [k | M] | unlimited on tablespace: the user's space usage quota on the tablespace. You can specify the quota of multiple tablespaces.
Example:
Create user US1 identitied by ABC123
Default tablespace user01
Temporary tablespace temp
Profile default
Quota 1000 m on user01;

2. modify a user
The syntax for modifying a user is similar to the syntax for creating a user. The main reason is that "create user" is changed to "alter user". For more information, see the Oracle document.

3. delete a user
Deleting a user deletes the schema object created by the user and the user from the database. As follows:
SQL> drop user US1;
If US1 contains a schema object, the execution fails without the preceding statement. You must add the keyword "cascade" to delete it, which means that the object is deleted together, as shown below:
SQL> drop user US1 cascade;