Database Security

Database Security (Common-sense Principles) Technorati Tag: Database Security Lately, database security issues have been flooding the media and Internet newswires. First with the Slammer worm and most recently criminals accessing over 8 million credit card numbers. So I sit back and say to myself, "Did the sysadmins fall asleep behind the wheel?" As the internet has boomed and we've increased our reliance on the convenience and relative low cost of web-enabled information systems, we have become lazy in our implementation of basic security practices. Now part of this problem is the pressure placed on today's system admin's by the upper-crust of corporate America. The first question to every sysadmin is, "How soon can this be up?" and not "How much of a security risk is this?". In light of current events it has become painfully obvious we need to re-adjust our thinking. So, let me begin this article with a brief synopsis of how security policies should be implemented and then move into actual system configuration. Basic Security Structure Through my travels as a Network Security Specialist I have consistently come across companies with a single focus in mind, "Software Security". They place so much emphasis on this single portion of security that they loose sight of the big picture. The big picture is of course, "Without a structured security hierarchy any basic security policy will fail!" To often system administrators are left to their own accord, managing the security of their systems with little or no oversight by a higher security administrator. This raises the following questions: • • • •

Who ensures system administrators are following security guide-lines? How does an organization ensure all system administrators are applying the latest patches? What organization ensures that the latest patches have been tested to ensure they do not cause additional system faults Who performs security audits on the corporation as a whole?

An example of a good clean and effective network security organization.

Without a proper structure you begin to get chaos when it comes to such an important topic as security, chaos could be cataclismic. For example: Jim of the East Coast Office has all of his patches up to date, but he has an insecure link with Bill on the west-coast, who has failed to properly configure his firewall. This situation would allow for a full system compromise. To ensure a situation such as this does not occur someone or a group of someone's should be looking at the big picture. Now that I have gotten that basic security organization rant out of my system, let me begin a technical look at database security. Database Vulnerabilities (The many fronts of the security war!) Basically database security can be broken down into the following key points of interest. • • • •

Server Security Database Connections Table Access Control Restricting Database Access

Server Security Server security is the process of limiting actual access to the database server itself, and in my humble opinion it is the most important angle of security and should be carefully planned. The basic idea is this, "You can't access what you can't see". Why in the name of the Almighty (or whoever else you believe in, or if you are an Atheist, substitute your own name here) would you let your database server be visible to the world. This is not a web server here, there should be no such thing as an anonymous connection. Now some people would say, "Well, what if your database server is supplying information to dynamic web pages?", well I'll turn that around and say, "Your database back end should never be on the same machine as your web server, not just for security, but for performance!" If your database server is supplying information to a web server then it should be configure to allow connections only from that web server. Now that bring mes to the next point of discussion:

Here Trusted IP Access has limited the database server to only answering information requests from the known IP of the web server.

Trusted IP addresses Every server, should be configured to only allow trusted IP addresses. You don't allow just anyone to come into your house and talk to your children. In the same respect you should know exactly who should be allowed to "talk" to your database server. If it's a back end for a web server., then only that web server's address should be allowed to access that database server. If the database server is supplying information to a homegrown application that is running on the internal network, then it should only answer to addresses from within the internal network. Also please none of this cheap mentality of hosting your web databases on the same server that houses internal database information. Why would you have internal information out in the DMZ, its not called the DMZ for nothing. Database Connections These days with the number of Dynamic Applications it becomes tempting to allow immediate unauthenticated updates to a database. I say, "Ney!" to such laziness. If you are going to allow users to make updates to a database via a web page, ensure that you validate all updates to ensure that all updates are warranted and safe. For example ensure that you are removing any possible SQL code from a user supplied input. If a normal user should never be inputting it don't allow the data to ever be submitted. If you are one of those administrators that feels the need to use ODBC connections ensure that every connection uses it's own unique user to access the shared data. It personally makes my skin crawl when I see the user account "sa" used for every connection and data source on the server. Does every employee in your company have keys to every room in the building? I'll let you address that problem quietly if they do. Table Access Control Table access control is probably one of the most overlooked forms of database security because of the inherent difficult in applying it. Properly using Table access control will require the collaboration of both system administrator and database developer, and we all know that "collaboration" is a foreign word in the IT industry. An example would be allowing read access to user imputed information to the public. If a user just imputed the information why would they have to look at it within the same session. Or, if a table is just used for system reference why should it have any other permissions beside read available? Unfortunately table structure and proper relational database structure and development is a little out of the scope of this article. But, keep a look out for it in my upcoming articles. Restricting Database Access Now being that we have completed a basic overview of database security I want to dive a little further into the specifics of server security. Mainly into the network access of the system. Specifically targeting Internet based databases, since they have been the most recent targets of attacks. All web-enabled applications have ports that they listen to ( I know this is pretty basic to most of you but, it needs to be said for the beginners!).

Most cyber criminals ( I always refrain from the media sensationalized term "Hackers" or "Crackers") are going to do a simple "port scan" to look for ports that are open that popular database systems use by default. Now I say by default, because you can change the ports a service listens on, which I personally feel is a great way to throw off a criminal. First they will attempt to determine if a machine is even at a specific address. They will do this by pinging the system. (If you don't know what ping is quietly close this article, you need to do some studying first!) This is done by simply opening up a command line and typing "ping". C:\ ping 127.0.0.1 or root@localhost: ~$: ping 127.0.0.1 The response should look like this: Pinging 127.0.0.1 with 32 bytes of data: Reply from 127.0.0.1: bytes=32 time<10ms TTL=128 Reply from 127.0.0.1: bytes=32 time<10ms TTL=128 Reply from 127.0.0.1: bytes=32 time<10ms TTL=128 Reply from 127.0.0.1: bytes=32 time<10ms TTL=128 Ping statistics for 127.0.0.1: Packets: Sent = 4, Received = 4, Lost = 0 (0% Approximate round trip times in milli-seconds: Minimum = 0ms, Maximum = 0ms, Average = 0ms An example of the ping command being used on a windows box. Click to enlarge . The criminal now knows there is a system answering at this address. First thing to prevent this is to disable any ICMP packets. This will prevent ping requests from being replied to. There are many ways to prevent open access from the Internet and each database system has it's own set of unique features as well as each OS. So I am merely going to touch on a few methods. •

•

•

Trusted IP addresses - UNIX servers are configured to answer only pings from a list of trusted hosts. In UNIX, this is accomplished by configuring the rhosts file, which restricts server access to a list of specific users. Server account disabling- If you suspend the server ID after three password attempts, attackers are thwarted. Without user ID suspension, an attacker can run a program that generates millions of passwords until it guesses the user ID and password combination. Special tools -Products such as RealSecure by ISS send an alert when an external server is attempting to breach your system's security.

Oracle has a wealth of authentication methods:

• • • •

• •

Kerberos security- This popular "ticket"-based authentication system sidesteps several security risks. Virtual private databases- VPD technology can restrict access to selected rows of tables. Role-based security- Object privileges can be grouped into roles, which can then be assigned to specific users. Grant-execute security- Execution privileges on procedures can be tightly coupled to users. When a user executes the procedures, they gain database access, but only within the scope of the procedure. Authentication servers-Secure authentication servers provide positive identification for external users. Port access security - All Oracle applications are directed to listen at a specific port number on the server. Like any standard HTTP server, the Oracle Web Listener can be configured to restrict access.

hope that I have broadened your view of database security, and quite possibly helped eliminate or at least lower the threat of criminals looking for the "easy kill". (I know that is a little egotistical of me) If you have any questions or wish to contact me. (Flames and praise are welcomed equally) contact me at: [email protected] Blake Wiedman

Automatic Encryption Of Sensitive Information Most encryption solutions require specific calls to encryption functions within the application code. This is expensive because it typically requires extensive understanding of an application as well as the ability to write and maintain software. In general, most organizations don't have the time or expertise to modify existing applications to make calls to encryption routines. Oracle Transparent Data Encryption addresses the encryption problem by deeply embedding encryption in the Oracle database. Application logic performed through SQL will continue to work without modification. In other words, applications can use the same syntax to insert data into an application table and the Oracle database will automatically encrypt the data before writing the information to disk. Subsequent select operations will have the data transparently decrypted so the application will continue to work normally. This is important because existing applications generally expect to see application data unencrypted. Displaying encrypted data may, at a minimum, confuse the application user and may even break an existing application. Setting The Encryption Key Oracle Transparent Data Encryption provides the key management infrastructure necessary for implementing encryption. Encryption works by passing clear text data along with a secret, known as the key, into an encryption program. The encryption program encrypts the clear text data using the supplied key and returns the data encrypted. Historically the burden of creating and maintaining the secret or key has

been on the application. Oracle Transparent Data Encryption solves this problem by automatically generating a master key for the entire database. Upon starting up the Oracle database, an administrator must open an object known as an Oracle Wallet using password separate from the system or DBA password. The administrator then initializes the database master key. The master key is automatically generated. Performance Encryption typically creates problems for existing application indexes because the index data isn't encrypted. Oracle Transparent Data Encryption encrypts the index value associated with a given application table. This means that equality searches within an application will see little to no decrease in performance. For example, assume an index exists on application PERSON ID and the application executes the following statement: SQL> Select rating from credit where person id = '23590'; The Oracle database will use the existing application index even though the PERSON ID information is encrypted in the database. Scenario In this tutorial, you will create a table that contains an encrypted column. You will create an index on the encrypted column and grant access to the column for a particular user. This user will then make a change to the data. You will then create a function which contains proper access control to the encrypted data and then apply the function through a VPD policy. Transparent Data Encryption encrypts data right before it is written to disk and decrypts it when it is read. It's transparent to all applications that use the SQL layer. Hence there is no easy way to verify that data has truly been encrypted. Since Oracle LogMiner records what is written to disk, you can access the information.

Preparing the Database for Encryption In this section, you will update your sqlnet.ora, create an encrypted wallet (ewallet.p12), open the wallet, and create the master key for TDE. Perform the following: 1. You need to update your sqlnet.ora file to include an entry for ENCRYPTED_WALLET_LOCATION. Open a terminal window and enter the following commands: cd $ORACLE_HOME/network/admin gedit sqlnet.ora

Add the following entry to the end of the file: ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA=

(DIRECTORY=/u01/app/oracle/product/10.2.0/db_1/)))

