Shulou(Shulou.com)06/02 Report--

The CREATE USER statement creates a user. When a user connects to the ORACLE database, it must be authenticated. There are three types of validation in ORACLE:

Database verifies user account

External verifies user account

Global verifies user account

The default is database authentication. When a user connects to the database, oracle detects whether the user is a legitimate user of the database and provides the correct password

External authentication, oracle will only detect whether the user is a legitimate user, password has been verified by the network or system.

Global authentication also only detects whether it is a legitimate user, and password is verified by oraclesecurity server.

Database verifies user account

Database authentication user account is the default type of authentication and the most common type. To set up an account with the name piyush and password welcome, you only need to execute the following command:

CREATE USE piyush IDENTIFIED BY welcome

Piyush can change the password to saraswatt with the following statement:

ALTER USER piyush IDENTIFIED BY saraswati

Externally validate user account

The user account can enter the database without providing a password. In this case, it is the client operating system that replaces the database identification password. External verification accounts are sometimes called OPS$ accounts, and when they were first introduced in oracle6, oracle accounts were all related to the keyword prefix OPS$, which is why the init.ora parameter os_authent_prefix is the default feature of OPS$-- that is consistent with oracle6. The string defined by os_authent_prefix must be preprocessed to the operating system account name used to identify the Oracle external account. The statement to create the operating system user appl is:

CREATE USER ops$appl IDENTIFIED EATERNALLY

However, in general, the os_authent_prefix will be set to empty, like this:

CREATE USER appl IDENTIFIED EATERNALLY

The effect is the same, and the keyword IDENTIFIED EXTERNALLY tells ORACLE that this is an externally recognized account.

GLOBAL user account

The user account database of GLOBAL type does not detect the password, but the X.509 directory server detects the password. The way to create a user account of type GLOBAL is:

CREATE USER scott IDENTIFIED GLOBALLY AS "CN=scott,OU=divisional,O=sybex,C=US"

The keyword IDENTIFIED GLOBALLY AS indicates that a user account of type GLOBAL is being created.

Create and change user accounts

CREATE USER is used to create user accounts and assign values to the attributes of user accounts. ALTER USER is used to change user accounts and attributes. However, the CREATE USER statement must include a user name and password.

Some of the properties are set with CREATER USER and ALTER USER statements. These attributes are described below:

Assign default tablespaces to users

Table space (tablespace) places user objects such as tables, indexes, clusters, and so on. If the tablespace is not included in the create user statement, the default is the system tablespace.

CREATE USER piyush IDENTIFIED BY saraswati

DEFAULTE TABLESPACE user_data

ALTER USER manoj DEFAULTE TABLESPACE dev1_data

Assign temporary tablespaces to users

A temporary tablespace, as its name implies, is a temporary segment that temporarily stores user objects such as tables, indexes, and so on. In the same way.

CREATE USER piyush IDENTIFIED BY saraswati

Temporary TABLESPACE user_data

ALTER USER manoj Temporary TABLESPACE dev1_data

Assign the usage quota of tablespace to the user

Use quotas to limit the number of disks that users can use in the tablespace. The quota can be set in bytes, kilobytes, megabytes or unlimited.

CREATE USER piyush IDENTIFIED BY saraswati

DEFAULT TABLESPACE user_data

QUOTA UNLIMITED ON user_data

QUOTA 20M ON tools

ALTER USER manoj QUOTA 2500K ON tools

Assign a profile to the user

A summary table can limit the resources a user consumes during a session. These resources include: the time to connect to the database, idle time, the amount of logical read data per session, and so on. The default summary table has no limit to the resources.

CREATE USER piyush IDENTIFIED BY saraswati

PROFILE TABLESPACE user_data

ALTER USER manoj Temporary TABLESPACE dev1_data

Specify a role for the user response

This property can only be set by the ALTER USER statement, and an attempt to set it with the CREATE USER statement will return an exception.

ALTER USER manoj DEFAULT ROLE ALL EXCEPT salary_adm

Set the expiration time for the user's password to change the next time the user logs in

When the user's password expires, the next login will force the modified password,oracle to prompt the user for the old password, and then enter the new password. This feature is often used for new users, and when new users log in with the default password, they must modify the password immediately.

ALTER USER manoj IDENTIFIED BY welcome

ALTER USER manoj PASSWORD EXPIRE

Lock the account, but the user cannot log in.

ALTER USER ql ACCOUNT LOCK

Unlock the account so that users can log in to the database

ALTER USER ql ACCOUNT UNLOCK

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.