| "The mission of the older tip was to hide salary and commission_pct columns on a report if the department_id was equal to 60. The predicate is a static value, so a relational view could certainly have been used to do the same thing. " " shows a view that uses the selector CASE statement (which has been available in Oracle SQL since version 8i) to compare the department_id value to 60, return NULL if it is equal, or return the salary if it is any other value. A similar CASE statement could mask the commission_pct data. The data would be protected by granting access only to the view and not to the base table (Employees)." "Your first thought might be to simply create more views -- one for each department -- and grant each user access to the correct view. There are many reasons that this is not a good solution, which include the following:" " shows a small modification to the VPD policy function from the previous article. Instead of testing for department 60 directly, it uses the SYS_CONTEXT function to return the user's department. (Assume this value was set at logon for the user.) The function then returns a different predicate (the WHERE clause) for each user. The salary will only be shown in rows for which the predicate is true. If a department number is not set, the always false predicate of "1=2" will be returned, causing the salary to be masked for the whole report." "Even better is the fact that the call to SYS_CONTEXT acts as a bind variable in the query; only one copy of this query needs to be stored in the Shared Pool to handle all departments. A relational view could be built that uses the SYS_CONTEXT function in the same way, but a different view in the application would be
... read the whole article |