2. Save your changes and close the file. Note: Any directory can be picked for the encrypted wallet, but the path should not point to the standard obfuscated wallet (cwallet.sso) created during DB installation.

3. Next, you need to open the wallet and create the master encryption key. From your terminal window, enter the following commands: cd /home/oracle/wkdir sqlplus /nolog @tde00_dbsetup connect / as sysdba alter system set key identified by "welcome1";

The above alter command does the following: If no encrypted wallet is present in the directory specified, the encrypted wallet is created (ewallet.p12), the wallet is opened, and the master key for TDE is created/recreated. If the encrypted wallet is present in the directory specified, the walled is opened, and the master key for TDE is created/recreated. Note: only users with the 'alter system' privilege can create a master key or open the wallet. The master key should only be created once, unless you want to re-encrypt your data with a new encryption key !!! For later sessions, you do not want to use the command given above; you need the wallet to be open (it has been closed when you shut down your database), but you don't want to create a new master key. Then the command is: alter system set wallet open identified by "welcome1";

The master encryption key is necessary because each table has its own encryption key. These column keys are stored in the database. Since the wallet can only store a limited number of keys and is not very scalable, the column keys are encrypted with the master key. This way, you can have as many column keys as needed, with only a small number of master keys stored in the wallet (including retired keys, that you may need one day to decrypt data from an old backup-tape). By default, the command above generates a key using the Advanced Encryption Standard with 192 bits (AES192). 3DES could also be used, or a smaller or bigger number of bits for the AES encryption.

Creating a Table with an Encrypted Column In this tutorial, you will create some users, create a table with an encrypted column to store credit card information. You will insert some data into the table, create an index on the encrypted column and grant access to the data. Perform the following: 1. You first need to create some users. From your SQL*Plus session, execute the following script: @tde01_crusers connect system/oracle prompt Create users: JKING, LSMITH and LDORAN grant connect to JKING identified by welcome1; grant connect, DBA to LSMITH identified by welcome1; grant connect to LDORAN identified by welcome1;

Note: You have granted LSMITH DBA role to demonstrate that TDE supports indices for equality searches. As a result, the output of dbms_xplan should include INDEX RANGE SCAN. 2. Next you will create a table that contains a column to store encrypted (default AES192) credit card information. NO SALT is specified since there will be an index on the credit_card_number column, which is not possible when the encrypted values are salted. From your SQL*Plus session, execute the following script: @tde02_crtabl connect oe/oe create table cust_payment_info (first_name varchar2(11), last_name varchar2(10), order_number number(5), credit_card_number varchar2(16) ENCRYPT NO SALT, active_card varchar2(3));

3. Now you can add some data to the table you just created . From your SQL*Plus session, execute the following script: @tde03_poptabl insert into cust_payment_info values ('Jon', 'Oldfield', 10001, '5446959708812985','YES'); insert into cust_payment_info values ('Chris', 'White', 10002, '5122358046082560','YES'); insert into cust_payment_info values ('Alan', 'Squire', 10003, '5595968943757920','YES'); insert into cust_payment_info values ('Mike', 'Anderson', 10004, '4929889576357400','YES'); insert into cust_payment_info values

('Annie', 'Schmidt', 10005, '4556988708236902','YES'); insert into cust_payment_info values ('Elliott', 'Meyer', 10006, '374366599711820','YES'); insert into cust_payment_info values ('Celine', 'Smith', 10007, '4716898533036','YES'); insert into cust_payment_info values ('Steve', 'Haslam', 10008, '340975900376858','YES'); insert into cust_payment_info values ('Albert', 'Einstein', 10009, '310654305412389','YES');

4. You need to grant access to your users to the customer payment information table. In this case, LSMITH is the only user who can update the information. The other users can only view it. From your SQL*Plus session, execute the following script: @tde05_grant_access grant select on oe.CUST_PAYMENT_INFO to LDORAN; grant select, update on oe.CUST_PAYMENT_INFO to LSMITH; grant select on oe.CUST_PAYMENT_INFO to JKING;

Making Changes to Encrypted Data In this section, you will make a change to the table as LSMITH. Perform the following: 1. Since you gave LSMITH update access, you should be able to make a change. From your SQL*Plus session, execute the following script: @tde06_make_update prompt *** Connect as Lindsay Smith (Card_V) conn LSMITH/welcome1; update oe.CUST_PAYMENT_INFO set ACTIVE_CARD='NO' where CREDIT_CARD_NUMBER='4556988708236902';

2. In order to see the execution plan, execute the following script: @tde06a_review_xplan

select * from table (dbms_xplan.display_cursor);

3. In order to see the execution plan, execute the following script: @tde06a_review_xplan select * from table (dbms_xplan.display_cursor);

4. The table user_encrypted_columns will tell you which column is encrypted and its algorithm. From your SQL*Plus session, execute the following script: @tde07_select_encrypt_col connect oe/oe col TABLE_NAME format a18; col COLUMN_NAME format a19; col ENCRYPTION_ALG format a17; select * from user_encrypted_columns;

Adding and Applying a VPD Policy Since encryption does not replace proper access control, you will limit access to the rows with a very simple VPD policy: At first you will check if the person who logs into the database is an employee, and then you will limit access to oe.cust_payment_info by credit card number: Card_A begins with '34' or Janette King '37' Card_V begins with '4' Lindsay Smith Card_M begins with '5' Louise Doran If you review the data you loaded into the table, you see that the credit card number associated with 'Albert Einstein' will never be selected by authorized users. It is only selected by intruders who circumvented the access control policies or administrative users who are exempt from these policies. This allows for highly focused auditing of this table. Perform the following: 1. You first need to create the function that contains the logic you want for the credit card and employee validation. From your SQL*Plus session, execute the following script: @tde08_crfunction connect system/oracle; prompt prompt *** Create policy function to create the where-clause: create or replace function f_policy_oe_cust_payment_info -- Function must have the following parameters (schema in varchar2, tab in varchar2) -- Function will return a string that is used as a WHERE clause return varchar2 as v_manager_id number:=0; is_employee number:=0; v_user varchar2(20); out_string varchar2(70) default '1=2 '; begin -- get session user

v_user := lower(sys_context('userenv','session_user')); -- Is the user an employee? begin select manager_id into v_manager_id from hr.employees where lower(email) = v_user; is_employee:=1; exception when no_data_found then is_employee:=2; end; -- create where clause when user is authorized to see parts of the table if is_employee=1 and lower(v_user)='jking' and v_manager_id=146 then out_string := out_string ||'or CREDIT_CARD_NUMBER like ''34%'' or CREDIT_CARD_NUMBER like ''37%'''; elsif is_employee=1 and lower(v_user)='lsmith' and v_manager_id=146 then out_string := out_string ||'or CREDIT_CARD_NUMBER like ''4%'''; elsif is_employee=1 and lower(v_user)='ldoran' and v_manager_id=146 then out_string := out_string ||'or CREDIT_CARD_NUMBER like ''5%'''; end if; return out_string; end; /

2. Now you can add the policy to the cust_payment_info table. From your SQL*Plus session, execute the following script: @tde09_addpolicy prompt prompt *** Add policy to 'oe.cust_payment_info' table: begin dbms_rls.add_policy('oe','cust_payment_info','ac_cust_payment_info', 'system','f_policy_oe_cust_payment_info', policy_type => dbms_rls.context_sensitive); end; /

Testing the Policy In this section, you will connect as each employee to see if the policy works. Three things happen to show the true transparency of TDE: An index has been used on the encrypted column Even though the credit card numbers are stored encrypted, the where clause in the VPD policy looks for numbers in clear text and retrieves the correct rows. The rows visible to the three employees contain the credit card numbers in clear text. Most likely, the employees wouldn't even know that the data was stored encrypted. Perform the following: Using LogMiner to View Redo Logs Since TDE is done right before the data is written and transparent to all applications, there is no easy way to verify that the data has truly been encrypted. Since Oracle LogMiner records what has been written to disk, you can use it to view what is contained in the log files. Perform the following: 1. You will first connect as Janette King. She has access to Card_A that begins with '34' or '37'. From your SQL*Plus session, execute the following script: @tde10_testpolicy_jking

prompt prompt *** Connect as Janette King (Card_A) conn JKING/welcome1; col CREDIT_CARD_NUMBER heading Card_A format a18; select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;

2. You will now connect as Louise Doran. She has access to Card_M that begins with '5'. From your SQL*Plus session, execute the following script: @tde11_testpolicy_ldoran prompt prompt *** Connect as Louise Doran (Card_M) conn LDORAN/welcome1; col CREDIT_CARD_NUMBER heading Card_M format a18; select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;

3. You will then connect as Lindsay Smith. She has access to Card_V that begins with '4'. From your SQL*Plus session, execute the following script: @tde12_testpolicy_lsmith prompt prompt *** Connect as Lindsay Smith (Card_V) conn LSMITH/welcome1; col CREDIT_CARD_NUMBER heading Card_V format a18; select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;

4. From your SQL*Plus session, execute the following script: @tde13_logminer connect / as sysdba; alter database add supplemental log data; REM select member as LOG_FILE_LOCATION from v$logfile; EXECUTE DBMS_LOGMNR.ADD_LOGFILE ('+MY_DG2/racdb/onlinelog/group_3.263.562151437', DBMS_LOGMNR.NEW); EXECUTE DBMS_LOGMNR.ADD_LOGFILE ('+MY_DG2/racdb/onlinelog/group_2.262.562151433', DBMS_LOGMNR.ADDFILE); EXECUTE DBMS_LOGMNR.ADD_LOGFILE ('+MY_DG2/racdb/onlinelog/group_1.261.562151431', DBMS_LOGMNR.ADDFILE) prompt start LogMiner: EXECUTE DBMS_LOGMNR.START_LOGMNR (options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY); select sql_redo from v$logmnr_contents where table_name = 'CUST_PAYMENT_INFO' and operation='INSERT';

LogMiner does not support encrypted data, so the encrypted values in the credit_card_number column are displayed as Unsupported Type. Note: If you run this tutorial in your own environment, you need to run the following command to determine the logfile names and then modify the script accordingly: select member as LOG_FILE_LOCATION from v$logfile;

Recreating Your Table Without the Encrypted Column To show the difference between what you would see if the column was not encrypted, you will drop the table and recreate it with no columns encrypted. Perform the following: 1. From your SQL*Plus session, execute the following script: @tde14_crtabl2 connect oe/oe drop table cust_payment_info; create table cust_payment_info (first_name varchar2(11), last_name varchar2(10), order_number number(5), credit_card_number varchar2(20), active_card varchar2(3)); insert into cust_payment_info values ('Jon', 'Oldfield', 10001, 5446959708812985,'YES'); insert into cust_payment_info values ('Chris', 'White', 10002, 5122358046082560,'YES'); insert into cust_payment_info values ('Alan', 'Squire', 10003, 5595968943757920,'YES'); insert into cust_payment_info values ('Mike', 'Anderson', 10004, 4929889576357400,'YES'); insert into cust_payment_info values ('Annie', 'Schmidt', 10005, 4556988708236902,'YES'); insert into cust_payment_info values ('Elliott', 'Meyer', 10006, 374366599711820,'YES'); insert into cust_payment_info values ('Celine', 'Smith', 10007, 4716898533036,'YES'); insert into cust_payment_info values ('Steve', 'Haslam', 10008, 340975900376858,'YES'); insert into cust_payment_info values ('Albert', 'Einstein', 10009, 310654305412389,'YES'); create index cust_payment_info_idx on cust_payment_info (credit_card_number); grant select on oe.CUST_PAYMENT_INFO to LDORAN; grant select, update on oe.CUST_PAYMENT_INFO to LSMITH; grant select on oe.CUST_PAYMENT_INFO to JKING; prompt *** Connect as Lindsay Smith (Card_V) conn LSMITH/welcome1; update oe.CUST_PAYMENT_INFO set ACTIVE_CARD='NO' where CREDIT_CARD_NUMBER=4556988708236902;

Reviewing the Redo Logs Again You can now rerun the logminer script to see what it contains. Perform the following: From your SQL*Plus session, execute the following script: @tde15_logminer2 connect / as sysdba; EXECUTE DBMS_LOGMNR.ADD_LOGFILE ('+MY_DG2/racdb/onlinelog/group_3.263.562151437', DBMS_LOGMNR.NEW); EXECUTE DBMS_LOGMNR.ADD_LOGFILE ('+MY_DG2/racdb/onlinelog/group_2.262.562151433', DBMS_LOGMNR.ADDFILE); EXECUTE DBMS_LOGMNR.ADD_LOGFILE ('+MY_DG2/racdb/onlinelog/group_1.261.562151431', DBMS_LOGMNR.ADDFILE) prompt start LogMiner: EXECUTE DBMS_LOGMNR.START_LOGMNR (options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY); select sql_redo from v$logmnr_contents where

table_name = 'CUST_PAYMENT_INFO' and operation='INSERT';

The column is not encrypted, and LogMiner shows the clear text data which has been written to disk. Cleanup To cleanup your environment, perform the following: From your SQL*Plus session, execute the following script: @tde16_cleanup connect system/oracle drop user JKING cascade; drop user LSMITH cascade; drop user LDORAN cascade;

drop function f_policy_oe_cust_payment_info; connect oe/oe drop table cust_payment_info; exit;

Using Oracle Label Security Purpose The goal of this tutorial is to use Oracle Label Security to set up security based on label policies. Time to Complete Approximately 30 minutes Topics This tutorial covers the following topics: Overview Setup Creating a Policy Setting User Authorizations Applying a Policy to a Table Adding Labels to the Data Creating an Index on OLS_COLUMN Add a VPD WHERE Clause to the Policy Revoking Access From Admin Users Testing the Policy Implementation Cleanup Summary Viewing Screenshots Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

Overview Oracle Label Security makes separation of duty easy: When LBACSYS, the default Oracle-DBA for OLS, creates a policy, a role with the name "<policy_name>_DBA" is automatically granted to LBACSYS with the 'ADMIN' option, so that it can be granted to other users for them to complete and 'own the policy. In this tutorial, the users are called "sec_admin" and "HR_sec". There are three parts to an access control policy: 1. The table containing the sensitive data (LOCATIONS) and the owner of this data (hr), who determines the sensitivity of his data and who will get access to which level of sensitivity. 2. The user-related part of the OLS policy is maintained by a user called HR_sec, who creates database users and roles and grants clearances to them.

3. The Oracle Label Security labels (both for data and users), which enables the access mediation defined by the data owner, are created by sec_admin. Furthermore, this user is responsible for maintaining the performance of the application. When the policy is tested and ready for production, LBACSYS revokes all necessary execution rights and roles from both "HR_sec" and "sec_admin". Back to Topic List

