Oracle Database – Users, Schemas and Privileges

In MySQL and Postgres a database holds schemas, and users are separate things you grant access to. In Oracle those two ideas are the same idea: a user is a schema. Create the user SHOP and you have created the namespace SHOP.ORDERS. There is nothing else to create, and nothing to USE.

Once that clicks, the rest of Oracle's security model is straightforward. This post covers the account you should actually run an application as.

Create the schema owner

Connect to the PDB, not the CDB root:

sql system/Welcome1@//localhost:1521/FREEPDB1

CREATE USER shop IDENTIFIED BY "Sh0p_dev_1"
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA 500M ON users;

GRANT CREATE SESSION TO shop;   -- without this the user cannot log in at all
GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE,
      CREATE TRIGGER, CREATE TYPE, CREATE SYNONYM TO shop;

Two things bite here.

A quota is not optional. CREATE TABLE lets you create the table; putting a row in it needs space in the tablespace. Without a quota the first insert fails with:

ORA-01950: no privileges on tablespace 'USERS'

Grant a real number (QUOTA 500M) or QUOTA UNLIMITED ON users. Avoid the UNLIMITED TABLESPACE system privilege — it applies to every tablespace including SYSTEM, which is how application data ends up in the wrong place.

Password case. Unquoted passwords are folded to uppercase in older compatibility modes; quote them and they are taken literally. Quote always.

CONNECT and RESOURCE — convenient, and worse than they look

Every tutorial on the internet says this:

GRANT CONNECT, RESOURCE TO shop;   -- fine for a sandbox

CONNECT is now just CREATE SESSION, so that half is harmless. RESOURCE bundles the object-creation privileges and historically dragged UNLIMITED TABLESPACE along with it. It is fine on your laptop. In a real deployment, grant the specific CREATE … privileges the schema needs, as above, and nothing more.

Three kinds of privilege

Roles are how you keep grants manageable. The pattern that scales: the schema owner owns the objects and never logs in; applications connect as a separate account holding a role.

-- Owner of the tables. Locked so nobody can connect as it.
ALTER USER shop ACCOUNT LOCK;

-- A role describing what the application is allowed to do.
CREATE ROLE shop_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.orders   TO shop_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.order_items TO shop_app;
GRANT SELECT ON shop.order_seq TO shop_app;

-- The account the connection pool actually uses. It owns nothing.
CREATE USER shop_svc IDENTIFIED BY "Sv3_dev_1";
GRANT CREATE SESSION TO shop_svc;
GRANT shop_app TO shop_svc;

A compromised application account can then read and write rows but cannot drop a table, and the day you need a read-only reporting account it is one more role away.

Qualifying names, and how to stop

shop_svc owns nothing, so SELECT * FROM orders fails with ORA-00942: table or view does not exist — Oracle looked in the SHOP_SVC schema and found nothing. Oracle's "table does not exist" also covers "exists but you cannot see it", which is deliberate: it refuses to confirm the object is there.

Three ways out, in increasing order of tidiness:

-- 1. Qualify every reference. Correct, verbose.
SELECT * FROM shop.orders;

-- 2. Change the default for this session only.
ALTER SESSION SET CURRENT_SCHEMA = shop;
SELECT * FROM orders;

-- 3. Synonyms, so unqualified names resolve for everyone, permanently.
CREATE SYNONYM shop_svc.orders FOR shop.orders;              -- private
CREATE PUBLIC SYNONYM orders   FOR shop.orders;              -- every user

Option 2 is the one to reach for in JDBC: it is a single statement on connection setup and it means your SQL and your JPA entities carry no schema prefix, so the same code runs against shop_dev and shop_prod.

Inspecting what you granted

-- System privileges held directly
SELECT privilege FROM user_sys_privs;

-- Roles held, and what is inside them
SELECT granted_role, admin_option FROM user_role_privs;
SELECT role, privilege FROM role_sys_privs;

-- Object privileges granted TO me, and BY me
SELECT owner, table_name, privilege FROM user_tab_privs_recd;
SELECT grantee, table_name, privilege FROM user_tab_privs_made;

-- Quota actually in force
SELECT tablespace_name, bytes, max_bytes FROM user_ts_quotas;

-- Account state: OPEN, LOCKED, EXPIRED(GRACE)…
SELECT username, account_status, default_tablespace
FROM   dba_users WHERE username IN ('SHOP', 'SHOP_SVC');

Passwords that expire on you

Oracle applies a password lifetime by default — 180 days on the DEFAULT profile — and a locked-out service account at 3 a.m. is a memorable way to learn this. For a development database:

-- One account
ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME UNLIMITED;

-- Or a dedicated profile, which is the better habit
CREATE PROFILE app_service LIMIT
  PASSWORD_LIFE_TIME UNLIMITED
  FAILED_LOGIN_ATTEMPTS 10;
ALTER USER shop_svc PROFILE app_service;

-- Already expired? This clears it.
ALTER USER shop_svc IDENTIFIED BY "Sv3_dev_1" ACCOUNT UNLOCK;

Watch for ACCOUNT_STATUS = 'EXPIRED(GRACE)' in DBA_USERS — that is the warning shot before connections start failing.

Never build on SYSTEM

It is tempting, because it is the account the container handed you. Don't: SYS and SYSTEM own the data dictionary, objects you create there are mixed in with Oracle's own, an export of your schema becomes impossible to separate, and every mistake runs with full privileges. Create the schema owner in the first five minutes.

Next

You have a schema to build in. Next up: Oracle's data types, and which ones to pick so you don't regret the column three years later.