{"id":73372,"date":"2011-10-04T17:42:05","date_gmt":"2011-10-04T17:42:05","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/introduction-to-virtual-private-databases\/"},"modified":"2021-07-14T13:08:08","modified_gmt":"2021-07-14T13:08:08","slug":"introduction-to-virtual-private-databases","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/introduction-to-virtual-private-databases\/","title":{"rendered":"Introduction to Virtual Private Databases"},"content":{"rendered":"

 <\/p>\n

You know the story. A group of blind men come across an elephant and each one feels a part of it. One touches the tusk and goes away thinking elephants are pointy, hard and sharp; another feels its side and is amazed at how wide and featureless elephants are; and one unfortunate man feels the elephant up its backside, and is probably somewhere right now scrubbing his hands with soap!<\/p>\n

The story of the blind men and the elephant actually illustrates Virtual Private Databases perfectly. It is functionality that allows different users run the exact same query against a table and each come away with a different, tailored, resultset.<\/p>\n

Being able to automatically sculpt the output of a query based on the identity of the end user or any other determinable variables is hugely useful \u2013 only limited by the imagination of the developer. Example: imagine you have a table containing staff details including sensitive information such as salaries, wouldn\u2019t it be useful to apply a policy to that table that, while allowing all managers to query the table, will only allow each manager view the salaries of people in their own teams? Example: imagine you are happy to give your users access to a table \u2013 but only between 9 and 5 from Monday to Friday. Or perhaps, only when they are using a particular machine.<\/p>\n

Mere role-based security using grants would wither in the face of these requirements; and while you may be able to code them into your front-end application, a wily user will then be able to bypass your security by logging onto SQL*Plus.<\/p>\n

Virtual Private Databases (VPD) is technology that allows you to tack on an extra clause (an extra predicate) to any query that is run against a specified object, irrespective of the source of the query \u2013 a Forms application, an Apex application, SQL*Plus, TOAD or a reporting tool. It is also called Row-Level Security (RLS). It is also called Fine-Grained Access Control (FGAC). It has more nicknames than the rapper Sean Combs (aka Puff Daddy aka Puffy aka P Diddy aka Diddy).<\/p>\n

RLS is usually accomplished with the aid of application contexts. Application contexts, which are outside the scope of this article, can be used to obtain defining attributes of a user\u2019s session \u2013 from environmental attributes to developer-defined attributes set as part of a database logon trigger.<\/p>\n

Row-Level Security is achieved using the DBMS_RLS package.\u00a0 Using the ADD_POLICY procedure in this package, you can add a \u2018policy\u2019 to a table. To do this you will need, obviously, to specify the object to which you wish to add the policy; you will need to specify the types of DML statements that should trigger your policy; and you will also need to identify a function your policy should use. This function must return a varchar2, the string that will be tacked on to the DML applied to your object.<\/p>\n\n\n\n\n\n\n\n\n\n\n
DBMS_RLS.ADD_POLICY (<\/td>\n<\/tr>\n
<\/td>\nOBJECT_SCHEMA<\/td>\nVARCHAR2<\/td>\nThe schema of the object to which you wish to apply the policy<\/td>\n<\/tr>\n
<\/td>\nOBJECT_NAME<\/td>\nVARCHAR2<\/td>\nThe table, view, synonym to which you wish to apply the policy<\/td>\n<\/tr>\n
<\/td>\nPOLICY_NAME<\/td>\nVARCHAR2<\/td>\nA name for your policy<\/td>\n<\/tr>\n
<\/td>\nFUNCTION_SCHEMA<\/td>\nVARCHAR2<\/td>\nThe schema of the function that returns your predicate<\/td>\n<\/tr>\n
<\/td>\nPOLICY_FUNCTION<\/td>\nVARCHAR2<\/td>\nThe function that returns the predicate<\/td>\n<\/tr>\n
<\/td>\nSTATEMENT_TYPES<\/td>\nVARCHAR2<\/td>\nStatement types to which the policy should apply: Any combination of SELECT, UPDATE, DELETE, INSERT, INDEX<\/td>\n<\/tr>\n
);<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Functions that can be used as policy functions must obey certain rules. They must accept 2 parameters \u2013 the object schema and the object name \u2013 and return a varchar2. A simple example will illustrate this; let us assume we have been asked to ensure that the data in EMP is inaccessible outside of office hours.<\/p>\n

FUNCTION work_hours_fnc (pObj_Schema IN VARCHAR2, pObj_Name IN VARCHAR2)\r\nRETURN VARCHAR2 is\r\nBEGIN\r\n\tIf (to_char(sysdate,\u2019DY\u2019) in (\u2018SAT\u2019,\u2019SUN\u2019) or to_char(sysdate,\u2019HH24\u2019) not between 9 and 17) then\r\n\t   Return \u20181 = 2\u2019;\r\n        Else\r\n\t   Return \u20181=1\u2019;\r\n\tEnd if;\r\nEND work_hours_fnc;<\/pre>\n
And now that we have our simple function, we can create a policy that will apply it to the EMP table.<\/p>\n
Exec dbms_rls.add_policy(\u2018SCOTT\u2019,\u2019EMP\u2019,\u2019WORK_HOURS\u2019,\u2019SCOTT\u2019,\u2019WORK_HOURS_FNC\u2019,\u2019SELECT\u2019);<\/pre>\n
The effect this will have is that every time a select statement is executed against the EMP table our function will be evaluated and an extra predicate will be added on to that query. If the following is run during office hours:<\/p>\n
SELECT *\r\nFROM EMP;<\/pre>\n
The actual query that will be executed will be:<\/p>\n
SELECT *\r\nFROM EMP\r\nWHERE 1 = 1;<\/pre>\n
However, if the exact same query is executed on a Sunday or on a weekday evening, the database will read it as:<\/p>\n
SELECT *\r\nFROM EMP\r\nWHERE 1 = 2;<\/pre>\n
Which, of course, will result in no data being returned.<\/p>\n
This simple example illustrates the power of fine-grained access control, and it should be obvious that the intricacy of the function can be enhanced to make it more, well, fine-grained.<\/em> Characteristics of the end user can be examined and can be used to structure the data they have access to.<\/p>\n
This article could end here and you would be suitably impressed with RLS. However, there is more. Sherlock Holmes once solved a crime because a dog did not<\/em> bark: sometimes something that does not happen \u2013 or is not where you expect it to be \u2013 can reveal a lot. Imagine you have a table named \u00a0MEDICAL_HISTORY and you apply a policy that allows end users select details for all people\u00a0 \u2013 except one. Would your end users not immediately deduce that that one person probably has some horrendous disease \u2013 adult bed-wetting or something?<\/p>\n
The dbms_rls.add_policy procedure allows some parameters that tell the policy to only kick in when certain \u2013 presumably sensitive \u2013 columns are queried. If the end user queries other columns in that table their query will be fulfilled as normal and they would be none the wiser. \u00a0But the instant they add a sensitive column to the query, the policy springs to life.<\/p>\n
It gets better. Dbms_rls.add_policy gives you the option to return NULLs when named columns are queried. It is the perfect way to mask the contents of sensitive columns without withholding the whole record.<\/p>\n
To accomplish this dbms_rls.add_policy accepts two further parameters \u2013 sec_relevant_cols and sec_relevant_cols_opt.<\/p>\n
Sec_relevant_cols accepts a comma-separated list of columns. The policy will only kick in if any of these columns are selected by the user\u2019s query.<\/p>\n
Sec_relevant_cols_opt accepts a binary_integer. Pass it DBMS_RLS.ALL_ROWS and any columns passed to the sec_relevant_cols parameter will be masked, appearing as nulls in any affected queries.<\/p>\n
CONCLUSION<\/strong><\/p>\n
Row-level security is a powerful tool that gives you the ability to practically give each type of user a customised view of your data. Because it works at the database level it is application-agnostic and cannot be easily circumvented. Bear in mind, however, that, once set up, policies work silently in the background; this often makes them easy to forget and therefore difficult to debug. This notwithstanding, it is immensely useful and your database will probably benefit from some implementation of it.<\/p>\n","protected":false},"excerpt":{"rendered":"
  You know the story. A group of blind men come across an elephant and each one feels a part of it. One touches the tusk and goes away thinking elephants are pointy, hard and sharp; another feels its side and is amazed at how wide and featureless elephants are; and one unfortunate man feels the elephant up its backside,…<\/p>\n","protected":false},"author":221907,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[],"coauthors":[],"class_list":["post-73372","post","type-post","status-publish","format-standard","hentry","category-oracle-databases"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73372","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221907"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73372"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73372\/revisions"}],"predecessor-version":[{"id":91806,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73372\/revisions\/91806"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73372"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73372"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73372"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73372"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}