Which SQL-based mechanism prevents a privilege from being granted indirectly…

Concept

In SQL authorisation a privilege can reach a user along two paths: a direct GRANT made to that user, or an indirect path in which the privilege sits inside a role — and because a role may itself be granted to another role, the privilege can travel down a whole chain of roles before it arrives. Closing that indirect path therefore means either refusing to let such a role be nested inside another role, or refusing to let it become active outside a sanctioned code path. Integrity features work on a different axis altogether (which values a row may store), while a grant-propagation clause works in the opposite direction, widening how far an already-granted right may travel.

Applying it here

The mechanism defined with both of those restrictions built in is the secure application role — a role created with an authorising PL/SQL package named in it, as in CREATE ROLE hr_app_role IDENTIFIED USING hr.security_pkg. Oracle's GRANT reference states that an IDENTIFIED BY, IDENTIFIED USING or IDENTIFIED EXTERNALLY role cannot be granted to another role, so its bundled privileges can never be picked up by holding some other role that contains it; the nesting is refused at grant time. The same definition also puts activation under program control: the database switches the role on for a session only when the named package does so, typically by calling DBMS_SESSION.SET_ROLE after verifying the conditions the application defines. A user who merely holds the role therefore gains nothing by connecting with SQL*Plus or any other ad-hoc tool.

Contrast with the other mechanisms

• Mandatory constraints (NOT NULL and similar declared rules) restrict the values a column may store, and the database applies them identically to every session whatever privileges or roles that session holds.

• A CASCADE DELETE constraint is a referential action on a foreign key: deleting a parent row automatically deletes its dependent child rows. It propagates data changes between tables, not authority between users.

• WITH ADMIN OPTION is a clause on GRANT <role> TO <grantee>; Oracle's reference defines it as enabling the grantee to grant that role onward to another user or role, so it widens the indirect path instead of closing it.

Of the four mechanisms offered, the secure application role is the one whose very definition blocks the indirect route: it cannot be nested inside another role, and it becomes usable only when its authorising package enables it.