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.
Subscribe to:
Posts (Atom)