Have you ever thought about the difference between a software flaw and bug? Or are they referring to the same thing? This mini post will explain.
It is true that both software flaws and bugs result in vulnerabilities that hackers can exploit, but these two terms refer to two different things. The former is more serious than the latter.
Software flaw - caused by a design failure
Software bug - cause by a implementation failure (design is perfectly fine)
Note that software can be vulnerable due to both software flaw and bug at the same time - incorrect design and incorrect implementation.
Let me quote a famous bug which was exploited to produce one of the first Internet worm, Morris worm, in 1988. Morris worm exploited a buffer overflow vulnerability in Unix finger daemon. Specifically, finger used get() function to obtain user input. The problem with get() is that it does not have provision to limit or check user input string length. So, the bug is not checking/validating input string. It is not a flaw as it is an implementation issue and has nothing to do with the design of finger.
Another recent one is heartbleed bug. It is a serious software bug in OpenSSL library. Exploiting this vulnerability, attackers can eavesdrop on OpenSSL encrypted communication, steal data from server and clients, and also impersonate either of the entity. It is caused by an implementation failure of the heartbeat part of OpenSSL library and it has nothing to do with the design of TLS.
Now let's look at a recent design flaw vulnerability. iOS 6 had a vulnerability in mobile hotspot passwords. A hacker can do a simple dictionary/brute-force attack to recover this password. This vulnerability is not an implementation issue, but rather resulted from the design flaw in how passwords are generated. This password is generated from a set of predefined words. These words are randomly selected and padded with a 4 digit random number. The flaw here is that selection of words from the pool of words is not truly random and also the number of words in the pool is also small.
Good read: Avoiding the top 10 software security design flaws
Bottom line is that use secure design principles and coding techniques to avoid such vulnerabilities.
Tuesday, December 30, 2014
Top data breaches of 2014 and lessons
May - eBay [1, 2]
September - Home Depot [1]
December - Sony Pictures [1]
- 145 million
- The problem has been exploited at least from February
- Information compromised - encrypted passwords, usernames, emails, DoBs, phone numbers, postal addresses
- How - eBay allows sellers to use active content such as Javscript and Flash. Hackers have embedded password harvesting scripts in listings and used XSS attack to gather information from logged in users
- Lessons - If you allow active content in your web site, you need to have preventive (such as limiting what is allowed) as well as risk mitigation techniques (detection, monitoring, etc.) in place. If not, it is better to block active content.
September - Home Depot [1]
- 56 million
- Information compromised - credit and debit card numbers
- How - resulted from the compromise of a third-party vendor
- Lessons - Organizations should evaluate the security measures taken by third-party vendors and also have intrusion detection in place.
- Attack discovered in July and disclosed in September
- 76 million households (83 million accounts)
- Information compromised - names, addresses, phone numbers and email addresses
- How - Hackers got access via a neglected server which is not upgraded to two factor authentication
- Potentially took 2 months to notice unusual activities after the initial attack
- Lessons - Security is only as strong as the weakest link in your system. Make sure you do not leave any unguarded holes in your network. Cannot emphasize more of the need for better and faster detection techniques in place.
- Number of people affected is still not known
- Attack initially detected in November
- Information compromised - unreleased movies, company emails, executive salaries, personal information, internal strategy information
- How - wiper malware (still it is not known how it got into the system). It is likely that lapses in internal security measures may resulted in this attack.
- Lessons - Never take security lightly. Internal security measures and auditing should always be a top priority.
Monday, December 29, 2014
Explaining public cloud computing to my kid
Subway - Public Cloud Computing
Bob is living in a very busy city. In order for him to go to work, either he could take the subway train or drive his own car. Bob is a recent college graduate. He neither has a lot of money nor a good credit score. Therefore, buying a car is somewhat out of his options. Besides, it is really time consuming to drive in the city to commute. So, Bob opts to take the subway to work. This is exactly how cloud computing works. Instead of Bob buying his own vehicle, he is using the public transportation to commute. In cloud computing, companies, without having to invest on computer hardware, rent hardware or software in the cloud.
When Bob takes the subway, he shares the ride with many other commuters. Similarly, many organizations share the computing infrastructure made available through cloud computing.
Bob pays for his subway ride everyday. He pays only when he takes it and only for the distance he travels. For example, Alice, who is also a subway commuter, has to pay more than Bob as she travels longer distance than Bob. Likewise, with cloud computing, companies pay only for the resources they use. If a company uses more resources than another, they have to pay more for the cloud provider.
Subway trains are available 24x7. Bob can catch a train virtually any time he wants. Similarly, cloud is available 24x7.
Trains have more compartments during peak hours than the normal hours. So, Bob does not have to worry about getting on-board anytime he wants to take a train. Subway accommodates all passengers catering for varying demands through out the day. The cloud is also like that. The resources in the cloud can expand or shirk based on the demand. We call it elasticity. Elasticity is a very useful property that lets businesses to timely respond to their customers for varying amounts of demand through out the year.
If Bob used his own vehicle, he needs to regularly maintain his car on his own and repair any damages. Since Bob is using the public transport, he does not have to worry about any of that. Similarly, companies can have the peace of mind that they don't have to worry about maintaining computer hardware and having a dedicated staff to look after them.
When Bob boards in to the train, he gets a seat to sit and travel peacefully. Usually, everyone respects each other and does not invade others' space. Bob can browse his smart phone or read a book without getting unnecessary peaking from others. Similarly, even though the cloud is public, companies get their own bounded space to do their business so that their data is not mixed with other companies.
Usually subways have one or more train operators and ticket security officers. They make sure that Bob and other passengers travel safely as well as catch bad people who misuse it. Similarly, the cloud provides a several security measure to make sure that your business is safe and secure.
In summary, a public cloud is a shared resource, paid-per-usage, elastic, 24x7 available, transparent, virtually isolated, and secure.
Tuesday, December 23, 2014
Taxonomy of Information Protection Mechanisms
Is there a single silver bullet solution that solve all security problem? No, there isn't any. In fact, there won't be any in the future as well. Security is a complex landscape that requires multiple solutions to protect against many threats. The following diagram tentatively groups those solutions into 4 main categories:
The first line of defense is to have denial and isolation techniques to keep bad people away from your system. For example, a firewall can prevent SQL injections by malicious users. Persistent hackers may penetrate through this defense by exploiting a vulnerability in the perimeter security or using a social engineering technique. Degradation and obfuscation mechanisms such as encryption and anonymization are utilized to make it difficult to access the real data if hackers penetrate through the first layer of defense. That is, if an attacker get his hand on the encrypted data, it will difficult to decrypt the data without having access to keys. In order to make it even more harder for attackers, negative information and deception techniques are used. For example, mix real passwords with fake passwords (honeytokens). Nowadays, attackers are so sophisticated that they may carry out attacks in your system without your knowledge, i.e. evading any detection of unauthorized actions. In fact, statistics show that most attacks on systems remain undetected or are detected after a long time from the inception of the attack. Therefore, it is important to have detection mechanisms and then counter-operations to cut down the damage caused by the attacks and take corrective measures.
Notice that some techniques provide protection for multiple overlapping areas. For example, honeytokens serve as a deception as well as a detection mechanism.
As you can see, there is no one single silver bullet that protects your system. We need to deploy multiple solutions to achieve defense in depth. The idea behind multiple defense mechanisms is to make attackers life difficult so that they give up when their effort is more than what they get in return.
References: http://goo.gl/1YecXg
Figure: Taxonomy of information protection mechanisms (source: http://goo.gl/1YecXg)
The first line of defense is to have denial and isolation techniques to keep bad people away from your system. For example, a firewall can prevent SQL injections by malicious users. Persistent hackers may penetrate through this defense by exploiting a vulnerability in the perimeter security or using a social engineering technique. Degradation and obfuscation mechanisms such as encryption and anonymization are utilized to make it difficult to access the real data if hackers penetrate through the first layer of defense. That is, if an attacker get his hand on the encrypted data, it will difficult to decrypt the data without having access to keys. In order to make it even more harder for attackers, negative information and deception techniques are used. For example, mix real passwords with fake passwords (honeytokens). Nowadays, attackers are so sophisticated that they may carry out attacks in your system without your knowledge, i.e. evading any detection of unauthorized actions. In fact, statistics show that most attacks on systems remain undetected or are detected after a long time from the inception of the attack. Therefore, it is important to have detection mechanisms and then counter-operations to cut down the damage caused by the attacks and take corrective measures.
Notice that some techniques provide protection for multiple overlapping areas. For example, honeytokens serve as a deception as well as a detection mechanism.
As you can see, there is no one single silver bullet that protects your system. We need to deploy multiple solutions to achieve defense in depth. The idea behind multiple defense mechanisms is to make attackers life difficult so that they give up when their effort is more than what they get in return.
References: http://goo.gl/1YecXg
Friday, November 14, 2014
Friday, October 10, 2014
[Oracle] Defense in depth
As we know, we are fighting an asymmetrical battle with attackers when it comes to defend our valuable artifacts such as databases, web servers, mail servers, etc. Also, I am not aware of any system that is free of vulnerabilities. Attackers exploit those vulnerabilities to gain access to these systems. Further, you cannot eliminate the human factor. No amount of technology can make a system secure if your users are attack vectors. The reality is that we cannot make any system completely secure.
So, I hear you ask if we cannot secure our systems, aren't we doomed to fail? Not really. Having been working in the security field for a long time, one thing I have realized is that security is not about building 100% secure systems, but rather it is about building systems that makes attackers life harder to break in. Security is all about economics. If you can make the system harder to break in, financially motivated attackers will give up eventually as the effort (aka investment on time and resources) they put in is far greater than the outcome (aka profit by breaking in) they get.
So, if we cannot build fully secure systems, how can we make systems harder to break in? You have to deploy multiple layers of security in order to protect your valuable assets. In other words, you need to build defense in depth into your system. Think about a bank safe. The safe is not kept in a public area, but rather you have to go through multiple doors to get into where the safe is mounted. Once you reach there, then you are left with the task of breaking the safe itself. That's a lot of labor. Most attackers would simply give up.
Let me show how Oracle database 12c takes the defense in depth approach.
Perimeter security:
- Strong authentication (preventive)
- On the wire encryption (preventive)
- AVDF - Audit Vault and Database Firewall (preventive and detective)
Out-bound data:
- Secure backup
- Data masking
- Data redaction (for applications)
Access control:
- DAC and RBAC based privileges and roles
- VPD (Virtual Private Database) - row level access control
- OLS (Oracle Label Security) - multi-level security
- DV (Database Vault) - privileged user controls
- RAS (Real Application Security) - middleware-application security
Data at rest:
- Standard ACID controls
- TDE (Transparent Data Encryption) - data-at-rest encryption
- Oracle wallet/OKV (Oracle Key Vault) for key management
Administrative/Hardening Tools - help reduce the attack surface:
- Secure configuration management
- Privilege analysis
- Sensitive data discovery
Monitoring:
- Unified auditing framework
- AVDF (Audit Vault)
Similar to the bank safe example mentioned above, it is also very important to harden the environment (OS, network) where the database is deployed as well.
So, I hear you ask if we cannot secure our systems, aren't we doomed to fail? Not really. Having been working in the security field for a long time, one thing I have realized is that security is not about building 100% secure systems, but rather it is about building systems that makes attackers life harder to break in. Security is all about economics. If you can make the system harder to break in, financially motivated attackers will give up eventually as the effort (aka investment on time and resources) they put in is far greater than the outcome (aka profit by breaking in) they get.
So, if we cannot build fully secure systems, how can we make systems harder to break in? You have to deploy multiple layers of security in order to protect your valuable assets. In other words, you need to build defense in depth into your system. Think about a bank safe. The safe is not kept in a public area, but rather you have to go through multiple doors to get into where the safe is mounted. Once you reach there, then you are left with the task of breaking the safe itself. That's a lot of labor. Most attackers would simply give up.
Let me show how Oracle database 12c takes the defense in depth approach.
Perimeter security:
- Strong authentication (preventive)
- On the wire encryption (preventive)
- AVDF - Audit Vault and Database Firewall (preventive and detective)
Out-bound data:
- Secure backup
- Data masking
- Data redaction (for applications)
Access control:
- DAC and RBAC based privileges and roles
- VPD (Virtual Private Database) - row level access control
- OLS (Oracle Label Security) - multi-level security
- DV (Database Vault) - privileged user controls
- RAS (Real Application Security) - middleware-application security
Data at rest:
- Standard ACID controls
- TDE (Transparent Data Encryption) - data-at-rest encryption
- Oracle wallet/OKV (Oracle Key Vault) for key management
Administrative/Hardening Tools - help reduce the attack surface:
- Secure configuration management
- Privilege analysis
- Sensitive data discovery
Monitoring:
- Unified auditing framework
- AVDF (Audit Vault)
Similar to the bank safe example mentioned above, it is also very important to harden the environment (OS, network) where the database is deployed as well.
Thursday, October 9, 2014
[Oracle] Proxy Authentication
You are the DBA of your organisation managing a cluster of Oracle databases. You want to give full access to a schema (say hr) to multiple database users (say bob and alice). How can you do it?
One approach is to give schema name and password to the users. Do you see any problem with that approach? I can think of at least a few:
1. Accountability - multiple users using the same username/password makes tracing actions to database users impossible.
2. Security - shared password is more vulnerable to exposure risks.
3. Revocation - in order to revoke access to the hr schema, you need to change the password of hr user.
Another approach would be to grant system privileges to bob and alice so that they can do anything on the hr schema. However, this does not follow the principle of least privileges and is prone to misuse as users are given more privileges than they require to do the job. Therefore, it is not a good idea to grant system privileges. In fact, as a rule of thumb, system privileges should be granted sparingly in order to improve security.
An improved approach would be to grant all privileges on the hr schema to bob and alice. This requires granting object privileges on each object in hr schema. Managing many object privileges is a nightmare. Further, this does not allow to create new objects in the hr schema. Therefore, this solution also has limitations.
So, what can we do about it? Having identified the above limitations, Oracle actually introduced what is called proxy authentication for such use cases. You simply grant proxy authentication to the users. The following toy script shows how to: (You can also further restrict access to the schema by specifying what roles can be used during the proxy session. For simplicity, I am omitting such fine tuning here.)
SQL> conn / as sysdba
Connected.
SQL> alter user hr grant connect through bob;
User altered.
SQL> alter user hr grant connect through alice;
User altered.
SQL>
SQL> conn hr
Password:
SQL> create table emp(name varchar2(100), sal int);
Table created.
SQL> insert into emp values ('tim', 125000);
1 row created.
SQL> insert into emp values ('eve', 150000);
1 row created.
One approach is to give schema name and password to the users. Do you see any problem with that approach? I can think of at least a few:
1. Accountability - multiple users using the same username/password makes tracing actions to database users impossible.
2. Security - shared password is more vulnerable to exposure risks.
3. Revocation - in order to revoke access to the hr schema, you need to change the password of hr user.
Another approach would be to grant system privileges to bob and alice so that they can do anything on the hr schema. However, this does not follow the principle of least privileges and is prone to misuse as users are given more privileges than they require to do the job. Therefore, it is not a good idea to grant system privileges. In fact, as a rule of thumb, system privileges should be granted sparingly in order to improve security.
An improved approach would be to grant all privileges on the hr schema to bob and alice. This requires granting object privileges on each object in hr schema. Managing many object privileges is a nightmare. Further, this does not allow to create new objects in the hr schema. Therefore, this solution also has limitations.
So, what can we do about it? Having identified the above limitations, Oracle actually introduced what is called proxy authentication for such use cases. You simply grant proxy authentication to the users. The following toy script shows how to: (You can also further restrict access to the schema by specifying what roles can be used during the proxy session. For simplicity, I am omitting such fine tuning here.)
SQL> conn / as sysdba
Connected.
SQL> alter user hr grant connect through bob;
User altered.
SQL> alter user hr grant connect through alice;
User altered.
SQL>
SQL> conn hr
Password:
SQL> create table emp(name varchar2(100), sal int);
Table created.
SQL> insert into emp values ('tim', 125000);
1 row created.
SQL> insert into emp values ('eve', 150000);
1 row created.
SQL> -- bob connecting as proxy user
SQL> conn bob[hr]
Password:
SQL> select sys_context('userenv', 'current_user') from dual;
SYS_CONTEXT('USERENV','CURRENT_USER')
-------------------------------------
HR
SQL> select sys_context('userenv', 'proxy_user') from dual;
SYS_CONTEXT('USERENV','PROXY_USER')
-----------------------------------
BOB
SQL> -- bob can do anything on hr schema
SQL> select * from hr.emp;
NAME SAL
------------------
tim 125000
eve 150000
Tuesday, September 2, 2014
API Facade Pattern
A simple interface to a complex system!
Furniture showrooms display the furniture to customers in a pleasing manner so that buyers can make better choices.
It is not practical to store the furniture like that for variety of reasons including space, transportation, identification, etc. Furniture warehouses are not so pleasing to browse with piled up with furniture but they do serve the business requirements of storing them in a way that helps the business.
It is difficult for customers to visualize what the end product look like if the business only operated a warehouse. It looks way too complicated for customers. Showrooms hide all the complexity of storing the furniture in a warehouse and showing furniture in a way that helps customers to make smart buying choices. In other words, showrooms are facades of warehouses.
You've just learnt the facade pattern!
This is exactly what happens in the digital world as well. Organizations have complex and legacy systems. They are way too complex and inflexible to consume. Further, most consumer applications now obtain information from multiple systems. The API facade pattern hides all the complexity and present a simple interface to consumers.
It looks all spaghetti with no Facade. It is just like customers browsing the warehouse:
Furniture showrooms display the furniture to customers in a pleasing manner so that buyers can make better choices.
Figure: Furniture showroom
It is not practical to store the furniture like that for variety of reasons including space, transportation, identification, etc. Furniture warehouses are not so pleasing to browse with piled up with furniture but they do serve the business requirements of storing them in a way that helps the business.
Figure: Furniture warehouse
It is difficult for customers to visualize what the end product look like if the business only operated a warehouse. It looks way too complicated for customers. Showrooms hide all the complexity of storing the furniture in a warehouse and showing furniture in a way that helps customers to make smart buying choices. In other words, showrooms are facades of warehouses.
You've just learnt the facade pattern!
This is exactly what happens in the digital world as well. Organizations have complex and legacy systems. They are way too complex and inflexible to consume. Further, most consumer applications now obtain information from multiple systems. The API facade pattern hides all the complexity and present a simple interface to consumers.
It looks all spaghetti with no Facade. It is just like customers browsing the warehouse:
Figure: No Facade Architecture
Facade pattern hides the complexity of connecting to multiple systems, dealing with multiple security issues, message formats by presenting a simple interface to clients.
Figure: Facade Pattern in Action
How do we implement such a system? It is actual a simple three step process:
Figure: Three Step Process of Implementation
OK great. Do I have to implement these three steps from scratch? That sounds a lot of work. Yes, you are right - it's a lot of work to implement them on your own. The good news is that there are both commercial as well as open source products available for you to just focus only on the business logic and let these products take care of the rest. Can you give a practical architecture for it? You can use an API gateway together with an ESB. You just need to write the
Figure: A Practical Architecture to Realize Facade Pattern
Introduction to Tokenization
In the following presentation, I am going over the basic details of security tokenization starting from its definition.
I will be going over practical integration and deployment options for tokenization in another presentation. Please stay tuned!
I will be going over practical integration and deployment options for tokenization in another presentation. Please stay tuned!
Introduction to Oracle Transparent Data Encryption (TDE) 12c
The following slide deck gives you an introduction to Oracle TDE. It is by no means a complete introduction. However, it should give you sufficient information to get started with TDE.
I encourage you to follow Oracle documentation to learn more details.
I encourage you to follow Oracle documentation to learn more details.
Monday, August 25, 2014
Introduction to Oracle Label Security 12c
In the previous post, we looked at how to support fine-grained access control of tables using Oracle VPD technology. In this post, we are going to have a quick look at another technology with similar purpose called OLS (Oracle Label Security) which is built on top of VPD.
A commonly raised question is that if both VPD and OLS serve the same purpose, why do we need OLS? While VPD is a generic solution, OLS addresses a very specific and common use case in many government and corporate environment - allow access to data based on the need to know basis. Unlike VPD which requires administrator to write PL/SQL code for the policy function, OLS requires no programming. You can think of OLS as a out-of-the-box VPD with built-in policies.
What exact does OLS address?
Many government and business organizations, label their data using different sensitivity levels, such as top secret (TS), secret (S), confidential (C), unclassified (UC), depending on the perceived risk levels so that they can selectively protect most valuable assets of the organization. Users are assigned different clearance levels based on their job functions and seniority. Each user is allowed to access only those data records with the sensitivity level no more than the clearance level of the user. For example, if user tom is assigned the clearance level C, he can access data records with the sensitive levels C or UC only. He is not allowed to access data records with sensitivity levels greater than C, that is, TS or S.
How is OLS enforced?
OLS is a security in depth feature, meaning that users must first have the DAC table privileges necessary to access the OLS protected rows. On top that, there could be VPD policies that may be enforced as well. The following diagram shows the order of enforcement. (Not shown in the diagram: a database vault policy may be enforced on the table as well.)
How does OLS work?
The following diagram shows the example that we will walk through later in this post. The table flight has data of different sensitivity level and based users' clearance levels they see different set of records even though they issue the same query. Row level access control is enforced transparent to end users.
How are row sensitivity labels assigned?
When a new record is inserted to an OLS protected table, there are three ways to assign sensitivity label to each row:
- Explicit specify a label (which should be between the maximum and minimum clearance of the user)
- Select 'LABEL_DEFAULT' option when creating the policy and the sessions ROW_LABEL value will be used automatically when inserting records
- Create a labeling function and specify when applying the policy on the table. This will automatically call your labeling function when inserting records
How are user clearance levels decided?
If it is a database user, SA_USER_ADMIN.SET_LEVELS procedure assigns a default level to each user. At the time of login user will have this clearance level. In a session, user can change the clearance level to a level less than the maximum level granted using SA_SESSION.SET_LABEL procedure.
If it is an application user who does not have a database user, SA_SESSION.SET_ACCESS_PROFILE procedure allows to set an existing database user's profile to be the profile of the application user.
A working example:
SQL> -- First do the test setup
SQL> conn sys/password@cdb1_pdb1 as sysdba
Connected.
SQL> create user gov identified by gov;
User created.
SQL> create user bob identified by bob;
User created.
SQL> create user tim identified by tim;
User created.
SQL> grant create session to gov, bob, tim;
Grant succeeded.
SQL> grant create table, unlimited tablespace to gov;
Grant succeeded.
SQL> conn gov/gov@cdb1_pdb1
Connected.
SQL> create table flight(flight# number, destination varchar2(100), payload varchar2(100));
Table created.
SQL> grant select, insert on flight to bob, tim, scott;
Grant succeeded.
SQL> -- insert test data
SQL> insert into flight values (505, 'Iraq', 'Weapon');
1 row created.
SQL> insert into flight values (506, 'Canada', 'Charcoal');
1 row created.
SQL> insert into flight values (706, 'Japan', 'Battery');
1 row created.
SQL> insert into flight values (501, 'Syria', 'Weapon');
1 row created.
SQL> insert into flight values (508, 'Israel', 'Jets');
1 row created.
SQL> insert into flight values (509, 'India', 'Aid');
1 row created.
SQL>
SQL> conn lbacsys/password@cdb1_pdb1
Connected.
SQL> BEGIN
2 -- Create OLS Policy
3 -- Notice that the default_options is set to no_control to disable the policy
4 -- in order add labels to the existing data items
5 SA_SYSDBA.CREATE_POLICY(
6 policy_name => 'ols_pol1',
7 column_name => 'lb_col',
8 default_options => 'no_control'
9 );
10
There is more to OLS:
I only scratched the surface of OLS. A data label is actually consists of three components:
1. Sensitivity level - has an ordering relationship
2. Compartment (optional) - used to segregate data within a level
3. Group (optional) - used to organizationally segregate data within a level - has a hierarchical relationship
In order for a user to able to access a labeled record, the following conditions must be satisfied:
a. User's clearance level should be at least as high as the the data record's sensitivity level
b. User has all the compartments associated with the data record
c. User belongs to one of the groups associated with the data record or a parent group of one of the associated groups
With the quick understanding of the OLS technology, I encourage you to look the Oracle documentation to learn more about OLS and its new powerful features.
A commonly raised question is that if both VPD and OLS serve the same purpose, why do we need OLS? While VPD is a generic solution, OLS addresses a very specific and common use case in many government and corporate environment - allow access to data based on the need to know basis. Unlike VPD which requires administrator to write PL/SQL code for the policy function, OLS requires no programming. You can think of OLS as a out-of-the-box VPD with built-in policies.
What exact does OLS address?
Many government and business organizations, label their data using different sensitivity levels, such as top secret (TS), secret (S), confidential (C), unclassified (UC), depending on the perceived risk levels so that they can selectively protect most valuable assets of the organization. Users are assigned different clearance levels based on their job functions and seniority. Each user is allowed to access only those data records with the sensitivity level no more than the clearance level of the user. For example, if user tom is assigned the clearance level C, he can access data records with the sensitive levels C or UC only. He is not allowed to access data records with sensitivity levels greater than C, that is, TS or S.
How is OLS enforced?
OLS is a security in depth feature, meaning that users must first have the DAC table privileges necessary to access the OLS protected rows. On top that, there could be VPD policies that may be enforced as well. The following diagram shows the order of enforcement. (Not shown in the diagram: a database vault policy may be enforced on the table as well.)
Figure: OLS Enforcement
How does OLS work?
The following diagram shows the example that we will walk through later in this post. The table flight has data of different sensitivity level and based users' clearance levels they see different set of records even though they issue the same query. Row level access control is enforced transparent to end users.
Figure: OLS Example
How are row sensitivity labels assigned?
When a new record is inserted to an OLS protected table, there are three ways to assign sensitivity label to each row:
- Explicit specify a label (which should be between the maximum and minimum clearance of the user)
- Select 'LABEL_DEFAULT' option when creating the policy and the sessions ROW_LABEL value will be used automatically when inserting records
- Create a labeling function and specify when applying the policy on the table. This will automatically call your labeling function when inserting records
How are user clearance levels decided?
If it is a database user, SA_USER_ADMIN.SET_LEVELS procedure assigns a default level to each user. At the time of login user will have this clearance level. In a session, user can change the clearance level to a level less than the maximum level granted using SA_SESSION.SET_LABEL procedure.
If it is an application user who does not have a database user, SA_SESSION.SET_ACCESS_PROFILE procedure allows to set an existing database user's profile to be the profile of the application user.
A working example:
SQL> -- First do the test setup
SQL> conn sys/password@cdb1_pdb1 as sysdba
Connected.
SQL> create user gov identified by gov;
User created.
SQL> create user bob identified by bob;
User created.
SQL> create user tim identified by tim;
User created.
SQL> grant create session to gov, bob, tim;
Grant succeeded.
SQL> grant create table, unlimited tablespace to gov;
Grant succeeded.
SQL> conn gov/gov@cdb1_pdb1
Connected.
SQL> create table flight(flight# number, destination varchar2(100), payload varchar2(100));
Table created.
SQL> grant select, insert on flight to bob, tim, scott;
Grant succeeded.
SQL> -- insert test data
SQL> insert into flight values (505, 'Iraq', 'Weapon');
1 row created.
SQL> insert into flight values (506, 'Canada', 'Charcoal');
1 row created.
SQL> insert into flight values (706, 'Japan', 'Battery');
1 row created.
SQL> insert into flight values (501, 'Syria', 'Weapon');
1 row created.
SQL> insert into flight values (508, 'Israel', 'Jets');
1 row created.
SQL> insert into flight values (509, 'India', 'Aid');
1 row created.
SQL>
SQL> conn lbacsys/password@cdb1_pdb1
Connected.
SQL> BEGIN
2 -- Create OLS Policy
3 -- Notice that the default_options is set to no_control to disable the policy
4 -- in order add labels to the existing data items
5 SA_SYSDBA.CREATE_POLICY(
6 policy_name => 'ols_pol1',
7 column_name => 'lb_col',
8 default_options => 'no_control'
9 );
10
11 -- Create label component levels
12 -- TOP_SECRET has the highest level of access
13 SA_COMPONENTS.CREATE_LEVEL(
14 policy_name => 'ols_pol1',
15 level_num => 4,
16 short_name => 'TS',
17 long_name => 'top_secret'
18 );
19
20
21 SA_COMPONENTS.CREATE_LEVEL(
22 policy_name => 'ols_pol1',
23 level_num => 3,
24 short_name => 'S',
25 long_name => 'secret'
26 );
27
28 SA_COMPONENTS.CREATE_LEVEL(
29 policy_name => 'ols_pol1',
30 level_num => 2,
31 short_name => 'C',
32 long_name => 'confidential'
33 );
34
35 SA_COMPONENTS.CREATE_LEVEL(
36 policy_name => 'ols_pol1',
37 level_num => 1,
38 short_name => 'UC',
39 long_name => 'unclassified'
40 );
41
42 -- Create data labels
43 SA_LABEL_ADMIN.CREATE_LABEL(
44 policy_name => 'ols_pol1',
45 label_tag => 40,
46 label_value => 'TS',
47 data_label => TRUE
48 );
49
50 SA_LABEL_ADMIN.CREATE_LABEL(
51 policy_name => 'ols_pol1',
52 label_tag => 30,
53 label_value => 'S',
54 data_label => TRUE
55 );
56
57 SA_LABEL_ADMIN.CREATE_LABEL(
58 policy_name => 'ols_pol1',
59 label_tag => 20,
60 label_value => 'C',
61 data_label => TRUE
62 );
63
64 SA_LABEL_ADMIN.CREATE_LABEL(
65 policy_name => 'ols_pol1',
66 label_tag => 10,
67 label_value => 'UC',
68 data_label => TRUE
69 );
70
71 -- Apply access_pol policy on table gov.flight
72 SA_POLICY_ADMIN.APPLY_TABLE_POLICY(
73 policy_name => 'ols_pol1',
74 schema_name => 'gov',
75 table_name => 'flight',
76 table_options => null,
77 label_function => null,
78 predicate => null
79 );
80
81 -- Add user authorizations (i.e. clearance levels)
82 SA_USER_ADMIN.SET_LEVELS(
83 policy_name => 'ols_pol1',
84 user_name => 'bob',
85 max_level => 'S',
86 min_level => 'UC',
87 def_level => 'S',
88 row_level => 'S'
89 );
90
91 SA_USER_ADMIN.SET_LEVELS(
92 policy_name => 'ols_pol1',
93 user_name => 'tim',
94 max_level => 'UC',
95 min_level => 'UC',
96 def_level => 'UC',
97 row_level => 'UC'
98 );
99 END;
100 /
PL/SQL procedure successfully completed.
SQL>
SQL> conn system/password@cdb1_pdb1
Connected.
SQL> -- Add labels to rows
SQL> update gov.flight set lb_col = char_to_label('ols_pol1', 'TS') where payload in ('Weapon');
2 rows updated.
SQL> update gov.flight set lb_col = char_to_label('ols_pol1', 'S') where payload in ('Jets');
1 row updated.
SQL> update gov.flight set lb_col = char_to_label('ols_pol1', 'C') where payload in ('Battery');
1 row updated.
SQL> update gov.flight set lb_col = char_to_label('ols_pol1', 'UC') where payload in ('Charcoal', 'Aid');
2 rows updated.
SQL>
SQL> conn lbacsys/password@cdb1_pdb1
Connected.
SQL> BEGIN
2 -- Now we change the policy to enfoce on read by first altering the policy
3 -- and then removing and applying the policy again
4 SA_SYSDBA.ALTER_POLICY(
5 policy_name => 'ols_pol1',
6 default_options => 'read_control, label_default'
7 );
8
9 SA_POLICY_ADMIN.REMOVE_TABLE_POLICY(
10 policy_name => 'ols_pol1',
11 schema_name => 'gov',
12 table_name => 'flight',
13 drop_column => false
14 );
15
16 SA_POLICY_ADMIN.APPLY_TABLE_POLICY(
17 policy_name => 'ols_pol1',
18 schema_name => 'gov',
19 table_name => 'flight'
20 );
21 END;
22 /
PL/SQL procedure successfully completed.
SQL> -- This is to demonstrate OLS can be bypassed with
SQL> -- special privileges. Here, scott is granted to read
SQL> -- the OLS protected table without any specific
SQL> -- clearance level
SQL> BEGIN
2 SA_USER_ADMIN.SET_USER_PRIVS(
3 policy_name => 'ols_pol1',
4 user_name => 'scott',
5 privileges => 'READ'
6 );
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> column flight# format 9999;
SQL> column destination format a15;
SQL> column payload format a15;
SQL>
SQL>
SQL> conn bob/bob@cdb1_pdb1
Connected.
SQL> -- The following demonstrates how to change the row label
SQL> -- of the current session so that that label is used
SQL> -- when bob is inserting new records to gov.flight.
SQL> select SA_SESSION.ROW_LABEL('ols_pol1') from DUAL;
SA_SESSION.ROW_LABEL('OLS_POL1')
--------------------------------
S
SQL> select SA_SESSION.LABEL('ols_pol1') from DUAL;
SA_SESSION.LABEL('OLS_POL1')
----------------------------
S
SQL>
SQL> BEGIN
2 SA_SESSION.SET_ROW_LABEL(
3 policy_name => 'ols_pol1',
4 label => 'UC'
5 );
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> -- The label of this row is automatically assigned as UC
SQL> insert into gov.flight (flight#, destination, payload) values (599, 'Peru', 'Medicine');
1 row created.
SQL> select flight#, destination, payload from gov.flight;
FLIGHT# DESTINATION PAYLOAD
------- --------------- ---------------
506 Canada Charcoal
706 Japan Battery
508 Israel Jets
509 India Aid
599 Peru Medicine
SQL>
SQL> conn tim/tim@cdb1_pdb1
Connected.
SQL> select flight#, destination, payload from gov.flight;
FLIGHT# DESTINATION PAYLOAD
------- --------------- ---------------
506 Canada Charcoal
509 India Aid
599 Peru Medicine
SQL>
SQL> conn scott/tiger@cdb1_pdb1
Connected.
SQL> select * from gov.flight;
FLIGHT# DESTINATION PAYLOAD LB_COL
------- --------------- --------------- ----------
505 Iraq Weapon 40
506 Canada Charcoal 10
706 Japan Battery 20
501 Syria Weapon 40
508 Israel Jets 30
509 India Aid 10
599 Peru Medicine 10
7 rows selected.
SQL> -- Now we do the clean up
SQL> conn lbacsys/password@cdb1_pdb1
Connected.
SQL> BEGIN
2 SA_SYSDBA.DROP_POLICY(
3 policy_name => 'ols_pol1'
4 );
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> conn sys/password@cdb1_pdb1 as sysdba
Connected.
SQL> drop user gov cascade;
User dropped.
SQL> drop user bob cascade;
User dropped.
SQL> drop user tim cascade;
User dropped.
I only scratched the surface of OLS. A data label is actually consists of three components:
1. Sensitivity level - has an ordering relationship
2. Compartment (optional) - used to segregate data within a level
3. Group (optional) - used to organizationally segregate data within a level - has a hierarchical relationship
In order for a user to able to access a labeled record, the following conditions must be satisfied:
a. User's clearance level should be at least as high as the the data record's sensitivity level
b. User has all the compartments associated with the data record
c. User belongs to one of the groups associated with the data record or a parent group of one of the associated groups
With the quick understanding of the OLS technology, I encourage you to look the Oracle documentation to learn more about OLS and its new powerful features.
Thursday, August 21, 2014
Introduction to Oracle Virtual Private Database (VPD) 12c
Before we get our hands on Virtual Private Databases (VPDs), let's try to understand if there is a real use case for VPD? Organizations have data of different sensitivity levels. For example, highly confidential trade secrets to classified publicly available news articles. From the point of view of databases, organizations needs to store data of different sensitivity levels in database tables and allow different users to access different set of data. For example, a manager is allowed to access records for all employees working under him/her whereas an engineer is allowed to access only his employment record. Oracle DAC privileges (or the privileges in any other major database system for that matter) either allow or deny access to the complete database objects such as tables, and views. There is no way to grant partial access to tables or views. What can we do about it?
One possible solution is to segregate data into different tables based on access control requirements. For the employee table example above, for each manager, one employee table with all of his/her subordinates could be created. That's a little too many tables! A better idea would be to create one table and multiple views on the same table for each manager. Even such an approach could be difficult to manage with so many objects and grants. Further, such approaches are not transparent to users as users need to know which table/view they can access in order to view employee records. Is there a magical way to do this segregation overcoming the above challenges? VPD is the answer! A bunch of smart people at Oracle got together and implemented a way to transparently present different records to different users based on context information. VPD has been around since Oracle 8i. Over time, now 12c, VPD has matured and evolved to support many powerful features. In this post, we are going to look at the very basic features of VPD as our goal is to get an idea of what VPD does. Once you have a hang of it, it is just a matter of referring to the user guide to absorb more advanced features of VPD.
So, what exactly VPD does? The basic idea behind VPD is really simple. It modifies user queries to add a predicate to show different set of records to different users. The query modification is transparent to users and based on the context information. The following diagram shows a very simple example.
As you can see from the above diagram, the context sensitive access policy is as follows:
"An employee can access her records and her subordinates' records if she is a manager"
Notice that VPD is transparent to database users. All employees execute the same query "select * from hr.emp". However, based on the access control policy, different users see different set of records. VPD does this by adding the predicate "upper(user) = SYS_CONTEXT('USERENV', 'CURRENT_USER') OR upper(manager) = SYS_CONTEXT('USERENV', 'CURRENT_USER'), where SYS_CONTEXT('USERENV', 'CURRENT_USER') returns the current database user.
Simple Code Example:
The following CDB setting is used to illustrate a dynamic VPD policy in action. (There are a few types of VPD polices such static, shared-static, and shared context-sensitive. These different types decide when to re-evaluate VPD polices as they use different caching strategies. For dynamic VPD policies, no caching strategy is used and policies are evaluated every time the a related statement is parsed or executed.)
Create the necessary users and grant privileges.
SQL> CONN sys/password@cdb1_pdb1 AS SYSDBA
Connected.
SQL> CREATE USER bob IDENTIFIED BY bob;
User created.
SQL> CREATE USER anne IDENTIFIED BY anne;
User created.
SQL> CREATE USER tim IDENTIFIED BY tim;
User created.
SQL> CREATE USER hr IDENTIFIED BY hr;
User created.
SQL> CREATE USER vpdadmin IDENTIFIED BY vpdadmin;
User created.
SQL>
SQL> GRANT CREATE SESSION TO hr, bob, anne, tim, vpdadmin;
Grant succeeded.
SQL> GRANT CREATE TABLE, UNLIMITED TABLESPACE TO hr;
Grant succeeded.
Notice that one needs execute privilege on DBMS_RLS package in order to manage VPD policies.
SQL> GRANT EXECUTE ON DBMS_RLS TO vpdadmin;
Grant succeeded.
SQL> GRANT CREATE PROCEDURE TO vpdadmin;
Grant succeeded.
SQL>
SQL> CONN hr/hr@cdb1_pdb1
Connected.
SQL> CREATE TABLE emp (ename VARCHAR2(50), title VARCHAR2(50), manager VARCHAR2(50), deptid NUMBER, salary NUMBER);
Table created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('bob', 'manager', NULL, 1, 10000);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('alice', 'engineer', 'bob', 1, 8000);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('tim', 'engineer', 'bob', 1, 8000);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('eve', 'analyst', 'bob', 1, 7500);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('anne', 'manager', NULL, 2, 10500);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('denis', 'engineer', 'anne', 2, 9000);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('jack', 'engineer', 'anne', 2, 9500);
1 row created.
SQL>
SQL> GRANT SELECT ON emp TO bob, anne, tim;
Grant succeeded.
Now vpdadmin user creates the VPD policy; first creates the policy function and then uses it to create the policy.
SQL>
SQL> CONN vpdadmin/vpdadmin@cdb1_pdb1
Connected.
SQL> -- Should have two input arguments and a return value - all of them are
SQL> -- of type VARCHAR2
SQL> -- Return value should be a predicate (that fits in the WHERE clause).
SQL> CREATE OR REPLACE FUNCTION get_emp_pred
2 (schema_v IN VARCHAR2, tbl_v IN VARCHAR2) RETURN VARCHAR2 IS
3 BEGIN
4 RETURN 'upper(ename) = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'') OR upper(manager) = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')';
5 END;
6 /
Function created.
SQL> -- create VPD policy
SQL> -- Note: for INSERT statements, you need to specify update_check => true as well
SQL> BEGIN
2 SYS.DBMS_RLS.ADD_POLICY(
3 object_schema => 'hr',
4 object_name => 'emp',
5 policy_name => 'emp_vpd1',
6 function_schema => 'vpdadmin',
7 policy_function => 'get_emp_pred',
8 statement_types => 'select'
9 );
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> column ename format a10;
SQL> column title format a10;
SQL> column manager format a10;
SQL> column deptid format 99;
SQL> column salary format 999999;
SQL>
SQL> CONN bob/bob@cdb1_pdb1
Today we looked at a very basic example of VPD. In a real world application, a user defined context is utilized to construct the VPD predicate. In addition to row level control, VPD allows column level control as well.
One possible solution is to segregate data into different tables based on access control requirements. For the employee table example above, for each manager, one employee table with all of his/her subordinates could be created. That's a little too many tables! A better idea would be to create one table and multiple views on the same table for each manager. Even such an approach could be difficult to manage with so many objects and grants. Further, such approaches are not transparent to users as users need to know which table/view they can access in order to view employee records. Is there a magical way to do this segregation overcoming the above challenges? VPD is the answer! A bunch of smart people at Oracle got together and implemented a way to transparently present different records to different users based on context information. VPD has been around since Oracle 8i. Over time, now 12c, VPD has matured and evolved to support many powerful features. In this post, we are going to look at the very basic features of VPD as our goal is to get an idea of what VPD does. Once you have a hang of it, it is just a matter of referring to the user guide to absorb more advanced features of VPD.
So, what exactly VPD does? The basic idea behind VPD is really simple. It modifies user queries to add a predicate to show different set of records to different users. The query modification is transparent to users and based on the context information. The following diagram shows a very simple example.
Figure: A simple VPD example
As you can see from the above diagram, the context sensitive access policy is as follows:
"An employee can access her records and her subordinates' records if she is a manager"
Notice that VPD is transparent to database users. All employees execute the same query "select * from hr.emp". However, based on the access control policy, different users see different set of records. VPD does this by adding the predicate "upper(user) = SYS_CONTEXT('USERENV', 'CURRENT_USER') OR upper(manager) = SYS_CONTEXT('USERENV', 'CURRENT_USER'), where SYS_CONTEXT('USERENV', 'CURRENT_USER') returns the current database user.
Simple Code Example:
The following CDB setting is used to illustrate a dynamic VPD policy in action. (There are a few types of VPD polices such static, shared-static, and shared context-sensitive. These different types decide when to re-evaluate VPD polices as they use different caching strategies. For dynamic VPD policies, no caching strategy is used and policies are evaluated every time the a related statement is parsed or executed.)
Figure: CDB with a single PDB
Create the necessary users and grant privileges.
SQL> CONN sys/password@cdb1_pdb1 AS SYSDBA
Connected.
SQL> CREATE USER bob IDENTIFIED BY bob;
User created.
SQL> CREATE USER anne IDENTIFIED BY anne;
User created.
SQL> CREATE USER tim IDENTIFIED BY tim;
User created.
SQL> CREATE USER hr IDENTIFIED BY hr;
User created.
SQL> CREATE USER vpdadmin IDENTIFIED BY vpdadmin;
User created.
SQL>
SQL> GRANT CREATE SESSION TO hr, bob, anne, tim, vpdadmin;
Grant succeeded.
SQL> GRANT CREATE TABLE, UNLIMITED TABLESPACE TO hr;
Grant succeeded.
Notice that one needs execute privilege on DBMS_RLS package in order to manage VPD policies.
SQL> GRANT EXECUTE ON DBMS_RLS TO vpdadmin;
Grant succeeded.
SQL> GRANT CREATE PROCEDURE TO vpdadmin;
Grant succeeded.
SQL>
SQL> CONN hr/hr@cdb1_pdb1
Connected.
SQL> CREATE TABLE emp (ename VARCHAR2(50), title VARCHAR2(50), manager VARCHAR2(50), deptid NUMBER, salary NUMBER);
Table created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('bob', 'manager', NULL, 1, 10000);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('alice', 'engineer', 'bob', 1, 8000);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('tim', 'engineer', 'bob', 1, 8000);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('eve', 'analyst', 'bob', 1, 7500);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('anne', 'manager', NULL, 2, 10500);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('denis', 'engineer', 'anne', 2, 9000);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('jack', 'engineer', 'anne', 2, 9500);
1 row created.
SQL>
SQL> GRANT SELECT ON emp TO bob, anne, tim;
Grant succeeded.
Now vpdadmin user creates the VPD policy; first creates the policy function and then uses it to create the policy.
SQL>
SQL> CONN vpdadmin/vpdadmin@cdb1_pdb1
Connected.
SQL> -- Should have two input arguments and a return value - all of them are
SQL> -- of type VARCHAR2
SQL> -- Return value should be a predicate (that fits in the WHERE clause).
SQL> CREATE OR REPLACE FUNCTION get_emp_pred
2 (schema_v IN VARCHAR2, tbl_v IN VARCHAR2) RETURN VARCHAR2 IS
3 BEGIN
4 RETURN 'upper(ename) = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'') OR upper(manager) = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')';
5 END;
6 /
Function created.
SQL> -- create VPD policy
SQL> -- Note: for INSERT statements, you need to specify update_check => true as well
SQL> BEGIN
2 SYS.DBMS_RLS.ADD_POLICY(
3 object_schema => 'hr',
4 object_name => 'emp',
5 policy_name => 'emp_vpd1',
6 function_schema => 'vpdadmin',
7 policy_function => 'get_emp_pred',
8 statement_types => 'select'
9 );
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> column ename format a10;
SQL> column title format a10;
SQL> column manager format a10;
SQL> column deptid format 99;
SQL> column salary format 999999;
SQL>
SQL> CONN bob/bob@cdb1_pdb1
Connected.
SQL> select * from hr.emp;
ENAME TITLE MANAGER DEPTID SALARY
---------- ---------- ---------- ------ -------
bob manager 1 10000
alice engineer bob 1 8000
tim engineer bob 1 8000
eve analyst bob 1 7500
SQL>
SQL> CONN tim/tim@cdb1_pdb1
Connected.
SQL> select * from hr.emp;
ENAME TITLE MANAGER DEPTID SALARY
---------- ---------- ---------- ------ -------
tim engineer bob 1 8000
SQL>
SQL> CONN anne/anne@cdb1_pdb1
Connected.
SQL> select * from hr.emp;
ENAME TITLE MANAGER DEPTID SALARY
---------- ---------- ---------- ------ -------
anne manager 2 10500
denis engineer anne 2 9000
jack engineer anne 2 9500
Now we are going to clean up.
SQL>
SQL> CONN vpdadmin/vpdadmin@cdb1_pdb1
Connected.
SQL> BEGIN
2 SYS.DBMS_RLS.DROP_POLICY(
3 object_schema => 'hr',
4 object_name => 'emp',
5 policy_name => 'emp_vpd1'
6 );
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> CONN sys/password@cdb1_pdb1 as SYSDBA
Connected.
SQL> DROP USER bob CASCADE;
User dropped.
SQL> DROP USER anne CASCADE;
User dropped.
SQL> DROP USER tim CASCADE;
User dropped.
SQL> DROP USER hr CASCADE;
User dropped.
SQL> DROP USER vpdadmin CASCADE;
User dropped.
Wednesday, August 13, 2014
Is Oracle Data Redaction broken?
In blackhat 2014 and earlier in 2013, David Litchfield has pointed out that "Oracle data redaction is broken and trivial to bypass". Here is an exaggerated scribe of his blackhat 2014 talk. Is that true that you can trivially bypass data redaction? Yes, of course, if you are using ad-hoc queries. You can trivially write a join query to fetch sensitive data if you have direct access to the database. Does that mean it is broken? No, not really; the problem is that Oracle data redaction does not claim to protect against ad-hoc queries. It is designed to be a helper function for applications to redact sensitive data before showing to application users. It can be considered as an application layer security feature. It is not a Database security feature that prevents leakage of sensitive data from the database when an attacker has direct access to the database.
Let me provide you an analogy using encryption which is not the defense for all threats. Transparent database encryption techniques provided by many database vendors, including, Oralce, Microsoft and IBM, is designed to protect data at rest. That is, if an attacker steals or gets access to a hard drive where the data is stored, they won't be able to access the actual data as it is encrypted and they don't have keys to decrypt it. However, if the attacker can compromise an application that access these encrypted data, they get access to the plaintext data. Does that mean the transparent encryption provided by all these vendors is broken? Certainly not. The same applies for Oracle data redaction.
Without the data redaction technique, an app developer would do the following to mask sensitive data, for example, credit card numbers:
1. Fetch credit card numbers from the database
2. Write application code to mask credit card numbers
3. Apply the application defined masking function before pushing the data user interface (e.g. web browser)
From the development and maintenance point of view, there are some pratical issues with the above approach:
- App developers need to write custom redaction code
- If multiple apps uses similar redaction or accesses multiple databases, it is difficult to maintain or update these redaction so that consistency is maintained in the long run. There is no way to centrally control it. It may require constant code changes.
Oracle data redaction helps overcome these practical issues. Further, notice that app already has privilege to access full credit card numbers. The objective of Oracle data redaction is not to block access to these credit card numbers. If the application is compromised, the attacker can use trivial bypass techniques to get his hands on the credit card numbers. Instead, the objective is to defend against app user compromises. If an app user is compromised, the can use his/her credential to login to the application and get access only to the redacted credit card number. In short, Oracle data redaction is designed to show the least information to app users.
If you want to prevent leakage of sensitive data from the database, you need use other preventive and detective techniques such as Oracle database vault and Oracle audit vault and firewall. This is a good example why organizations should have defense in depth to protect their valuables in databases!
Let me provide you an analogy using encryption which is not the defense for all threats. Transparent database encryption techniques provided by many database vendors, including, Oralce, Microsoft and IBM, is designed to protect data at rest. That is, if an attacker steals or gets access to a hard drive where the data is stored, they won't be able to access the actual data as it is encrypted and they don't have keys to decrypt it. However, if the attacker can compromise an application that access these encrypted data, they get access to the plaintext data. Does that mean the transparent encryption provided by all these vendors is broken? Certainly not. The same applies for Oracle data redaction.
Without the data redaction technique, an app developer would do the following to mask sensitive data, for example, credit card numbers:
1. Fetch credit card numbers from the database
2. Write application code to mask credit card numbers
3. Apply the application defined masking function before pushing the data user interface (e.g. web browser)
From the development and maintenance point of view, there are some pratical issues with the above approach:
- App developers need to write custom redaction code
- If multiple apps uses similar redaction or accesses multiple databases, it is difficult to maintain or update these redaction so that consistency is maintained in the long run. There is no way to centrally control it. It may require constant code changes.
Figure: Oracle data redaction example (Source: oracle.com)
Oracle data redaction helps overcome these practical issues. Further, notice that app already has privilege to access full credit card numbers. The objective of Oracle data redaction is not to block access to these credit card numbers. If the application is compromised, the attacker can use trivial bypass techniques to get his hands on the credit card numbers. Instead, the objective is to defend against app user compromises. If an app user is compromised, the can use his/her credential to login to the application and get access only to the redacted credit card number. In short, Oracle data redaction is designed to show the least information to app users.
If you want to prevent leakage of sensitive data from the database, you need use other preventive and detective techniques such as Oracle database vault and Oracle audit vault and firewall. This is a good example why organizations should have defense in depth to protect their valuables in databases!
Subscribe to:
Posts (Atom)