Oracle user Creation

ArticleDirectory

• Delete a user

Create a user:

Create user syntax

To create a new user (password verification user, which is the same as the following), you can use the create USER command. The following is the syntax of the create USER command.

Create User Username identified by password

Or identified exeternally

Or identified globally as 'cn = user'

[Default 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,

Using create user username: user name, which is generally alphanumeric and "#" and.

Identified identified by password: the user password, which is generally a alphanumeric character and a symbol "#" and.

Authenticated identified exeternally: indicates that the user name is verified in the operating system. The user name must be the same as the user name defined in the operating system.

Authenticated 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.

Tablespace [default tablespace]: The default tablespace.

Partition [temporary tablespace]: The default temporary tablespace.

Limit [quota [integer k [m] [unlimited] On tablespace: the number of bytes in a table space that you can use.

Program [profiles profile_name]: name of the resource file.

Reset [password expire]: Set the password to expired immediately. You must change the password before logging on again.

Unlock [account lock or account unlock]: whether the user is locked. By default, the user is not locked.

/*
Create User Files
Create permission
*/
Create user adpost identified by "******" default tablespace users temporary tablespace temp quota unlimited on users;
/* Authorize */

Grant create session to adpost;
/*

Modify user syntax and instance

After the user is created, the administrator can modify the user, including changing the user password, changing the user's default tablespace, temporary tablespace, disk quota, and resource limit. Modify the user's command syntax as follows.

Alter User Username identified by password

Or identified exeternally

Or identified globally as 'cn = user'

[Default 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]

[Default role [, role]

Or [default role all [role t role [, role]

Or [default role Note]

For the meanings of parameters, refer to the preceding crreate user syntax.

If the DBA specifies the disk quota of a user in a tablespace when creating a user, after a period of time, when the user uses the tablespace that has reached the disk quota set by the DBA, the Oracle System displays an error message similar to the following.

ORA-01536: space quota exceeded for tablespace 'users'

In this case, the DBA should promptly use the alter USER command to increase the user's quota in the tablespace.

(1) modify the user's disk quota.

When the Oracle system prompts a ORA-01536 error, it indicates that the user's resource has exceeded the limit and you need to add resources to the user.

Example 1:

SQL> sqlplus system/Password

SQL> alter user wbtest quota 100 m on users;

Use the preceding command to extend the disk quota of user wbtest in users tablespace to 100 MB.

(2) modify the user's password.

Change Scott's password to tigerabc.

Example 2:

SQL> alter user Scott identified by tigerabc;

After oracle is installed by default, many users are in the locked status for security reasons. users in the locked status can be unlocked.

(3) query the Locked User information in the Oracle system.

/*

Example 3:

SQL> select username, account_status, lock_date from dba_users;

Username account_status lock_dtae

Bytes --------------------------------------------------------------------------------------

Sys open

System open

Dbsnmp open

House open

Scott open

OE open

Outln expired & locked 18-8-07

Wmsys expired & locked 18-8 month-07

Ordsys expired & locked 18-8 month-07

MDSYS expired & locked 18-8 month-07

(4) use alter user to unlock the locked MDSYS user.

SQL> show user;

User is "system"

SQL> alter user MDSYS account unlock;

The user has changed.

Delete a user

The drop USER command is used to delete a user. After the user is deleted, Oracle deletes the user, solution, and all object solutions from the data dictionary. The syntax is as follows:

*/

Drop user TMS cascade;