Disclaimer

The views expressed in this blog are my own and do not necessarily reflect the views of Oracle Corporation. All content is provided on an 'as is' basis, without warranties or conditions of any kind, either express or implied, including, without limitation, any warranties or conditions of title, non-infringement, merchantability, or fitness for a particular purpose. You are solely responsible for determining the appropriateness of using or redistributing and assume any risks.

By Joel Nation

Tag: Database

In our recent Label Security presentation we used a feature called Proxy Authentication. This allowed us to connect to the database as one user, but proxy the credentials of another so that we can access resources that proxied user can see. Without this we wouldn’t be able to use Label Security with our OSB services. It’s been brought to my attention that this is a useful feature that others would be interested in, but it’s hidden within my other post. So I’ve extracted the material out here to its own post so it can be found easier. Enjoy!

So by now you’ve seen how to install Label Security (here), configure a policy (here) and create a UI to access the data (here). Particularly in the UI post, you would have seen how Label Security helps to simplify our application development, as we no longer have to worry about configuring data security, the database takes care of it for us. Whilst a UI is a great way to access our data and demonstrates a common use case, it’s not the only way to access our data. So in this post we are going to demonstrate how to create a web service that will talk to our database and return the correct documents for each user.

If we weren’t using Label Security, we’d have to add some significant logic to poll the database for the security permissions of the user (ie: JCooper has Top Secret access, but CDoyle can only see Secret Narcotics documents). We’d then need to apply that to our query to return the documents. Whilst this will work, what happens if the developer writes the query incorrectly and some users start seeing documents they shouldn’t? Or what happens if the service is compromised and a hacker gets access to the underlying database using the application database credentials. This poses a significant risk to our organisation and it’s data security. With Label Security we can avoid all this as the service just has to query the database and pass down the user credentials. The database will take care of the rest, there is no opportunity for the developer to mess up the query or if the user account is compromised only the documents that user had access to will be affected.

I’ve uploaded the OSB project that we will create below to GitHub. You can access it here: https://github.com/Joelith/SecureOSB. To get it working, import the project into JDeveloper and configure the database source in WebLogic (detailed below). Otherwise read on to see how it’s all put together

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.

In the previous post we outlined Oracle Label Security and set it up in our 12c database. Now we are actually going to implement the policy against our table. What we want to produce is a table (let’s call it ‘documents’) that will store sensitive data. That data will either be ‘Top Secret’ or ‘Secret’ and belong to one of a number of groups or compartments (‘Fraud’, ‘Narcotics’ and ‘Terrorism’). We want users to be able to log in and not have to worry about security (nor do we want them to be able to look at documents they don’t have access to). Basically we want every user to be able run ‘SELECT * FROM documents’ and get only the documents they are supposed to see.

I’ve uploaded a script to GistBox that will run all the SQL below. It contains some clean-up before it runs the SQL below, so you can run it as many times as you like. Just modify all the defines at the beginning of the script to match your environment and then run the script in sqlplus with @secdemo

Oracle Label Security is an Oracle Database feature that provides row level security on records within your tables. It’s a way to centralise your data security in a single source – the Oracle Database. Basically it works by allowing you to create labels that are applied to records in your table. When a user queries for data in the database their access to the labels will be checked and only records that match will be returned. The beauty of this is that the user label checking is invisible to the user, it’s all done by the database. For instance the query ‘SELECT * FROM documents’ (assumming documents has a label policy applied) will return different results depending on the permissions of the user. Contrast this to the traditional approach where we would need to add a WHERE clause, eg: SELECT * FROM documents WHERE label=’Secret’;

You might be asking, so what? Adding a WHERE clause isn’t that hard and that’s exactly the point. Adding a WHERE clause is pretty easy, but so is not adding one. If I can get access to your database then I can just ‘SELECT * FROM documents’ and get every document in your system, regardless of permission. But with Label Security, even if I manage to bypass your application, I won’t be able to get any documents beyond those that I have permission for. This is especially important in a post-Snowden world, where we no longer trust our database admins (sorry guys!). With Label Security we can ensure that our DBAs and application developers can still have access to the underlying tables (for maintenance, development etc), but they won’t actually see any data (technically we’d also need to employ Database Vault to ensure that our DBAs don’t just modify the policy or add themselves to it, but that’s a post for another day).

In SOA Suite you can use the Database Adapter to perform CRUD operations on data within a database. You can even work with multiple (related) tables within in a single database adapter. I recently had a customer that was having problems inserting data into multiple tables using the database adapter. The problem turned out to be related to a mis-configured database adapter but it gave me a chance to get a little deeper in using the Database Adapter and how to insert data using the Sequence feature of Oracle Database.

I’ve never really used Oracle Database (grew up with MySQL) so I wasn’t familiar with how to use Sequences (or an AUTO-INCREMENT column in MySQL speak) in an Oracle Database. I created this post just in case there are any others out there like me and so that I have somewhere to refer to when I need to do it again.