Privileges and Roles
Two Kinds of Privileges
| Type | What it allows | Example |
|---|---|---|
| System privilege | Do an action in the database | CREATE TABLE, CREATE USER, ALTER SYSTEM |
| Object privilege | Use one specific object | SELECT 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
| Role | What it gives |
|---|---|
CONNECT | Almost nothing, only CREATE SESSION |
RESOURCE | Basic rights to create tables and code in own schema |
DB_DEVELOPER_ROLE | A good set for application developers (newer versions) |
DBA | Nearly 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
| Privilege | For |
|---|---|
SYSDBA | Full control, including startup, shutdown and recovery |
SYSOPER | Startup, shutdown and basic operations, no access to data |
SYSBACKUP | Backup and recovery only, used by RMAN |
SYSDG | Data Guard administration |
SYSKM | Encryption 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
PUBLICgrants you do not need and review them regularly. - Review who has
DBArole 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.