Setup In this tutorial, you create a set of users and roles to demonstrate how OLS works. To create users and roles, perform the following steps: 1. Open a terminal window and execute the following commands: cd /home/oracle/wkdir sqlplus /nolog @ols_create_admin_users_and_roles set echo off prompt *** Create admin users: sec_admin and hr_sec prompt connect system/oracle grant connect, create any index to sec_admin identified by welcome1; grant connect, create user, drop user, create role, drop any role to hr_sec identified by welcome1; prompt prompt ***** Create roles: emp_role connect hr_sec/welcome1; create role emp_role; prompt ***** Grant system and object privileges to roles and users connect system/oracle; grant connect to emp_role; connect hr/hr; grant select on hr.locations to emp_role; connect hr_sec/welcome1; prompt ***** Create Steven King (President) create user SKING identified by welcome1; grant emp_role to SKING; prompt ***** Create Karen Partners (Sales Manager rep. to SKing) create user KPARTNER identified by welcome1; grant emp_role to KPARTNER;

prompt ***** Create Louise Doran (Sales Rep in Karen Partners team) create user LDORAN identified by welcome1; grant emp_role to LDORAN;

t

Creating a Policy In this section, you will create a policy, grant the role to the admin users, creates the levels and labels for the policy. Perform the following: 1. LBACSYS creates a policy which will control access to the hr.LOCATIONS table; the name of the policy is 'ACCESS_LOCATIONS'; the name of the hidden column which will be appended to the hr.LOCATIONS table to hold the data labels is called 'OLS_COLUMN'. From a SQL*Plus session, execute the following script to create your policy. @ols_create_policy connect lbacsys/lbacsys BEGIN SA_SYSDBA.CREATE_POLICY ( policy_name => 'ACCESS_LOCATIONS', column_name => 'OLS_COLUMN', default_options => 'READ_CONTROL,LABEL_DEFAULT,HIDE'); END; /

2. When the policy is created, an administration role for this policy is automatically granted to LBACSYS with the 'admin' option. In order to enable proper separation of duty, LBACSYS grants this role and some additional execution rights to the admin users 'HR_sec' and 'sec_admin'. From a SQL*Plus session, execute the following script: @ols_grant_role

Prompt grant ACCESS_LOCATIONS_DBA to sec_admin and HR_sec: grant ACCESS_LOCATIONS_DBA to sec_admin; grant ACCESS_LOCATIONS_DBA to HR_sec; Prompt grant execute on SA_COMPONENTS to sec_admin: grant execute on SA_COMPONENTS to sec_admin; Prompt grant execute on SA_USER_ADMIN to HR_sec: grant execute on SA_USER_ADMIN to HR_sec;

3. The sec_admin user creates the levels for the policy. Each policy consists of levels (one or more), and optional compartments and groups, which are not included in this example. Execute the following script to create levels for your policy. @ols_create_level

connect sec_admin/welcome1; BEGIN SA_COMPONENTS.CREATE_LEVEL ( policy_name => 'ACCESS_LOCATIONS', level_num => 1000, short_name => 'PUB', long_name => 'PUBLIC'); END; / execute SA_COMPONENTS.CREATE_LEVEL ('ACCESS_LOCATIONS',2000,'CONF','CONFIDENTIAL'); execute SA_COMPONENTS.CREATE_LEVEL ('ACCESS_LOCATIONS',3000,'SENS','SENSITIVE');

4. The sec_admin user also creates the labels (which only contain levels, no compartments or groups). Execute the following script: @ols_create_label connect sec_admin/welcome1; execute SA_LABEL_ADMIN.CREATE_LABEL('ACCESS_LOCATIONS',1000,'PUB'); Prompt execute SA_LABEL_ADMIN.CREATE_LABEL('ACCESS_LOCATIONS',2000,'CONF') execute SA_LABEL_ADMIN.CREATE_LABEL('ACCESS_LOCATIONS',2000,'CONF'); Prompt execute SA_LABEL_ADMIN.CREATE_LABEL('ACCESS_LOCATIONS',3000,'SENS') execute SA_LABEL_ADMIN.CREATE_LABEL('ACCESS_LOCATIONS',3000,'SENS');

Setting User Authorizations Later, data access rights will be limited by applying the labels you created earlier to the data. Before this, you need to authorize users and grant privileges to the policies, in order to define the matching access rights to these users. Perform the following: 1. The HR_sec user binds the labels to the users, defining their clearance. From a SQL*Plus session, execute the following script to create user label authorizations: @ols_set_user_label connect hr_sec/welcome1; BEGIN SA_USER_ADMIN.SET_USER_LABELS ( policy_name => 'ACCESS_LOCATIONS', user_name => 'SKING', max_read_label => 'SENS', max_write_label => 'SENS', min_write_label => 'CONF', def_label => 'SENS', row_label => 'SENS'); END; / Prompt Karin Partners is allowed to read public and confidential data Prompt from hr.LOCATIONS. execute SA_USER_ADMIN.SET_USER_LABELS ('ACCESS_LOCATIONS','KPARTNER','CONF','CONF','PUB','CONF','CONF'); Prompt Louise Doran is allowed to read public data from hr.LOCATIONS. execute SA_USER_ADMIN.SET_USER_LABELS ('ACCESS_LOCATIONS', 'LDORAN', 'PUB','PUB','PUB','PUB','PUB');

2. HR, the owner of the LOCATIONS table, needs 'FULL' access to the table, since the user will later add the data labels into the hidden OLS_COLUMN defined earlier. From a SQL*Plus session, execute the following scrip: @ols_set_user_privs

connect hr_sec/welcome1; execute SA_USER_ADMIN.SET_USER_PRIVS ('ACCESS_LOCATIONS','HR','FULL');

Applying a Policy to a Table You can apply Oracle Label Security policies to entire application schemes or to individual application tables. You will apply it to the LOCATIONS table. Perform the following: 1. The sec_admin user applies the policy to the table. From now on, since READ_CONTROL has been set in the policy definition and no labels are added to the rows, no one can read the data (except HR). Execute the following script: @ols_apply_policy connect sec_admin/welcome1; execute SA_POLICY_ADMIN.APPLY_TABLE_POLICY ('ACCESS_LOCATIONS', 'HR', 'locations');

Adding Labels to the Data Before you can test the policy, you must add the label to the data by performing the following: 1. HR, the owner of the LOCATIONS table, adds the labels for each row into the hidden column 'OLS_COLUMNS'. In this case, you will assign the Sensitive label to the cities: Beijing, Tokyo and Singapore. You will assign the Confidential label to the cities: Munich, Oxford and Roma. And all other cities, you will assign the label Public. @ols_add_label_column connect hr/hr; update locations set OLS_COLUMN = char_to_label('ACCESS_LOCATIONS','SENS') where upper(city) in ('BEIJING', 'TOKYO', 'SINGAPORE'); update locations set OLS_COLUMN = char_to_label('ACCESS_LOCATIONS','CONF')

where upper(city) in ('MUNICH', 'OXFORD', 'ROMA'); update locations set OLS_COLUMN = char_to_label('ACCESS_LOCATIONS','PUB') where OLS_COLUMN is NULL;

Creating an Index on OLS_COLUMN To improve performance of data access, you can create a BITMAP INDEX on the OLS_COLUMN. Perform the following steps: 1. In order to increase performance, sec_admin creates a BITMAP INDEX on the OLS_COLUMN: @ols_create_index connect sec_admin/welcome1; create bitmap index hr.LOCATIONS_idx on hr.LOCATIONS (OLS_COLUMN);

Add a VPD WHERE Clause to the Policy In order to limit access for all users to a certain range of IP addresses, SEC_ADMIN uses Oracle Policy Manager to add a WHERE clause to the policy. Perform the following steps: 1. From a DOS prompt, enter the following command: oemapp opm

2. Login as the SEC_ADMIN user. 3. Navigate to the protected table and select the Predicate tab. 4. Check the box to make the text field editable and enter the following text and then click Apply. sys_context ('userenv','ip_address') between '130.35.44.0' and '130.35.44.255'

5. Select File > Exit.

