Home » RDBMS Server » Performance Tuning » Document security model
Document security model [message #123397] Mon, 13 June 2005 03:43 Go to next message
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
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 Go to previous messageGo to next message
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 Go to previous message
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...
Previous Topic: Oracle sequence
Next Topic: who is the user executing query which is using more disk space?
Goto Forum:
  


Current Time: Sun Sep 20 22:39:01 CDT 2020