DBA Hub

📋Steps in this guide1/4

Control Invoker Rights Privileges in Views in Oracle Database 12c Release 1 (12.1) (BEQUEATH CURRENT_USER)

This article describes support for invoker rights function calls in views in Oracle Database 12c Release 1.

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

Setup

Create the following users to support the examples below.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
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;
2

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 (the default) and clauses are added to a basic view querying the view. When both views are queried from the view owner, they both produce the same output, showing the two views that were created. Remember, is the same as not including the clause. It is used explicitly here for clarity. If we switch to the second user, no objects are displayed. Regardless of the clause setting, the results are based on the current user. Remember, these settings do not affect basic view usage. They only affect the behaviour of invoker rights functions called inside the view.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
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>

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>
3

Identifying the Invoking User

The and functions are used to determine the invoking username and userid respectively. These functions are used in the example below.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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>
4

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 clause, signifying it is an invoker rights function. The following two views query the invoker rights function, but have differing clauses. Both views produce the same output when queried from the view owner, since the invoking user matches the view owner. 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. So the default interaction between views and invoker rights functions is unchanged in Oracle Database 12c, but the addition of the clause in the view definition allows it to honour the invoker rights. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
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;
/

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;

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>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!