Admit that your own private Mount Everest exists. That is half the battle.
– Hugh Macleod
Other options
In the example we used pretty much the minimum number of parameters to make it work. There are more parameters and options available. For example UPDATE_CHECK. This does pretty much the same as WITH UPDATE CHECK in views. If you insert or update a value the Oracle server checks if the new record will be visible to you using your policy. If it is not visible, and this parameter was set to TRUE then it will not allow you to execute this command.
| update_check | Optional argument for INSERT or UPDATE statement types. The default is FALSE. Setting update_check to TRUE causes the server to also check the policy against the value after insert or update. |
| enable | Indicates if the policy is enabled when it is added. The default is TRUE. |
| static_policy | The default is FALSE. If it is set to TRUE, the server assumes that the policy function for the static policy produces the same predicate string for anyone accessing the object, except for SYS or the privilege user who has the EXEMPT ACCESS POLICY privilege. policy_type Default is NULL, which means policy_type is decided by the value of static_policy. The available policy types are listed in Table 110-4. Specifying any of these policy types overrides the value of static_policy. |
| long_predicate | Default is FALSE, which means the policy function can return a predicate with a length of up to 4000 bytes. TRUE means the predicate text string length can be up to 32K bytes.Policies existing prior to the availability of this parameter retain a 32K limit. |
| sec_relevant_cols | Enables column-level Virtual Private Database (VPD), which enforces security policies when a column containing sensitive information is referenced in a query. Applies to tables and views, but not to synonyms. Specify a list of comma- or space-separated valid column names of the policy-protected object. The policy is enforced only if a specified column is referenced (or, for an abstract datatype column, its attributes are referenced) in the user SQL statement or its underlying view definition. Default is all the user-defined columns for the object. |
| sec_relevant_cols_opt | Use with sec_relevant_cols to display all rows for column-level VPD filtered queries (SELECT only), but where sensitive columns appear as NULL. Default is set to NULL, which allows the filtering defined with sec_relevant_cols to take effect. Set to dbms_rls.ALL_ROWS to display all rows, but with sensitive column values, which are filtered by sec_relevant_cols, displayed as NULL. See “Usage Notes” for restrictions and additional information about this option. |
The ENABLE parameter allows you to create policies without enabling them a the time of creation. You can enable the policy by using the ENABLE_POLICY procedure in the DBMS_RLS package.
|
1 2 3 4 5 |
DBMS_RLS.ENABLE_POLICY ( object_schema <span class="kwrd">IN</span> <span class="kwrd">VARCHAR2</span> <span class="kwrd">NULL</span>, object_name <span class="kwrd">IN</span> <span class="kwrd">VARCHAR2</span>, policy_name <span class="kwrd">IN</span> <span class="kwrd">VARCHAR2</span>, enable <span class="kwrd">IN</span> <span class="kwrd">BOOLEAN</span>); |
| object_schema | Schema containing table, view, or synonym (current default schema if NULL). |
| object_name | Name of table, view, or synonym with which the policy is associated. |
| policy_name | Name of policy to be enabled or disabled. |
| enable | TRUE to enable the policy, FALSE to disable the policy. |
Data Redaction (12c)
If you don’t want to restrict the rows visibile, but just want to hide the values of certain columns there are a couple of possibilities to accomplish this. You can use column-level VPD to have the security policy applied only when a particular column is accessed in the user’s query. You can choose to show the entire row, but a NULL value for the column or hide the entire row.
|
1 2 3 4 5 |
dbms_rls.add_policy (... ,sec_relevant_cols => 'SAL' ,sec_relevant_cols_opt => dbms_rls.all_rows ); |
Another option in Oracle 12c is data redaction. This approach puts a mask on the data in the column, just before it is sent back to the client. This means the optimizer still ‘sees’ the actual data and can make the plan accordingly.
You can redact column data by using one of the following methods:
| Full redaction. | You redact all of the contents of the column data. The redacted value returned to the querying user depends on the data type of the column. For example, columns of the NUMBER data type are redacted with a zero (0), and character data types are redacted with a blank space. |
| Partial redaction. | You redact a portion of the column data. For example, you can redact most of a Social Security number with asterisks (*), except for the last 4 digits. |
| Regular expressions. | You can use regular expressions to look for patterns of data to redact. For example, you can use regular expressions to redact email addresses, which can have varying character lengths. It is designed for use with character data only. |
| Random redaction. | The redacted data presented to the querying user appears as randomly generated values each time it is displayed, depending on the data type of the column. |
| No redaction. | This option enables you to test the internal operation of your redaction policies, with no effect on the results of queries against tables with policies defined on them. You can use this option to test the redaction policy definitions before applying them to a production environment. |
Oracle Database applies the redaction at runtime, at the moment users attempt to access the data (that is, at query-execution time). During the time that the data is being redacted, all of the data processing is performed normally, and the back-end referential integrity constraints are preserved.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DBMS_REDACT.ADD_POLICY ( object_schema <span class="kwrd">IN</span> <span class="kwrd">VARCHAR2</span> := <span class="kwrd">NULL</span>, object_name <span class="kwrd">IN</span> <span class="kwrd">VARCHAR2</span> := <span class="kwrd">NULL</span>, policy_name <span class="kwrd">IN</span> <span class="kwrd">VARCHAR2</span>, policy_description <span class="kwrd">IN</span> <span class="kwrd">VARCHAR2</span> := <span class="kwrd">NULL</span>, column_name <span class="kwrd">IN</span> <span class="kwrd">VARCHAR2</span> := <span class="kwrd">NULL</span>, column_description <span class="kwrd">IN</span> <span class="kwrd">VARCHAR2</span> := <span class="kwrd">NULL</span>, function_type <span class="kwrd">IN</span> <span class="kwrd">BINARY_INTEGER</span> := DBMS_REDACT.FULL, function_parameters <span class="kwrd">IN</span> <span class="kwrd">VARCHAR2</span> := <span class="kwrd">NULL</span>, expression <span class="kwrd">IN</span> <span class="kwrd">VARCHAR2</span>, enable <span class="kwrd">IN</span> <span class="kwrd">BOOLEAN</span> := <span class="str">TRUE</span>, regexp_pattern <span class="kwrd">IN</span> <span class="kwrd">VARCHAR2</span> := <span class="kwrd">NULL</span>, regexp_replace_string <span class="kwrd">IN</span> <span class="kwrd">VARCHAR2</span> := <span class="kwrd">NULL</span>, regexp_position <span class="kwrd">IN</span> <span class="kwrd">BINARY_INTEGER</span> := <span class="str">1</span>, regexp_occurrence <span class="kwrd">IN</span> <span class="kwrd">BINARY_INTEGER</span> := <span class="str">0</span>, regexp_match_parameter <span class="kwrd">IN</span> <span class="kwrd">VARCHAR2</span> := <span class="kwrd">NULL</span>); |
There are a lot of similarities to Row Level Security as you can see by the parameters but instead of getting the predicate from a function to limit the rows visible, you add a SQL predicate using the EXPRESSION parameter. If this predicate evaluates to true, then the redaction function is applied. Otherwise it is bypassed. If you want the redaction to be applied always, just add ‘1=1’ as an expression. Be careful with NULL values in your expressions, since comparisons to NULL evaluate to false, thereby bypassing the redaction function.
In the image above the creditcard number is redacted for certain users. This is done by using the DBMS_REDACT.PARTIAL option and specifying the value for FUNCTION_PARAMETERS, in this case
|
1 |
'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,1,12' |
The first part of the comma separated string is the current format of the string. A V represents a character that can be redacted, an F represents a character that will be replaced with a fixed character, for example a hyphen (-). The second part of the string is the output string, with the V representing the redacted value. The third part of the string is the redacted character to be used, in this case an asterisk (*). The fourth part represents the starting character to be redacted, the fifth part represents the last character to be redacted, just counting the V’s in the first part.
If the column is of type number or date then the format of this parameter changes accordingly. For numbers, for example, you might enter
|
1 |
'9,2,4' |
to redact a number of 12345 to 19995. The first part is the mask character to display (0-9), the second is the starting position and the third is the ending position for the redaction.
Conclusion
The Oracle database provides us with many ways to protect our data from visibility to different users. Since version 8i we have the possibilities of Row Level Security to limit the visibility of rows for different users. Version 12 adds redaction to limit visibility of the data in certain columns.
ref:
http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm
http://www.oracle-developer.net/display.php?id=311
http://tkyte.blogspot.com/2013/07/12c-sql-text-expansion.html
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_rls.htm
http://www.oracle.com/technetwork/database/options/advanced-security/index-1964787.html
http://docs.oracle.com/cd/E16655_01/network.121/e17729/redaction.htm
http://docs.oracle.com/cd/E16655_01/network.121/e17729/redaction_config.htm
Load comments