vpd2_oracleLabel.pptVIP

VPD and Oracle Label Security Grant Execute on DBMS_RLS CONNECT TO ORACLE AS SYSDBA SQL connect sys as sysdba Enter password: ****** Connected. SYSDBA creates a user called SECMAN (Security manager) SQL create user secman identified by s default tablespace users; SYSDBA GRANTS EXECUTE ON DBMS_RLS TO SECMAN so this user can create the VPD SQL grant dba to secman; SQL grant execute on dbms_rls to secman; CONNECT AS SECMAN SQL connect secman/s; VPD - Definition Sample project /~mguimara/8080/guimaraes_securityProject2.doc III.1) CREATE TABLES III.2) LOAD TABLES III.3) CREATE USERS and ROLES III.4) CREATING VIEWS VERSUS VPD SQL connect sys as sysdba Enter password: ****** Connected. SQL create user mn668b14 identified by m; SQL grant dba to mn668b14; SQL grant execute on dbms_rls to mn668b14; Solution with views SQL CREATE VIEW employeeView as select * from employees e where upper(e.lastName) = user; SQL GRANT update ON employeeView to EmployeeRole; SQL UPDATE mn668b14.EmployeeView set ext = 777 -- note that mn668b14 is schema where data was created - VPD – Update Example Security Policy -- create security policy BEGIN DBMS_RLS.drop_policy (object_schema = MN668B14, object_name = EmployeeUpdateOrderView, policy_name = EMP_UPDATE_ORDERS_POLICY); END; BEGIN dbms_rls.add_policy ( object_schema = MN668B14, object_name = EmployeeUpdateOrderView , policy_name = EMP_ORDERS_UPDATE__POLICY’, function_schema = MN668B14, policy_function = EMPUSER_ONLY, statement_types = SELECT,UPDATE,INSERT,DELETE, update_check = TRUE ); END; Virtual Private Databases VPD security provides a new way to control access to Oracle data.