Definer's rights, invoker's rights and roles

"""Whose rights a PL/SQL unit runs with, and where a name such as accounts resolves.

LAB owns the practice table accounts and procedures that read it: balance_dr is AUTHID DEFINER, and
balance_ir and balance_ir_lab (which names lab.accounts) are AUTHID CURRENT_USER. APP_USER holds only
EXECUTE on them. The model follows Oracle's rules for each call: a definer's-rights unit switches
CURRENT_USER and CURRENT_SCHEMA to its owner and enables only the role PUBLIC; an invoker's-rights unit
keeps the caller's. It resolves the name, checks the privilege and prints the outcome. rights_demo.sql
makes the same calls in the database. Edit CALLS or GRANTS and run it again.
"""

TABLES = {("LAB", "ACCOUNTS"): {3: "760.25"}}  # (schema, table): {account_id: balance}
UNITS = {  # unit: (owner, AUTHID, the table name its query uses)
    "LAB.BALANCE_DR": ("LAB", "DEFINER", "accounts"),
    "LAB.BALANCE_IR": ("LAB", "CURRENT_USER", "accounts"),
    "LAB.BALANCE_IR_LAB": ("LAB", "CURRENT_USER", "lab.accounts"),
}
GRANTS = {("APP_USER", "EXECUTE", "LAB.BALANCE_DR"), ("APP_USER", "EXECUTE", "LAB.BALANCE_IR"),
          ("APP_USER", "EXECUTE", "LAB.BALANCE_IR_LAB")}


def can(user, privilege, obj):
    return obj.split(".")[0] == user or (user, privilege, obj) in GRANTS  # owners hold every privilege


def call(session_user, unit, account_id):
    owner, authid, name = UNITS[unit]
    if not can(session_user, "EXECUTE", unit):
        return session_user, "-", "PLS-00201: identifier must be declared"
    # DEFINER: the owner's identity and schema, only PUBLIC enabled; CURRENT_USER: the caller's, unchanged.
    current_user = owner if authid == "DEFINER" else session_user
    schema, table = name.upper().split(".") if "." in name else (current_user, name.upper())
    target = f"{schema}.{table}"
    if (schema, table) not in TABLES or not can(current_user, "SELECT", target):
        # Oracle gives the same error for a table that is missing and one the user may not read.
        return current_user, target, f'ORA-00942: table or view "{schema}"."{table}" does not exist'
    return current_user, target, f"balance {TABLES[(schema, table)][account_id]}"


CALLS = [
    ("APP_USER", "LAB.BALANCE_DR"),
    ("APP_USER", "LAB.BALANCE_IR"),
    ("APP_USER", "LAB.BALANCE_IR_LAB"),
    ("LAB", "LAB.BALANCE_IR"),
]
print("Each call reads the balance of account 3")
for who, unit in CALLS:
    current_user, target, outcome = call(who, unit, 3)
    print(f"  {who:<8} calls {unit:<18} runs as {current_user:<8} reads {target:<16} -> {outcome}")

GRANTS.add(("APP_USER", "SELECT", "LAB.ACCOUNTS"))
print("After GRANT SELECT ON lab.accounts TO app_user")
for who, unit in [("APP_USER", "LAB.BALANCE_IR_LAB")]:
    current_user, target, outcome = call(who, unit, 3)
    print(f"  {who:<8} calls {unit:<18} runs as {current_user:<8} reads {target:<16} -> {outcome}")

Each call reads the balance of account 3
  APP_USER calls LAB.BALANCE_DR     runs as LAB      reads LAB.ACCOUNTS     -> balance 760.25
  APP_USER calls LAB.BALANCE_IR     runs as APP_USER reads APP_USER.ACCOUNTS -> ORA-00942: table or view "APP_USER"."ACCOUNTS" does not exist
  APP_USER calls LAB.BALANCE_IR_LAB runs as APP_USER reads LAB.ACCOUNTS     -> ORA-00942: table or view "LAB"."ACCOUNTS" does not exist
  LAB      calls LAB.BALANCE_IR     runs as LAB      reads LAB.ACCOUNTS     -> balance 760.25
After GRANT SELECT ON lab.accounts TO app_user
  APP_USER calls LAB.BALANCE_IR_LAB runs as APP_USER reads LAB.ACCOUNTS     -> balance 760.25

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; PDB FREEPDB1. Client: SQL*Plus or SQLcl.
-- Definer's rights against invoker's rights for a second user. Step 1 runs as an administrator of the lab PDB,
-- step 2 as LAB, step 3 as APP_USER. The password is asked for at run time with ACCEPT ... HIDE, never written in the
-- script and never shown: SET VERIFY OFF keeps SQL*Plus from printing the line with the value put in.
-- examples/model/rights_model.py prints the same outcomes.
SET SERVEROUTPUT ON
SET VERIFY OFF

