Wednesday, 7 August 2013

Data Pump, SYS account and non working jobs

Lately I have had an issue with a load framework, which stopped working after moving the whole thing to a test environment. From the beginning it was not clear why it does not work. The reason provided in the DBA_SCHEDULER_JOB_RUN_DETAILS was exactly that the job was stopped because it was terminated, so not really helpful :-).
However, every trace indicated that jobs throw the ORA-28031 exception (maximum of 148 enabled roles exceeded).

The only role with such a number of roles was the SYS account. Apparently a job process during a piece of its lifetime is run on the SYS privileges. So the processes had been started and almost immediately terminated. And this actually explains everything. The only mystery left was why the SYS account get so many roles.
I am not sure of the exact scenario, but:
  • we have on these databases the quite numerous quantity of custom roles and exactly those roles were assigned to the SYS account
  • the migration was done with the Data Pump tools
  • the import was done on the SYS privileges and apparently then all those roles were assigned
  • there was no explicit operation of assigning those roles to the SYS schema
  • it is possible in the impdp parameters there was added the exclusion of default roles, which are enabled on the SYS by default
The main solution was to revoke all those unnecessary roles. To make the framework work other adjustments were also required, but rather obvious.

No comments: