We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
COURSE of the MONTH
11 days, 22 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
need to understand different ways of restricting data access, to meet government guidelines
Posted on 2016-08-02
In our government agency, we are required to "harden" databases, e.g., database access, prevent Personally Identifiable Information from unauthorized access, etc.
The chosen solution is leave current data in the ERP d.b. (~5 years) and delete everything else. The data warehouse then contains all data back to the beginning of time, and it's refreshed weekly. So historical reporting that previously was done from the main ERP database will have to be re-directed to the DW.
My question is this - isn't it reasonable (from a security perspective) to restrict data to current (~5 years) via security profiles ?
For example
- implement VPN policies (this is a non-programmatic solution)
- simply implement views and restrict data (Oracle views or BusinessObjects "views")
- could partitions be a way of restricting data access ?
My perspective on this is that we currently do all reporting out of the ERP database. But creating the DW means that now we have TWO very big databases to maintain. So all kinds of views, functions, procedures, etc. now have to be maintained in the DW in addition to the main ERP system. Why not let the computer do the work, instead of two physical databases ?
I also have recently come to learn about in-memory computing, and Professor Plattner of SAP (1st 5 minutes) says in-memory computing (via Hana of course) REDUCES code footprint, data footprint, etc., so much so that the DW isn't even necessary !
So my main question again is - is it really necessary to physically segregate the data to "harden" access ? Couldn't the power of the computer meet this "hardening" criteria ?
The chosen solution is leave current data in the ERP d.b. (~5 years) and delete everything else. The data warehouse then contains all data back to the beginning of time, and it's refreshed weekly. So historical reporting that previously was done from the main ERP database will have to be re-directed to the DW.
My question is this - isn't it reasonable (from a security perspective) to restrict data to current (~5 years) via security profiles ?
For example
- implement VPN policies (this is a non-programmatic solution)
- simply implement views and restrict data (Oracle views or BusinessObjects "views")
- could partitions be a way of restricting data access ?
My perspective on this is that we currently do all reporting out of the ERP database. But creating the DW means that now we have TWO very big databases to maintain. So all kinds of views, functions, procedures, etc. now have to be maintained in the DW in addition to the main ERP system. Why not let the computer do the work, instead of two physical databases ?
I also have recently come to learn about in-memory computing, and Professor Plattner of SAP (1st 5 minutes) says in-memory computing (via Hana of course) REDUCES code footprint, data footprint, etc., so much so that the DW isn't even necessary !
So my main question again is - is it really necessary to physically segregate the data to "harden" access ? Couldn't the power of the computer meet this "hardening" criteria ?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
6
5
12 Comments
Active 2 days ago
So, if you want to protect the PIM information, credit card information, etc, maybe you should look to encrypt your sensitive field through TDE:
http://www.oracle.com/technetwork/database/options/advanced-security/index-099011.html
From what I can understand, it provides the much needed data at rest protection, when people can try to bypass the database and, based on the use I did, based on roles, protect the use through the normal database usage as well.
This will prevent to make changes related to fields encryption.
Now, about how long should you have information stored, this needs to keep up with your company policies for data retention. Usually, this would go around keep at a maximum of 7 years of information for the most cases.
Now, if you want to prevent users to see that not related to them, then you need to think a row level security, which could be implemented via security profiles. So for example, a manager can see his data and of everyone under him, but an Individual Contributor can only see his own information. On the data warehouse I work with, we use that + a security predicate function to filter each and every query at database level, without changing OBIEE or SAP BO models.
http://www.oracle.com/technetwork/database/options/advanced-security/index-099011.html
From what I can understand, it provides the much needed data at rest protection, when people can try to bypass the database and, based on the use I did, based on roles, protect the use through the normal database usage as well.
This will prevent to make changes related to fields encryption.
Now, about how long should you have information stored, this needs to keep up with your company policies for data retention. Usually, this would go around keep at a maximum of 7 years of information for the most cases.
Now, if you want to prevent users to see that not related to them, then you need to think a row level security, which could be implemented via security profiles. So for example, a manager can see his data and of everyone under him, but an Individual Contributor can only see his own information. On the data warehouse I work with, we use that + a security predicate function to filter each and every query at database level, without changing OBIEE or SAP BO models.
Walter,
very good, thank you.
I hadn't thought about the encryption, but that's a good point, the link is helpful.
Our focus is on rogue users who run reports, download to Excel, and then send to the Huffington Post. But the encryption for data at rest is a good point that I'll put forward.
Our data retention for the key components is forever, but it's not a big d.b., so storage and performance is not much of an issue.
Row level security sounds good - how do you implement that ?
Is the security predicate VPN logic ? I've done that before and it's a bit tricky but slick.
very good, thank you.
I hadn't thought about the encryption, but that's a good point, the link is helpful.
Our focus is on rogue users who run reports, download to Excel, and then send to the Huffington Post. But the encryption for data at rest is a good point that I'll put forward.
Our data retention for the key components is forever, but it's not a big d.b., so storage and performance is not much of an issue.
Row level security sounds good - how do you implement that ?
Is the security predicate VPN logic ? I've done that before and it's a bit tricky but slick.
Active 2 days ago
The idea is to create a table that specify the hierarchical relation of the employees and which roles each employee is part of. Then, create a hierarchical query that, based on the employee_id and role of the logged user, the query will result in a list of allowed employee_ids that user can see the information.
This query will be the security predicate added to each query involving the employee table, limiting the information that can be seen.
For example, to enable a policy on top of employee dimension:
Then, it is just a matter of having a function that will add the predicate:
This query will be the security predicate added to each query involving the employee table, limiting the information that can be seen.
For example, to enable a policy on top of employee dimension:
BEGIN
SYS.DBMS_RLS.DROP_POLICY (
object_schema => 'schema name'
,object_name => 'employee table'
,policy_name => 'EMP_SEC');
END;
/
BEGIN
SYS.DBMS_RLS.ADD_POLICY (
object_schema => 'schema name'
,object_name => 'Employee table'
,policy_name => 'EMP_SEC'
,function_schema => 'schema name'
,policy_function => 'secure employee function'
,statement_types => 'SELECT'
,policy_type => dbms_rls.dynamic
,long_predicate => FALSE
,update_check => FALSE
,static_policy => FALSE
,enable => TRUE );
END;
/
BEGIN
dbms_rls.enable_policy(object_schema => 'schema name',
object_name => 'employee table',
policy_name => 'EMP_SEC',
enable => TRUE );
END;
/
Then, it is just a matter of having a function that will add the predicate:
CREATE OR REPLACE FUNCTION schema name.function name(obj_schema VARCHAR2,
obj_name VARCHAR2) RETURN VARCHAR2 IS
d_predicate VARCHAR2(20000);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF dbms_mview.i_am_a_refresh THEN
RETURN NULL;
END IF;
-- implement logic to return the predicate
d_predicate := '1=1'; -- standard predicate
RETURN d_predicate;
EXCEPTION
WHEN OTHERS THEN
RETURN '1=0'; -- when an exception occurs, prevent to see any data
END function name;
/
Excellent, thank you.
Does this fall under the heading of row level security.? Are there other means to implement row level secu5?
Does this fall under the heading of row level security.? Are there other means to implement row level secu5?
Active 2 days ago
Yes, it is for row level security.
The only other way I know would be use this same table as part of the model and declaratively make it part of the queries.
I prefer this predicate implementation as it is more transparent to the rest of the layers of the application.
The only other way I know would be use this same table as part of the model and declaratively make it part of the queries.
I prefer this predicate implementation as it is more transparent to the rest of the layers of the application.
Active 3 days ago
you could use VPD > Virtual Private Database
https://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm
with those policies you could restrict in time
it's an extra option for the enterprise edition
https://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm
with those policies you could restrict in time
it's an extra option for the enterprise edition
Active 2 days ago
Gadsden,
just a clarification: my solution for row level security uses Oracle VPD.
just a clarification: my solution for row level security uses Oracle VPD.
oops, I didn't study the code before closing the question.
I understand the setup part, have done that before.
But I have no clue what the function is doing :-(
- where's the sql ? I was expecting some sql logic that restricted access to the employee table based on hierarchy.
I understand the setup part, have done that before.
But I have no clue what the function is doing :-(
- where's the sql ? I was expecting some sql logic that restricted access to the employee table based on hierarchy.
Active 2 days ago
The routine needs to return a string that will be what we call the predicate, which is a piece of SQL that will be appended to the query being executed an filter.
So, basically, you need to write the logic that will add the predicate to the query.
Example: You have create the policy on top of the employee_table and the business rule implemented is this: if the today is a weekend day, the query should not return any data.
So, when you do that and in TOAD, SQL Developer or any SQL tool, you query the table employee, what will happen is this:
QUERY you wrote: SELECT * FROM employee_table;
QUERY Oracle executes on week day: SELECT * FROM employee_table WHERE 1=1;
QUERY Oracle executes on weekend: SELECT * FROM employee_table WHERE 1=0;
So, basically, you need to write the logic that will add the predicate to the query.
Example: You have create the policy on top of the employee_table and the business rule implemented is this: if the today is a weekend day, the query should not return any data.
CREATE OR REPLACE FUNCTION schema name.function name(obj_schema VARCHAR2,
obj_name VARCHAR2) RETURN VARCHAR2 IS
d_predicate VARCHAR2(20000);
cnt_weekend INTEGER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF dbms_mview.i_am_a_refresh THEN
RETURN NULL;
END IF;
d_predicate := '1=1'; -- standard predicate
select count(1) into cnt_weekend
from dual
where MOD(TO_CHAR(sysdate, 'J'), 7) + 1 IN (6, 7);
if cnt_weekend = 1 then
d_predicate := '1=0';
end if;
RETURN d_predicate;
EXCEPTION
WHEN OTHERS THEN
RETURN '1=0'; -- when an exception occurs, prevent to see any data
END function name;
/
So, when you do that and in TOAD, SQL Developer or any SQL tool, you query the table employee, what will happen is this:
QUERY you wrote: SELECT * FROM employee_table;
QUERY Oracle executes on week day: SELECT * FROM employee_table WHERE 1=1;
QUERY Oracle executes on weekend: SELECT * FROM employee_table WHERE 1=0;
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Let's recap what we learned from yesterday's Skyport Systems webinar.
- Ransomware
- Experts Exchange
- Skyport Systems
- Active Directory
- Security
- *privileged credentials
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
- Acronis
- Software-Other
- Ransomware
- Microsoft Office
- Security
- *Backup Software
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email
Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Suggested Courses
Course of the Month11 days, 22 hours left to enroll
752 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.