Oracle Label Security Part 3: Adding a UI

If you’ve been following along in our series of Oracle Label Security posts (Part 1, Part 2) you should now have a table of ‘documents’ that depending on the users permission will return different sets of documents. In this post we’re going to add a UI so that we can see how Label Security makes application development easier by freeing us from having to worry about security in the application. We won’t actually be doing any database work (that’s been done in the previous posts) and will just focus on getting a simple UI up. You could use any language to build this UI as long as it can connect to the Oracle database (generally through ODBC), which is pretty much of all of them. For this example we are going to build a NodeJs application with a Bootstrap UI. It will be a good demonstration of how to connect NodeJs and the Oracle database together.

The completed application is available on GitHub: Read on to see how it’s built


First we need to install NodeJs. This is pretty easy depending on your environment. On Oracle Linux I did the following:

curl -sL | bash

To connect to the Oracle database we need a driver that can link NodeJs and the Oracle database. I recommend Node Oracle ( To get it to work you first need to download and install the Oracle Instant Client Basic and SDK. They are both available here: Download the Instant Client Package – Basic (rpm) and Instant Client Package – SDK (rpm) and then run:

rpm -ivh oracle-instantclient12.1-basic-
rpm -ivh oracle-instantclient12.1-devel-
-- Now fix up some environment variables
nano ~/.bash_profile
export OCI_LIB_DIR=/usr/lib/oracle/12.1/client64/lib
export OCI_INCLUDE_DIR=/usr/include/oracle/12.1/client64
export OCI_VERSION=12

Now install the client with

echo '/usr/lib/oracle/12.1/client64/liborac' | sudo tee -a /etc/
sudo ldconfig

Write the app

With that out of the way, let’s create our application to serve up our documents. I’m not going to build a full-fledged application, I’m just going to build a log in page and then show the documents the user has access to. This should be sufficient to show you how we can work with label security in an app.

I’ve already created a functioning app on GitHub so grab that code and run npm install to install the dependencies (this will download the required prerequisites like Node Oracle, Express etc).Before you start the app, edit config/database.js and adjust the database settings accordingly:

  • hostname: The hostname of your database.
  • port: The database listener port
  • database: The instance name of your database (‘xe’ or ‘orcl’ etc)

Now just run the following to start you app on port 9000:

node server.js

Go to localhost:9000 and you should see this:


Just log in as one of the users we set up in the last post eg: JCooper/welcome1 and you should be presented with the documents that user is entitled to see:


Here you can see that JCooper has access to 2 documents as he is allowed to see all documents in the system regardless of classfication. But if we log in as CDoyle we only see one document:


This is because CDoyle only has access to the one Secret document in the system. At first glance this doesn’t look that special. Any application will have a way to only show documents that a user is allowed to see. To see Oracle Label Security lets take a look at the code where we query the database for the documents (app/route.js)

app.get('/', isLoggedIn, function (req, res) {
	configDB.user = req.user.username;
	configDB.password = req.user.password;
	console.log('connecting as', configDB);
	oracle.connect(configDB, function (err, connection) {
		if (err) { 
			console.log('Error connecting to db:', err);
		connection.execute("SELECT * FROM SECDEMO", [], function(err, results) {
			if (err) { console.log('Error executing query:', err); return; }
			res.render('index.ejs', {
				documents	: results,
				user		: req.user

Basically the app.get function is a NodeJs Express construct that handles the default url and checks that the user is logged in (the isLoggedIn argument). If the user is logged in we pass a req object that contains all elements of the request and a res object that holds anything we want to return the browser. As we are using Oracle Label Security we need to connect to the database as the user we want to get documents for (ie: JCooper or CDoyle). The user details are stored in the req.user object and so we pass them to our configuration object for the database. Then we connect to the database with our user details:

oracle.connect(configDB, function (err, connection) {

Finally we run our query on the database. Since we are using Oracle Label Security we don’t have to construct a WHERE clause on our SQL statement and can pass the same query for every user, the database will take care of only returning the correct documents.

connection.execute("SELECT * FROM SECDEMO", [], function(err, results) {

Contrast this to an approach where we didn’t have Oracle Label Security – we would have to check the users permissions (probably from the database), add some logic to construct the correct WHERE clause and then append this to our result. As you can see OLS makes a developers life a lot easier, as they can rely on the database managing data security.


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s