November 9, 2005 at 2:44 am
I have proposed a solution within my company and before the guys implement it I'l like to get the opinion of some of you guys if the stuff will actually work under production stress loads (Peak would be 100 Queries / minute, with a DB size of approx 1 GB when its fully loaded. Hardware will be the best possible.)
OK.
Heres a brief history...
The Original code is written in C# (i dont know C# btw). Keyed below is a step-by-step algorithm (crude one)
1) Fire queries and collects ID's as comma separated values.
2) Check if the user who is logged in has access to the objects which have the id's returned by the queries in point 1. If no then check if the everyone role has access to those ids. (We have 2 levels of authorisations, the first is that we check if the user has privilege on the ids and the second we check if everyone role has access to the ids. If either of the conditions is true the user is granted permission on the object. By default every user belongs to everyone role, but access within the system is generally given to individual users and sometimes to everyone role). This was done in a loop as the Id's were being stored as comma separated values.
3) Prepare a query and fire it with those ID's in a IN QUERY. eg:
Select x, y from abc where x in (List of ids).
4) Then they used this result set and prepared an XML out of it.
The one, which I proposed, was
1) Prepare the queries for collecting the id's and get the result in a table variable.
2) Left outer Join this table with the authorisation table and insert the result in another table variable. This second table variable will be used to find if “everyone” role has access to the objects selected. The reason is that I just need to check if everyone has access for objects for which the user has returned a null in the joined table so that i don’t end up rechecking access on the same objects for which the user has access. All I do is check for those objectids which have null in the prevlige column and join them with my authorisation table and I union this result with my second table variable.
3) Use the result set in a join with a query to return the XML using the "for xml" clause.
My concern is that since this procedure will be called for almost every data seek operation in the system, will using table variables be detrimental to its performance. I have tested it will come batch queries from 5 machines (each machine firing a batch of 100 queries with 5 windows in query analyzer) and it worked fine. Though the data was not much.
November 9, 2005 at 5:34 am
Not an answer - sry - but I'm curious. What's wrong with the securitymodels already in place?
Why not use either windows authentications or SQL logins security models? Seems to me this is only reinventing the wheel of sorts..
/Kenneth
November 9, 2005 at 6:05 am
First of all this would be a platform independent implementation hence to keep it generic we have to have our own representation of security model.
Secondly "Flexibility" is very important here. The idea is to give the clients administrative and configurational rights for setting up security.
Besides its cumbersome to apply windows/sql security models to a complex structure.
November 9, 2005 at 10:53 pm
/*I declare a table variable here to hold the object ids from the Target tables returned by query (select planid, 1,…..).
The reason this table is created is to use it in a JOIN condition as opposed to an IN CLAUSE which is inefficient.*/
declare @TEObj table
(objectid int,
userid int,
everyoneid int)
INSERT INTO @TEObj (objectid, userid, everyoneid)
<select planid, 1, 101 from plan
union select activityid,1,101 from activity
union select taskeid, 1, 101 from tasks>
/*This table will contain the object id’s on which authorization has been applied and which have the required privileges for the “specified user”. This is where I get the ID for which the specified userid i.e. “1” in this example has the necessary prevlidges i.e. 2 in this example. I also check for PRIVILEGEVALUE <> 0 which means no access
Left outer join is used because we want to retain the ids which do not have the specified privilege for the USERID but EVERYONE could have the access to that object.*/
declare @TEPrevUser table
(objectid int,
PRIVILEGEVAL int)
insert into @TEPrevUser(objectid,PRIVILEGEVAL)
SELECT a.objectid, PRIVILEGEVALUE as ''MAXPREV'' from @TEObj a
left outer join
ALOM_AUTHORIZATIONAPPLIED ad
on ad.objectid = a.OBJECTID
and ad.ACCESSORID = a.userid
and ad.PRIVILEGEVALUE = 2
and PRIVILEGEVALUE <> 0
/*This table contains the final list of ID’s which have been filtered after applying authorization for which either the USER or EVERYONE has the required access. Since the ID’s for the user are already with us, this section checks for everyone access.
Hence this table variable contains the final list of ids which contain the join of the id’s returned by @ TEPrevUser and @TEPrevEve (which contains the ids for which everyone role has the specified prevliges)*/
declare @TEPrevEve table (objectid1 int,PRIVILEGEVAL1 int)
insert into @TEPrevEve(objectid1,PRIVILEGEVAL1)
select objectid,PRIVILEGEVAL from @TEPrevUser where PRIVILEGEVAL is not null
union
SELECT a.objectid, PRIVILEGEVALUE from @TEPrevUser a
inner join
ALOM_AUTHORIZATIONAPPLIED al
on al.objectid = a.OBJECTID
and al.ACCESSORID = @everyoneid
and al.PRIVILEGEVALUE = 2
and PRIVILEGEVALUE <> 0
and a.objectid not in (SELECT a.objectid from @TEObj a
inner join
ALOM_AUTHORIZATIONAPPLIED am
on am.objectid = a.OBJECTID
and am.ACCESSORID = a.Userid
and PRIVILEGEVALUE = 0)
/* Finally I join the @TEPrevEve table variable, which contains the id’s of all the entities for which access control has been checked with the table from which I want to derive the id’s from */
<select AllTaskDetails.GenId, AllTaskDetails.PlanName from @TEPrevEve TargetEntity inner join AllTaskDetails
on AllTaskDetails.GenId = TargetEntity.objectid1 for xml auto,elements>
Please note that this is part of a dynamic SQL where the queries are added and then it takes shape of the query I mentioned above, and is executed using sp_ececutesql.
I was wondering it its an efficient way of doing it. OH and another thing, I would be needing the values stored in @TEPrevEve if I wish to do any more processing of joins in the same batch.
November 10, 2005 at 10:23 am
A major problem that I have encountered with table variables is that SQL Server cannot handle them under parallel processing. Performance plummets on a multi-processor machine if table variables are involved in a parallel execution plan. The only benefit that they afford, as far as I know, is that they avoid any locking contention issues in tempdb (and are dropped automatically on going out of scope - but that just encourages lazy programming!). I would suggest only using table variables for short look-up lists.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply