Oracle Label Security Part 4: Accessing with Service Bus

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: 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

Before proceeding make sure that you’ve installed and configured Label Security (see the previous posts)

The primary difference when working with Label Security is that we now need to connect to the database as the user, as opposed to connecting as an application super user the way we would normally. In the previous post we were able to access the driver directly and log in as the user once they had logged in. The problem with this approach is that its not very scalable, there’s no way to pool connections and re-use them. Also when working with JCA in WebLogic you can only provide a single user at design-time, there’s no way to log in as different users at run-time.

Thankfully Oracle has thought of this and added a feature to the database called ‘Proxy Authentication’. Basically you create an application super-user like normal. We’ll use this to connect to the database from WebLogic. Then you give permission for that user to log in as another user (our jcooper and cdoyles). When that happens that user has all the roles and permissions of the normal user. This way we can log in as one user, but still access the right documents through label security (and our auditing will still be correct as it will be as if the normal user had logged in).

To configure this in your database run the following in SQLPlus to create a new super user called ‘sec_osb’

connect dvacctmgr/welcome1;
create user sec_osb identified by welcome1;
GRANT SECDEMO_USR to sec_osb; -- so we can see the documents table, you could remove this after development

Now modify each user so that we can connect as them with our sec_osb user

alter user SECDEMO_jcooper GRANT CONNECT THROUGH sec_osb;
alter user SECDEMO_cdoyle GRANT CONNECT THROUGH sec_osb;

Unlike a normal application super user, this user does not have any labels associated with him so at worst if the account was compromised the attacker would get no documents back (or only low-level documents without labels). Give it a try and log in as the sec_osb user and run ‘SELECT * FROM SECDEMO’; you should get no results back.

Configure WebLogic

Now that we’ve configured our proxy user we need to create our database connection in WebLogic. You could install your own version of WebLogic, but the for this demo I’m just going to use the integrated WebLogic that comes with JDeveloper (make sure to download the SOA Quickstart version which includes OSB). Just click ‘Run’->’Start Server Instance’ from the menu to start WebLogic. Once it’s started go to ‘localhost:7101/console’ and lets start configuring our data source.

  • Go to ‘Services->Data Sources’ and click ‘New (Generic Data Source)’
  • Enter a name (SecureOSB) and a JNDI name (jdbc/proxy/sec_osb). The JNDI name will be used in our OSB project to reference this data source
  • Keep moving through the wizard until you get to the ‘Connection Properties’ page. Enter the details as follows:
    • Database Name: orcl
    • Host name: localhost
    • Port: 1521
    • Database User Name: sec_osb
    • Password: welcome1
  • Click ‘Next’ and on the next tick the box next to ‘Default Server’ to target this data source to our server

Before we proceed we need to create some users in our WebLogic security realm. We will use these users later when we are authenticating with our web service. This is pretty straight forward, just go to ‘Security Realms’ from the left hand menu and click the default realm (myrealm). In a real-world example you may not have to create users as you could use your LDAP/Active Directory service that you configured in WebLogic. Click ‘Users and Groups’ and add 2 users: jcooper and cdoyle.

Now go back to the data source we created and click the ‘Oracle’ tab (under ‘Configuration’). Check the box next to ‘Oracle Proxy Session’.

Configure Proxy

Now we need a way to map the WebLogic users we created to their respective database user (ie: jcooper = SECDEMO_jcooper). To do this go to the ‘Security’ tab and click ‘Credential Mappings’. Add 2 mappings to map between our WebLogic users and our remote database users.

Security Mapping

Note: This may seem like an onerous task, if we were using this in real-life we might have to map between hundreds of logins! But you can simplify this process by using the Oracle Identity and Access Management tools. Talk to your local friendly Oracle representative for more details.

With the users mapped, we now need to add this data source to the DbAdapter so that we can use it in our web service. Go to ‘Deployments’->’DbAdapter’ and then click ‘Configuration’->’Outbound Connection Pools’. Create a new connection with a JNDI name of ‘eis/DB/sec_osb’. Click on the newly created instance and enter ‘jdbc/proxy/sec_osb’ in the XADataSourceName (make sure to press ‘enter’ before clicking the ‘Save’ button, otherwise your change won’t be remembered).

Outbound Properties

The last thing we need to do to configure our datasource is to update our DbAdapter deployment. To do so, just go back to the ‘Deployments’ screen and select the ‘DbAdapter’ and press ‘Update’. On the next screen just click ‘Finish’ to update the deployment. Now just restart WebLogic and we are done

Service Bus Project

