Next, we will create a policy that will be applied on UPDATE statements that will ensure that the salaries of employees who currently make less than 3000 USD and who do not earn a commission will receive an additional 1500 USD raise when the UPDATE statement is executed.
- Connect as
HRand create a new table namedemployees_test_vpd, based on theemployeestable as follows:SQL> conn HR Enter password: Connected. SQL> create table employees_test_vpd as select * from employees where salary is not null and commission_pct is null; Table created.
- Connect as
systemand create theSALRISE_POL_FUNCpolicy function defined on theUPDATEstatement as follows:SQL> conn system Enter password: Connected. SQL> CREATE OR REPLACE FUNCTION salrise_pol_func ( schema_v IN VARCHAR2, tbl_v VARCHAR2) RETURN VARCHAR2 IS ret_val VARCHAR2(200); BEGIN ret_val := 'commission_pct is null and salary < 3000'; RETURN ret_val; END; SQL> / Function created. SQL>
- Connect to OEM with the user system, navigate to the Server tab, and in the Security panel click on Virtual Private Database:
- Above current policies, click on the Create button:
- The Create Policy page will be loaded. Enter the following values:
- Policy Name:
SALARY_RISE_POLICY - Object Name:
HR.EMPLOYEES_TEST_POLICY - Policy Type: STATIC
- Check the Enabled checkbox
- Policy Function:
SYSTEM.SALRISE_POL_FUNC - For Enforcement check the UPDATE checkbox
- Click on the OK button to finish
- Policy Name:
- To retrieve the current policies defined on the tables from the
HRschema, in the Policy tab typeHRin Schema Name and click on the Go button as follows:
- Now verify the users whose conditions fit our policy definition as follows:
SQL> select first_name,last_name,salary from hr.employees_test_vpd where sal <3000 and commission_pct is null order by 3 desc 2 ; FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Shelli Baida 2900 Timothy Gates 2900 Michael Rogers 2900 Vance Jones 2800 Sigal Tobias 2800 Girard Geoni 2800 Mozhe Atkinson 2800 John Seo 2700 Irene Mikkilineni 2700 Randall Matos 2600 Donald OConnell 2600 FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Douglas Grant 2600 Guy Himuro 2600 Joshua Patel 2500 Randall Perkins 2500 Karen Colmenares 2500 Martha Sullivan 2500 Peter Vargas 2500 James Marlow 2500 Ki Gee 2400 James Landry 2400 Hazel Philtanker 2200 FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Steven Markle 2200 TJ Olson 2100 24 rows selected. SQL>
- Next, proceed to raise the salaries by 1500 USD for those with a salary of less than 3000 USD using the following policy:
SQL> update employees_test_vpd set salary=salary+1500; 24 rows updated. SQL> commit; Commit complete. SQL>
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.