Greetings,
I have a schema that lost privileges it had to execute a pkg.procedure from another schema. Re-granting role and granting specific privilege directly to user failed to allow the schema to execute the pkg.procedure.
Originally the schema was granted a Role which had privileges to execute targeted schema.pkg. the Packages/Procedures were compiled using AUTHID CURRENT_USER. Public synonym also used.
Granting execute directly to the schema that lost privileges (not all privileges however) failed.
The schema was exported, then imported with remap_schema to a new schema name. The user/ID for the new schema was created first with same code that created the original schema (grants, roles). The new schema did NOT have privilege problems, it was able to execute the targeted SQL.
I am looking for causes of the unexplained privilege loss, and why the granting of privileges to this 1 specific schema failed.
Thanks for your time,