PL/SQL Programs Require Direct Grants...
In order for a PL/SQL program to compile, the schema in which the program resides must have a direct grant to any database object referenced by the program. In other words, a role is not sufficient. This is true whether the program uses invoker rights or definer rights.
Per the Oracle Documentation
Roles ease the administration of end-user system and schema object privileges. However, roles are not meant to be used by application developers, because the privileges to access schema objects within stored programmatic constructs must be granted directly.
...With One Exception (that I'm aware of)
The exception is when it comes to accessing a remote database. If scott has a database link between instanceA and instanceB, and on instanceB scott has been granted a ROLE with select access to tableT somewhere on instanceB, then on instanceA a program in the scott schema that references tableT across the link will compile, even though scott has never been granted direct access to tableT.
No comments:
Post a Comment