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:
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:
So let's start to follow below steps to create user and grant ALL PRIVILEGES to 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.
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.
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;
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
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', '[email protected]');
INSERT INTO contacts (contact_id, first_name, last_name, contact_number, contact_email) VALUES (0002, 'Monira', 'Haque', '01917116677', '[email protected]');
Filter Data:
select * from contacts;
Enjoy!!!