<!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="10. Database Event Hooks" />
<meta name="dc.subject" content="10. Database Event Hooks" />
<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="hooks.html" title="Chapter Contents" />
<link rel="prev" href="fn_dbev_shutdown.html" title="Database Shutdown" />
<link rel="next" href="sqlparsetree.html" title="SQL Parse Tree" />
<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>10. Database Event Hooks</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="fn_dbev_prepare" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>10. Database Event Hooks</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="hooks.html">Chapter Contents</a> | <a class="link" href="fn_dbev_shutdown.html" title="Database Shutdown">Prev</a> | <a class="link" href="sqlparsetree.html" title="SQL Parse Tree">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="hooks.html">Database Event Hooks</a>
</div>
<br />
<div>
<a href="fn_dbev_startup.html">Database Startup</a>
</div>
<div>
<a href="fn_dbev_connect.html">Database Connections</a>
</div>
<div>
<a href="fn_logins.html">Database Logins</a>
</div>
<div>
<a href="fn_disconnect.html">Database Disconnections</a>
</div>
<div>
<a href="fn_dbev_shutdown.html">Database Shutdown</a>
</div>
<div class="selected">
<a href="fn_dbev_prepare.html">SQL Statement Preparation</a>
</div>
<div>
<a href="sqlparsetree.html">SQL Parse Tree</a>
</div>
<div>
<a href="fn_davlogins.html">WebDAV Logins</a>
</div>
<div>
<a href="assocauxdata.html">Associating Auxiliary Data With A Connection</a>
</div>
<br />
</div>
<div id="text">
<a name="fn_dbev_prepare" />
<h2>10.6. SQL Statement Preparation</h2>
<p>
<span class="computeroutput">DB.DBA.DBEV_PREPARE(<span class="parameter">inout tree any</span>)</span>
</p>
<div>
<pre class="programlisting">
DB.DBA.DBEV_PREPARE (inout tree any)
</pre>
</div>
<p>
If defined, this function is called after parsing any dynamic SQL
statements by any users. The parse tree will be a
syntactically correct SQL parse tree. The user and connection variables are defined.
The hook should not produce a result set and any return values are discarded.
The function runs in the transaction which is current on the connection and
the transaction is not automatically committed, so that the hook does not
modify application transaction boundaries.
</p>
<p>
The tree may be modified by replacing it with any other correct parse tree
or destructively splicing it. The tree is a regular SQL heterogeneous array.
If the tree is modified incorrectly, it is probable that the server will crash.
</p>
<p>
The parse tree manipulation is best written in C as a Virtuoso Server
Extension using the supplied SQL parse tree typedef and constants.
</p>
<p>
If an error occurs inside this hook the error is simply ignored and the unmodified parse tree is used.
To signal an error to a user it is possible to change the parse tree into a call to the signal SQL function.
</p>
<a name="" />
<div class="example">
<div class="exampletitle">SQL Prepare Hook</div>
<div>
<pre class="programlisting">
CREATE TABLE REPORT (
R_AUTHOR VARCHAR,
R_ID INTEGER IDENTITY,
R_CLASS INTEGER,
R_TEXT LONG VARCHAR,
PRIMARY KEY (R_ID)
);
CREATE TABLE NEED_TO_KNOW (
NK_CLASS INTEGER,
NK_USER INTEGER,
PRIMARY KEY (NK_CLASS, NK_USER)
);
grant select on REPORT to public;
create procedure DB.DBA.DBEV_PREPARE (inout tree any)
{
declare uid integer;
uid := (select U_ID from SYS_USERS where U_NAME = user);
need_to_know (uid, tree);
dbg_obj_print ('compiled by ', user, ': ', tree);
}
</pre>
</div>
<p>
This example has a table of variously secret reports, each having a class or
compartment and different users having a need to know about a certain collection of compartments.
The need_to_know table references U_ID in SYS_USERS and R_CLASS in REPORT. Each select referencing
REPORT is modified by the <span class="computeroutput">need_to_know</span> VSE in order to add a check for the need to know.
</p>
<p>
For example,
</p>
<div>
<pre class="programlisting">
select * from REPORT
</pre>
</div>
<p>becomes</p>
<div>
<pre class="programlisting">
select * from REPORT
where exists (select 1 from NEED_TO_KNOW
where NK_CLASS = R_CLASS and NK_USER = <user>)
</pre>
</div>
<p>
where <user> is the id of the user preparing the query.
</p>
<p>
As a result, all queries referencing the REPORT table, no matter how they are phrased,
will not access rows for which the user does not have a need to know.
Note that the REPORT table can be granted to public, unauthorized users will just get an empty result.
Further, note that the NEED_TO_KNOW table is not granted to anyone, hence the user does
not even need to know the extent of his need to know let alone that of any other user.
The expansion of the need to know test inserts the table reference as in a view expansion,
where it's privileges are not those of the user but of the view owner, or in this case the
procedure owner, which is always dba.
</p>
</div>
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="fn_dbev_shutdown.html" title="Database Shutdown">Previous</a>
<br />Database Shutdown</td>
<td align="center" width="34%">
<a href="hooks.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="sqlparsetree.html" title="SQL Parse Tree">Next</a>
<br />SQL Parse Tree</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
>
237
