Home » RDBMS Server » Security » Design question regarding security (Oracle 10g, Windows XP)
Design question regarding security [message #534361] Mon, 05 December 2011 09:31 Go to next message
venuspvr
Messages: 7
Registered: December 2011
Location: a
Junior Member
Hi all,

We have a requirement where we have 4 schemas sch1, sch2, sch3 and sch4. Each of the schemas have identical table structures built. Reports will be built by the users based on these schemas. The users should only be able to see the data unioned in all the schemas to which they can access. for example user1 should have permissions to see data from sch1 and sch2 unioned together, but not sch3 and sch4.

Below is what I implemented:
I created a 5th schema "CONSOLIDATEDschema" with a view as below:
CREATE VIEW ITEM_MASTER_TEST_View
AS
(
SELECT ITEM_CATEGORY,LIST_PRICE_PER_UNIT, 'sys1' as SCHEMA_NAME FROM sys1.ITEM_MASTER_TEST
UNION ALL
SELECT ITEM_CATEGORY,LIST_PRICE_PER_UNIT, 'sys2' as SCHEMA_NAME FROM sys2.ITEM_MASTER_TEST
UNION ALL
SELECT ITEM_CATEGORY,LIST_PRICE_PER_UNIT, 'sys3' as SCHEMA_NAME FROM sys3.ITEM_MASTER_TEST
UNION ALL
SELECT ITEM_CATEGORY,LIST_PRICE_PER_UNIT, 'sys4' as SCHEMA_NAME FROM sys4.ITEM_MASTER_TEST
)

I also created tables user_details and user_privilege_details in "CONSOLIDATEDschema"

Below is the sample data from user_details:
INSERT ALL
INTO user_details(userid,username) VALUES (1,'user1')
INTO user_details(userid,username) VALUES (2,'user2')
INTO user_details(userid,username) VALUES (3,'user3')
INTO user_details(userid,username) VALUES (4,'user4')
INTO user_details(userid,username) VALUES (5,'consolidatedschema')
SELECT * FROM DUAL

Below is the sample data from user_privilege_details:
INSERT ALL
INTO user_privilege_details(user_privilegeid,schemaname,userid) VALUES (1,'sys1',1)
INTO user_privilege_details(user_privilegeid,schemaname,userid) VALUES (2,'sys2',1)
SELECT * FROM DUAL

Below query can be used against "CONSOLIDATEDschema"
to get the data for user1:
SELECT imt.* FROM item_master_test_view imt INNER JOIN
(SELECT upd.schema_name FROM user_details ud INNER JOIN user_privilege_details upd ON ud.user_id= upd.user_id WHERE ud.user_name='user1') sch
ON imt.schema_name=sch.schema_name

But with this design I will have to give read access to every user to every schema and then use the tables user_privilege details to filter out the records. Is this a good design. Is there a more secure way of designing this. Iam looking at various options I have. Please suggest.
Re: Design question regarding security [message #534363 is a reply to message #534361] Mon, 05 December 2011 09:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Is this a good design.
quantify "good"?
Good based upon which metric.

>Iam looking at various options I have.
use single schema & add new column, data_owner, to every table
Re: Design question regarding security [message #534364 is a reply to message #534363] Mon, 05 December 2011 10:12 Go to previous messageGo to next message
venuspvr
Messages: 7
Registered: December 2011
Location: a
Junior Member
Sorry, I wasn't clear. I want to implement the most secure method so that a user accidentally doesn't see the records of a system to which he doesn't have access to.

Multiple schemas were designed, one for each system to ensure the users see only the data in their system. But very few users have a request of seeing data from more than one schema but not all the existing schemas which makes it a little complicated. I need advise on how to implement the security.

Please let me know if you need more information
Re: Design question regarding security [message #534366 is a reply to message #534364] Mon, 05 December 2011 10:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I need advise on how to implement the security.

Virtual Private Database
Re: Design question regarding security [message #534369 is a reply to message #534366] Mon, 05 December 2011 10:24 Go to previous messageGo to next message
venuspvr
Messages: 7
Registered: December 2011
Location: a
Junior Member
Thank you for the quick reply. I will try that option and see how it works out
Re: Design question regarding security [message #534370 is a reply to message #534369] Mon, 05 December 2011 10:32 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Yes, VPD is the answer, but you do need Enterprise Edition licenses to use it. If you don't have EE, you can achieve the same result by covering the tables with views that include a predicate with a sys_context call.
Re: Design question regarding security [message #534372 is a reply to message #534370] Mon, 05 December 2011 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
you can achieve the same result by covering the tables with views that include a predicate with a sys_context call.


Which is a better way even if you have EE.
Only use VDP if you can't do it with views.

Regardas
Michel

[Updated on: Mon, 05 December 2011 11:51]

Report message to a moderator

Re: Design question regarding security [message #534373 is a reply to message #534372] Mon, 05 December 2011 10:47 Go to previous messageGo to next message
venuspvr
Messages: 7
Registered: December 2011
Location: a
Junior Member
Could you please also give references to some articles which illustrate using views to implement security.

Thank you very much Michel
Re: Design question regarding security [message #534374 is a reply to message #534373] Mon, 05 December 2011 10:55 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Here's an example:
drop user jon cascade;
drop user jim cascade;
drop user apps cascade;
grant dba to jon identified by jon;
grant dba to jim identified by jim;
grant dba to apps identified by apps;
create table apps.names(c1 varchar2(10));
insert into apps.names values('JIM');
insert into apps.names values('JON');

create view apps.v1 as select * from apps.names where c1=sys_context('userenv','current_user');

conn jon/jon
select * from apps.v1;
conn jim/jim
select * from apps.v1;
Re: Design question regarding security [message #534714 is a reply to message #534374] Thu, 08 December 2011 11:27 Go to previous message
venuspvr
Messages: 7
Registered: December 2011
Location: a
Junior Member
Thank you John, it worked great
Previous Topic: Password verifiy function while creating a profile
Next Topic: Stored Procedure - Security Question
Goto Forum:
  


Current Time: Thu Mar 28 03:43:29 CDT 2024