Revoking Access from Admin Users In order to secure the policy you need to revoke policy-specific execution rights and roles from sec_admin and HR_sec. Perform the following steps: 1. From your SQL*Plus session, execute the following script: @ols_revoke_access connect lbacsys/lbacsys; Prompt revoke ACCESS_LOCATIONS_DBA from sec_admin and HR_sec: revoke ACCESS_LOCATIONS_DBA from sec_admin; revoke ACCESS_LOCATIONS_DBA from HR_sec; Prompt revoke execute on SA_COMPONENTS from sec_admin: revoke execute on SA_COMPONENTS from sec_admin; Prompt revoke execute on SA_USER_ADMIN from HR_sec: revoke execute on SA_USER_ADMIN from HR_sec;

Testing the Policy Implementation After establishing policies to tables and users, and adding labels to the data, you can now test them by performing the following: 1. Execute the following script to test the access from the SKING user. @ols_test_policy_sking connect SKING/welcome1; col city heading City format a25 col country_id heading Country format a11 col Label format a10 select city, country_id, label_to_char (OLS_COLUMN) as Label from hr.locations order by ols_column;

Note that the user SKING can see PUBLIC, CONFIDENTIAL and SENSITIVE data.

2. Now you can test the policy for the KPARTNER user by executing the following script: @ols_test_policy_kpartner connect kpartner/welcome1; col city heading City format a25 col country_id heading Country format a11 col Label format a10 select city, country_id, label_to_char (OLS_COLUMN) as Label from hr.locations order by ols_column;

Note that KPARTNER can see PUBLIC and CONFIDENTIAL data.

3. Now you can test the PRIVACY policy by executing the following script: @ols_test_policy_ldoran connect ldoran/welcome1; col city heading City format a25 col country_id heading Country format a11 col Label format a10 select city, country_id, label_to_char (OLS_COLUMN) as Label from hr.locations order by ols_column;

Note that LDORAN can only see PUBLIC data.

Cleanup Now that you have tested your policies, you can drop the users and the policies by performing the following: 1. Execute the following script: @ols_cleanup Prompt ==================================================================== prompt Clean up: prompt ==================================================================== Prompt connect LBACSYS conn lbacsys/lbacsys; prompt ==================================================================== prompt Drop policy (and remove label column) in case it already exists. prompt (Ignore the error message when it does not exist). prompt ==================================================================== execute sa_sysdba.drop_policy ('ACCESS_LOCATIONS', true); Prompt connect HR_sec conn HR_sec/welcome1; prompt ====================================================================

prompt HR_sec drops database users and roles (Ignore the error messages prompt should they not exist): prompt ==================================================================== drop user SKING cascade; drop user KPARTNER cascade; drop user LDORAN cascade; drop role emp_role; Prompt connect SYSTEM conn system/oracle; prompt ==================================================================== prompt SYSTEM drops admin users (Ignore the error messages should prompt they not exist): prompt ==================================================================== drop user sec_admin cascade; drop user HR_sec cascade;

Restricting Data Access Using Virtual Private Database Purpose The goal of this tutorial is to show the power of using the Virtual Private Database capability to restrict access to certain data to certain users. Time to Complete Approximately 30 minutes Topics This tutorial covers the following topics: Overview Scenario Setup Enabling a VPD Policy on the ORDERS Table Testing the VPD Policy on the ORDERS Table Viewing the Policy on the ORDERS Table Enabling a VPD Policy With Relevant Columns and Column Filtering on the CUSTOMERS Table Testing the VPD Policy on the CUSTOMERS Table Viewing the Policy on the CUSTOMERS Table Cleanup Summary Viewing Screenshots Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

Overview The Virtual Private Database (VPD) provides row-level access control beyond the capabilities of roles and views. For Internet access, the Virtual Private Database can ensure that online banking customers see only their own accounts. The Web-hosting companies can maintain data of multiple companies in the same Oracle database, while permitting each company to see only its own data. Within the enterprise, the Virtual Private Database results in lower costs of ownership in deploying applications. Security can be built once, in the data server, rather than in each application that accesses data. Security is stronger, because it is enforced by the database, no matter how a user accesses data. Security is no longer bypassed by a user accessing an ad hoc query tool or new report writer. The Virtual Private Database is a key technology that enables organizations to build hosted, Web-based applications.

Indeed, many Oracle applications themselves use VPD to enforce data separation for hosting, including Oracle SalesOnline.com and Oracle Portal. How the Virtual Private Database Works The Virtual Private Database is enabled by associating one or more security policies with tables or views. Direct or indirect access to a table with an attached security policy causes the database to consult a function that implements the policy. The policy function returns an access condition known as a predicate (a WHERE clause), which the database appends to the user's SQL statement, thus dynamically modifying the user's data access. You can implement VPD by writing a stored procedure to append a SQL predicate to each SQL statement that controls row-level access for that statement. For example, if John Doe (who belongs to Department 10) inputs the SELECT * FROM emp statement, then you can use VPD to add the WHERE DEPT = 10 clause. In this way, you use query modification to restrict data access to certain rows. The Virtual Private Database ensures that, no matter how a user gets to the data (through an application, a report writing tool, or SQL*Plus), the same strong access control policy is enforced. In this way, VPD can help banks ensure that customers see only their own accounts, that telecommunications firms can keep customer records safely segregated, and that human resources applications can support their complex rules of data access to employee records.

Scenario In this tutorial, you will create two different administrator users: sec_admin This user will create the policy function, apply the policy to the table and create an index on the table to improve performance. hr_sec

This user will create the database users and roles after verifying they are contained in either the CUSTOMERS or EMPLOYEES tables.

Setup In this tutorial, you create a set of users and roles to demonstrate how VPD works. To create users and roles, perform the following steps: 1. Open a terminal window and execute the following commands: cd /home/oracle/wkdir sqlplus /nolog @create_admin_users_and_roles set echo off prompt *** Create admin users: sec_admin and hr_sec

prompt connect / as sysdba grant connect, create procedure to sec_admin identified by welcome1; grant execute on sys.dbms_rls to sec_admin; grant connect, create user, drop user, create role, drop any role to hr_sec identified by welcome1; prompt prompt ***** Create roles: employee_role and customer_role connect hr_sec/welcome1; create role employee_role; create role customer_role; prompt ***** Grant system and object privileges to roles and users connect system/oracle; grant connect to employee_role; grant connect to customer_role; connect oe/oe; grant select on grant select on grant select on grant select on

oe.orders to oe.orders to oe.customers oe.customers

employee_role; customer_role; to sec_admin; to employee_role;

connect hr/hr; grant select on hr.employees to sec_admin; connect hr_sec/welcome1; prompt ***** Create Steven King (President) create user SKING identified by welcome1; grant employee_role to SKING; prompt ***** Create Karen Partners (Sales Manager rep. to SKing) create user KPARTNER identified by welcome1; grant employee_role to KPARTNER; prompt ***** Create Louise Doran (Sales Rep in Karen Partners team) create user LDORAN identified by welcome1; grant employee_role to LDORAN; prompt ***** Create Eleni Zlotkey (another Sales Manager) create user EZLOTKEY identified by welcome1; grant employee_role to EZLOTKEY; prompt ***** Create Matthias Hannah (Customer) create user "[email protected]" identified by welcome1; grant customer_role to "[email protected]";

Enabling a VPD Policy on the ORDERS Table You will apply a VPD policy to the ORDERS table, so that both internal (employees) and external users (customers) have access only to their information. Perform the following steps: 1. From your terminal window, execute the following script: @enable_vpd_policy

The enable_vpd_policy.sql script contains the following: connect sec_admin/welcome1; prompt *** Create policy function to be called when 'ORDERS' table is accessed create or replace function f_policy_orders -- Function must have the following parameters (schema in varchar2, tab in varchar2) -- Function will return a string that is used as a WHERE clause return varchar2 as v_employee_id number:=0; v_customer_id number:=0; is_sales_rep number:=0; is_sales_manager number:=0; is_president number:=0; is_customer number:=0; is_employee number:=0; v_job_id varchar2(20); v_user varchar2(100); out_string varchar2(400) default '1=2 '; -- out_string will be the return value. -- It is initialized to '1=2' because 'WHERE 1=2' means