Now let’s create a Service Bus project to expose our documents through a web service. Go into JDeveloper and create a new Service Bus application with Project. Let’s call it ‘SecureApp’ and the project ‘SecureOSB’

In the composite, drag a ‘Database Adapter’ on to the ‘External Services’ Column. In the wizard do the following:

  • Enter the name as ‘Secure Documents’. Click Next
  • On the next screen, click the green ‘+’ button to add a new connection. This is just so that we can access the tables we need in this wizard. Enter the following details:
    • Connection name: SecureOSB
    • Username: sec_osb
    • Password: welcome1
    • Host Name: localhost
    • SID: orcl
    • Test the connection and (if it works) click ‘Ok’
  • Back on the connection screen, enter the JNDI name as ‘eis/DB/sec_osb’ and click ‘Next’
  • Change the operation to ‘Select’ (under ‘Perform an Operation on a Table) and click ‘Next’
  • On the next screen, click ‘Import Tables’. A new window should pop up to let us select the table we want
    • Change the ‘Schema’ to ‘APP_SECDEMO_OWNER’ (as this schema owns the ‘Documents’ table we are interested in
    • Move the ‘DOCUMENTS’ table across to the ‘Selected’ column and press ‘Ok’
  • On the next screen, let’s select all the columns (though you may want to turn off the ‘olsColumnSecdemo’ as this just returns the internal security code for the document. It’s not a security issue to reveal it, but might confuse users as it’s an auto-generated column)
  • Click ‘Finish’ and you should now have a new database adapter in your project. You could deploy this now and test it, but let’s assume it works and continue on to create our proxy service.

To create our proxy service, click the blue arrow next to the newly created database adapter and drag it to the central ‘Pipeline/Split Joins’ column in our composite. On the new screen name the service as ‘SecureDocumentsPipeline’. Accept the defaults on the next screen.

Service Type

Optional: Expose as REST

At this point we have a working solution but it only communicates through traditional SOAP. You can optionally expose this as a REST service as well, which may make mobile access a lot easier. To do this right click the pipeline and select ‘Expose as REST’. That’s it! Though this service will be REST it will only communicate via XML. I like to add JSON support as well, so to do so select the ‘SecureDocumentSelect’ operation within the REST window and then click the pencil next ‘Operation Bindings’. On the ‘Response’ tab click ‘JSON’ and you’re done

Configure REST operation

Your composite should now look like this (assuming you created the REST service)

Completed Process

Apply security

Now our final step is to provide some security on our services, this will serve two purposes – 1) to ensure only valid users can access our services before we even try to connect to the database and 2) so we can log into the database as the correct user (remember our mapping above). Now, we could go off and implement a proper web security policy, but for the sake of simplicity I’m just going to use Basic Authentication on the proxy service. This will require the user to provide a username and password (though the password will be sent in clear text). Not the most secure, but you get the picture.

First create a service account by right clicking the project name and selecting ‘New’->’Service Account’. Click finish and your account is created. We will leave this service account as ‘pass-through’. We will use this to pass the credentials the user logged in at the proxy service to the business service (our database adapter in this case).

Now open the ‘SecureDocuments.bix’ business service and go to the ‘Transport Details’ tab. Click the magnifying glass next to ‘JNDI Service Account’ and select the service account we just created. Then go to the same place in the proxy services (you’ll need to do this twice if you created a REST proxy service) and select ‘Basic’ next to ‘Authentication’ to turn on our basic authentication requirement.

The Result

We’re done! Now just deploy this to WebLogic and go localhost:7101/servicebus to start our test. Navigate to the ‘GetDocumentsPS’ service and click the ‘Launch Test Console’ button (the green play button). On the test console, scroll down and expand the ‘Transport’ section. Enter jcooper/welcome1 as the user and click ‘Execute’. You should see 2 documents returned:

GetDocumentPS Response

Repeat the same, but log in as cdoyle/welcome1. You should now only get 1 response back. Check the JSON version as well:

JSON response


As you can see this has greatly simplified the development of our web service, at the same time as ensuring security. The developer is free to focus on business logic and does not have to appear aware of how security works in the database. Also the business can rest easy knowing that there is no way the developer can screw up the security and let people access documents they shouldn’t.

And by using proxy authentication we avoided the need to create a different data source in WebLogic for each of our users. We can still use all the great caching and pool management tools in WebLogic, but we can still log in as each user.

If you need more information about Label Security and how to integrate it into your applications or middleware, please reach out to me or your local Oracle rep. That about wraps up this series on Label Security, but I’ll be expanding the discussion around data security in other posts in the future.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s