Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 38 ยท Performance and Administration

Oracle Users, Roles, Privileges and Tablespaces

Create Oracle users, grant system and object privileges, build roles, set quotas on tablespaces, and understand the CDB and PDB architecture, profiles and the data dictionary.

Developers who know only SQL and PL/SQL still run into administration all the time: "ORA-01950: no privileges on tablespace USERS", "ORA-00942: table or view does not exist" for a table that is certainly there, a deployment that needs a new schema. This lesson covers the administrative basics every Oracle developer should know: how storage is organized, how to create users, how privileges and roles work, and where to look things up.

Oracle cannot run on this site, and administration commands need a real instance, so everything below is read-only. The one runnable example is a Python model of how privileges and roles combine.

The architecture in one picture

Oracle storage layers

Database All files of one system
Tablespace A logical storage unit
Datafile A file on disk
Segment A table or an index

A segment lives in a tablespace, and a tablespace is made of datafiles.

1 / 2

A tablespace is a named, logical container. Behind it are one or more datafiles. Tables and indexes ( segments ) are stored in tablespaces, and the database grows by adding or extending datafiles. Special ones: SYSTEM and SYSAUX (the data dictionary and components), UNDO (before-images for rollback and read consistency), and TEMP (sorting and hash joins that spill to disk). In a 12c+ multitenant database, one container database (CDB) hosts many pluggable databases (PDBs). Application schemas and their users live in a PDB, and Oracle 23ai uses this architecture exclusively.

SQL
SELECT tablespace_name, status, contents FROM dba_tablespaces;

CREATE TABLESPACE app_data
  DATAFILE 'app_data01.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 20G;

Users and schemas

A user is an account; a schema is the collection of objects that account owns. In Oracle the two words are nearly interchangeable: creating a user creates its schema.

SQL
CREATE USER shop_app IDENTIFIED BY "Str0ng#Passw0rd"
  DEFAULT TABLESPACE app_data
  TEMPORARY TABLESPACE temp
  QUOTA 2G ON app_data;

GRANT CREATE SESSION TO shop_app;

Three details cause most beginner errors. A user cannot connect without CREATE SESSION. A user cannot create anything without privileges. And a user cannot store rows in a tablespace without a quota on it (ALTER USER shop_app QUOTA UNLIMITED ON app_data), which is the cause of ORA-01950. Use a strong password and prefer external or wallet authentication in production. Never put real passwords into scripts under version control. Change or lock accounts with ALTER USER shop_app IDENTIFIED BY ... and ALTER USER shop_app ACCOUNT LOCK.

System privileges and object privileges

A system privilege is the right to perform an action in general, such as CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE USER or the dangerous ANY privileges (SELECT ANY TABLE, DROP ANY TABLE). An object privilege is the right to use one specific object: SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES.

SQL
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE TRIGGER TO shop_app;

GRANT SELECT, INSERT ON hr.employees TO shop_app;
GRANT EXECUTE ON hr.give_raise TO shop_app;
GRANT SELECT ON hr.employees TO shop_app WITH GRANT OPTION;   -- may pass it on

REVOKE INSERT ON hr.employees FROM shop_app;

By default a user can use only objects of their own schema. To use someone else's table the user needs an object privilege and must name it with the owner (hr.employees) or through a synonym: CREATE SYNONYM employees FOR hr.employees;. "ORA-00942: table or view does not exist" is Oracle's deliberately vague answer for both "no such table" and "you may not see it".

A critical rule for PL/SQL: privileges granted through a role do not apply inside a definer-rights stored procedure. A procedure that reads hr.employees compiles only if its owner holds SELECT on it directly, not through a role. That puzzles many developers (PLS-00201 or ORA-00942 at compile time while the same SQL works in SQL*Plus). It is why stored procedures with AUTHID CURRENT_USER behave differently from the default.

Roles

A role is a named bundle of privileges, so you grant one name instead of twenty rights:

SQL
CREATE ROLE report_reader;
GRANT SELECT ON hr.employees  TO report_reader;
GRANT SELECT ON hr.departments TO report_reader;

GRANT report_reader TO alice, bob;
GRANT report_reader TO analyst_role;      -- roles can contain roles

Oracle ships predefined roles: CONNECT (basically CREATE SESSION today), RESOURCE (create basic objects), DBA (everything, never give it to applications), and the better-scoped DB_DEVELOPER_ROLE in recent releases such as 23ai. Follow least privilege: give an application account only the rights its job needs, and keep the schema that owns tables separate from the account that the application logs in with.

The Python stand-in below models the rule that a user's effective rights are direct grants plus role grants, and that stored code sees only the direct ones:

Python Direct grants versus roles (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Profiles and resource limits

A profile sets password rules and resource limits for a group of users:

SQL
CREATE PROFILE app_profile LIMIT
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LIFE_TIME 90
  PASSWORD_VERIFY_FUNCTION ora12c_verify_function
  SESSIONS_PER_USER 20
  IDLE_TIME 30;

ALTER USER shop_app PROFILE app_profile;

The data dictionary

Oracle describes itself through read-only views. Three families exist for each kind of object: USER_* (yours), ALL_* (what you may see) and DBA_* (everything, for administrators).

SQL
SELECT table_name FROM user_tables;
SELECT object_name, object_type, status FROM user_objects WHERE status = 'INVALID';
SELECT * FROM user_role_privs;
SELECT * FROM session_privs;
SELECT * FROM user_ts_quotas;
SELECT owner, segment_name, ROUND(bytes/1024/1024) AS mb
  FROM dba_segments ORDER BY bytes DESC FETCH FIRST 10 ROWS ONLY;

Dynamic performance views (V$SESSION, V$SQL, V$PARAMETER) show live state, and SELECT SYS_CONTEXT('USERENV', 'CURRENT_USER') FROM dual; tells you who you currently are.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A new user can connect but gets ORA-01950 when creating a table. What is missing?

    Storing data needs a quota on the tablespace (or the UNLIMITED TABLESPACE privilege).
  2. 2. A stored procedure fails to compile with a table or view does not exist error, though the same SELECT works in SQL*Plus. What is a likely cause?

    Grant the privilege directly to the procedure's owner.
  3. 3. What is a role for?

    Roles simplify privilege management.
  4. 4. Which data dictionary view family lists only the objects you own?

    USER_* shows your own objects; ALL_* what you can access; DBA_* everything.

Summary

  • Data lives in segments inside tablespaces made of datafiles; in a multitenant database, you connect to a PDB.
  • A user needs CREATE SESSION, system privileges to create objects, and a quota on its tablespace.
  • Object privileges grant access to specific objects; roles bundle privileges, but do not apply inside definer-rights PL/SQL.
  • Profiles control passwords and limits; the USER_*, ALL_*, DBA_* views describe the database.

Next: Project: A Bank Ledger