| Document security model [message #123397] |
Mon, 13 June 2005 03:43  |
vpronnikov
Messages: 2
Registered: June 2005
|
Junior Member |
|
|
Hi all,
I'm working on the project which requires to implement following document security model in application.
Document may be PUBLIC (all users may see it) and document may be PRIVATE(only specified users/users in some group may see it).
We have a document table, which contains document information, documentID (numeric) and bit flag PUBLIC/PRIVATE (PPFlag)
Also we have ACL table which contains pairs documentID - userID/usergroupID.
Based on the functional requirements we need to show to users a portion of accessible documents sorting by some fields (user selects sorting field) and implements navigation PREV/NEXT Page
ACL Table Structure:
UserID - int - contains UserID od UserGroupID
DocID - int - document ID
UserOrGroupFlag - bit - contains flag the record for user or users' group
Our select looks like the following:
SELECT * FROM (SELECT Field1, Field2 FROM DocumentTable DT
WHERE (((PPFlag = 0
OR EXISTS (SELECT 1 FROM ACL WHERE docID = DT.DocID
AND (UserID = {current_user_id} AND USERORGROUPFLAG = 1
OR EXISTS (SELECT 1 FROM UserGroups ug WHERE ug.UserGroupID = UserID
AND EXISTS (SELECT 1 FROM User_UserGroups uug
WHERE uug.UserGroupID = ug.UserGroupID AND uug.UserLoginID = {current_user_id})))))))
ORDER BY UPPER(SomeField) ASC, DocID ASC) WHERE ROWNUM<=26
This query shows a slow performance on the millions records in DocumentTable and ACL tables.
After performance investigation we removed all OR operations from query by using UNION ALL, also we removed using of User_UserGroups table, because we know all groups in which current user consist, implement checking as for the user also by using UNION ALL operation
and as final step we denormalize sorting data to remove function UPPER in ORDER BY clause. After all this improvements we get a query on several pages.
Performance of the resulting query becomes better, but under the heavy load (~1000 concurrent sessions) we get is about one minute response time (we should have response time in 2-5 seconds) for millions records in both tables.
All required indexes were added, also we tried to use hints, but nothing helps.
May somebody knows a fast document security model pattern or how we can improve our query/model?
Thanks a lot.
|
|
|
|
| Re: Document security model [message #123436 is a reply to message #123397] |
Mon, 13 June 2005 08:29  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Did you look into VPD ( virtual private database)?. VPD may be an ideal solution.
Regarding the performance of this query,
Please post information on Oracle Version/ OS.
Are the statistics updated / maintained properly?
a certain parameters like pga_aggregate_target needs to be looked into for workload related issues.
[Updated on: Mon, 13 June 2005 08:42] Report message to a moderator |
|
|
|
| Re: Document security model [message #123448 is a reply to message #123397] |
Mon, 13 June 2005 09:26 |
vpronnikov
Messages: 2
Registered: June 2005
|
Junior Member |
|
|
Thank you for the hit about VPD, we will look at it.
We update statistic every time before running tests.
We did the tests on Oracle 9.2.0.5/6 under Win2k
and Oracle 10.1.0.3 under SUN Solaris.
Under Win2k we have following memory configuration
SGA Max Size - 1GB
PGA - 500Mb
Under Sun Solaris
SGA Max Size - 7GB
PGA - 5GB
On the both platforms there is a high CPU and Memory utilization, Disk I/O is not critical in our case.
|
|
|
|
| Re: Document security model [message #123457 is a reply to message #123397] |
Mon, 13 June 2005 10:03 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Hmm, I havn't tested anything yet, but have you tried something along the lines of:
select doc.doc_id, doc.doc_name, doc.doc_author, etc
from (
select doc_id from acl
where usergroup_id = myuserid and idtype = 'USER'
union
select doc_id from acl
where usergroup_id = myusergroupid and idtype = 'GROUP'
union
select doc_id from doc
where publicflag = 'PUBLIC'
) a, doc
where doc.id = a.doc_id
order by whatever;
And is your ACL table index organized, with usergroupid first?
And are your user ids and group ids unique or do they need a flag to tell them apart as I assumed above?
You could also try doing it as two tables. An associative table between documents and userids, and another associative table between documents and usergroupids. Both would be index organized with documentid second.
Might also try making public not an attribute of a document, but instead a group that all users are a member of.
Which reminds me above, I didn't account for users being in more than one group...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
