| Design question regarding security [message #534361] |
Mon, 05 December 2011 09:31  |
 |
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 #534370 is a reply to message #534369] |
Mon, 05 December 2011 10:32  |
John Watson
Messages: 8965
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 #534374 is a reply to message #534373] |
Mon, 05 December 2011 10:55 |
John Watson
Messages: 8965
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; |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
