hey guys ... apparently you guys are the SQL legends that may be able to come up with some sensible way to handle heirarchies in SQL
2
I have a hierarchy of data (e.g. like files and folders) and I want to apply security like windows does to directory structures
I need to "when asking for a set of rows" be able to append the "effective permissions"
where effective here means the permissions from additively blending from the stack of items up to and including the one I'm currently considering returning
I have been thinking about building a function
so I can do a where clause in my app code that would do something like ...
select * from Items where MyFunction(read = true)
I read somewhere though that SQL has quite low limits with regards to recursion also what happens if I call that for each of say 10k+ rows do the recursion iterations / limits stack?
I'm getting some hits for this kind of problem on the main site but I'm not really happy with those. I think, if presented well, this would be a terrific question for our site.
@AndriyM but what if I dosomething like "select *, effectivePermissionsFunction(params) from table" and the function is recursive and the table contains 100,000 rows?
@AndriyM I think 0 will still blow chunks at the upper boundary of 32k
But is that 100k deep heirarchy or 100k total results
You can return the latter but the maximum recursion depth was 32k. At least that's the value my brain keeps telling me we ran into when I was mucking with this stuff a few years back
@billinkc 100k rows in the table that are all part of a single tree or multiple trees, the trees themselves shouldn't be crazy deep (probably 20 levels max I would think at this point)
overall "recursions" would be greater than 100k I would think
@War First of all, a scalar UDF is likely to slow down your query considerably, but what do you mean by recursive function anyway? A function that calls itself or one that uses a recursive CTE?
Will permissions be defined for roles only or for individual users as well? Will they be defined on "items" only or on "groups of items" as well? And which of these does the "20 level max" thing apply to?
in reality it'll likely not quite be that (probably be a bit more complex tbh) but that's a good place to start and easy to build from if we assume that
@ypercubeᵀᴹ yeh they could be, this is really just what the role information may contain
there's no rule stating they have to contain the permissions
A roles table would be just a list of roles, with attributes like name, description, create date, whatever. Another table would store your objects. And a third table would store permissions on those objects for the roles in the roles table.
You could have a permissions table as a reference table. As I see it, it could be a list of all combinations of CRUD flags, with IDs. Then the ObjectRole table would actually become an ObjectRolePermission table: RoleID, ObjectID, PermissionID, if that helps you to sort things out outside SQL.
In computer systems security, role-based access control (RBAC) is an approach to restricting system access to authorized users. It is used by the majority of enterprises with more than 500 employees, and can implement mandatory access control (MAC) or discretionary access control (DAC). RBAC is sometimes referred to as role-based security.
Role-based-access-control (RBAC) is a policy neutral access control mechanism defined around roles and privileges. The components of RBAC such as role-permissions, user-role and role-role relationships make it simple to perform user assignments. A study by NIST...
The additional reference table would of course require another join. Not sure it's justified in this specific case, but, as they say, whatever floats your boat :)
Alternatively it could be a list of actual permission types ("create", "read" etc.), but then the actual object permissions would be stored in rows rather than in columns: RoleID, ObjectID, PermissionID (where PermissionID references an individual permission: create, read and so on; that way you can add custom permissions dynamically, without recompilation).
@AndriyM I'm thinking more, when I administer a role I probably just want to say something like "when I link users to this role I am granting them these rights"
adding the Object simply states what I am granting the user the rights to
@ypercubeᵀᴹ yeh my RBAC is considerably simpler because of what it is managing
no heirarchy in the roles here I feel would hugely help, on top of that the wiki article talks about the permission and operation as being separate things, I'm thinking just having a single table for permissions is enough
@War But you want the same flag values to apply to all associated objects, right? Say, you set the flags C and R for a role, then associate objects X and Y with that role. Now if you set the U flag for that role, it should apply to both X and Y (and their children, I guess) – is that your intention?
I basically want a simple way to say "these permissions, these users, these objects"
and at the root i'll do exactly that
I'll have basic groups like an admin group and a read only group, but then admins may want to build sub sections out that contain smaller sets of users, and they may even grant some of them admin rights to particular parts
Here's the problem. User A belongs to Roles M and N. Object X is associated with Roles M and N. Role M has CRUD = (1100), role N has CRUD=(0011). What permissions should User A have on Object X?
That's easy, in my C# model I solved this with an additive blend ... I was thinking about having the permissions be nullable bits so the available values are true, false, not defined where the following is true ... false + anything = false true + not defined = true true + true = true not defined + not defined = false
in other words you got no permissions unless you are granted them, and a deny rule overrides an allow rule
That way you could do stuff like setup a contractor in the system then have an inactive users role that denies everything, when the contractor is not working you simply add them to that role, and when they are you remove them and they get all their permissions throughout the system
the inactive role would of course have to be put on the root level objects too
@AndriyM does that make sense?
no what I need is a way to for all objects, for the current, get all objects where effective permissions read = true, return the object + effective permissions
It doesn't have to come back as a single row, the results could be multiple rows, I can multi read them on the code side
I just need this to be as fast as possible ... my gut feeling is that the fastest way to do this is to pre-compute this stuff and de-normalise it, but I figured some SQL genius will go, "naw just do this magic thing and it'll all wizard in to place cuz SQL rulez"
I think I could figure out how you could return all permissions available for an (Object, User) pair. From there to getting a (true/false) kind of answer for a specific kind of permission is probably not far, but I guess it would mean you would need to implement your additive blend in T-SQL as well as in C#.
the thing is what we have is, each company is basically going to have a tree of pages that make up their version of the system (so to speak) and it gets funky when you get in to peoples jobs that involve doing stuff for more than 1 company ... this felt like the cleanest way to bridge the gap but also allowed us to merge or separate as much as we wanted by simply using data
I was hoping that the ideal solution might be a one where the result was effectively the object + the effective permissions , even if I had to apply the effective permissions with a projection in some way
my worry of course was recursion because of the trees
this is a dead simple problem when you have something like C# and the full dataset in ram but I can see this dataset growing beyond what we could ever want to put in ram
I imagine it something like this: SELECT o.* FROM dbo.Object AS o CROSS APPLY dbo.fnPermissions(@CurrentUserID, o.ObjectID, 'read'). I'm assuming here that the context (current user) is set as a query parameter (@CurrentUserID). The recursion would be implemented in the dbo.fnPermissions function (inline TVF) as a recursive CTE.
there we go ... now you're where I was thinking when I joined this room
good idea / bad idea?
also is a CTE better than recursive function?
I'm actually super impressed I found someone I can explain shit too and they don't go ... eh ... wtf ... use oracle clearly ... leaving me thinking, wtf?
I've got EF sat on top of SQL (yeh that's probably cost me some credibility in here but here, it's not like I have to keep it) so from within that I can register the function that should then let me use it from linq queries
what I wasn't sure of though ... will it scale?
If I do something like you said ...
SELECT o.* FROM dbo.Object AS o CROSS APPLY dbo.fnPermissions(@CurrentUserID, o.ObjectID, 'read')
and I have 100k+ rows in there do you think it'll shit the bed?
I obviously saw the whole thing differently in the beginning. Now that you've explained it, I'm not sure I'm in a good position to judge whether it's good or bad. You've explained how it works, and that kind of justifies your choice for me, even though I disagreed at first. Besides, I'm a developer much more than a database designer, I'm afraid, and at this point I'm already looking at your model as a given and thinking of ways to resolve a specific problem (how to determine permissions).
Yeh it's basically the back end model for a content management system
@AndriyM my thoughts are pretty much the same as yours ... kinda like ... meh ... I have no idea if this is going hurt later, but I really don't want to find out when I have a db with a million + rows in it
Well that long discussion is basically my question ... no idea how to put that in to a simple 1 para "so how I do this and not hang myself?" type so question.
plus I have really bad rep on dba.so ... I can't seem to ask good questions for some reason
dba's are brutal
brb .. gonna try and wear the dog out (pfft, fat chance)
I did also consider going lower level and just using say ... lucene for it ... it would be crazy fast, and in that implementation I would de-normalise the shit out of it ... but something about that made me laugh
doesn't oracles pl-sql let you do funky shit (because it's a full blown programming lang in the db
I'm thinking like a computed column with a complex procedure in it
SELECT
o.*
FROM
dbo.Object AS o
CROSS APPLY dbo.fnPermissions(o.ObjectID, @CurrentUserID, 'read') AS p
WHERE
p.IsAllowed = 1
;
Where dbo.fnPermissions is defined like this:
CREATE FUNCTION dbo.fnPermissions
(
@ObjectID int,
@UserID int,
@PermissionName varchar(50)
)
RETURNS TABLE
AS
RETURN
(
WITH
hierarchy AS
(
SELECT
o.ObjectID,
o.ParentObjectID,
Level = 0
FROM
dbo.Object AS o
WHERE
o.ObjectID = @ObjectID
UNION ALL
SELECT
o.ObjectID,
o.ParentObjectID,
Level = h.Level + 1
FROM
dbo.Object AS o
INNER JOIN hierarchy AS h ON o.ObjectID = h.ParentObjectID
No idea about performance at this point, you'd have to test. But I'm using an inline table-valued function here, which should at least be better than a scalar function or a multi-statement TVF
@Lamak I would really be happy to see the question, because that way others would have a chance to suggest something and I might learn something as well.
I know I'm still far from good when it comes to query tuning, even though I've learnt a lot thanks to SE (and this site especially)
I say, take your time writing the Q. Database design questions are easy to get wrong here (all the more so considering how brute the DBA folk are). One wrong move – and it's too broad, or too localised, or unclear.
The question
Assuming the following database structure (modifiable if need be) ...
... I am looking for a nice way to determine the "effective permissions" for a given user on a given page in a way that allows me to return a row containing the Page and the effective permissions.
I am thinkin...
I'm really curious as to what the community might come up with here
Do you think it's worth adding that i'm using EF and OData to expose this data?
@AndriyM yeh I agree ... getting this right could pull out a lot of talented folk and get some really cool answers, getting in wrong however could be a bit of a waste
both roles may define true (user can do something) false (user cannot do something or null (it's not defined if the user can do that thing) for each CRUD part
the idea is that you can leave a decision open to imply "this decision falls back to the parent effective permission"
I think @AndriyM had some really good stuff above where he appears to have provided a solution, so I'm thinking I might start there and tweak and see if i can get that to fit nicely :)
Say a user has false permissions for a folder. And in a subfolder it has true. According to your rules, it should not have access to the subfloder. Corect?
@War My function implements that wrongly then. It assumes that explicit permissions take precedence, and if different roles have explicit true and false for the same object, false takes precedence. I guess the ORDER BY needs tweaking.