Recently, we had a client run into an issue where certain users were being assigned roles, but entitlements were not being assigned according to the access policies. In this case, the roles, access policies, and scheduled jobs were configured properly, and there were no errors appearing in the logs. The access policies were not being evaluated due to an error with the user_provisioning_attrs table in the database.
When the Evaluate User Policies scheduled job runs, it checks this table to determine which users need to be evaluated (determined by the POLICY_EVAL_NEEDED field), and whether or not a current evaluation is in progress (determined by the POLICY_EVAL_IN_PROGRESS field).
In ground state, both fields are set to 0. When a change is made to a user’s account, POLICY_EVAL_NEEDED is updated from 0 to 1. The Evaluate User Policies scheduled job runs, recognizes the user needs to be evaluated, and updates POLICY_EVAL_IN_PROGRESS from 0 to 1. It then performs the evaluation, the appropriate entitlements are provisioned, and both fields are set back to 0.
If POLICY_EVAL_NEEDED is set to 0, or POLICY_EVAL_IN_PROGRESS is set to 1, the Evaluate User Policies scheduled job will not evaluate the user.
To check the state of both attributes, use the below query:
select * from user_provisioning_attrs where usr_key=(select usr_key from usr where usr_login='<user login>’);
In some instances, errors can cause POLICY_EVAL_NEEDED to fail to update from 0 to 1, as was the case with our client. In this scenario, manually updating POLICY_EVAL_NEEDED allowed the Evaluate User Policies to properly evaluate the users, and provision entitlements accordingly.
update user_provisioning_attrs set POLICY_EVAL_NEEDED = 1, UPDATE_DATE = sysdate where usr_key=(select usr_key from usr where usr_login='<user login>’);
It is possible for both flags to be set to 1, either because a server was shut down during the evaluation, or due to a runtime exception being thrown. In this scenario, simply update the POLICY_EVAL_IN_PROGRESS flag to 0, and re-run the Evaluate User Policies scheduled job.
*Please note, we do not recommend making direct database modifications except when in critical need and other solutions are not viable (as when these race conditions occur). Directly modifying the database carries inherent risks, and is not supported by Oracle.
For more information, please see Evaluating User Policies in Oracle documentation.