-- 'Nothing to access' and this can be combined with -- other conditions by OR begin -- get session user v_user := lower(sys_context('userenv','session_user')); -- Is the user a customer? begin select customer_id into v_customer_id from oe.customers where lower(cust_email) = v_user; is_customer:=1; exception when no_data_found then v_customer_id := 0; end; -- Is the user an employee? begin select employee_id,job_id into v_employee_id,v_job_id from hr.employees where lower(email) = v_user; is_employee:=1; exception when no_data_found then v_employee_id := 0; end; -- get role of employee if user is an employee if v_employee_id != 0 and v_job_id='SA_REP' then -- User is Sales Rep is_sales_rep := 1; elsif v_employee_id != 0 and v_job_id='SA_MAN' then -- User is Sales Manager is_sales_manager := 1; elsif v_employee_id != 0 and v_job_id='AD_PRES' then -- User is President is_president := 1; end if; -- Now create the string to be used as the WHERE clause. If the user is e.g. sales rep and customer, both conditions are valid. if is_president = 1 or v_user='oe' then -- The president and the owner of the table (OE) are allowed to see all orders (WHERE 1=1 or anything) means all rows out_string := out_string||'or 1=1 '; end if; if is_customer = 1 then -- Customers are allowed to see their orders only out_string := out_string||'or customer_id = '||v_customer_id||' '; end if; if is_sales_rep = 1 then -- Sales Reps are allowed to see orders they have worked on out_string := out_string||'or sales_rep_id = '||v_employee_id||' '; end if; if is_sales_manager = 1 then -- Sales Managers are allowed to see orders of customers who belong to their Sales Reps; -- In this case the WHERE clause needs a subquery in order to find if their sales reps have any customers in the orders table: out_string := out_string||'or sales_rep_id in (select employee_id from hr.employees where manager_id = '|| v_employee_id||')'; end if; -- If the user is none of the above the WHERE clause will be

(WHERE 1=2), the default and that means nothing to access return out_string; end; /

2. From your terminal window, execute the following script: @apply_vpd_policy

The apply_vpd_policy.sql script contains the following: begin dbms_rls.add_policy('oe','orders','accesscontrol_orders','sec_admin', 'f_policy_orders',policy_type => dbms_rls.context_sensitive); end; /

Testing the VPD Policy on the ORDERS table Now you can test the policy. Perform the following steps: 1. The user Matthias has a CUSTOMER_ID of 106. The security policy verifies his login name in the Application Context against the CUSTOMERS table and then allows access only to his own orders in the ORDERS table. From your terminal window, execute the following script: @select_orders_as_matthias connect "[email protected]"/welcome1; select ORDER_ID, ORDER_TOTAL, CUSTOMER_ID from oe.orders;

2. Louise Doran is a Sales Rep with EMPLOYEE_ID=160. You will run a query to show only the orders placed by her own customers. From your terminal window, execute the following script: @select_orders_as_ldoran connect LDORAN/welcome1; select ORDER_ID, CUSTOMER_ID, ORDER_TOTAL, SALES_REP_ID from oe.orders;

3. Karen Partner is a Sales Manager with EMPLOYEE_ID=146. You will run a query to show only the orders placed by customers of her team of Sales Reps. From your terminal window, execute the following script: @select_orders_as_kpartner connect KPARTNER/welcome1; select ORDER_ID, CUSTOMER_ID, ORDER_TOTAL, SALES_REP_ID from oe.orders order by sales_rep_id;

4. You will run a query to see how many orders are in the ORDERS table as the OE owner. From your terminal window, execute the following script: @select_count_orders_as_oe connect oe/oe; select count(*) from oe.orders;

5. Now run the same query as Steven King. Notice how the result is the same number and he can see all the orders. This is because he is the president and can see everything. From your terminal window, execute the following script: @select_count_orders_as_sking connect SKING/welcome1; select count(*) from oe.orders;

Viewing the Policy on the ORDERS Table The VPD policy appends a WHERE clause to all queries against the protected table. To verify the WHERE clauses are correct (your auditor may ask for this), perform the following steps: 1. From your terminal window, execute the following script: @vpd_where_clause_orders connect system/oracle select predicate as "predicate (WHERE clause)" from v$vpd_policy where object_name='ORDERS';

Note: After the policy is applied to the table, all queries by authorized users are filtered according to the WHERE clauses defined in the policy function. In order to improve performance, you can apply an index to the protected table over the column used in the WHERE clause. In this tutorial, several indexes are already applied to the ORDERS table by default.

Enabling a VPD Policy with Relevant Columns and Column Filtering on the CUSTOMERS Table In this section, you will enable and apply a VPD policy with Relevant Columns and Column Filtering. This policy is applied to the CUSTOMERS table, so that only employees have access to information they really need. Customers will not be granted any access rights. Access to confidential information (i.e. CREDIT_LIMIT) is regulated by the policy. Perform the following steps:

1. From your terminal window, execute the following script: @enable_vpd_policy2 connect sec_admin/welcome1; create or replace function f_policy_customers -- Function must have the following parameters (schema in varchar2, tab in varchar2) -- Function will return a string that is used as a WHERE clause return varchar2 as v_employee_id number:=0; is_sales_manager number:=0; is_sales_rep number:=0; is_president number:=0; is_employee number:=0; v_job_id varchar2(20); v_user varchar2(100); out_string varchar2(400) default '1=2 '; -- out_string will be the return value. -- It is initialized to '1=2' because 'WHERE 1=2' means -- 'Nothing to access' and this can be combined with -- other conditions by OR begin -- get session user v_user := lower(sys_context('userenv','session_user')); begin select employee_id,job_id into v_employee_id,v_job_id from hr.employees where lower(email) = v_user; is_employee:=1; exception when no_data_found then v_employee_id := 0; end; -- get role of employee: if v_employee_id != 0 and v_job_id='SA_MAN' then -- User is Sales Manager is_sales_manager := 1; elsif v_employee_id != 0 and v_job_id='AD_PRES' then -- User is President is_president := 1; end if; -- Now create the string to be used as the WHERE clause; if is_president = 1 or v_user='oe' then -- The president and the owner of the table (OE) are allowed to see all orders (WHERE 1=1 or anything) means all rows out_string := out_string||'or 1=1 '; end if; if is_sales_manager = 1 then -- Sales Managers are allowed to see all information about their own customers; the Credit Limit of all other customers is hidden from them: out_string := out_string||'or ACCOUNT_MGR_ID = '||v_employee_id||''; end if; -- If the user is none of the above the WHERE clause will be (WHERE 1=2), the default and that means nothing to access return out_string; end; /

2. Now you can apply the policy to the table. To increase performance, the parameter 'context_sensitive' is added, so that the function is only executed when the content of the application context changes (i.e. when a new user logs in). From your terminal window, execute the following script: @apply_vpd_policy2 begin dbms_rls.add_policy('oe','customers','accesscontrol_customers','sec_a dmin', 'f_policy_customers', sec_relevant_cols=>'CREDIT_LIMIT', sec_relevant_cols_opt => dbms_rls.ALL_ROWS, policy_type => dbms_rls.context_sensitive); end; /

Testing the VPD Policy on the CUSTOMERS table Now you can test the policy. Perform the following steps: 1. Matthias Hannah is using a customer_role, which does not include access rights to the CUSTOMERS table at all. From your terminal window, execute the following script: @select_customers_as_matthias connect "[email protected]"/welcome1; select * from oe.customers;

2. Eleni Zlotkey is a Sales Manager with EMPLOYEE_ID=149. She will be able to only see the Credit Limit for the customers she worked on. All others are hidden from her. From your terminal window, execute the following script:

@select_customers_as_ezlotkey connect EZLOTKEY/welcome1; select CUST_FIRST_NAME, CUST_LAST_NAME, CREDIT_LIMIT, ACCOUNT_MGR_ID from oe.customers order by ACCOUNT_MGR_ID;

Scroll up a bit to see another ACCOUNT_MGR_ID. Notice that the credit limit is hidden.

3. You will run a query to see how the list of customers for Steven King. Since he is the President, he can see everything.. From your terminal window, execute the following script: @select_customers_as_sking connect SKING/welcome1; select CUST_FIRST_NAME, CUST_LAST_NAME, CREDIT_LIMIT, ACCOUNT_MGR_ID from oe.customers order by ACCOUNT_MGR_ID;

Scroll up a bit to see that Steven King can see all the credit limits for any ACCOUNT_MGR_ID.

