8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Control Invoker Rights Privileges in Views in Oracle Database 12c Release 1 (12.1) (BEQUEATH CURRENT_USER)
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
- What Invoker Rights Views Do Not Do!
- Identifying the Invoking User
- Invoker Rights Views Containing Invoker Rights Functions
Related articles.
- Code Based Access Control (CBAC) : Granting Roles to PL/SQL Program Units in Oracle Database 12 Release 1 (12.1)
- Control Invoker Rights Privileges for PL/SQL Code in Oracle Database 12c Release 1 (12.1) (INHERIT [ANY] PRIVILEGES)
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.
For more information see:
- Controlling Definer's Rights and Invoker's Rights in Views
- Code Based Access Control (CBAC) : Granting Roles to PL/SQL Program Units in Oracle Database 12 Release 1 (12.1)
- Control Invoker Rights Privileges for PL/SQL Code in Oracle Database 12c Release 1 (12.1) (INHERIT [ANY] PRIVILEGES)
Hope this helps. Regards Tim...