Oracle User Management :#01

The CREATE USER command creates a system user as shown here.

CREATE USER Test IDENTIFIED BY test12345;

• A DBA must have the CREATE USER system privilege.
• The IDENTIFIED BY clause specifies the user password
• User to connect to Oracle, you must grant the user the CREATE SESSION system privilege.
• Each username must be unique within a database
• Each user/schema for the storage of objects within the database
• Two users can name objects identically because the objects are referred to globally by using a combination of the username and object name.

A complete example of the CREATE USER command:

CREATE USER Test
IDENTIFIED BY test12345
DEFAULT TABLESPACE Users
TEMPORARY TABLESPACE Temp
QUOTA 1M ON Users
PROFILE Developer
ACCOUNT UNLOCK
PASSWORD EXPIRE;

Test has two identified , one for DEFAULT storage of objects and one for TEMPORARY objects.
Test  has the resource limitations allocated by the PROFILE named Developer. The account is unlocked (the default – alternatively the account could be created initially with the LOCK specification).
The PASSWORD EXPIRE clause requires Test to change the password prior to connecting to the database. After the password is set, when the user logs on using SQLPlus or any other software product that connects to the database, the user receives the following message at logon, and is prompted to enter a new password:

[oracle@localhost admin]$ sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 11 07:49:55 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Enter user-name: test
Enter password:
ERROR:
ORA-28001: the password has expired
Changing password for test
New password:
Retype new password:
Password changed


Alter user command : 
To make any other use of the command, a user must have the ALTER USER system privilege.

Use below statement change password for users

alter user test identified by test54321;

we can use Alter command to change tablespace,profile,password,quota etc.

Drop User Command

The DROP USER command is used to drop a user. Examples:

SQL> drop user test1;
User dropped.

Drop user including all reference objects

SQL>DROP USER Test CASCADE;  

Dropping a user causes the user and the user schema to be immediately deleted from the database.
If the user has created objects within their schema, it is necessary to use the CASCADE option in order to drop a user.
If you fail to specify CASCADE when user objects exist, an error message is generated and the user is not dropped.
For drop the user, the DBA must have the DROP USER system privilege.

If you want to deny access to the database, but do not want to drop the user and the
user's objects, you should revoke the CREATE SESSION privilege for the user temporarily.
You cannot drop a user who is connected to the database - you must first terminate the user's session with the ALTER SYSTEM KILL SESSION command.

Default Tablespace:

If one is not specified, the default tablespace for a user is the SYSTEM tablespace – not a good choice for a default tablespace

Temporary Tablespace:

The default Temporary Tablespace for a user is also the SYSTEM tablespace.
Allowing this situation to exist for system users will guarantee that user processing will cause contention with access to the data dictionary.
Generally a DBA will create a TEMP tablespace that will be shared by all users for processing that requires sorting and joins.

Database Authentication:

Database authentication involves the use of a standard user account and password.
Oracle performs the authentication.
System users can change their password at any time.
Passwords are stored in an encrypted format.
Each password must be made up of single-byte characters, even if the database uses a multi-byte character set.

Advantages:

• User accounts and all authentication are controlled by the database. There is no reliance on anything outside of the database.
• Oracle provides strong password management features to enhance security when using database authentication.
• It is easier to administer when there are small user communities.
• Oracle recommends using password management that includes password aging/expiration, account locking, password history, and password complexity verification.