Read-Only Table Access
Overview
At checkout the broker creates the requesting user if needed, then grants SELECT on a specific table. At checkin it revokes that SELECT. Use this for scoped, read-only data access rather than full DBA escalation.
Before You Begin
- The Access Broker is deployed and connected
- Oracle SQL*Plus on the broker host and TNS connectivity (default port
1521) - A service account granted:
SELECT ON dba_users,CREATE USER,ALTER USER, andSELECT ON <schema>.<table> ... WITH GRANT OPTION
Environment Variables
| Variable | Notes |
|---|---|
user | Injected — target username |
table | Injected — target table to grant SELECT on |
DB_HOST, DB_PORT, DB_SERVICE_NAME | Connection details |
DB_USER, DB_PASS | Service account credentials |
Checkout / Checkin
Full scripts: db_readonly_checkout.sh · db_readonly_checkin.sh
# checkout — grant SELECT on the target table
GRANT SELECT ON $table TO $USERNAME;
# checkin — revoke it
REVOKE SELECT ON $table FROM $USERNAME;Configure in Britive
Create the permission
Resource Manager → Resource Type Permissions → New Permission. Language = Shell. Paste both scripts. Declare table, DB_HOST, DB_PORT, DB_SERVICE_NAME, DB_USER, DB_PASS (user is system-defined).
Create a profile and policy
Create a profile (e.g. 2h), add the permission, set the table variable value, and add a policy assigning members by tag.
Verify
-- as the checked-out user
SELECT COUNT(*) FROM <schema>.<table>; -- succeeds during checkout
-- after checkin: ORA-00942: table or view does not exist (no privilege)Troubleshoot
| Symptom | Cause | Fix |
|---|---|---|
ORA-01031 granting SELECT | Service account lacks GRANT OPTION on the table | GRANT SELECT ON <schema>.<table> TO <svc> WITH GRANT OPTION |
| Wrong table granted | table variable misconfigured | Set the table value on the profile permission |
Next Steps
Last updated on