<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="9. SQL Procedure Language Guide" />
<meta name="dc.subject" content="9. SQL Procedure Language Guide" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="sqlprocedures.html" title="Chapter Contents" />
<link rel="prev" href="pldebugger.html" title="Procedure Language Debugger" />
<link rel="next" href="contents.html" title="Contents" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>9. SQL Procedure Language Guide</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="rowlevelsecurity" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>9. SQL Procedure Language Guide</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="sqlprocedures.html">Chapter Contents</a> | <a class="link" href="pldebugger.html" title="Procedure Language Debugger">Prev</a> | <a class="link" href="hooks.html" title="Database Event Hooks">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="sqlprocedures.html">SQL Procedure Language Guide</a>
</div>
<br />
<div>
<a href="GENERALPRINCIPLES.html">General Principles</a>
</div>
<div>
<a href="SCOPEOFDECLARATION.html">Scope of Declarations</a>
</div>
<div>
<a href="sqlplDATATYPES.html">Data Types</a>
</div>
<div>
<a href="RESULTSETS.html">Handling Result Sets</a>
</div>
<div>
<a href="ARRAYS.html">Result Sets and Array Parameters</a>
</div>
<div>
<a href="EXCEPTIONS.html">Exception Semantics</a>
</div>
<div>
<a href="PLREF.html">Virtuoso/PL Syntax</a>
</div>
<div>
<a href="EXECSQLPROCSELECT.html">Execute Stored Procedures via SELECT statement</a>
</div>
<div>
<a href="EXECSQLPROCFORK.html">Execute Stored Procedures In Background</a>
</div>
<div>
<a href="createassembly.html">CREATE ASSEMBLY Syntax - External Libraries</a>
</div>
<div>
<a href="createexthostproc.html">CREATE PROCEDURE Syntax - External hosted procedures</a>
</div>
<div>
<a href="ASYNCEXECMULTITHREAD.html">Asynchronous Execution and Multithreading in Virtuoso/PL</a>
</div>
<div>
<a href="PERFTIPS.html">Performance Tips</a>
</div>
<div>
<a href="PROCEDURES_TRANSACTIONS.html">Procedures and Transactions</a>
</div>
<div>
<a href="twopcimplementation.html">Distributed Transaction & Two Phase Commit</a>
</div>
<div>
<a href="TRIGGERS.html">Triggers</a>
</div>
<div>
<a href="charescaping.html">Character Escaping</a>
</div>
<div>
<a href="PLSCROLLCRSRS.html">Virtuoso/PL Scrollable Cursors</a>
</div>
<div>
<a href="plmodules.html">Virtuoso PL Modules</a>
</div>
<div>
<a href="handlingplcondit.html">Handling Conditions In Virtuoso/PL Procedures</a>
</div>
<div>
<a href="pldebugger.html">Procedure Language Debugger</a>
</div>
<div class="selected">
<a href="rowlevelsecurity.html">Row Level Security</a>
<div>
<a href="#pldrlsfunctions" title="Row Level Security Functions">Row Level Security Functions</a>
</div>
</div>
<br />
</div>
<div id="text">
<a name="rowlevelsecurity" />
<h2>9.22. Row Level Security</h2>
<p>Organizations often need to compartmentalize access to data. This may be implemented at the level of physically segregating systems, building specific application logic or within the database.
</p>
<p>Physically disconnected systems can be troublesome in an increasingly integrated corporate IS environment. Building access rules into application logic, typically in a middle tier is possible and flexible but the protection runs the risk of being circumvented by direct access to the database, through business intelligence tools for example.</p>
<p>For these reasons some database level security enforcement is needed in most applications. SQL provides table and column level privileges which can be granted to users and roles. These do not address issues where one department's data should be accessible when another department's data should not. Such segregation within a table is usually done with views which hard-code selection criteria. The table itself will not be granted but views to specific ranges of rows will be granted to users.</p>
<p>This has the inconvenience of requiring administration of views and requiring applications to use different views for different end users.</p>
<p>Row level security or policy based security allows the SQL compiler to make choices according to which user is accessing any given table. Extra conditions will be introduced into a SQL statement at compile time in order to limit the user to a specific range of rows. This will apply equally to reading and modifying the table.</p>
<p>A policy is a SQL function that will be called by the SQL compiler each time a table having the policy is accessed in a user's query or stored procedure. The policy can return extra conditions which will be and'ed to the conditions in the query. After this is done, the new query is optimized and compiled. This mechanism makes it possible to transparently customize which information a user account sees without having to maintain a multiplicity of static views. This mechanism cannot be subverted by a user and will work no matter what application is used against the database.</p>
<p>Let us consider the example of a table of classified documents. To access a document, the user needs to have a record in a need-to-know table which forms a many-to-many between classifications and users. If the user is a member of the security_auditor role, all documents will be accessible. Users themselves may neither read nor update the need-to-know table.</p>
<div>
<pre class="programlisting">
create role staff;
create role security_auditor;
grant staff to security_auditor;
create table document (d_id varchar, d_changed datetime, d_author varchar, d_classification int, d_text long varchar, primary key (d_id));
create table document_access (da_classification int, da_user varchar,
primary key (da_d_id, da_user));
grant all privileges on document to staff;
grant all privileges on document_access to security_auditor;
create procedure d_policy (in tb varchar, in op varchar)
{
if (user_has_role (user, 'security_auditor')) return '';
if (user_has_role (user, 'staff'))
return 'exists (select 1 from document_access where da_user = user and da_classification = d_classification)';
return '1=2';
}
table_set_policy ('document', 'd_policy', 'IDUS');</pre>
</div>
<p>These operations first define two roles, staff and security_auditor. The staff is not granted access to document_access, which is configured by security_auditor.</p>
<p>The policy function will, for each query accessing document, check if the user is a member of security_auditor. If so, there will be no extra conditions and all rows will appear. If the user is a member of staff, an extra condition checking the existence of a need to know is added. This will check that there is an entry in the document_access where the accessing user is granted access to the document's classification. If the user is neither member of staff nor of security_auditor, no rows will be returned since the always false condition of 1=2 is added.</p>
<p>Besides selects, the policy rule will apply to inserts, updates and deletes. This is caused by the 'IDUS' parameter to table_set_policy. Different policies may be defined for select, insert, update and delete. Most often these will be the same. Only one policy function is allowed per table and operation. In order to set a policy, one must either be the table's owner or dba. Policies do not apply to dba users.</p>
<p>We note that the need to know check accesses a table which is not granted to staff, even for reading. This is OK, since the predicate coming from the policy function has access rights of the owner of the policy function. The predicate is treated like a view body that were granted to the user making the query without requiring that component tables of the view were granted.</p>
<p>Since policies are processed at the level of SQL compilation, these will equally apply to local and remote tables.</p>
<p>Queries inside stored procedures are subject to policy. The user whose policies are consulted is the owner of the stored procedure. The policy conditions are fixed at the time of compiling the procedure. Reassigning a policy counts as a change to the table, causing recompilation of all concerned procedures, triggers and client statements.</p>
<div class="note">
<div class="notetitle">Conclusions</div>
<p>Policies are an effective way of implementing a 'virtual private database,' providing for isolation between classes of users of the same application. The maintenance overhead is small compared to the trouble of maintaining views for each class of users. The table concerned remains one schema object with one name. Policies can be set and maintained without modification to applications and existing applications can be made to use policy based access control at no additional cost.</p>
</div>
<a name="pldrlsfunctions" />
<h3>9.22.1. Row Level Security Functions</h3>
<p>
<a href="fn_table_set_policy.html">table_set_policy()</a>
</p>
<p>
<a href="fn_table_drop_policy.html">table_drop_policy()</a>
</p>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="pldebugger.html" title="Procedure Language Debugger">Previous</a>
<br />Procedure Language Debugger</td>
<td align="center" width="34%">
<a href="sqlprocedures.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="hooks.html" title="Database Event Hooks">Next</a>
<br />Contents of Database Event Hooks</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
861
