Hi Tom,
I have a particular question related to Oracle VPD (Virtual Private Database) behavior in case of Stored Procedures. I tried searching in Oracle Documentation as well as in the questions answered in AskTom, but couldn't find anything that might help me.
I'm working on a project where I need to restrict the Application users to specific records in a table.
For example, let's take a sample table ORGANIZATION_DETAILS
CREATE TABLE ORGANIZATION_DETAILS
(ORG_KEY NUMBER,
ORG_NAME VARCHAR2(20)
);
INSERT INTO ORGANIZATION_DETAILS VALUES
(1,'ORG_A'
);
INSERT INTO ORGANIZATION_DETAILS VALUES
(2,'ORG_B'
);
INSERT INTO ORGANIZATION_DETAILS VALUES
(3,'ORG_C'
);
The bare-bone functionality of Oracle VPD (when we talk about Row Level Security) is to append customized filters (WHERE Clause) to a skeleton Query fired by the user, based on the access level of that user.
So if User1 has a Policy defined for him which allows his to access only ORG_KEY = 1, he will only be able to SELECT the record with ORG_KEY = 1 from ORGANIZATION_DETAILS table.
When the User fires
SELECT * FROM ORGANIZATION_DETAILS
, VPD will append the filter
WHERE ORG_KEY = 1
The same goes for INSERT/UPDATE/DELETE statements as well
My question is : VPD works for explicit SQL statements, whereby the User writes S/I/U/D queries on some table and executes them.
However, let's assume there's a Stored procedure (PROC_UPDATE_ORG_DETAILS), which Updates a specific record in the ORGANIZATION_DETAILS table with some user-supplied value:
CREATE OR REPLACE PROCEDURE PROC_UPDATE_ORG_DETAILS(
in_org_key NUMBER,
in_new_org_name VARCHAR2)
AS
BEGIN
UPDATE ORGANIZATION_DETAILS
SET ORG_NAME = in_new_org_name
WHERE ORG_KEY = in_org_key;
COMMIT;
END;EXEC PROC_UPDATE_ORG_DETAILS(1,'NEW_ORG_A');
SELECT * FROM ORGANIZATION_DETAILS;
ORG_KEY ORG_NAME
1 new_org_a
2 ORG_B
3 ORG_C
3 rows selected.
Since User1 has access only to ORG_KEY = 1, he can't update the record ORG_KEY = 1 via a
direct Update statement.
However, can he execute this procedure by passing in_org_key as 2 and update that record (Of course provided he has execute privilege on that procedure)?
As far as I know, Procedures and Packages are created with AUTHID DEFINER by default.
If I want to restrict that user from accessing any other records except ORG_KEY = 1, will Creating the procedure with AUTHID CURRENT_USER be of any help?
In short, I'm not sure how VPD behaves when there's a Procedure in the picture. All I know is how VPD behaves with Explicit SQL queries.
Can you please help me with this?
My Oracle DB Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Thanks for the livesql test case - because of that your question is the first one I'm looking at today :-)
Even *within* the procedure, you'll be protected, eg
SQL> conn connor/admin
Connected.
SQL> drop table ORGANIZATION_DETAILS purge;
Table dropped.
SQL>
SQL> CREATE TABLE ORGANIZATION_DETAILS
2 (ORG_KEY NUMBER,
3 ORG_NAME VARCHAR2(20)
4 );
Table created.
SQL>
SQL> INSERT INTO ORGANIZATION_DETAILS VALUES
2 (1,'ORG_A'
3 );
1 row created.
SQL>
SQL> INSERT INTO ORGANIZATION_DETAILS VALUES
2 (2,'ORG_B'
3 );
1 row created.
SQL>
SQL> INSERT INTO ORGANIZATION_DETAILS VALUES
2 (3,'ORG_C'
3 );
1 row created.
SQL>
SQL> SELECT * FROM ORGANIZATION_DETAILS;
ORG_KEY ORG_NAME
---------- --------------------
1 ORG_A
2 ORG_B
3 ORG_C
3 rows selected.
SQL>
SQL> CREATE OR REPLACE
2 PROCEDURE PROC_UPDATE_ORG_DETAILS(
3 in_org_key NUMBER,
4 in_new_org_name VARCHAR2)
5 AS
6 BEGIN
7 UPDATE ORGANIZATION_DETAILS
8 SET ORG_NAME = in_new_org_name
9 WHERE ORG_KEY = in_org_key;
10
11 dbms_output.put_line('rows = '||sql%rowcount);
12 COMMIT;
13 END;
14 /
Procedure created.
SQL>
SQL> grant execute on connor.PROC_UPDATE_ORG_DETAILS to demo;
Grant succeeded.
SQL>
SQL> conn demo/demo
Connected.
SQL> set serverout on
SQL> exec connor.PROC_UPDATE_ORG_DETAILS(1,'x');
rows = 1
PL/SQL procedure successfully completed.
SQL> exec connor.PROC_UPDATE_ORG_DETAILS(2,'x');
rows = 1
PL/SQL procedure successfully completed.
SQL> exec connor.PROC_UPDATE_ORG_DETAILS(3,'x');
rows = 1
PL/SQL procedure successfully completed.
SQL>
SQL> conn connor/admin
Connected.
SQL> create or replace
2 function my_security_function(
3 p_schema in varchar2,
4 p_object in varchar2 )
5 return varchar2
6 as
7 begin
8 return 'ORG_KEY = 3';
9 end;
10 /
Function created.
SQL>
SQL> begin
2 begin
3 dbms_rls.drop_policy( user, 'ORGANIZATION_DETAILS', 'MY_POLICY' );
4 exception
5 when others then null;
6 end;
7 dbms_rls.add_policy
8 ( object_schema => user,
9 object_name => 'ORGANIZATION_DETAILS',
10 policy_name => 'MY_POLICY',
11 function_schema => user,
12 policy_function => 'MY_SECURITY_FUNCTION',
13 statement_types => 'select,insert,update,delete',
14 update_check=>true);
15 end;
16 /
PL/SQL procedure successfully completed.
SQL>
SQL> conn demo/demo
Connected.
SQL> set serverout on
SQL> exec connor.PROC_UPDATE_ORG_DETAILS(1,'x');
rows = 0
PL/SQL procedure successfully completed.
SQL> exec connor.PROC_UPDATE_ORG_DETAILS(2,'x');
rows = 0
PL/SQL procedure successfully completed.
SQL> exec connor.PROC_UPDATE_ORG_DETAILS(3,'x');
rows = 1
PL/SQL procedure successfully completed.
SQL>