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).
The downside to Label Security is that you have to move user management down to the database. Generally you just create a single database user for the application and have the application manage the user and permission checking. With Label Security you need to log into the database as the user that will be doing the queries. Whilst this is a major change, you do get to make your applications simpler (as they can just query and assume permissions will be applied) and you can use the database auditing facilities (Audit Vault) to monitor what each user is doing (as opposed to all queries running as a single user). You can also move the user management to the Oracle Identity products so that you don’t have to manage them within the database.
For more information on how Oracle Label Security works check out the Oracle Label Security Administration Guide.
* Technically this isn’t part of the Middleware space, as it’s a database feature, but I feel that it’s an important starting point for more complex applications in the middleware and application layers. If you can get the database to manage this, you don’t have to worry about it further up the chain. *
In this series of posts we are going to create a simple demo that demonstrates OLS and some other features of the Oracle Database and then eventually combine this with some middleware (OSB, BPEL etc) to demonstrate how to leverage it in your applications. Before we do that, let’s discuss the data model and labels we want to create. We are going to create a table to store documents (pdfs, word docs etc) and have a number of different users that will be able to access some documents, but not others, based on our labelling policy. Labels are made up of 3 elements – Levels, Compartments and Regions. Only Levels is mandatory, the rest are optional. For our demo we are going to have the following:
- Levels: TOP SECRET and SECRET
- Compartments: Fraud, Terrorism and Narcotics
We are also going to have the following users:
So for example, CDoyle can see any documents that are SECRET in the Narcotics compartment. MTwain on the other hand can see any SECRET document, regardless of the compartment.
Before we can use Label Security you need to enable it in the database. In Oracle Database 12c, that’s pretty easy as it’s installed by default in the Enterprise Edition (Label Security is only available in Enterprise Edition). Log into sqlplus as the sys user and run
EXEC LBACSYS.CONFIGURE_OLS; EXEC LBACSYS.OLS_ENFORCEMENT.ENABLE_OLS; SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Label Security';
The query should return ‘TRUE’. For this demo we are also going to set up Database Vault (DV). DV will be used to secure users and provide a bit more security on our database (more information available here). We could also use it to create more fine-grained controls on Factors (ie: we could restrict labels depending on whether the user is logging in via the corporate network or a public network. I might do a post on DV later). To enable it, first create two accounts to manage Database Vault:
CREATE USER dvowner IDENTIFIED BY welcome1; CREATE USER dvacctmgr IDENTIFIED BY welcome1;
Now enable DV:
GRANT CREATE SESSION TO dvowner IDENTIFIED BY welcome1; GRANT CREATE SESSION TO dvacctmgr IDENTIFIED BY welcome1; BEGIN DVSYS.CONFIGURE_DV ( dvowner_uname => 'dvowner', dvacctmgr_uname => 'dvacctmgr' ); END; / @?/rdbms/admin/utlrp.sql CONNECT dvowner EXEC DBMS_MACADM.ENABLE_DV;
Lastly we need to set up our encryption wallet. We are going to be using encryption throughout the demo, so we need to store our encryption key somewhere. In an Oracle Database that is the encryption wallet. To set it up create a wallet folder somewhere (eg: ‘mkdir ~/WALLET) and then modify $ORACLE_HOME/network/admin/sqlnet.ora:
Then in sqlplus run:
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "welcome1"
Finally restart the database:
CONNECT / AS SYSDBA SHUTDOWN IMMEDIATE STARTUP
Label Security (and some other stuff for our demo) is now set up. Before you can start using it though, you should unlock the LBACSYS user as it’s locked by default. To do so:
CONNECT dvacctmgr ALTER USER LBACSYS ACCOUNT UNLOCK IDENTIFIED BY welcome1;
In the next post we will actually configure the label security policy, create the users etc.