How to Create a User, Grant Permissions, use user as database in Oracle

In this tutorial, we will learn how to create oracle user under PDB database and how to use the Oracle GRANT ALL PRIVILEGES statement to grant all privileges to a user.

A user account is identified by a user name and defines the attributes of the user, including the following:

• Authentication method
• Password for database authentication
• Default tablespaces for permanent and temporary data storage
• Tablespace quotas
• Account status (locked or unlocked)
• Password status (expired or not)

When you create a user account, you must not only assign a user name, a password, and default tablespaces for the account, but you must also do the following:

• Grant the appropriate system privileges, object privileges, and roles to the account.
• If the user will be creating database objects, then give the user account a space usage quota on each tablespace in which the objects will be created.

So let's start to follow below steps to create user and grant ALL PRIVILEGES to a user

Step 1 Connect and set PDB where you want to create a user.

First of all, connect database with SQLPlus with sys user: 

sqlplus /nolog

SQL> connect sys as sysdba;
Enter password: 

At now, to check connection name to make sure you’re in the correct location, type

SQL> show con_name; 

Output will as like: 

CON_NAME
------------------------------
CDB$ROOT

And, to check PDBs, execute show pdbs command as like 

SQL> show pdbs; 

  CON_ID CON_NAME            OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
     1 PDB$SEED            READ ONLY NO
     2 ORAPDB               READ WRITE NO
     3 PDBDEV               READ WRITE NO

Here we have two PDB and one root PDB which is called SEED PDB. 

To switch to the pdbdev pluggable database, you use the following statement:

SQL> ALTER SESSION SET CONTAINER = pdbdev;

Session altered.

Again we can check pdbs and con_name after altering session

SQL> show pdbs;

  CON_ID CON_NAME            OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
     3 PDBDEV            READ WRITE NO

SQL> show con_name;

CON_NAME
------------------------------
PDBDEV 

Optional: Before creating a new user, you need to change the database to open if database mounted by executing the following command:

SQL> ALTER DATABASE OPEN;

Database altered.

Step 2 Create User

Once connected as SYSTEM and PDB is selected and open, you need to execute the CREATE USER command to generate a new account.

SQL> CREATE USER PDBUSER1 IDENTIFIED BY PDBUSER1#123 ; 
User created.

The above statement created a new user named PDBUSER1 with a password specified after the IDENTIFIED BY clause, which is PDBUSER1#123 in this case.

Step 3 Grant User

Without privileges, this created user can not do anything. There are a lots of privileges in oracle user. Depending on user's responsibilities, DBA gives privileges.

GRANT is a very powerful statement with many possible options, but the core functionality is to manage the privileges of both users and roles throughout the database.

You grant privileges to the PDBUSER1 user by using the following GRANT statement:

SQL> GRANT CONNECT, RESOURCE, DBA TO PDBUSER1 ;

Grant succeeded.

GRANT CREATE SESSION GRANT ANY PRIVILEGE TO PDBUSER1; 

We also need to ensure our new user has disk space allocated in the system to actually create or modify tables and data, so we’ll GRANT TABLESPACE like so:

GRANT UNLIMITED TABLESPACE TO PDBUSER1; 

Step 4 Connect new user.

Finally, you can connect to the pluggable database (PDBDEV) using the PDBUSER1 user account. Type the password (PDBUSER1#123) for the PDBUSER1 user when SQL*plus prompts you for the password.

SQL> CONNECT PDBUSER1@PDBDEV
Enter password:
Connected

Step 5 use and enjoy

Create Table: 

For test purposes, we can create a table as 

CREATE TABLE contacts(
  contact_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
  first_name VARCHAR2(50) NOT NULL,
  last_name VARCHAR2(50) NOT NULL,
  contact_number VARCHAR2(50) NOT NULL,
  contact_email VARCHAR2(50) NOT NULL,
  PRIMARY KEY(contact_id)
)

View Tables: 

To view the list of table owned by the current user, you query from the user_tables view.

SELECT table_name
FROM user_tables
ORDER BY table_name;

Insert Data: 

The INSERT statement adds one or more new rows of data to a database table.

INSERT INTO contacts (contact_id, first_name, last_name, contact_number, contact_email)
  VALUES (0001, 'Md Saidul', 'Haque', '01917223344', 'saidul@saidul.com');

INSERT INTO contacts (contact_id, first_name, last_name, contact_number, contact_email)
  VALUES (0002, 'Monira', 'Haque', '01917116677', 'monira@monira.com');

Filter Data: 

select * from contacts; 

Enjoy!!!