System/Object Privileges, Roles, Administrative Privileges, Definer/Invoker Rights, and Least Privilege

Learning outcomes

ServiceHub has three very different identities: a schema that owns tables, a PL/SQL API that implements trusted business operations, and an application runtime that should only connect and invoke that API. Granting DBA to all three “because it fixes permission errors” destroys the security boundary. Oracle authorization distinguishes system privileges, object privileges, roles, powerful administrative privileges, and the privilege domain used by stored PL/SQL.

1. System privileges authorize classes of database actions

A system privilege authorizes an action such as CREATE SESSION, CREATE TABLE, or CREATE PROCEDURE. Some privileges ending in ANY cross schema boundaries and are correspondingly powerful. Granting SELECT ANY TABLE to an application just to read one table violates least privilege because future tables become readable too.

SELECT grantee,privilege,admin_option,common,inheritedFROM dba_sys_privsWHERE grantee IN ('SERVICEHUB_OWNER','SERVICEHUB_APP')ORDER BY grantee,privilege;

2. Object privileges authorize operations on named objects

An object privilege targets a specific table, view, sequence, procedure, package or other object. The Chapter 1 runtime pattern—CREATE SESSION plus selected DML on ServiceHub objects and no CREATE TABLE—is intentionally narrower than an owner schema.

SELECT  grantee,  owner,  table_name,  privilege,  grantableFROM dba_tab_privsWHERE grantee='SERVICEHUB_APP'ORDER BY owner,table_name,privilege;

The dictionary proves grants, not whether the application actually uses them. Privilege Analysis can measure usage on supported deployments, but static least-privilege review remains necessary.

3. Roles group privileges, but roles are not a substitute for every direct grant

A role is a named bundle of privileges. Roles simplify assignment to users and can be enabled/disabled per session. However, definer-rights stored PL/SQL is intentionally compiled/executed with a restricted security domain: required privileges on referenced external objects must be granted directly to the definer, not merely inherited through a role.

ALTER SESSION SET CONTAINER=FREEPDB1;BEGIN EXECUTE IMMEDIATE 'DROP USER sh20_api CASCADE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -1918 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP USER sh20_data CASCADE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -1918 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP USER sh20_runtime CASCADE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -1918 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP ROLE sh20_reader_role';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -1919 THEN RAISE; END IF; END;/CREATE USER sh20_data NO AUTHENTICATION  DEFAULT TABLESPACE users QUOTA 10M ON users;CREATE USER sh20_api NO AUTHENTICATION  DEFAULT TABLESPACE users QUOTA 10M ON users;CREATE USER sh20_runtime NO AUTHENTICATION;GRANT CREATE TABLE TO sh20_data;GRANT CREATE PROCEDURE TO sh20_api;GRANT CREATE SESSION TO sh20_api,sh20_runtime;CREATE TABLE sh20_data.secure_work_orders (  work_order_id NUMBER PRIMARY KEY,  status_code   VARCHAR2(12) NOT NULL);INSERT INTO sh20_data.secure_work_orders VALUES (1,'OPEN');COMMIT;CREATE ROLE sh20_reader_role;GRANT SELECT ON sh20_data.secure_work_orders TO sh20_reader_role;GRANT sh20_reader_role TO sh20_api;

NO AUTHENTICATION creates schema-only accounts: they can own objects but cannot be used for password login. For this lab, temporarily enable interactive authentication for SH20_API without embedding its password in the lesson.

PASSWORD sh20_api-- SQLcl/SQL*Plus prompts for the new password and confirmation.-- Do not spool, echo, or commit that secret.

4. Deliberate failure: role privilege does not compile a definer-rights procedure

CONNECT sh20_api@//localhost:1521/FREEPDB1-- Enter password interactively.CREATE OR REPLACE PROCEDURE get_open_count(  p_count OUT NUMBER)AUTHID DEFINERASBEGIN  SELECT COUNT(*)  INTO p_count  FROM sh20_data.secure_work_orders  WHERE status_code='OPEN';END;/SHOW ERRORS-- Expected compile diagnostics include:-- PL/SQL: ORA-00942: table or view does not exist

