Access and Privilege Issues

Troubleshoot issues with data grant enforcement, role propagation, and user access to secured database objects.

Topics:

• End User Denied Access to Secured Data

• Data Grants Not Enforced

• Data Role Not Effective in End-User Session

End User Denied Access to Secured Data

If an end user cannot access data they are expected to be authorized to access, the user may not be provisioned correctly, or the required data grants or data roles may not be in effect.

Issue description

An end user cannot view or modify data they should have access to. Queries against tables or views protected by Oracle Deep Data Security (Deep Sec) return no rows, return fewer rows than expected, or the database raises an access-related error.

Probable causes

• The end user has not been created as a local end user in the database or as an external end user in the IAM system, or a data role in the database has not been mapped to the end user’s IAM application role.

• No data grant exists on the target object for the affected end user or data role.

• The data grant exists but specifies an incorrect privilege, predicate, or grantee.

• The required data roles are not enabled in the current end-user security context.

• The data grant predicate references context attributes whose current values do not satisfy the predicate condition.

Resolution procedure

1. Confirm that the end user is correctly provisioned. Query the DBA_END_USERS view for local end users, or verify provisioning in the IAM system for external end users.

2. Query the DBA_DATA_GRANTS view and verify that a data grant exists for the target object, grants the required privilege (SELECT, UPDATE, INSERT, or DELETE), and names the correct end user or data role as the grantee.

3. If data roles mediate the grant, query DBA_DATA_ROLES and DBA_DATA_ROLE_GRANTS to confirm that the expected data roles and database roles are granted to the data role.

4. Verify that the correct data roles are enabled in the session by querying V$END_USER_DATA_ROLE from within the affected session, or by querying DBA_END_USER_SECURITY_CONTEXT_DATA_ROLES as a database administrator (DBA) after obtaining the context identifier (CONTEXT_ID) from DBA_END_USER_SECURITY_CONTEXTS.

5. If the data grant includes a WHERE predicate that references context attributes, query END_USER_CONTEXT from within the session (or DBA_END_USER_SECURITY_CONTEXT_ATTRIBUTES as a DBA) and confirm that the attribute values satisfy the predicate logic.

6. If the issue persists, enable diagnostic tracing and examine the trace output. See Enable Diagnostic Tracing.

Data Grants Not Enforced

If data grants do not restrict or permit access as expected, the grant configuration may be incorrect.

Issue description

Data grants defined on a table or view do not restrict or permit data access as expected. End users see more or fewer rows than the data grant predicate specifies, end users see actual values for more or fewer columns than the data grant specifies, or the data grant appears to have no effect on query results.

Probable causes

The data grant targets the wrong object or column(s), specifies an incorrect privilege or predicate, or names the wrong grantee.

Resolution procedure

1. Query the DBA_DATA_GRANTS view and verify that each data grant targets the correct object and column(s), specifies the intended privilege, includes the correct WHERE predicate, and names the correct grantee.

2. If the issue persists, enable diagnostic tracing and examine the trace output. See Enable Diagnostic Tracing.

Data Role Not Effective in End-User Session

If a granted data role does not take effect during a session, the grant may be outside its valid time window, an intermediate data role may not be enabled, or the IAM role mapping may be incorrect.

Issue description

A data role that is directly or indirectly granted to an end user does not take effect during the session. The end user does not receive the data access associated with that data role.

Probable causes

• The data role grant has a START_TIME or END_TIME constraint, and the current time falls outside the valid window.

• The data role is granted indirectly through other data roles, and one or more intermediate data roles are not enabled in the session.

• For externally managed end users, the data role in the database is not correctly mapped to the IAM application role.

Resolution procedure

1. Query the DBA_DATA_ROLES and DBA_DATA_ROLE_GRANTS views. Confirm that the START_TIME and END_TIME values are properly set and that the current time falls within the valid window.

2. If the data role is granted indirectly (through other data roles), obtain the end user’s context ID from DBA_END_USER_SECURITY_CONTEXTS and query DBA_END_USER_SECURITY_CONTEXT_DATA_ROLES to verify that the intermediate data roles are enabled in the session.

3. For externally managed end users, verify that the IAM application role is correctly mapped to the data role in the database. See Data Role in the Database Is Not Mapped to IAM Application Role.
4. If the issue persists, enable diagnostic tracing and examine the trace output. See Enable Diagnostic Tracing.