Privileges and Roles

Oracle DBA Certification 14 min min read Updated: Nov 16, 2026 Beginner
Privileges and Roles
Beginner Topic 2 of 6

Two Kinds of Privileges

TypeWhat it allowsExample
System privilegeDo an action in the databaseCREATE TABLE, CREATE USER, ALTER SYSTEM
Object privilegeUse one specific objectSELECT on shop.orders

Grant and Revoke

sql -- system privileges GRANT CREATE SESSION, CREATE TABLE TO report_user; -- object privileges GRANT SELECT ON shop.orders TO report_user; GRANT SELECT, INSERT, UPDATE ON shop.customers TO report_user; -- take back REVOKE INSERT ON shop.customers FROM report_user;

Roles: Bundles of Privileges

Granting each privilege to each user is slow and error-prone. A role is a named group of privileges. Give privileges to the role once, then give the role to many users.

sql CREATE ROLE sales_reader; GRANT SELECT ON shop.orders TO sales_reader; GRANT SELECT ON shop.customers TO sales_reader; GRANT SELECT ON shop.products TO sales_reader; GRANT sales_reader TO report_user; GRANT sales_reader TO another_user;
Simple example: A role is like a job badge. Instead of giving every new employee fifty separate keys, you give them the 'Sales Reader' badge and the right doors open.

Predefined Roles

RoleWhat it gives
CONNECTAlmost nothing, only CREATE SESSION
RESOURCEBasic rights to create tables and code in own schema
DB_DEVELOPER_ROLEA good set for application developers (newer versions)
DBANearly all system privileges. Give to very few people.

ADMIN OPTION and GRANT OPTION

sql GRANT CREATE TABLE TO team_lead WITH ADMIN OPTION; -- can pass the privilege on GRANT SELECT ON shop.orders TO team_lead WITH GRANT OPTION;

Be careful with these. The person can pass the privilege to anyone else.

Check What Someone Has

sql SELECT * FROM dba_sys_privs WHERE grantee = 'REPORT_USER'; SELECT * FROM dba_role_privs WHERE grantee = 'REPORT_USER'; SELECT * FROM dba_tab_privs WHERE grantee = 'REPORT_USER';

Administrative Privileges

PrivilegeFor
SYSDBAFull control, including startup, shutdown and recovery
SYSOPERStartup, shutdown and basic operations, no access to data
SYSBACKUPBackup and recovery only, used by RMAN
SYSDGData Guard administration
SYSKMEncryption key management
Separation of duties: Use SYSBACKUP for backup jobs instead of SYSDBA. If the backup account is compromised, the damage is smaller.

Least Privilege Rules

  • Give users only the rights they need, nothing more.
  • Use roles, not direct grants, for people.
  • Never let applications connect as SYS or SYSTEM.
  • Revoke PUBLIC grants you do not need and review them regularly.
  • Review who has DBA role every few months.
sql SELECT grantee FROM dba_role_privs WHERE granted_role = 'DBA';

Conclusion

Privileges allow actions, roles bundle them and least privilege keeps the database safe. Always grant to roles and check with the DBA_ views.

Quick Summary: System privilege = action, object privilege = access to one object, role = bundle.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators