Oracle User Operations

Yusuf SEZER January 15, 2020 ORACLE

This article contains information about Oracle User Operations like creating a new Oracle user, authorizing, changing password and dropping Oracle User.

Create Oracle User

CREATE USER is used to create the user for the Oracle database.

The keywords to be used during user creation are as follows.

CREATE USER user_name 
  IDENTIFIED { BY password
             | EXTERNALLY [ AS 'certificate_DN' ]
             | GLOBALLY [ AS '[ directory_DN ]' ]
             }
  [ DEFAULT TABLESPACE tablespace
  | TEMPORARY TABLESPACE
       { tablespace | tablespace_group }
  | QUOTA integer [ K | M | G | T | P | E ]
        | UNLIMITED }
        ON tablespace
    [ QUOTA integer [ K | M | G | T | P | E ]
        | UNLIMITED }
            ON tablespace
    ]
  | PROFILE profile_name
  | PASSWORD EXPIRE
  | ACCOUNT { LOCK | UNLOCK }
     [ DEFAULT TABLESPACE tablespace
     | TEMPORARY TABLESPACE
         { tablespace | tablespace_group }
     | QUOTA integer [ K | M | G | T | P | E ]
           | UNLIMITED }
           ON tablespace
       [ QUOTA integer [ K | M | G | T | P | E ]
           | UNLIMITED }
           ON tablespace
        ]
     | PROFILE profile
     | PASSWORD EXPIRE
     | ACCOUNT { LOCK | UNLOCK } ]
     ] ;

CREATE USER user_name

IDENTIFIED { BY password

| EXTERNALLY [ AS 'certificate_DN' ]

| GLOBALLY [ AS '[ directory_DN ]' ]

}

[ DEFAULT TABLESPACE tablespace

| TEMPORARY TABLESPACE

{ tablespace | tablespace_group }

| QUOTA integer [ K | M | G | T | P | E ]

| UNLIMITED }

ON tablespace

[ QUOTA integer [ K | M | G | T | P | E ]

| UNLIMITED }

ON tablespace

]

| PROFILE profile_name

| PASSWORD EXPIRE

| ACCOUNT { LOCK | UNLOCK }

[ DEFAULT TABLESPACE tablespace

| TEMPORARY TABLESPACE

{ tablespace | tablespace_group }

| QUOTA integer [ K | M | G | T | P | E ]

| UNLIMITED }

ON tablespace

[ QUOTA integer [ K | M | G | T | P | E ]

| UNLIMITED }

ON tablespace

]

| PROFILE profile

| PASSWORD EXPIRE

| ACCOUNT { LOCK | UNLOCK } ]

] ;

We can create user as follows using only username and password.

CREATE USER Your_User_Name IDENTIFIED BY Your_User_Password;

1	CREATE USER Your_User_Name IDENTIFIED BY Your_User_Password;

After the command is executed, it will create the user according to the value specified with Your_User_Name and Your_User_Password.

Change Oracle User Password

ALTER USER keywords are used to change the user’s password.

ALTER USER Your_User_Name IDENTIFIED by New_Password;

1	ALTER USER Your_User_Name IDENTIFIED by New_Password;

Grant Permission To Oracle User

The GRANT keyword is used to grant permission to Oracle user.

GRANT Permission1,Permission2,Permission3 TO Your_User_Name;

1	GRANT Permission1,Permission2,Permission3 TO Your_User_Name;

You can grant permissions like CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE PRODECURE, SELECT, INSERT, UPDATE or custom table and column based.

Oracle grant column privileges

GRANT UPDATE(column1, column2) ON Your_Table_Name TO Your_User_Name;

1	GRANT UPDATE(column1, column2) ON Your_Table_Name TO Your_User_Name;

Oracle grant all privileges

The ALL PRIVILEGES value is used to grant all privileges to a user.

GRANT ALL PRIVILEGES TO Your_User_Name;

1	GRANT ALL PRIVILEGES TO Your_User_Name;

Revoke Permission From Oracle User

The REVOKE keyword is used to revoke user privileges.

REVOKE Permission1,Permission2,Permission3 TO Your_User_Name;

1	REVOKE Permission1,Permission2,Permission3 TO Your_User_Name;

Lock and Unlock Oracle User

In Oracle user processes, the user can be locked. You can change the parameters that cause the user to crash in Oracle Profiles. You can find more information in the article “How To Create Oracle Profiles“. You may also be interested in the following articles.

“How To Change User Profile On Oracle“,

“How To Keep Same Password For Oracle User When Its EXPIRED(GRACE) State“,

“How To Set Password Policy in Oracle“,

“Oracle Password Expired Error“,

“Profile password verify function issue(FROM ROOT)”

The ALTER USER keyword is used for user locking.