Detailed description of Oracle user Creation

Detailed description of Oracle user Creation

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.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian
 
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 user resource access, including: cpu usage time limit, memory logical read count limit, session data limit that each user can connect to at the same time,
One session space and time limit, one session duration limit, and each session's dedicated 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

For more details, please continue to read the highlights on the next page:

1 2 Next Page