Viewing the Policy on the CUSTOMERS Table The VPD policy appends a WHERE clause to all queries against the protected table. To verify the WHERE clauses are correct (your auditor may ask for this), perform the following steps: 1. From your terminal window, execute the following script: @vpd_where_clause_customers connect system/oracle select predicate as "predicate (WHERE clause)" from v$vpd_policy where object_name='CUSTOMERS';

Note: After the policy is applied to the table, all queries by authorized users are filtered according to the WHERE clauses defined in the policy function. In order to improve performance, you can apply an index to the protected table over the column used in the WHERE clause. In this tutorial, several indexes are already applied to the CUSTOMERS table by default.

Using Secure Application Roles to Enforce Security Purpose This tutorial shows you how to use DB roles to enable password protected roles and secure application roles. Time to Complete Approximately 30 minutes

Topics This tutorial covers the following topics: Overview Scenario Creating Users and Setting Table Access Using a Database Role Using Secure Application Roles Cleanup Summary Viewing Screenshots Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

Overview Roles are a powerful method for managing privileges in the Oracle Database. Roles can be granted to users and other roles. Once granted to a user, roles can be set to be default roles, so that the privileges associated with it are active as soon as the user has successfully authenticated to the database. If a role is not set as default, it can be invoked using the "set_role" command. In this tutorial you'll learn how to protect the roles from being granted to unauthorized person by using "Secure Application Roles".

Scenario This tutorial covers how two employees of OSRD, namely Karen Partners and Louise Doran, are trying to gain information from the oe.orders table. Access rights to this table is defined in the role 'ots_role'. Karen is Louise's manager, so Karen, as opposed to Louise, will be able to access the information in oe.orders.

Creating Users and Setting Table Access Before you start creating and assigning roles, you need to create your users and set access to the appropriate tables. Perform the following: 1. Open a terminal window and execute the following commands and script: cd wkdir sqlplus /nolog @sec_approles_create_users

The @sec_approles_create_users script contains the following commands: connect system/oracle drop user "[email protected]" cascade; drop user "[email protected]" cascade; create user "[email protected]" identified by welcome1; create user "[email protected]" identified by welcome1; grant connect, create session to "[email protected]"; grant connect, create session to "[email protected]"; connect hr/hr update employees set email='[email protected]' where email='LDORAN'; update employees set email='[email protected]' where email='KPARTNER';

2. Now you can set access to the tables you will use during this tutorial. Execute the following script: @sec_approles_set_access

The @sec_approles_set_access script contains the following commands: connect oe/oe revoke select on oe.orders from public; revoke select on oe.customers from public; connect hr/hr grant select on hr.employees to public;

Using a Database Role In this topic you create a database role and grant the role to Karen but not Louise. Perform the following steps: 1. You first need to create the role. From your terminal window and execute the following SQL script: @sec_approles_create_role

The @sec_approles_create_role script contains the following commands: connect system/oracle drop role ots_role; create role ots_role;

2. Next you need to grant select access to oe.orders and oe.customers. Then grant the role to each user and set their profile role to none. From your terminal window and execute the following SQL script: @sec_approles_grant_role

The @sec_approles_grant_role script contains the following commands: connect oe/oe

grant select on oe.orders to ots_role; grant select on oe.customers to ots_role; connect system/oracle grant ots_role to "[email protected]"; alter user "[email protected]" default role none; grant ots_role to "[email protected]"; alter user "[email protected]" default role none;

3. You can now set the role for Karen and perform a select on the oe.orders table. From your terminal window and execute the following SQL script: @sec_approles_test_role_karen

The @sec_approles_test_role_karen script contains the following commands: connect "[email protected]"/welcome1; set role ots_role; select sales_rep_id, order_total from oe.orders order by order_total desc;

4. Notice what happens if you do not set the role before performing the select. Execute the following SQL script: @sec_approles_test_wo_role_louise

The @sec_approles_test_wo_role_louise script contains the following commands: connect "[email protected]"/welcome1; select sales_rep_id, order_total from oe.orders order by order_total desc;

5. Louise has not been granted the 'ots_role', so she has no access to the tables defined in this role. But all she needs to know is the name of the role ('ots_role'), and the command 'set role', and she can fix this 'problem' herself. In other words, she very easily gained access to information she's not supposed to know. Run the following script: @sec_approles_test_w_role_louise

The @sec_approles_test_wo_role_louise script contains the following commands:

set role ots_role; select sales_rep_id, order_total from oe.orders order by order_total desc;

Using a Secure Application Role In this topic you create and use a secure application role. Perform the following steps: 1. You first need to create the role. From your terminal window and execute the following SQL script: @sec_approles_sar_create_role

The @sec_approles_sar_create_role script contains the following commands: connect system/oracle drop role ots_role; create role ots_role IDENTIFIED USING sec_roles;

2. Next you need to grant select access to oe.orders and oe.customers. Then grant the role to each user and set their profile role to none. From your terminal window and execute the following SQL script: @sec_approles_grant_role

The @sec_approles_grant_role script contains the following commands: connect oe/oe grant select on oe.orders to ots_role; grant select on oe.customers to ots_role; connect system/oracle grant ots_role to "[email protected]"; alter user "[email protected]" default role none; grant ots_role to "[email protected]"; alter user "[email protected]" default role none;

3. Now you can create the security application role procedure. From your terminal window, execute the following SQL script; note that all kinds of security checks can be performed by this procedure. In this example, you first compare the 'session_user' with the email-address from the hr.employees table. Then you check the employee's manager_id. The procedure sets the role for the user only when manager_id=100, otherwise the role is not set. @sec_approles_sar_create_proc

The @sec_approles_sar_create_proc script contains the following commands: connect system/oracle CREATE OR REPLACE procedure sec_roles authid current_user as v_user varchar2(50); v_manager_id number :=1; begin

v_user := (sys_context ('userenv', 'session_user')); select manager_id into v_manager_id from hr.employees where email=v_user; if v_manager_id = 100 then dbms_session.set_role('ots_role'); else null; end if; exception when no_data_found then v_manager_id:=0; end sec_roles; /

4. You need to grant execute rights to the procedure. From your terminal window, execute the following SQL script; in this example, the internal security policy of OSRD could determine that execution rights to this procedure are granted all employees: @sec_approles_sar_grant_proc

The @sec_approles_sar_grant_proc script contains the following commands: connect system/oracle GRANT EXECUTE ON sec_roles to "[email protected]"; GRANT EXECUTE ON sec_roles to "[email protected]";

5. Now you can test the access for Karen. Execute the following SQL script: @sec_approles_sar_test_role_karen

The @sec_approles_sar_test_role_karen script contains the following commands: connect "[email protected]"/welcome1; execute system.sec_roles; select sales_rep_id, order_total from oe.orders order by order_total desc;

6. Now you can test the access for Louise. Execute the following SQL script: @sec_approles_sar_test_role_louise

The @sec_approles_sar_test_role_louise script contains the following commands: connect "[email protected]"/welcome1; execute system.sec_roles; select sales_rep_id, order_total from oe.orders order by order_total desc;

Cleanup Perform the following steps to cleanup the environment. You must follow these steps if you plan on performing any other OBE after performing this one:

1. From your terminal window, execute the following SQL script: @sec_appsrole_cleanup

The @sec_appsrole_cleanup script contains the following commands: connect system/oracle drop role ots_role; drop procedure sec_roles; drop user "[email protected]" cascade; drop user "[email protected]" cascade; update employees set email='LDORAN' where email='[email protected]'; update employees set email='KPARTNER' where email='[email protected]'; exit;

Using Fine Grained Auditing Purpose The goal of this tutorial is to show how fine-grained auditing can be used to detect access to a 'honey token' in a table . Time to Complete Approximately 30 minutes Topics This tutorial covers the following topics: Overview Setup Creating and Applying a VPD Policy Testing the Policy Implementation Checking the Audit Violations Cleanup Summary Viewing Screenshots Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

Overview Policies you establish with fine-grained auditing can monitor data access based on content. Using policies, you can specify the columns and conditions that you want audit records for. Conditions can include limiting the audit to specific types of DML statements used in connection with the columns that you specify. You can also provide the name of the routine you want called when an audit event occurs. This routine can notify or alert administrators or handle errors and anomalies.

Scenario This tutorial shows how fine-grained auditing is used to detect access to a 'honey token' in a table. In this tutorial, a VPD policy is created that limits access to rows by credit card vendor. The honey token is a credit card number that will never be selected. It will fail the usual verifications, and the number cannot be related to any vendor, so authorized access to this row is not possible. The VPD policy excludes OE, the owner of this table. OE is granted full access, so when OE (or a hacker who obtained this identity) executes

