By user12587121 on Oct 24, 2011
Future versions of the Oracle database may include integration with Oracle Entitlement Server for fine grained authorization configuration. However with the current versions (11g, 10g for example) any such integration is not available out of the box.
How then might we use OES to protect data in an Oracle database ?
As an example, consider a stored procedure querying and returning values from some database tables. We can use OES to provide authorization on this data in the following way: we ask OES for an authorization decision and if the decision is allow we interpret any obligations as additional where clauses that we use to constrain the queries.
With OES performance is best if we can make the call from a Java SM client as we can then benefit from local copies of the authorization policies This reduces the overhead of the call to OES to the microsecond level. So in the case of our stored procedure, if it is being invoked from Java, then it would be best to call OES from Java and pass in any constraints to the stored procedure as parameters. However this may not always be possible.
In this case we can use the facility of the Oracle database to load java classes and to call them from stored procedures. As OES offers a webservice XACML interface one could load some Java code that would call out to OES. Another technique would be to use the OES gateway from my previous posting, calling it using a very simple Java class that does a HTTP GET for the appropriate URL.
This set of scripts and SQL demonstrate how to load such a Java class to the Oracle database and configure a database function to allow the Java class to be called from a sample SQL script. If one is using Oracle 10g database then as it has a 1.4 JDK one must compile the class with JDK 1.4. Oracle Database 11g has a 1.5 JDK so JDK 1.5 can be used in that case.
The steps to prepare the example are:
- compile the Java class with the appropriate JDK (use the
- load the java classes to the data base (use the
- run the database preparation script to define a function to interface to the Java class and allow the database user appropriate permissions to execute Java code. The example assumes the user SYSTEM is being used.
- run the SQL script to show the usage of the call to OES. It runs a raw query on the scott.emp table with no constraints and then it calls the Java class which will call the OES Gateway to recover an authorization from OES. If there are obligations returned the procedure looks for an obligation key of scott.emp and if it finds one it uses the value--for example (sal<2000)-- as a constraint when querying the table.