Control Invoker Rights Privileges in Views in Oracle Database 12c

In previous versions of the database, calling invoker rights functions within a view made the functions run in the context of the view owner, essentially breaking the invoker rights functionality. Oracle 12c Release 1 introduced proper support for invoker rights function calls in views for the first time.

◉ Setup

Create the following users to support the examples below.

CONN sys@pdb1 AS SYSDBA

DROP USER user1 CASCADE;
DROP USER user2 CASCADE;

CREATE USER user1 IDENTIFIED BY user1;
GRANT CREATE SESSION, CREATE VIEW, CREATE PROCEDURE TO user1;

CREATE USER user2 IDENTIFIED BY user2;
GRANT CREATE SESSION TO user2;

◉ What Invoker Rights Views Do Not Do!

The main thing to note about the use of invoker rights in a view is it does not affect the way the basic view works. It only affects how invoker rights functions called within the view work. In the following example the BEQUEATH DEFINER (the default) and BEQUEATH CURRENT_USER clauses are added to a basic view querying the USER_VIEWS view. When both views are queried from the view owner, they both produce the same output, showing the two views that were created.

CONN user1/user1@pdb1

CREATE OR REPLACE VIEW user_views_def_v BEQUEATH DEFINER AS
  SELECT view_name, bequeath
  FROM   user_views
  ORDER BY view_name;

CREATE OR REPLACE VIEW user_views_cu_v BEQUEATH CURRENT_USER AS
  SELECT view_name, bequeath
  FROM   user_views
  ORDER BY view_name;

GRANT SELECT ON user_views_def_v TO user2;
GRANT SELECT ON user_views_cu_v TO user2;

COLUMN view_name FORMAT A30

SELECT *
FROM   user1.user_views_def_v;

VIEW_NAME                      BEQUEATH
------------------------------ ------------
USER_VIEWS_CU_V                CURRENT_USER
USER_VIEWS_DEF_V               DEFINER

2 rows selected.

SQL>

SELECT *
FROM   user1.user_views_cu_v;

VIEW_NAME                      BEQUEATH
------------------------------ ------------
USER_VIEWS_CU_V                CURRENT_USER
USER_VIEWS_DEF_V               DEFINER

2 rows selected.

SQL>

Remember, BEQUEATH DEFINER is the same as not including the BEQUEATH clause. It is used explicitly here for clarity.

If we switch to the second user, no objects are displayed. Regardless of the BEQUEATH clause setting, the results are based on the current user.

CONN user2/user2@pdb1

SELECT *
FROM   user1.user_views_def_v;

no rows selected

SQL>

SELECT *
FROM   user1.user_views_cu_v;

no rows selected

SQL>

Remember, these settings do not affect basic view usage. They only affect the behaviour of invoker rights functions called inside the view.

◉ Identifying the Invoking User

The ORA_INVOKING_USER and ORA_INVOKING_USERID functions are used to determine the invoking username and userid respectively.

CONN user1/user1@pdb1

COLUMN ora_invoking_user FORMAT A20
COLUMN ora_invoking_userid FORMAT A20

SELECT ora_invoking_user,
       ora_invoking_userid
FROM   dual;

ORA_INVOKING_USER    ORA_INVOKING_USERID
-------------------- --------------------
USER1                111

1 row selected.

SQL>

These functions are used in the example below.

◉ Invoker Rights Views Containing Invoker Rights Functions

To see the functionality in action, we need to create an invoker rights function. The following function returns information about the invoking user. Notice the AUTHID CURRENT_USER clause, signifying it is an invoker rights function.

CREATE OR REPLACE FUNCTION get_invoking_user
  RETURN VARCHAR2
  AUTHID CURRENT_USER
AS
  l_result VARCHAR2(100);
BEGIN
  SELECT ora_invoking_user || ':' || ora_invoking_userid
  INTO   l_result
  FROM   dual;
 
  RETURN l_result;
END;
/

The following two views query the invoker rights function, but have differing BEQUEATH clauses.

CREATE OR REPLACE VIEW get_invoking_user_def_v BEQUEATH DEFINER AS
SELECT get_invoking_user AS invoking_user
FROM   dual;

CREATE OR REPLACE VIEW get_invoking_user_cu_v BEQUEATH CURRENT_USER AS
SELECT get_invoking_user AS invoking_user
FROM   dual;

GRANT SELECT ON get_invoking_user_def_v TO user2;
GRANT SELECT ON get_invoking_user_cu_v TO user2;

Both views produce the same output when queried from the view owner, since the invoking user matches the view owner.

SELECT *
FROM   user1.get_invoking_user_def_v;

INVOKING_USER
----------------------------------------------------------------------------------------------------
USER1:111

1 row selected.

SQL>

SELECT *
FROM   user1.get_invoking_user_cu_v;

INVOKING_USER
----------------------------------------------------------------------------------------------------
USER1:111

1 row selected.

SQL>

When queried from the second user, we can see the definer rights view runs the invoker rights function in the context of the view owner, just like it would in previous versions of the database. In contrast, the invoker rights view calls the invoker rights function in the context of the actual invoker.

CONN user2/user2@pdb1

SELECT *
FROM   user1.get_invoking_user_def_v;

INVOKING_USER
----------------------------------------------------------------------------------------------------
USER1:111

1 row selected.

SQL>

SELECT *
FROM   user1.get_invoking_user_cu_v;

INVOKING_USER
----------------------------------------------------------------------------------------------------
USER2:112

1 row selected.

SQL>

So the default interaction between views and invoker rights functions is unchanged in Oracle Database 12c, but the addition of the BEQUEATH CURRENT_USER clause in the view definition allows it to honour the invoker rights.