Help with Revoke Grant DBMS_SCHEDULER job
Hey all,
I am running 19.22 on RHEL 8 and I have created a daily job/procedure to revoke grants from a role. The DBMS_SCHEDULER job runs as SYS, but the REVOKE GRANTS created are on a different schema (STUDENT). The job works fine with the exception of actually executing the 'REVOKE GRANT' statements at EXECUTE IMMEDIATE SQL_READ; I get the following error:
ORA-01927: cannot REVOKE privileges you did not grant
ORA-06512: at "SYS.BUILD_REVOKE_FROM_STUDENT_RO_ROLE", line 31
ORA-06512: at "SYS.BUILD_REVOKE_FROM_STUDENT_RO_ROLE", line 31
ORA-06512: at line 1
The STUDENT_RO_ROLE gets reprocessed with a different dbms job everyday because new tables are added weekly. This job revokes specific tables from STUDENT_RO_ROLE that contain PII.