Oh, the challenges of implementing VPD on a mature system!
Like partitioning and other major features, implementing VPD on a “mature”* database that has been “evolving”* over many years can be nowhere near as simple as designing such a solution from scratch. (* “Mature” databases have not necessarily “evolved” in a way that you might recommend. Best practice might not apply.)
Anyway, I’ve been intending to write a case study of my current VPD implementation, dealing with some detail of the buy versus build decision of VPD versus OLS and the subsequent challenges and incompatibilities with various features. As usual, that article awaits but I wanted to kick off a mini-series on the various difficulties experienced (and this series might then help the coming together of a single article on the whole experience).
Challenge I
The challenge illustrated here is VPD + Result Cache.
Spoiler: For those who don’t want to read the whole article (it’s a bit long and the formatting which doesn’t help), the two work together but beware the subtle differences in behaviour between the SQL and PL/SQL Result Cache – read the documentation for each.
Quick Background
First a bit of background information which may help put these snippets of info into some sort of context.
- As mentioned, this is a database that has been around for quite a few years now.
- There has been a simple requirement to restrict access such that:
- Users in the US can only see US data.
- Users in the Rest of the World (ROW) cannot see US data.
- VPD, OLS and a separate database were the options considered with VPD considered the only practical solution for one reason or another.
Demo
Let’s get into it.
Step 1: First up, script to create users for this demo:
- User to own table – APP_DATA
- User to own manage VPD security – SECURITY_MGR
- User to simulate app user with US visibility
- User to simulate app user with ROW visibility
-- Schema to hold some data
CREATE USER APP_DATA
IDENTIFIED BY password
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS
ACCOUNT UNLOCK;
GRANT CREATE SESSION TO app_data;
GRANT CREATE TABLE TO app_data;
GRANT CREATE PROCEDURE TO app_data;
-- Schema to manage security policies
CREATE USER SECURITY_MGR
IDENTIFIED BY password
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS
ACCOUNT UNLOCK;
GRANT EXEMPT ACCESS POLICY TO security_mgr;
GRANT CREATE SESSION TO security_mgr;
GRANT CREATE TABLE TO security_mgr;
GRANT CREATE ANY CONTEXT TO security_mgr;
GRANT CREATE TRIGGER TO security_mgr;
GRANT ADMINISTER DATABASE TRIGGER TO security_mgr;
GRANT SELECT ANY TABLE TO security_mgr;
GRANT ALTER ANY TABLE TO security_mgr;
GRANT UPDATE ANY TABLE TO security_mgr;
GRANT EXECUTE ON DBMS_RLS TO security_mgr;
GRANT CREATE ANY TRIGGER TO security_mgr;
GRANT ADMINISTER DATABASE TRIGGER TO security_mgr;
-- User to see only US data
CREATE USER APP_USER_US
IDENTIFIED BY password
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS
ACCOUNT UNLOCK;
GRANT CREATE SESSION TO app_user_us;
-- User to see only non-US data
CREATE USER APP_USER_ROW
IDENTIFIED BY password
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS
ACCOUNT UNLOCK;
GRANT CREATE SESSION TO app_user_row;
Step 2: Next up, script to create table and data for this demo. It’s hardly imaginative nor a real world example…
conn app_data/password@dom11g
DROP TABLE cars;
CREATE TABLE cars
(id NUMBER NOT NULL
,make VARCHAR2(48) NOT NULL
,security_label VARCHAR2(6) NOT NULL
,CONSTRAINT pk_cars PRIMARY KEY (id));
INSERT INTO app_data.cars
VALUES (1,'Audi','ROW');
INSERT INTO app_data.cars
VALUES (2,'Buick','US');
INSERT INTO app_data.cars
VALUES (3,'BMW','ROW');
INSERT INTO app_data.cars
VALUES (4,'Chrysler','US');
INSERT INTO app_data.cars
VALUES (5,'Citroen','US');
INSERT INTO app_data.cars
VALUES (6,'Dodge','US');
COMMIT;
GRANT SELECT ON cars TO app_user_us, app_user_row;
Step 3: Create vpd policies including
- Create application context
- Create package to manage context
- Create logon trigger to set contexts
- Create policy function that will apply predicates to policied tables
- Apply policy to table
conn security_mgr/password@dom11g
CREATE CONTEXT security_ctx USING security_pkg;
CREATE OR REPLACE PACKAGE security_pkg
AS
--
PROCEDURE p_initialise_user;
--
END security_pkg;
/
CREATE OR REPLACE PACKAGE BODY security_pkg
AS
k_namespace CONSTANT VARCHAR2(12) := 'SECURITY_CTX';
--
PROCEDURE p_set_context (
i_attribute IN VARCHAR2,
i_value IN VARCHAR2
)
AS
BEGIN
--
dbms_session.set_context(k_namespace,i_attribute,i_value);
--
END p_set_context;
--
PROCEDURE p_set_us_context
AS
BEGIN
--
p_set_context('CAN_SEE_US','Y');
--
END p_set_us_context;
--
PROCEDURE p_set_row_context
AS
BEGIN
--
p_set_context('CAN_SEE_ROW','Y');
--
END p_set_row_context;
--
PROCEDURE p_initialise_user
AS
BEGIN
--
IF USER = 'APP_USER_US'
THEN
--
p_set_us_context;
--
ELSIF USER = 'APP_USER_ROW'
THEN
--
p_set_row_context;
--
ELSE
--
p_set_us_context;
p_set_row_context;
--
END IF;
--
END p_initialise_user;
--
END security_pkg;
/
CREATE OR REPLACE TRIGGER tri_logon_set_rls_security
AFTER LOGON ON DATABASE
BEGIN
--
security_pkg.p_initialise_user;
--
END tri_logon_set_rls_security;
/
CREATE OR REPLACE FUNCTION f_security_policy (
i_schema IN VARCHAR2 DEFAULT NULL,
i_object IN VARCHAR2 DEFAULT NULL
)
RETURN VARCHAR2
AS
BEGIN
--
RETURN CASE WHEN SYS_CONTEXT('SECURITY_CTX','CAN_SEE_US') = 'Y'
AND SYS_CONTEXT('SECURITY_CTX','CAN_SEE_ROW') = 'Y'
THEN NULL
WHEN SYS_CONTEXT('SECURITY_CTX','CAN_SEE_US') = 'Y'
THEN ' security_label = ''US'' '
WHEN SYS_CONTEXT('SECURITY_CTX','CAN_SEE_ROW') = 'Y'
THEN ' security_label = ''ROW'' '
END;
--
END f_security_policy;
/
begin
dbms_rls.add_policy
(object_schema => 'APP_DATA',
object_name => 'CARS',
policy_name => 'RLS_CARS',
function_schema => 'SECURITY_MGR',
policy_function => 'F_SECURITY_POLICY',
policy_type => SYS.DBMS_RLS.SHARED_CONTEXT_SENSITIVE,
statement_types => 'SELECT');
end;
/
Step 4: Test the VPD policies.
Step 4a: Connecting as APP_USER_US, we should only be able to see data from APP_DATA.CARS which is labelled as US:
conn app_user_us/password@dom11g
app_user_us@dom11g>select * from app_data.cars;
ID MAKE SECUR
--- ---------- -----
2 Buick US
4 Chrysler US
5 Citroen US
6 Dodge US
Elapsed: 00:00:00.01
Step 4b: Connecting as APP_USER_ROW, we should only be able to see data from APP_DATA.CARS which is labelled as ROW:
conn app_user_row/password@dom11g
app_user_row@dom11g>select * from app_data.cars;
app_user_row@dom11g>select * from app_data.cars;
ID MAKE SECUR
--- ---------- -----
1 Audi ROW
3 BMW ROW
Elapsed: 00:00:00.00
app_user_row@dom11g>
Good stuff. Spotted the mistake? Last time I checked, Citroen was French – should be labelled ROW. I’ll leave it in and update it after we’ve done some result cache queries just to show that that all works.
Step 5: Let’s take a look at the SQL result cache using the result_cache hint.
Step 5a: We’ll go with APP_USER_US first.
conn app_user_us/password@dom11g
app_user_us@dom11g>set autotrace on
app_user_us@dom11g>select /*+ result_cache */ * from app_data.cars;
ID MAKE SECUR
--- ---------- -----
2 Buick US
4 Chrysler US
5 Citroen US
6 Dodge US
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=4 Bytes=176)
1 0 RESULT CACHE OF '25tyax8u9ktwf2g61acmsb5apa'
2 1 TABLE ACCESS (FULL) OF 'CARS' (TABLE) (Cost=5 Card=4 Bytes=176)
Statistics
----------------------------------------------------------
121 recursive calls
0 db block gets
58 consistent gets
0 physical reads
0 redo size
508 bytes sent via SQL*Net to client
427 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed
app_user_us@dom11g>select /*+ result_cache */ * from app_data.cars;
ID MAKE SECUR
--- ---------- -----
2 Buick US
4 Chrysler US
5 Citroen US
6 Dodge US
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=4 Bytes=176)
1 0 RESULT CACHE OF '25tyax8u9ktwf2g61acmsb5apa'
2 1 TABLE ACCESS (FULL) OF 'CARS' (TABLE) (Cost=5 Card=4 Bytes=176)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
508 bytes sent via SQL*Net to client
427 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
I’m happy enough that it’s coming from the cache the second time.
Step 5a: How about APP_USER_US?
conn app_user_row/password@dom11g
app_user_row@dom11g>set autotrace on
app_user_row@dom11g>select /*+ result_cache */ * from app_data.cars;
ID MAKE SECUR
--- ---------- -----
1 Audi ROW
3 BMW ROW
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=2 Bytes=88)
1 0 RESULT CACHE OF 'g0ubwn87rvz5s15cj3089zgn3h'
2 1 TABLE ACCESS (FULL) OF 'CARS' (TABLE) (Cost=5 Card=2 Bytes=88)
Statistics
----------------------------------------------------------
89 recursive calls
0 db block gets
57 consistent gets
0 physical reads
0 redo size
473 bytes sent via SQL*Net to client
427 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
app_user_row@dom11g>select /*+ result_cache */ * from app_data.cars;
ID MAKE SECUR
--- ---------- -----
1 Audi ROW
3 BMW ROW
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=2 Bytes=88)
1 0 RESULT CACHE OF 'g0ubwn87rvz5s15cj3089zgn3h'
2 1 TABLE ACCESS (FULL) OF 'CARS' (TABLE) (Cost=5 Card=2 Bytes=88)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
473 bytes sent via SQL*Net to client
427 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
Step 6: Let’s update that Citroen row – double check what happens.
conn app_data/password@dom11g
app_data@dom11g>update cars set security_label = 'ROW' where id = 5;
1 row updated.
Elapsed: 00:00:00.00
app_data@dom11g>commit;
Commit complete.
Step 7: Back to the visibility users
Step 7a: APP_USER_US
app_user_us@dom11g>select /*+ result_cache */ * from app_data.cars;
ID MAKE SECUR
--- ---------- -----
2 Buick US
4 Chrysler US
6 Dodge US
Elapsed: 00:00:00.00
app_user_us@dom11g>
Step 7b: APP_USER_ROW
app_user_row@dom11g>select /*+ result_cache */ * from app_data.cars;
ID MAKE SECUR
--- ---------- -----
1 Audi ROW
3 BMW ROW
5 Citroen ROW
Elapsed: 00:00:00.00
app_user_row@dom11g>
Looking more accurate now.
SQL Query Result Cache
So, the SQL Result Cache apparently works fine with VPD.
Let’s review what the documentation says:
Cached results are parameterized with the parameter values if any of the following constructs are used in the query:
Bind variables.
The following SQL functions: dbtimezone, sessiontimezone, userenv/sys_context (with constant variables), uid, and user.
NLS parameters.
Parameterized cache results can be reused if the query is equivalent and the parameter values are the same.
So what of the PL/SQL Function Result Cache?
Step 8: Create a result cache function to demonstrate (this is an artificial and simplified function – it demos what I want to demo, it’s derived from a real-world implementation but I would advocate using the result cache for this purpose)
conn app_data/password@dom11g
CREATE OR REPLACE PACKAGE cars_pkg
AS
--
FUNCTION f_get_make (
i_id IN cars.id%TYPE
)
RETURN cars.make%TYPE;
--
END cars_pkg;
/
CREATE OR REPLACE PACKAGE BODY cars_pkg
AS
--
TYPE t_cars IS TABLE of cars%ROWTYPE INDEX BY PLS_INTEGER;
--
FUNCTION f_get_cars
RETURN t_cars
RESULT_CACHE
RELIES_ON (cars)
AS
--
l_cars t_cars;
l_tmp_cars t_cars;
i PLS_INTEGER;
--
BEGIN
--
SELECT *
BULK COLLECT INTO l_tmp_cars
FROM cars;
--
i := l_tmp_cars.FIRST;
--
WHILE (i IS NOT NULL)
LOOP
--
l_cars(l_tmp_cars(i).id).id := l_tmp_cars(i).id;
l_cars(l_tmp_cars(i).id).make := l_tmp_cars(i).make;
l_cars(l_tmp_cars(i).id).security_label := l_tmp_cars(i).security_label;
--
i := l_tmp_cars.NEXT(i);
--
END LOOP;
--
RETURN l_cars;
--
END f_get_cars;
--
FUNCTION f_get_make (
i_id IN cars.id%TYPE
)
RETURN cars.make%TYPE
AS
--
l_cars t_cars;
--
BEGIN
--
l_cars := f_get_cars;
RETURN l_cars(i_id).make;
--
EXCEPTION
WHEN no_data_found THEN
RETURN NULL;
END f_get_make;
--
END cars_pkg;
/
GRANT EXECUTE ON cars_pkg TO app_user_us, app_user_row;
Step 9: Let’s try it using this.
Step 9a: As APP_USER_US initially
app_user_us@dom11g>var v varchar2(48)
app_user_us@dom11g>set autoprint on
app_user_us@dom11g>select * from app_data.cars;
ID MAKE SECUR
--- ---------- -----
2 Buick US
4 Chrysler US
6 Dodge US
Elapsed: 00:00:00.01
app_user_us@dom11g>exec :v := app_data.cars_pkg.f_get_make(1);
PL/SQL procedure successfully completed.
V
---------------
Elapsed: 00:00:00.01
app_user_us@dom11g>exec :v := app_data.cars_pkg.f_get_make(2);
PL/SQL procedure successfully completed.
V
---------------
Buick
Elapsed: 00:00:00.01
As expected, APP_USER_US cannot see the Audi data, can see Buick. Perfect!
Step 9b: Let’s try APP_USER_ROW.
conn app_user_row/password@dom11g
app_user_row@dom11g>var v varchar2(48)
app_user_row@dom11g>set autoprint on
app_user_row@dom11g>select * from app_data.cars;
ID MAKE SECUR
---- ---------- -----
1 Audi ROW
3 BMW ROW
5 Citroen ROW
Elapsed: 00:00:00.00
app_user_row@dom11g>exec :v := app_data.cars_pkg.f_get_make(1);
PL/SQL procedure successfully completed.
V
----------
Elapsed: 00:00:00.01
app_user_row@dom11g>exec :v := app_data.cars_pkg.f_get_make(2);
PL/SQL procedure successfully completed.
V
----------
Buick
Elapsed: 00:00:00.01
app_user_row@dom11g>
Wrong! And just what I wanted to demo. By implementing a result function like this, I have bypassed my VPD and rendered results from this function unpredictable depending on who ran the results which were cached.
So, with the PL/SQL function RESULT_CACHE directive you have to be careful, not necessarily anymore so than certain other features but just something to be aware of.
Before I knew the solution, but after I had these results, what happened when I combined VPD with such a Result Cache mis-implementation was not a massive surprise, but I had expected that either an error message would be raised, similiar to Materialized Views or the MERGE statement (forward references to future articles on the challenges of combining VPD with those two), or perhaps that the RESULT_CACHE directive would be ignored.
However the behaviour is documented and the answer is very much documented in the PL/SQL Language Reference:
Making Result-Cached Functions Handle Session-Specific Settings
The PL/SQL function result-caching feature does not automatically handle dependence on session-specific application contexts. If you must cache the results of a function that depends on session-specific application contexts, you must pass the application context to the function as a parameter. You can give the parameter a default value, so that not every user must specify it.
The same advice applies to other session specific settings, e.g. NLS_DATE_FORMAT etc.
Step 10: Make safe the result_cache implementation of APP_DATA.CARS_PKG.
conn app_data/password@dom11g
CREATE OR REPLACE PACKAGE APP_DATA.cars_pkg
AS
--
FUNCTION f_get_make_safe (
i_id IN cars.id%TYPE,
i_can_see_us IN VARCHAR2 DEFAULT SYS_CONTEXT('SECURITY_CTX','CAN_SEE_US'),
i_can_see_row IN VARCHAR2 DEFAULT SYS_CONTEXT('SECURITY_CTX','CAN_SEE_ROW')
)
RETURN cars.make%TYPE;
--
END cars_pkg;
/
CREATE OR REPLACE PACKAGE BODY APP_DATA.cars_pkg
AS
--
TYPE t_cars IS TABLE of cars%ROWTYPE INDEX BY PLS_INTEGER;
--
FUNCTION f_get_cars_safe (
i_can_see_us IN VARCHAR2 DEFAULT SYS_CONTEXT('SECURITY_CTX','CAN_SEE_US'),
i_can_see_row IN VARCHAR2 DEFAULT SYS_CONTEXT('SECURITY_CTX','CAN_SEE_ROW')
)
RETURN t_cars
RESULT_CACHE
RELIES_ON (cars)
AS
--
l_cars t_cars;
l_tmp_cars t_cars;
i PLS_INTEGER;
--
BEGIN
--
SELECT *
BULK COLLECT INTO l_tmp_cars
FROM cars;
--
i := l_tmp_cars.FIRST;
--
WHILE (i IS NOT NULL)
LOOP
--
l_cars(l_tmp_cars(i).id).id := l_tmp_cars(i).id;
l_cars(l_tmp_cars(i).id).make := l_tmp_cars(i).make;
l_cars(l_tmp_cars(i).id).security_label := l_tmp_cars(i).security_label;
--
i := l_tmp_cars.NEXT(i);
--
END LOOP;
--
RETURN l_cars;
--
END f_get_cars_safe;
--
FUNCTION f_get_make_safe (
i_id IN cars.id%TYPE,
i_can_see_us IN VARCHAR2 DEFAULT SYS_CONTEXT('SECURITY_CTX','CAN_SEE_US'),
i_can_see_row IN VARCHAR2 DEFAULT SYS_CONTEXT('SECURITY_CTX','CAN_SEE_ROW')
)
RETURN cars.make%TYPE
AS
--
l_cars t_cars;
--
BEGIN
--
l_cars := f_get_cars_safe;
RETURN l_cars(i_id).make;
--
EXCEPTION
WHEN no_data_found THEN
RETURN NULL;
END f_get_make_safe;
--
END cars_pkg;
/
Step 11: With this our previous function lookup should work as expected:
Step 11a: As APP_USER_US
conn app_user_us/password@dom11g
app_user_us@dom11g>var v varchar2(48)
app_user_us@dom11g>set autoprint on
app_user_us@dom11g>select * from app_data.cars;
ID MAKE SECUR
---- ---------- -----
2 Buick US
4 Chrysler US
6 Dodge US
Elapsed: 00:00:00.01
app_user_us@dom11g>exec :v := app_data.cars_pkg.f_get_make_safe(1);
PL/SQL procedure successfully completed.
V
----------
Elapsed: 00:00:00.01
app_user_us@dom11g>exec :v := app_data.cars_pkg.f_get_make_safe(2);
PL/SQL procedure successfully completed.
V
----------
Buick
Elapsed: 00:00:00.01
Step 11a: As APP_USER_ROW
conn app_user_row/password@dom11g
app_user_row@dom11g>var v varchar2(48)
app_user_row@dom11g>set autoprint on
app_user_row@dom11g>select * from app_data.cars;
ID MAKE SECUR
---- ---------- -----
1 Audi ROW
3 BMW ROW
5 Citroen ROW
Elapsed: 00:00:00.01
app_user_row@dom11g>exec :v := app_data.cars_pkg.f_get_make_safe(1);
PL/SQL procedure successfully completed.
V
----------
Audi
Elapsed: 00:00:00.01
app_user_row@dom11g>exec :v := app_data.cars_pkg.f_get_make_safe(2);
PL/SQL procedure successfully completed.
V
----------
Elapsed: 00:00:00.01
app_user_row@dom11g>
Problem fixed.
Moral
The moral of the story is simple in this case – RTFM.
This time everything I needed was there in the docs. I hadn’t picked up on it fully when I implemented the result cache procs when VPD wasn’t in the picture and I didn’t review the information up front when looking at the VPD solution.
Caveat Developer.
Documentation
titled “Hibernate without Database Bottlenecks”.
