To operate Oracle Database securely, it is important to grant users only the minimum required privileges. Leaving unnecessary privileges can increase security risks, such as potential misuse in external attacks or accidental modification of critical data. An effective solution for this is DBMS_PRIVILEGE_CAPTURE. This feature records which privileges were actually used, making it possible to distinguish between required and unnecessary ones.
This article provides an overview of the feature, detailed usage instructions, common troubleshooting points, and operational notes.
Step Overview (Quick Procedure)
The basic flow when using DBMS_PRIVILEGE_CAPTURE is as follows. Beginners should first grasp this outline before diving into details.
- Grant the
CAPTURE_ADMINprivilege to an administrative user. - Create a capture policy using
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE. - Start recording with
...ENABLE_CAPTURE, have the target user perform operations, then stop with...DISABLE_CAPTURE. - Run
...GENERATE_RESULTto finalize the result and check usage through dictionary views. - Revoke privileges determined to be unnecessary in stages. Always keep backups so you can re-grant them if needed.
What Is DBMS_PRIVILEGE_CAPTURE?
DBMS_PRIVILEGE_CAPTURE is a security feature provided by Oracle Database. It records which privileges granted to users or roles were actually used and which were not. This enables privilege minimization (least privilege) based on real operational data.
For example: “We granted SELECT to this user, but are they also using INSERT or UPDATE?” With DBMS_PRIVILEGE_CAPTURE, such questions can be answered with evidence. Based on the results, you can safely clean up privileges by revoking those not in use.
Types of Captures
DBMS_PRIVILEGE_CAPTURE offers multiple capture modes. Use them depending on your needs.
| Type | Target | Use Case |
|---|---|---|
G_DATABASE | Entire database (excluding SYS) | Inventory of privileges in use across the database |
G_ROLE | Sessions where a specified role is enabled | Verify whether application-specific roles are being used |
G_CONTEXT | Sessions matching a given condition | Narrow down to a specific user or module |
G_ROLE_AND_CONTEXT | Sessions meeting both role and condition | Targeted investigation of specific production applications |
The condition for
G_CONTEXTmust be defined usingSYS_CONTEXT. Example:SYS_CONTEXT('USERENV','SESSION_USER')='APP_USER'.
Prerequisites
Before using DBMS_PRIVILEGE_CAPTURE, ensure the following:
- Target version: Oracle Database 19c (non-CDB configuration)
- OS: Linux
- Required privilege: An administrative user with
CAPTURE_ADMIN - Limit: At most two policies can be enabled at the same time
- Note: The
SYSuser is excluded (its activity is not recorded)
If these conditions are not met, prepare your environment in advance.
Usage and Example
The following section demonstrates the actual steps. We create sample users, grant roles, and capture their privilege usage.
0. Preparing Test Users and Objects
Create sample users and a table to build an environment where privilege usage can be verified later.
-- As DBA
CREATE USER app_owner IDENTIFIED BY "AppOwner#1" QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE TO app_owner;
CREATE USER app_user IDENTIFIED BY "AppUser#1";
GRANT CREATE SESSION TO app_user;
CREATE ROLE r_app_read;
-- As app_owner, create a sample table
CONN app_owner/"AppOwner#1"
CREATE TABLE emp (
empno NUMBER PRIMARY KEY,
ename VARCHAR2(30)
);
INSERT INTO emp VALUES (7369, 'SMITH');
COMMIT;
-- Return to DBA and grant privileges to the role
CONN / AS SYSDBA
GRANT SELECT ON app_owner.emp TO r_app_read;
GRANT r_app_read TO app_user;
At this point, app_user does not have direct privileges but can access the emp table through the role.
1. Creating an Administrative User for Capture
CREATE USER secadmin IDENTIFIED BY "SecAdmin#1";
GRANT CREATE SESSION TO secadmin;
GRANT CAPTURE_ADMIN TO secadmin;
The secadmin user manages capture policies, enables/disables them, and checks results.
2. Creating a Capture Policy
Here, we limit the scope to a specific user (APP_USER).
CONN secadmin/"SecAdmin#1"
BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
name => 'PA_APPUSER',
type => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
condition => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''APP_USER'''
);
END;
/
3. Starting Capture and Performing Operations
BEGIN
DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE(name => 'PA_APPUSER');
END;
/
-- Operate as app_user
CONN app_user/"AppUser#1"
SELECT * FROM app_owner.emp WHERE empno = 7369;
-- Stop capture
CONN secadmin/"SecAdmin#1"
BEGIN
DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE(name => 'PA_APPUSER');
END;
/
4. Checking Results
BEGIN
DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT(name => 'PA_APPUSER');
END;
/
-- Privileges that were used
SELECT username, used_role, obj_priv, object_name
FROM dba_used_objprivs
WHERE capture = 'PA_APPUSER';
-- Privileges that were unused
SELECT username, rolename, obj_priv, object_name
FROM dba_unused_objprivs
WHERE capture = 'PA_APPUSER';
If entries appear in dba_used_objprivs, it means those privileges were actually exercised. If they appear in dba_unused_objprivs, those privileges were not used during the capture period.
Command execution example
SQL> set lin 1000 pages 1000
SQL> col username for a20
SQL> col used_role for a20
SQL> col obj_priv for a20
SQL> col object_name for a30
SQL> SELECT username, used_role, obj_priv, object_name
2 FROM dba_used_objprivs
3 WHERE capture = 'PA_APPUSER';
USERNAME USED_ROLE OBJ_PRIV OBJECT_NAME
-------------------- -------------------- -------------------- ------------------------------
APP_USER PUBLIC EXECUTE DBMS_APPLICATION_INFO
APP_USER R_APP_READ SELECT EMP
APP_USER PUBLIC SELECT DUAL
SQL> SELECT username, rolename, obj_priv, object_name
2 FROM dba_unused_objprivs
3 WHERE capture = 'PA_APPUSER';
no rows selected
5. Dropping the Policy (Cleanup)
After the investigation, drop the policy to remove unnecessary data.
BEGIN
DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE(name => 'PA_APPUSER');
END;
/
Troubleshooting
Common errors encountered when using DBMS_PRIVILEGE_CAPTURE are summarized below.
| Error | Cause | Check / Resolution |
|---|---|---|
| ORA-01031 | CAPTURE_ADMIN not granted | Run GRANT CAPTURE_ADMIN TO <username>; |
| ORA-47934 | Already two policies enabled | Check with SELECT name, enabled FROM dba_priv_captures; and disable unnecessary ones |
| Empty result | No operations performed / condition too strict | Relax condition or try again using G_DATABASE |
| No SYS results | By design | SYS user is excluded and must be considered during design |
Notes
- SYSAUX management: Results are stored in the SYSAUX tablespace. If left unattended, it may grow significantly. Clean up periodically.
- Interpretation of unused privileges: Even if privileges appear unused during the capture period, they may be needed for monthly or annual jobs. Ensure the observation window is long enough.
- Revocation risks: Before revoking privileges, always take backups. If problems arise, re-grant immediately.
- Operational considerations: In production, it is more efficient to enable/disable captures during specific events or releases rather than leaving them always on.
FAQ
Q1. How do I check only a specific user?
→ Use G_CONTEXT with the condition SYS_CONTEXT('USERENV','SESSION_USER')='APP_USER'.
Q2. Which dictionary views should I check?
→ Start with DBA_USED_OBJPRIVS and DBA_UNUSED_OBJPRIVS. If necessary, use “_PATH” views to trace grant paths.
Q3. Can I leave capture always enabled?
→ Technically possible, but not recommended as it increases SYSAUX usage. Run it for limited periods instead.
Q4. Are there differences in newer versions (e.g., 23ai)?
→ The core functionality is the same, but documentation structure and view specifications may change. Always check version-specific manuals.
Q5. Can privilege usage through roles be identified?
→ Yes. The USED_ROLE column shows whether privileges were exercised via a role, distinguishing direct grants from role-based usage.
Summary
- DBMS_PRIVILEGE_CAPTURE enables you to clearly identify used and unused privileges.
- To narrow down to specific users or modules, use
G_CONTEXT. - At most two policies can be enabled at the same time.
- SYS user is excluded and must be considered in design.
- Ensure long enough observation periods, safely revoke unnecessary privileges, and manage SYSAUX regularly.
This article targets Oracle Database 19c. Screens and specifications may differ in other versions.
[reference]
Performing Privilege Analysis to Identify Privilege Use
[Read this article in Japanese]
Oracle DBMS_PRIVILEGE_CAPTUREによる権限チェック入門 | オラクルマスターを取得しよう
コメント