select * from oe.cust_payment_info the honey token will be selected and the FGA policy will record this event. In addition, you could invoke an event handler (not included in this tutorial), to send an email to audit_admin about the access to this special row. This tutorial separates duties between the following users: HR_sec

Creates database users (from a list of employees in the hr.employees table) and database roles. sec_admin Creates and applies the VPD and FGA policies according to the company's access control and auditing policies. audit_admin Queries the audit record in the database (dba_fga_audit_trail).

Setup In this tutorial, you create a set of users and roles to demonstrate how FGA works. To create users and roles, perform the following steps: 1. You will create a customer payment information table and populate it with credit card numbers. The credit card number associated with 'Honey Token' is impossible, so this row will never be selected by authorized users. It is only selected by administrative users who are exempt from access policies or intruders who became administrative 'insiders'. This allows for highly focused auditing on this table. Open a terminal window and execute the following commands: cd /home/oracle/wkdir sqlplus /nolog @01_fga_poptabl conn oe/oe create table cust_payment_info (first_name varchar2(11), last_name varchar2(10), order_number number(5), credit_card_number varchar2(16) ENCRYPT); insert into cust_payment_info values ('Jon', 'Oldfield', 10001, '5446959708812985'); insert into cust_payment_info values ('Chris', 'White', 10002, '5122358046082560'); insert into cust_payment_info values ('Alan', 'Squire', 10003, '5595968943757920'); insert into cust_payment_info values ('Mike', 'Anderson', 10004, '4929889576357400'); insert into cust_payment_info values ('Annie', 'Schmidt', 10005, '4556988708236902'); insert into cust_payment_info values ('Elliott', 'Meyer', 10006, '374366599711820'); insert into cust_payment_info values ('Celine', 'Smith', 10007, '4716898533036'); insert into cust_payment_info values ('Steve', 'Haslam', 10008, '340975900376858'); insert into cust_payment_info values ('Albert', 'Einstein', 10009, '310654305412389');

2. SYSTEM will create 3 admin users: HR_sec, sec_admin and audit_admin. HR_sec creates the role 'emp_role' and then grants system and object privileges to the emp_role. From your terminal window, run the following script: @02_fga_cradminusers connect system/oracle; create user sec_admin identified by welcome1; create user audit_admin identified by welcome1; create user HR_sec identified by welcome1; grant connect, create user, drop user, create role, drop any role to HR_sec; connect HR_sec/welcome1; create role emp_role; connect / as sysdba; grant execute on dbms_rls to sec_admin; grant execute on dbms_fga to sec_admin; grant select on dba_fga_audit_trail to audit_admin; connect system/welcome1; grant connect to emp_role; grant create procedure to sec_admin; connect OE/oe; grant select on oe.cust_payment_info to emp_role;

3. HR_sec creates the database users and grants the emp_role to them. From your terminal window, run the following script: @03_fga_crdbusers connect HR_sec/welcome1; Prompt create user Janette King (JKING) (access to Card_A) grant emp_role to JKING identified by welcome1; Prompt create user Lindsay Smith (LSMITH) (access to Card_V) grant emp_role to LSMITH identified by welcome1; Prompt create user Louise Doran (LDORAN) (access to Card_M) grant emp_role to LDORAN identified by welcome1; Prompt grant emp_role to sec_admin: grant emp_role to sec_admin; Prompt grant emp_role to audit_admin: grant emp_role to audit_admin; connect hr/hr; grant select on hr.employees to sec_admin;

Creating and Applying a VPD Policy In this section, you will define and limit access to the rows with a VPD policy. You will check if the person who logs ino the database is an employee, and then you will limit access to the cust_payment_info table by credit card number. Card_A begins with '34' or '37': Card_V begins with '4' Card_M begins with '5 Perform the following: 1. You first need to create a policy function to create the where-clause. Execute the following script: @04_fga_crpolicy_function conn sec_admin/welcome1; create or replace function f_policy_oe_cust_payment_info -- Function must have the following parameters (schema in varchar2, tab in varchar2) -- Function will return a string that is used as a WHERE clause return varchar2 as v_manager_id number:=0;

is_employee number:=0; v_user varchar2(20); out_string varchar2(70) default '1=2 '; begin -- get session user v_user := lower(sys_context('userenv','session_user')); -- Is the user an employee? begin select manager_id into v_manager_id from hr.employees where lower(email) = v_user; is_employee:=1; exception when no_data_found then is_employee:=2; end; -- create where clause when user is authorized to see parts of the table if is_employee=1 and lower(v_user)='jking' and v_manager_id=146 then out_string := out_string ||'or CREDIT_CARD_NUMBER like ''34%'' or CREDIT_CARD_NUMBER like ''37%'''; elsif is_employee=1 and lower(v_user)='lsmith' and v_manager_id=146 then out_string := out_string ||'or CREDIT_CARD_NUMBER like ''4%'''; elsif is_employee=1 and lower(v_user)='ldoran' and v_manager_id=146 then out_string := out_string ||'or CREDIT_CARD_NUMBER like ''5%'''; elsif is_employee=2 and lower(v_user)='oe' then out_string := '1=1'; end if; return out_string; end; /

2. You will now add the policy to 'oe.cust_payment_info' table. From a SQL*Plus session, execute the following script: @05_fga_applypolicy begin dbms_rls.add_policy('oe','cust_payment_info','ac_cust_payment_info',' sec_admin', 'f_policy_oe_cust_payment_info',policy_type => dbms_rls.context_sensitive); end; /

3. The sec_admin user will add an FGA policy to the sensitive table that captures access to the 'Albert Einstein'. From SQL*Plus, execute the following script: @05_fga_applypolicy_waudit begin DBMS_FGA.ADD_POLICY ( object_schema => 'OE', object_name => 'cust_payment_info', policy_name => 'fga_cust_payment_info', audit_condition => 'credit_card_number = 310654305412389', audit_column => NULL, handler_schema => NULL, handler_module => NULL, enable => TRUE, statement_types => 'UPDATE, DELETE, SELECT', audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED); end; /

Testing the Policy Implementation After establishing policies to tables, you can now test them by performing the following: 1. Execute the following script to test the access from the JKING user. @06_fga_test_policy_king conn JKING/welcome1; col CREDIT_CARD_NUMBER heading Card_A format a17; select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;

2. Now you can test the policy for the LDORAN user by executing the following script: @06_fga_test_policy_doran conn LDORAN/welcome1; col CREDIT_CARD_NUMBER heading Card_M format a17; select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;

3. Now you can test LSMITH access by executing the following script: @06_fga_test_policy_smith conn LSMITH/welcome1; col CREDIT_CARD_NUMBER heading Card_V format a17; select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;

4. Now you can test SYS access by executing the following script: @06_fga_test_policy_sys conn / as sysdba; col CREDIT_CARD_NUMBER heading 'All Cards' format a17; select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;

5. And finally, you can test the OE user access by executing the following script: @06_fga_test_policy_oe conn OE/oe; col CREDIT_CARD_NUMBER heading 'All Cards' format a17; select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;

Checking the Audit Violations The Audit_Admin user can check for any access violations by performing the following: 1. Execute the following script to check the dba_fga_audit_trail for the CUST_PAYMENT_INFO table. @07_fga_chk_audit_violations conn audit_admin/welcome1; col DB_USER format a10; col extended_timestamp heading 'ext. Time' format a35; select DB_USER, extended_timestamp, SQL_TEXT from dba_fga_audit_trail where object_name='CUST_PAYMENT_INFO' order by extended_timestamp;

Cleanup

Now that you have tested your policies, you can drop the users and the policies by performing the following: 1. Execute the following script: @08_fga_cleanup conn sec_admin/welcome1; begin DBMS_FGA.DROP_POLICY ( object_schema => 'OE', object_name => 'cust_payment_info', policy_name => 'fga_cust_payment_info'); end; / drop function f_policy_oe_cust_payment_info; conn oe/oe drop table cust_payment_info; conn HR_sec/welcome1; drop role emp_role; drop user JKING cascade; drop user LSMITH cascade; drop user LDORAN cascade; conn system/welcome1 drop user sec_admin cascade; drop user audit_admin cascade; drop user HR_sec cascade;