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
First we need to open the encryption wallet with the following command, so that we can begin work on our encrypted tablespaces
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "welcome1";
With the wallet open we can now create our tablespace. This will encrypt our data on the file system using AES256. This will mean that if someone manages to copy the database they won’t be able to read any of the data. Much more secure!
CREATE TABLESPACE TOPSECRET DATAFILE "/oracle/product/db/oradata/orcl/top_secret.dbf' SIZE 50M RESUSE AUTOEXTEND ON ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT MAXSIZE UNLIMITED);
Now we are going to create all our users. If you remember from our last post, we have the following users:
We also are going to add a few more accounts to manage our application
- WFaulk: Our Account Manager. He will manage all the accounts
- CDickens: Out Security Administrator. He will manage our security policies
- SFitzgerald: Our Resource DBA. He will be our DBA. We’ve added it here so we can demonstrate that he can’t see anything later
Let’s create all the users
CONNECT dvacctmgr CREATE USER wfaulk IDENTIFIED BY welcome1; -- Since we are using Database Vault we need to assign the dv_acctmgr role so that our Account Manager can create users GRANT dv_acctmgr TO wfaulk; CREATE USER cdickens IDENTIFIED BY welcome1; GRANT dv_admin to cdickens; -- Create our User Accounts as the Account Manager CONNECT wfaulk CREATE USER SFitzgerald IDENTIFIED BY welcome1; -- Create a schema owner to own our tables CREATE USER APP_OWNER IDENTIFIED BY welcome1 DEFAULT TABLESPACE TOPSECRET; CREATE USER JCooper IDENTIFIED BY welcome1; CREATE USER MTwain IDENTIFIED BY welcome1; CREATE USER LTolstoyIDENTIFIED BY welcome1; CREATE USER CDoyle IDENTIFIED BY welcome1; CREATE USER JAusten IDENTIFIED BY welcome1;
We need to apply some permissions to various users so they will be able to perform their work. Namely the APP_OWNER needs to dba permission and needs access to the Oracle Text functions (we don’t actually need that right now, but we will use them in a later post about Oracle Text).
CONNECT sys/welcome1 AS sysdba GRANT dba TO APP_OWNER; -- Give the APP_OWNER access to the Oracle Text functions so we can add indexes later GRANT execute on ctx_ddl to APP_OWNER; GRANT dba TO cdickens; GRANT dba to sfitzgerald; CREATE ROLE APP_USR; -- Given to anyone that should be able to access the documents (regardless of level) CONNECT wfaulk/welcome1 GRANT CONNECT TO APP_USR; -- Allow users to connect to the database
Now lets create the table. It’s a pretty straight forward table that will record all the information about our document. We will be using the sensitivity column for the Label Level (TopSecret, Secret etc) and the doc_category column for the Compartment (Narcotics, Fraud and Terrorism).
CONNECT APP_OWNER/welcome1 CREATE TABLE documents( doc_no VARCHAR2(10), author VARCHAR2(15), date_created VARCHAR2(10), doc_category VARCHAR2(10), doc_source BLOB, sensitivity VARCHAR2(10), mime VARCHAR2(50), doc_name VARCHAR2(50) ) LOB (doc_source) STORE AS SECUREFILE (COMPRESS CACHE);
Note also that we are using the SECUREFILE column store to hold our doc_source. SECUREFILE actually allows us to present the data in this table as a mountable file system. We will use that feature in a future post.
Now we’ve set up our roles, users and created the table. The next step is to actually create the Label Policy.
connect lbacsys/welcome1; begin SA_SYSDBA.CREATE_POLICY( policy_name => 'SECDEMO_ACCESS', column_name => 'SECDEMO_LABEL', default_options => 'READ_CONTROL,LABEL_DEFAULT,HIDE' ) end; /
Here we are creating a policy called ‘SECDEMO_ACCESS’ with the following options (see here for more details):
- READ_CONTROL: The policy will be applied to reading from the table only (INSERTs and UPDATEs are not controlled by the policy)
- LABEL_DEFAULT: This controls whether a document can be added without a label. In this case, we are allowing that. A label will have to be applied before it can be read in a query
- HIDE: Whether to hide the label column. In this example we’ve defined a column called ‘SECDEMO_LABEL’ that will be added to the table to store the label. We are going to hide it from our users so that it doesn’t confuse them when they query the table (and so that normal INSERTS don’t have to define it)
Once that’s created we need to configure the levels and compartments. Before we can do that we need to grant some permissions to our Security Administrator. Generally when defining label security policies you should use the LBACSYS user to create the policy and then have another user to configure the policy. This way you don’t have to give out the LBACSYS password to your policy managers.
-- The role for our label policy is '<policy name>_DBA' GRANT SECDEMO_ACCESS_DBA to cdickens; GRANT execute ON sa_components to cdickens; GRANT execute ON sa_user_admin to cdickens; GRANT execute ON sa_label_admin to cdickens; GRANT execute ON sa_policy_admin to cdickens; GRANT execute ON sa_audit_admin to cdickens;sdf
Now we can log in as our Security Administrator and create the levels and compartments
CONNECT cdickens/welcome1 -- Levels -- The first argument is the name of the policy we are creating the level for -- The second argument is a number for the level. This will be used to determine precedence later on -- The third and fourth argument are the name and description for the label exec sa_components.create_level('SECDEMO_ACCESS', 1000, 'SECRET', 'SECRET'); exec sa_components.create_level('SECDEMO_ACCESS', 2000, 'TOPSECRET', 'TOPSECRET'); -- Compartments -- The first argument is the name of the policy we are creating the compartment for -- The second argument is a number for the compartment. This will only be used for ordering the display of a compartment. It does not determine precedence -- The third and fourth argument are the name and description of the compartment exec sa_components.create_compartment('SECDEMO_ACCESS', 100, 'Narcotics', 'Narcotics'); exec sa_components.create_compartment('SECDEMO_ACCESS', 100, 'Narcotics', 'Fraud'); exec sa_components.create_compartment('SECDEMO_ACCESS', 100, 'Narcotics', 'Terrorism');
Finally we combine the levels and compartments to create all the data labels. The data labels are the combination of the levels and compartments and are the valid labels that can be applied to any document. Eg: ‘Top Secret:Narcotics’ or ‘Secret: Fraud’
-- The first argument is the name of the policy we are creating the label for -- The second argument is the identifier for the label. Note that is the combination of the level and compartment number. This is just to ensure it's unique -- The third argument is the name of the label exec sa_label_admin.create_label('SECDEMO_ACCESS', 1100, 'Secret:Narcotics'); exec sa_label_admin.create_label('SECDEMO_ACCESS', 1100, 'Secret:Fraud'); exec sa_label_admin.create_label('SECDEMO_ACCESS', 1100, 'Secret:Terrorism'); exec sa_label_admin.create_label('SECDEMO_ACCESS', 1100, 'TopSecret:Narcotics'); exec sa_label_admin.create_label('SECDEMO_ACCESS', 1100, 'TopSecret:Fraud'); exec sa_label_admin.create_label('SECDEMO_ACCESS', 1100, 'TopSecret:Terrorism');
Now, we assign the labels to our users.
exec sa_user_admin.set_user_labels('SECDEMO_ACCESS', 'jcooper', 'TopSecret:Narcotics,Fraud,Terrorism'); exec sa_user_admin.set_user_labels('SECDEMO_ACCESS', 'mtwain', 'TopSecret:Narcotics,Fraud'); exec sa_user_admin.set_user_labels('SECDEMO_ACCESS', 'ltolstoy', 'Secret:Narcotics,Fraud,Terrorism'); exec sa_user_admin.set_user_labels('SECDEMO_ACCESS', 'cdoyle', 'Secret:Fraud'); exec sa_user_admin.set_user_labels('SECDEMO_ACCESS', 'jausten', 'Secret:Terrorism');
When documents are added to our table we’d like to automatically apply the label. Since we have all the detail we need in the insert we can use a labelling function to apply this. This will just combine the Classification and the Compartment together. For a real system we should probably have a more robust method to ensure that the compartment and levels actually exist, but for our demo the worst that can happen is that a document can no longer be viewed.
-- First grant some permissions so we can call the to_lbac_data_label function GRANT execute ON to_lbac_data_label to APP_OWNER WITH GRANT OPTION; connect APP_OWNER/welcome1 CREATE OR REPLACE FUNCTION generate_label (Classification VARCHAR2, Compartment VARCHAR2) return LBACSYS.LBAC_LEVEL AS BEGIN return to_lbac_data_label('SECDEMO_ACCESS', Classification||':'||Compartment); END; / -- Give the lbacsys user permission to run this function GRANT execute ON APP_OWNER.generate_label TO lbacsys;
Now let’s actually apply the policy to the table. From this point forward the table will have label security applied and you’ll only be able to see results in it if you have the correct permission.
begin SA_POLICY_ADMIN.APPLY_TABLE_POLICY( policy_name => 'SECDEMO_ACCESS', schema_name => 'APP_OWNER', table_name => 'documents', table_options => NULL, label_function => 'APP_OWNER.generate_label(:new.sensitivity,:new.doc_category)'); end; /
Now we grant the labels to each of our users
-- Allow the APP_OWNER permission to assign labels to users exec SA_USER_ADMIN.SET_USER_PRIVS('SECDEMO_ACCESS', 'APP_OWNER', 'FULL'); connect APP_OWNER/welcome1 -- Let's create a synonym to our documents table so that we can access it with 'SECDEMO' instead of it's fully qualified name create or replace public synonym SECDEMO for APP_OWNER.documents; grant select,insert,update,delete on SECDEMO to APP_USR; grant select,insert,update,delete on documents to APP_USR;
Finally let’s add some data to the database. You can insert data using any of the users we defined (jcooper, mtwain, ltolstoy etc) as users can create documents that are outside or above their security level (eg: LTolstoy can’t access Top Secrete documents, but he’s allowed to insert documents that are Top Secret. He just won’t be able to see the documents once they are inserted. You can modify this behaviour to prevent insert outside your level).
connect jcooper/welcome1 INSERT INTO SECDEMO VALUES ('N1', 'CDoyle', '3/5/13', 'Narcotics', '', 'Secret', '', 'Doc2.pdf'); INSERT INTO SECDEMO VALUES ('X1', 'JCooper', '2/12/14', 'Terrorism', '', 'TopSecret', '', 'Doc3.pdf');
Now run ‘SELECT * FROM SECDEMO’ as JCooper and as CDoyle. You should see documents returned for JCooper, but only 1 for CDoyle:
As you can see both users ran the exact same query, but got different results. This is the power of Label Security, the database takes care of our data security! It also makes developing our applications a lot easier, as we don’t have to construct complex WHERE clauses to work out the permissions for our users. Not to mention the benefit from ensuring that our DBA’s and other prying eyes can’t see our data!
In the next post in this series we will construct a simple node.js website to show how we can simplify application development and still ensure data security. Stay tuned.