The table exists and the session can have the role enabled, but a definer-rights program unit needs the referenced object privilege directly. This prevents an enabled-role change from silently expanding the stored program's authority.

5. Repair: direct grant to the low-privileged API schema

GRANT SELECT ON sh20_data.secure_work_orders TO sh20_api;

CREATE OR REPLACE PROCEDURE get_open_count(  p_count OUT NUMBER)AUTHID DEFINERASBEGIN  SELECT COUNT(*)  INTO p_count  FROM sh20_data.secure_work_orders  WHERE status_code='OPEN';END;/SHOW ERRORS-- Expected: No errors.

The safe repair is not GRANT SELECT ANY TABLE and not GRANT DBA. It is the exact direct object privilege required by the API.

6. Runtime gets EXECUTE—not table access

GRANT EXECUTE ON sh20_api.get_open_count TO sh20_runtime;SELECT grantee,owner,table_name,privilegeFROM dba_tab_privsWHERE grantee='SH20_RUNTIME'ORDER BY owner,table_name,privilege;

The runtime needs CREATE SESSION and EXECUTE on the API. It does not need SELECT on the underlying table.

7. Invoker rights intentionally uses the caller's privilege domain

AUTHID CURRENT_USER creates an invoker-rights unit. At runtime, external SQL references are checked using the invoking user's direct privileges and enabled roles. This is useful for shared utilities that should never gain more authority merely because a privileged schema owns the code.

CREATE OR REPLACE PROCEDURE sh20_api.count_as_invoker(  p_count OUT NUMBER)AUTHID CURRENT_USERASBEGIN  EXECUTE IMMEDIATE    'SELECT COUNT(*) FROM sh20_data.secure_work_orders'    INTO p_count;END;/

If SH20_RUNTIME invokes this without table privilege, it fails even though the API owner can read the table. Oracle also protects invoker-rights callers with INHERIT PRIVILEGES/INHERIT ANY PRIVILEGES; removing those grants can produce ORA-06598 until the caller explicitly trusts the code owner.

8. Administrative privileges are authentication modes, not application roles

SYSDBA, SYSOPER, SYSBACKUP, SYSDG and SYSKM are purpose-specific administrative privileges. They can authenticate through the password file and bypass ordinary application authorization in powerful ways. Use the narrowest privilege—SYSBACKUP for RMAN, SYSKM for key management—rather than using SYSDBA everywhere.

SELECT  username,  sysdba,  sysoper,  sysbackup,  sysdg,  syskmFROM v$pwfile_usersORDER BY username;

9. Common versus local grants

A local user created in FREEPDB1 belongs only to that PDB. A common user is created in CDB$ROOT and normally uses the common-user prefix. Likewise, CONTAINER=CURRENT versus CONTAINER=ALL changes grant scope. ServiceHub application users should stay local unless a cross-container administrative use case explicitly requires a common identity.

10. Cleanup

DROP USER sh20_runtime CASCADE;DROP USER sh20_api CASCADE;DROP USER sh20_data CASCADE;DROP ROLE sh20_reader_role;

11. Production judgment

Separate ownership, trusted stored APIs and runtime identities. Prefer direct object grants where stored definer-rights code requires them, roles for human/job privilege bundles, and invoker-rights code when the caller's authority should constrain execution. Review ANY privileges, grant options, administrative privileges and common grants as high-risk surfaces.

No restart, pack, option, or COMPATIBLE increase is required. The lab is PDB-local and Free-compatible. Lesson 2 adds authentication/account lifecycle controls around these identities without confusing password policy with authorization.

Authoritative references

• Managing Security for Definer's and Invoker's Rights — direct grants, AUTHID and inheritance
• Invoker's Rights and Definer's Rights — PL/SQL AUTHID semantics
• GRANT — system/object/role grant syntax
• Managing Security for Database Users — least privilege and user scope
• Licensing Information — current security feature availability