Oracle Administration from Linux CLI (SQL*Plus): Create User/Schema + Grant Privileges (PDB)

This guide shows how to administer Oracle using SQL*Plus from a Linux terminal:

• Connect as SYSDBA
• Connect to the CDB
• List and switch to a PDB
• Create a user (user = schema in Oracle)
• Grant schema-level privileges
• Set tablespace quota
• (Optional) Data Pump directory privileges

Prerequisites

• Oracle database is running (CDB + PDB)
• You have OS access to the database server
• You have SQL*Plus available (sqlplus)

1) Connect as SYSDBA from Linux CLI

sqlplus / as sysdba

This connects using OS authentication (typical on the DB server as the oracle user).

2) Connect to the CDB as SYSTEM

You can connect using a service name:

sqlplus system/Welcome123@ORCLCDB

Or locally (if the environment is set and listener isn’t required):

sqlplus system/Welcome123

3) Show PDBs (Pluggable Databases)

Inside SQL*Plus:

SHOW PDBS;

You’ll see something like ORCLPDB1 as an available pluggable database.

4) Switch session to the PDB

To create users/schemas inside a specific PDB (recommended), switch the container:

ALTER SESSION SET CONTAINER=ORCLPDB1;

Now you are working inside ORCLPDB1.

5) Create the user (Oracle user = schema)

Create a schema owner user:

CREATE USER TEST_DATABASE IDENTIFIED BY "123456789";

6) Grant privileges (schema-level)

Option A: Simple development-style grants (common in labs)

GRANT CONNECT, RESOURCE TO TEST_DATABASE;
ALTER USER TEST_DATABASE QUOTA UNLIMITED ON USERS;

This allows login and basic object creation (tables, procedures, etc.), and unlimited quota on the USERS tablespace.Oracle usernames are usually uppercase unless quoted.

Option B: More explicit and controlled grants (recommended)

Instead of RESOURCE, you can grant only what’s needed:

GRANT CREATE SESSION TO CBL_LOAN;
GRANT CREATE TABLE TO CBL_LOAN;
GRANT CREATE SEQUENCE TO CBL_LOAN;
GRANT CREATE VIEW TO CBL_LOAN;
GRANT CREATE PROCEDURE TO CBL_LOAN;ALTER USER CBL_LOAN QUOTA UNLIMITED ON USERS;

This is clearer and follows least-privilege better.

7) Example: Grant Data Pump and directory privileges (if needed)

If you’re doing Data Pump imports/exports, you may grant roles and directory permissions.

Data Pump role example

GRANT DATAPUMP_IMP_FULL_DATABASE TO TEST_DATABASE;

Directory permissions example

GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO TEST_DATABASE;

8) Verify the user and privileges

Check the user exists

SELECT username, account_status
FROM dba_users
WHERE username = 'TEST_DATABASE';

Check granted roles

SELECT granted_role
FROM dba_role_privs
WHERE grantee = 'TEST_DATABASE';

Check system privileges

SELECT privilege
FROM dba_sys_privs
WHERE grantee = 'TEST_DATABASE';

Notes / Best Practices

• Create users in the PDB, not in the CDB root, unless you intend a common user.
• Prefer explicit grants over broad roles in production.
• Use strong passwords (and rotate them); avoid simple numeric-only passwords for real systems.