-- Step 1, as an administrator: a second user who may only connect.
ACCEPT app_user_password CHAR PROMPT 'Password for APP_USER: ' HIDE
CREATE USER app_user IDENTIFIED BY "&app_user_password";
GRANT CREATE SESSION TO app_user;

-- Step 2, as LAB: three procedures that read the balance of one account.
CREATE OR REPLACE PROCEDURE balance_dr (p_id IN NUMBER) AUTHID DEFINER IS
  v_balance accounts.balance%TYPE;
BEGIN
  SELECT balance INTO v_balance FROM accounts WHERE account_id = p_id;   -- runs as LAB: LAB.ACCOUNTS
  DBMS_OUTPUT.PUT_LINE('balance ' || v_balance);
END;
/
CREATE OR REPLACE PROCEDURE balance_ir (p_id IN NUMBER) AUTHID CURRENT_USER IS
  v_balance accounts.balance%TYPE;
BEGIN
  SELECT balance INTO v_balance FROM accounts WHERE account_id = p_id;   -- runs as the caller: caller.ACCOUNTS
  DBMS_OUTPUT.PUT_LINE('balance ' || v_balance);
END;
/
CREATE OR REPLACE PROCEDURE balance_ir_lab (p_id IN NUMBER) AUTHID CURRENT_USER IS
  v_balance lab.accounts.balance%TYPE;
BEGIN
  SELECT balance INTO v_balance FROM lab.accounts WHERE account_id = p_id;  -- LAB.ACCOUNTS, caller's privileges
  DBMS_OUTPUT.PUT_LINE('balance ' || v_balance);
END;
/
GRANT EXECUTE ON balance_dr TO app_user;
GRANT EXECUTE ON balance_ir TO app_user;
GRANT EXECUTE ON balance_ir_lab TO app_user;

-- Step 3, as APP_USER, who holds no privilege on any LAB table.
EXEC lab.balance_dr(3)
-- Expected: balance 760.25
EXEC lab.balance_ir(3)
-- Expected: ORA-00942: table or view "APP_USER"."ACCOUNTS" does not exist
EXEC lab.balance_ir_lab(3)
-- Expected: ORA-00942: table or view "LAB"."ACCOUNTS" does not exist (APP_USER may not read it)
SELECT balance FROM lab.accounts WHERE account_id = 3;
-- Expected: ORA-00942 as well: EXECUTE on balance_dr gave APP_USER no access to the table itself

-- Step 4, as an administrator, once APP_USER has disconnected: remove the lab user.
DROP USER app_user CASCADE;

"""rights_demo.sql in the lab, step by step, each in the client it names: an administrator (SYS in FREEPDB1),
LAB and APP_USER. The password of APP_USER is made for the run and given to the script's ACCEPT; it is
never shown."""

from oracle_session import Lab

STEP = r"^-- Step \d+, as (?P<who>an administrator|LAB|APP_USER)\b"
WHO = {"an administrator": ["admin"], "LAB": ["lab"], "APP_USER": ["app_user"]}

with Lab() as lab:
    typed = {"app_user_password": lab.new_password("app_user")}
    lab.steps("rights/rights_demo.sql", STEP, WHO, users={"admin": "sys", "lab": "lab", "app_user": "app_user"},
              typed=typed, close={4: ["app_user"]}, errors=3)

admin> Step 1, as an administrator: a second user who may only connect.
User created.


Grant succeeded.

lab> Step 2, as LAB: three procedures that read the balance of one account.
Procedure created.


Procedure created.


Procedure created.


Grant succeeded.


Grant succeeded.


Grant succeeded.

app_user> Step 3, as APP_USER, who holds no privilege on any LAB table.
balance 760.25

PL/SQL procedure successfully completed.

BEGIN lab.balance_ir(3); END;

*
ERROR at line 1:
ORA-00942: table or view "APP_USER"."ACCOUNTS" does not exist
ORA-06512: at "LAB.BALANCE_IR", line 4
ORA-06512: at line 1
Help: https://docs.oracle.com/error-help/db/ora-00942/


BEGIN lab.balance_ir_lab(3); END;

*
ERROR at line 1:
ORA-00942: table or view "LAB"."ACCOUNTS" does not exist
ORA-06512: at "LAB.BALANCE_IR_LAB", line 4
ORA-06512: at line 1
Help: https://docs.oracle.com/error-help/db/ora-00942/


SELECT balance FROM lab.accounts WHERE account_id = 3
                        *
ERROR at line 1:
ORA-00942: table or view "LAB"."ACCOUNTS" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/

admin> Step 4, as an administrator, once APP_USER has disconnected: remove the lab user.
User dropped.