User Management in Oracle

Creating and managing user accounts is one of the common requirements for Oracle database management. Every user who can connect to the database must be a legitimate user of the system. User

Creating and managing user accounts is one of the common requirements for Oracle database management. Every user who can connect to the database must be a legitimate user of the system. User

Create and manage user accounts
Users are one of the common requirements for Oracle database management. Every user who can connect to the database must be a legal user of the system. To use the Oracle management system, you must have the corresponding permissions. Creating a user and granting permissions is one of the common tasks of the Oracle system administrator.

1.1 configure authentication
Oracle provides three authentication methods for user accounts.

(1) password verification
When a user attempts to connect to the database using the password verification mechanism, the database will verify whether the user name is a valid database account and provide a password that matches the password stored by the user in the database.
Because user information and passwords are stored in the database, password verification users are also called database verification users.

(2) external verification
When an external authenticated user tries to connect to the database, the database checks whether the user name is a valid database account and is sure that the user has completed operating system-level authentication.
Note that an external authenticated user does not store a verification password in the database.

(3) Global verification
Global authenticated users do not store verification passwords in the database. This type of authentication is performed by the authentication service provided by an advanced security option.
Among the above three verification methods, password verification is the most commonly used method for user verification and will be described in detail. Unless otherwise stated, all users created and used in this book are password-verified users.
The other two types of verification are rarely used. They are only listed here. Interested readers can refer to the official Oracle documents.

1.2 create a user's syntax
To CREATE a new USER (password verification USER), run the create user command.
Create user username identified by password
OR IDENTIFIED EXETERNALLY
Or identified globally as 'cn = user'
[Default tablespace tablespace]
[Temporary tablespace temptablespace]
[QUOTA [integer K [M] [UNLIMITED] ON tablespace
[, QUOTA [integer K [M] [UNLIMITED] ON tablespace
[PROFILES profile_name]
[Password expire]
[Account lock or account unlock]
Where,
? Create user username: USER name, which is generally in the alphanumeric format and the "#" and "_" symbols.
? Identified by password: the user's password, which is generally in the alphanumeric format and "#" and.
? Identified exeternally: indicates that the user name is verified under the operating system. The user name must be the same as the user name defined in the operating system.
? Identified globally as 'cn = user': the user name is verified by the Oracle Security Domain Center Server. The CN name indicates the user's external name.
? [Default tablespace tablespace]: The default tablespace.
? [Temporary tablespace tablespace]: The default temporary tablespace.
? [QUOTA [integer K [M] [UNLIMITED] ON tablespace: the number of bytes in a table space that you can use.
? [PROFILES profile_name]: name of the resource file.
? [Password expire]: Set the PASSWORD to expired immediately. You must change the PASSWORD before logging on again.
? [Account lock or account unlock]: whether the user is locked. By default, it is not locked.

1.3 create a user instance
This section describes how to create a database user through a specific instance.
(1) create a user and specify the default tablespace and temporary tablespace.
Create a user named guord with a password of 12345. The default tablespace is system and the temporary tablespace is TEMP.
Example 1:
Create user guord identification BY 12345
Default tablespace system
Temporary tablespace temp;
To avoid occupying too much space when creating tables and index objects, you can configure the disk quota on the tablespace and use the QUOTA xxxM ON tablespace_name clause when creating a user.
(2) create a user and configure the disk quota.
Create a user named guord with a password of 12345. The default tablespace is system, and the temporary tablespace is TEMP. the user is not allowed to use Users tablespaces.
Example 2:
Create user guord identification BY 12345
Default tablespace system
TEMPORARY TABLESPACE TEMP
QUOTA 0 ON users;