Using BEQUEATH CURRENT_USER Views

Traditionally, views in Oracle Database use definer’s rights. This means that if you invoke an identity or privilege-sensitive SQL function or an invoker’s rights PL/SQL or Java function, then current schema, and current user, are set to the view owner and currently enabled roles is set to the view owner plus PUBLIC within the functions’s execution.

If you need background information on invoker’s rights and definer’s rights, see Oracle AI Database PL/SQL Language Reference.

Note: Certain built-in SQL functions, such as SYS_CONTEXT() and USERENV() are exceptions to the preceding rule. These functions always use the current application user’s environment, even when called from definer’s rights views.

Oracle Database 12c Release 1 (12.1) and later enables you to create views with the BEQUEATH clause, which lets you configure this behavior. The BEQUEATH clause determines whether identity or privilege-sensitive SQL functions, invoker’s rights PL/SQL program units, and Java functions referenced in the view inherit the current schema, current user, and currently enabled roles from the querying user’s environment. This is especially useful for Real Application Security applications, which often need to run code in the invoking application user’s environment.

Using BEQUEATH CURRENT_USER in the view definition creates a view that allows privilege-sensitive, and invoker’s rights functions referenced in the view to inherit current schema, current user, and currently enabled roles from the querying user’s environment. See Oracle AI Database SQL Language Reference for the syntax of the CREATE OR REPLACE VIEW statement.

Example: How a BEQUEATH CURRENT_USER View Works illustrates how a BEQUEATH CURRENT_USER view enables invoker right’s program units to run in the invoking application user’s environment. When USER2 selects from USER1’s view, the invoker’s rights function is invoked in USER2’s environment.

Using BEQUEATH DEFINER in the view definition creates a view that causes privilege-sensitive, and invoker’s rights functions referenced in the view to inherit current schema, current user, and currently enabled roles from the view definer’s environment. If no BEQUEATH clause is specified, then BEQUEATH DEFINER is assumed.

If a BEQUEATH_DEFINER view contains a reference to a BEQUEATH CURRENT_USER view, then invoker’s rights functions in the referenced view would use the parent view owner’s rights.

Example: How a BEQUEATH DEFINER View Works illustrates how a BEQUEATH DEFINER view defines a boundary for nested invoker right’s program units to run in the view owner’s environment. When USER2 selects from USER1’s view, the view’s invoker’s rights function is invoked in USER1’s environment.

See Also: Oracle AI Database Security Guide for the use of invoker’s rights and definer’s rights in VPD and FGA policies

Example: How a BEQUEATH CURRENT_USER View Works

SQL> CONNECT USER1/USER1
Connected.
SQL>
SQL> -- You first create an invoker's rights function to determine who the current SQL> -- user really is.
SQL> CREATE OR REPLACE FUNCTION CALLED_AS_USER RETURN VARCHAR2 AUTHID CURRENT_USER IS
2 BEGIN
3 RETURN SYS_CONTEXT('USERENV', 'CURRENT_USER');
4 END;
5 /
Function created.

SQL> -- Note that you do not need to grant EXECUTE to called_as_user, because even
SQL> -- BEQUEATH CURRENT_USER views do name resolution and privilege checking on
SQL> -- the references present in the view body using definer's rights.

SQL> CREATE OR REPLACE VIEW BEQUEATH_INVOKER_VIEW BEQUEATH CURRENT_USER AS
2 SELECT CALLED_AS_USER FROM DUAL;
View created.

SQL> GRANT SELECT ON BEQUEATH_INVOKER_VIEW TO PUBLIC;
Grant succeeded.

SQL> CONNECT USER2/USER2
Connected.

SQL> SELECT * FROM USER1.BEQUEATH_INVOKER_VIEW;
CALLED_AS_USER
--------------------------------------------------------------------------------
USER2

Example: How a BEQUEATH DEFINER View Works

SQL> CONNECT USER1/USER1
Connected.
SQL>
SQL> -- You first create an invoker's rights function to determine who the current SQL> -- user really is.
SQL> CREATE OR REPLACE FUNCTION CALLED_AS_USER RETURN VARCHAR2 AUTHID CURRENT_USER IS
2 BEGIN
3 RETURN SYS_CONTEXT('USERENV', 'CURRENT_USER');
4 END;
5 /
Function created.

SQL> -- Note that you do not need to grant EXECUTE to called_as_user, because even
SQL> -- BEQUEATH CURRENT_USER views do name resolution and privilege checking on
SQL> -- the references present in the view body using definer's rights.

SQL> CREATE OR REPLACE VIEW BEQUEATH_DEFINER_VIEW BEQUEATH DEFINER AS
2 SELECT CALLED_AS_USER FROM DUAL;
View created.

SQL> GRANT SELECT ON BEQUEATH_DEFINER_VIEW TO PUBLIC;
Grant succeeded.

SQL> CONNECT USER2/USER2
Connected.

SQL> SELECT * FROM USER1.BEQUEATH_DEFINER_VIEW;
CALLED_AS_USER
--------------------------------------------------------------------------------
USER1

This section includes the following topic: Using SQL Functions to Determine the Invoking Application User.

Using SQL Functions to Determine the Invoking Application User

SQL functions, such as SYS_CONTEXT() and USERENV(), and XS_SYS_CONTEXT(), always return the current application user’s environment, even when called from definer’s rights views. Sometimes, applications need to determine the invoking application user based on the security context (BEQUEATH property) of views referenced in the statement.

The following new functions introduced in Oracle Database 12c Release 1 (12.1) enable you to figure out the invoking application user taking into account the BEQUEATH property of views referenced in the statement:

The following example shows a database user USER1 querying ORA_INVOKING_USER and ORA_INVOKING_XS_USER. ORA_INVOKING_XS_USER returns NULL, as the user is not a Real Application security application user.

SQL> CONNECT USER1
Enter password:
Connected.
SQL> SELECT ORA_INVOKING_USER FROM DUAL;

ORA_INVOKING_USER
--------------------------------------------------------------------------------
USER1

SQL> SELECT ORA_INVOKING_XS_USER FROM DUAL;

ORA_INVOKING_XS_USER
--------------------------------------------------------------------------------

See Also: