Creating User in Oracle

Creating User / Schema / Account in Oracle Database

○ Only DBA (Database Administrator) can create new user. While installing Oracle software it asks a password for admin system. So here the database administrator is SYSTEM and password which we provided at the time of installation.

(Username: SYSTEM and Password: MANAGER)

Syntax: -

CREATE USER <NAME> IDENTIFIED BY <PASSWORD>

○ To create user connect as DBA

Username: - SYSTEM/MANAGER

SQL> (we will get sql prompt like this)

EXAMPLE: -

CREATE USER BATCH6PM IDENTIFIED BY ORACLE;

After executing above command a user BATCH6PM is created in database but it is not having permissions to do any operations.

Give the permissions to the user by using “GRANT” command after creating user.

EXAMPLE: -

GRANT CONNECT, RESOURCE TO BATCH6PM;

CONNECT: - To connect to Database

RESOURCE: - To create objects like Tables

(If we give both connect and resource permission to any user that will be a normal user. Normal user will not have any other access to do instead of changing password for his own user or schema)

DBA: - It means giving all permissions along with connect and resource (If we give this then the user will become Administrator).

To test with which user we are logged in the command is

SHOW USER

CHANGING PASSWORD: -

WHO CAN CHANGE PASSWORD?

 ○ Both user and Database Administrator can change password

BY USER (BATCH6PM/ORACLE)

SQL> PASSWORD

Changing password for BATCH6PM

Old Password:                           (It will not display the password even though we type)

New Password:                          (It will not display the password even though we type)

Retype Password:                      (It will not display the password even though we type)

Password changed

SQL>

BY DBA (SYSTEM/MANAGER)

SQL> ALTER USER BATCH6PM IDENTIFIED BY ORACLE12C;

DROPING USER: Only DBA can drop the user

SQL> DROP USER <NAME> [CASCADE]

Here cascade is optional, anything which is placed in the square brackets will be optional

If user is empty means without any objects then the CASCADE is not required.

If user in not empty means having objects like TABLES or VIEWS then CASCADE is required.

EXAMPLE OF DROPING USER:

DROP USER BATCH6PM;