How to create a user in Oracle

First, the concept of the user

Users, or user, is a popular way to access Oracle database "people." In Oracle, the user's various security parameters can be controlled to maintain the security of the database, these concepts include schemas, permissions, roles, storage settings, space quotas, access resource constraints, database auditing, and so on. Each user has a password that uses the correct user/password to log on to the database for data access.

Second, user default table space

A tablespace is the largest logical unit of information storage, and when a user connects to a database for data storage, the data is stored in the user's default tablespace if the target storage table space is not indicated.

For example: CREATE TABLE MyTable (ID varchar2 (), name VARCHAR2 (100)), this statement creates a table mytable and stores it in the current user's default tablespace.

To specify a tablespace: CREATE TABLE MyTable (ID varchar2 (), name VARCHAR2 ()) tablespace tbs1;.

The user's default tablespace can be specified when the user is created, or it can be specified using the Aler user command, as described later in this article.

Third, user temporary table space

Temporary table spaces are primarily used for ordering by statements and other intermediate operations.

Before oracle9i, you can specify that users use a different temporary tablespace, starting at 9i, the temporary tablespace is generic and the user uses temp as a temporary tablespace.

Iv. User Resource Files

User resource files are used to restrict user access to resources, including CPU usage time limits, memory logical read limits, session data limits that each user can connect to,

The space and time limit for a session, the duration limit for a session, and the dedicated SGA space limit for each session.

Five, the user table space limit

Tablespace storage limits are the total amount of storage space a user can use in a table space.

When creating or modifying a user, it can be indicated by the parameter quota. If a user exceeds this limit when they store data in a tablespace, an error occurs.

Error messages such as: ' Ora-01536:space quota exceeded for tablespace tablespacename ... '. You can view the table space quota information by querying the dictionary Dba_ts_quotas.

Vi. Creating User resource Files

The syntax for creating user resource files is as follows:

CREATE profile filename LIMIT

Session_per_user integer

Cpu_per_session integer

User_per_call integer

Connect_time integer

......

Kizhong:

Session_per_user: The number of sessions that users can connect to at the same time limit;

Cpu_per_session: The total amount of CPU time that the user can occupy during a database will be limited, the unit is 1% seconds;

User_per_call: The total amount of CPU time available to the user at a time of SQL call is 1% seconds;

Logical_reads_per_session: The limit of the number of database blocks that can be read during a database session;

Logical_reads_per_call: A limit on the number of database blocks that can be read by a single SQL call;

Idle_time: The user can be connected to the database after the free time limit, in minutes, if the idle time exceeds this value, the connection is disconnected;

Connect_time: The total time limit of a connection, the unit is minutes, the connection time exceeds this value, the connection is disconnected;

PRIVATE_SGA: Users do not have the size of the SGA area, the unit is a database block, the default value is unlimited;

Composite_limit: This is a composite resource item consisting of the above constraint parameters.

For example, suppose the resource settings are as follows:

Idle_time 20

Connect_time 120

Cpu_per_call 750

COMPOSITE_LIMT 800

Then, when the session space exceeds 20 minutes, or the connection time is more than 120 minutes, or the execution of a SQL takes more than 7.5 seconds, or the total number of resource constraints combined exceeds 800,

The system automatically terminates the session.

Failed_login_attempts: User login, allow user name/password checksum failure caused by the number of login failed, exceeding the number of times, the account is locked;

Password_life_time: Password valid time, the unit is the number of days, more than this moment, refused to login, must reset password, the default value is unlimited;

Password_reuse_time: A failure password after how many days before it can be used again, the default is unlimited;

Password_reuse_max: The number of times a password can be reused;

Password_lock_time: When the login fails to reach Failed_login_attemps, the account is locked, which is used to set the number of days locked;