Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gregoryagu
Yak Posting Veteran
80 Posts |
 Posted - 2007-09-18 : 19:02:43
|
| SELECT * FROM dbo.ObjectsWHERE (UserRights & dbo.GetUserRights()) > 0)The purpose of this view is to implement row level security. The Field, UserRights is a BigInt and holds up to 64 bits corresponding to the user or group that has a right to view this record.The Function GetUserRights() is a function which returns an Int corresponding to the user rights of the person who is currently logged in. In a bitwise fashion, it then "ands" them together to see if the value is greater than 0. If so, the user has rights to the record.If I hardcode a value to replace the function, the Select statement is very fast. However, if I use the function it is extremely slow. I believe this is due to the function getting run once for each row of the table, instead of just once.Is there a way to rewrite this so that it only runs once? I could do this in a stored procedure, but I need it in a view so that I can use replace "Objects" (the table) with "vwObjects" the view.Thanks,Greg |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2007-09-18 : 19:28:48
|
| you can try something like. select O.*from dbo.Objects Ocross join (select dbo.GetUserRights() as gur) Awhere O.UserRights & A.gur > 0 |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-09-18 : 19:44:11
|
| Don't try to be extra clever and store attributes in bits and do bit masks and the like. None of that will efficiently use any SQL indexes, and it makes your design, maintenance and SQL much more complicated than it needs to be.Simply store objects and a table that relates users or groups to the objects they have access to, then select with a simple join.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
gregoryagu
Yak Posting Veteran
80 Posts |
Posted - 2007-09-19 : 00:57:35
|
| Jeff,I have given the matter a lot of thought, and indeed, I am trying to come up with the simplist fastest way to get this done. Maybe I am missing the point here, but so far, bit-twiddling has been the fastest and no more complex. So far, the profiling time has been about 225 milliseconds for bit setting, and up to about 600 milliseconds for the join. (We have tried it both ways.)This is for selecting about 370,000 records. If the join was just as fast, or faster, I would be happy to go with that.Here is my join solution:Objects table has a field called object ID. Join this to a table called permissions which has ObjectID, UserID and permissions. There are three different permissions; Read or Write or Deny.SELECT * FROM OBJECTS O INNER JOIN PERMISSIONS P ON O.ObjectID = P.ObjectID where Permissons <> "Deny"This would exclude the Deny, and return all the Read Or Write Permissions which the GUI would have to deal with (ie display readonly or not.)The issue is if the person is a member of several groups, then he would have Several Records in the permission table that would apply to him. For example, a user who is a member of a group would have his own record in the permissions table, and the Managers Role would also have permissions on the table. So the two records would give duplicates in the Object table. Also if one has ReadOnly, and the other has Write Permissions, then how do you select the Highest or lowest priviledge? I suppose that one could have the permissions be an int, and thenSelect Objects.*, Max(Permissions) group by ObjectID Or Do a view of the Permissions Table as Select ObjectID, Max(Permission) from Permissions and then join that with the Object table. Readonly would be a 1, Read would be a two, and Deny would be a 3.The join would be Select * from Objects inner join vwPermissions on O.ObjectID = vwPermissions where Permissions < 3. It's getting late, I better give this a rest for the night!Greg |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2007-09-28 : 21:31:31
|
| Why are you using SQL as if you were writing Assembly language? Bits?! Records?! The third sub-language in SQL is DCL (Data Control Language) and it works with DDL and DML to handle permissions and access. It is not usually covered very well in most SQL courses or even in SQL books, but you can do a lot with it.--CELKO--Joe Celko, SQL Guru |
|
|
|
gregoryagu
Yak Posting Veteran
80 Posts |
Posted - 2007-09-28 : 23:24:28
|
| Dear Joe,Thanks very much, I will check that out.GregPS I consider it an honor to have you here! I still have Data & Databases: Concepts in Practice on my Bookshelf. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-09-29 : 17:23:36
|
| How can he use DCL to implement row-level security?e4 d5 xd5 Nf6 |
|
|
|
|
|
|
|
|
Function in a view