CODEWITHSUNDEEP
oracle-database
Ora_New
Ora_New

Reputation: 21

Oracle - Grant Create Job privilege to role

Oracle - Grant Create Job privilege to role

Login user = JOEL

User JOEL got error "ORA-27486: insufficient privileges ORA-06512: at 
"SYS.DBMS_ISCHED", line 124 ORA-06512: at "SYS.DBMS_SCHEDULER", line 271 ORA- 
06512: at line 1"

To resolve the above error, grant JOEL the following privilege: Grant Create Job to JOEL; Remarks: By granting Create Job to JOEL, this resolved the error above.

But, instead of granting "Create Job" directly to user JOEL, grant the 
privilege by ROLE as follow:

Create Role Job_Role;
Grant Create Job to Job_Role;
Grant Job_Role to JOEL;

Remarks: User JOEL got the same error even granted with Job_Role

May i know why granting role is not working? Thank you.

Upvotes: 2

Views: 26706

Answers (1)

wolφi
wolφi

Reputation: 8361

Yes, it will not work with the privilege CREATE JOB granted via a role, as stated in the documentation:

"Object privileges must be granted directly to the user." (source)

As to why Oracle implemented it that way, one can only guess that DBMS_SCHEDULER is implemented at least partly in a PL/SQL package. Inside stored procedures of all kind, roles are (in)famously disabled.

Upvotes: 2

Related Questions