Additional_VPD_Example

Additional_VPD_Example - Additional VPD Example Grant...

Info iconThis preview shows pages 1–8. Sign up to view the full content.

View Full Document Right Arrow Icon
Additional VPD Example
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Grant Execute on DBMS_RLS DUPLICATING THE EXAMPLE IN ORACLE SQL> connect sys as sysdba Enter password: ****** Connected. SQL> select username from user_users; USERNAME ------------------------------ SYS SYSDBA GRANTS EXECUTE ON DBMS_RLS TO THE SECURITY MANAGER SQL> grant execute on dbms_rls to secman; Grant succeeded. AND TO AN ALTERNATE SECURITY MANAGER SQL> grant execute on dbms_rls to michael; Grant succeeded.
Background image of page 2
Creating the Access Table YOU CONNECT AS THE SECURITY MANAGER TO CREATE THE VPD POLICY. THE ACCESS TABLE WILL BE USED IN THE POLICY SQL> SELECT USERNAME FROM USER_USERS; USERNAME ------------------------------ SECMAN SQL> CREATE TABLE ACCESS_POLICY ( AM_NAME VARCHAR2(20) NOT NULL, CUST_ID NUMBER NOT NULL, ACCESS_TYPE CHAR(1) NOT NULL); Table created.
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Loading Access Rights ACCESS RIGHT ARE CREATED BY LOADING THE TABLE SQL> INSERT INTO ACCESS_POLICY VALUES ('SCOTT', 123, 'S'); 1 row created. SQL> INSERT INTO ACCESS_POLICY VALUES ('SCOTT', 123, 'I'); 1 row created. SQL> INSERT INTO ACCESS_POLICY VALUES ('SCOTT', 123, 'D'); 1 row created. SQL> INSERT INTO ACCESS_POLICY VALUES ('SCOTT', 123, 'U'); 1 row created. SQL> INSERT INTO ACCESS_POLICY VALUES ('SCOTT', 456, 'S'); 1 row created. SQL> INSERT INTO ACCESS_POLICY VALUES ('SCOTT', 789, 'S'); 1 row created. SQL> INSERT INTO ACCESS_POLICY VALUES ('LARA', 456, 'I'); 1 row created. SQL> INSERT INTO ACCESS_POLICY VALUES ('LARA', 456, 'D'); 1 row created. SQL> INSERT INTO ACCESS_POLICY VALUES ('LARA', 456, 'U'); 1 row created. SQL> INSERT INTO ACCESS_POLICY VALUES ('LARA', 456, 'S'); 1 row created.
Background image of page 4
Showing Current Access Rights SHOWING THE VALUES FROM THE TABLE SQL> SELECT * FROM ACCESS_POLICY; AM_NAME CUST_ID A -------------------- ---------- - SCOTT 123 S SCOTT 123 I SCOTT 123 D SCOTT 123 U SCOTT 456 S SCOTT 789 S LARA 456 I LARA 456 D LARA 456 U LARA 456 S 10 rows selected.
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Creating A Policy Function STEP 1 CREATE A POLICY FUNCTION CREATING THE FUNCTIONS TO SUPPORT THE POLICY FUNCTION (THE POLICY CHECKS TO SEE IF THE USER EXECUTING THE FUNCTION IS IN THE TABLE – THE BANK’S ACCESS RULES (CONTAINED IN THE ACCESS_POLICY TABLE) MUST BE APPLIED. ) THIS FUNCTION RETURNS THE USERS THAT HAVE THE RIGHT TO SELECT CUSTOMER INFORMATION.
Background image of page 6
Details of Policy Function (1) SQL> create or replace function get_sel_cust_id 2 ( 3 p_schema in varchar2, 4 p_table in varchar2
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 8
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 12/22/2009 for the course DBST dbst 668 taught by Professor Yelena - ta during the Spring '09 term at MD University College.

Page1 / 24

Additional_VPD_Example - Additional VPD Example Grant...

This preview shows document pages 1 - 8. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online