« first day (2071 days earlier)      last day (2819 days later) » 

8:29 AM
Jul 6 at 12:16, by ConcernedOfTunbridgeWells
@ypercube @Martin Smith @MarkSinkinson @MarkStoreySmith @JackDouglas @JamesLupolt - How about September 15 for drinkies?
1 hour later…
9:35 AM
@ypercubeᵀᴹ Unfortunately I'm not free on the 15th now. Big release on that day, so I'm working all night :(
10:31 AM
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
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?
11:08 AM
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.
11:29 AM
@War Which RDBMS are you using? Ask a question on the site
12:09 PM
@Phil , @War is using sqlserver
12:51 PM
? ... MS SQL (specifically) ... thinking this might be better suited to something like raven not sure why though ... not put my finger on it yet
2 hours later…
2:37 PM
So quiet without those obnoxious americans in channel
The default recursion limit is 100 but you can override that up to 32k
@billinkc here you are trying to be useful again
@billinkc @War Or up to 0 (unlimited). But depending on the design, recursion might be unnecessary and a simple TOP (1) might do.
3:01 PM
@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?
@AndriyM that's an implementation detail I am trying to figure out
basically for each row, the row will be linked to a collection of role rows (standard many to many join table)
a role would define a set of permissions and link to a set of users
another standard many to many
Given that I am using OData I need some form of projected state that gives me Item, User, Effective heirarchy Permissions as a single result
from there I have a basic Queryable Set in code I can apply the users query to
the user may just say "give me everything" (the real answer to that is not everything but everyting they have an effective read permission on)
but they may say something like "get me everything that is a child of row with a given id and where the name contains foo"
I don't know what the questions will be I just need to know that no matter what they are I don't allow too much access to the data
@AndriyM see what I mean?
@War Don't use a recursive function. Don't use a function. Don't.
3:16 PM
I'm not sure about everything but I probably get the picture in general.
@ypercubeᵀᴹ i'm just thinking about the problem from a programmers point of view
it's likely not the right point of view I admit but it's all I know lol
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?
ok lets think of this in terms of directories
I create a root directory
I create a role and a user
I link them all
@War Andriy was asking whether the "resursive" (hierarchy) applies to the data structure (like folders and files) only or the roles as well.
I set the permissions on the role so I say stuff like "anyone in this role is granted these permissions on the linked folders"
now if I add a new folder and make the parent the first folder
It inherits that fodlers permissions unless explicitly over-ridden
so I don't need any new users or roles ... when I ask for that folder it should tell me the permissions are the same as the parent
3:24 PM
@War ... and a folder belongs to another folder ...
But can a role belong to another role?
@ypercubeᵀᴹ well its a child of
no roles are only linked to folders and users
I did think about role heirarchy but then I thought ... that would be rediculous
So the roles - users is not hierarchical structure. OK
so I told the boss it aint happening
3:26 PM
So permissions are assigned to roles on objects (files and/or folders), and never to individual users on objects, correct?
@ypercubeᵀᴹ no, only the directories / pages in my case
permissions are part of the role
roles can be linked to any users and any objects
And linking a role to an object is what essentially constitutes a permission, correct?
Id ,c,r,u,d
1, 1,1,1,1
roles might look like that
where all but the first column are bits
3:28 PM
That looks redundant.
oh ?
how so ?
No, I probably misunderstood that, sorry
Didn't get the c r u d bit at first
ah ok ... you know what I mean when I talk about CRUD functionality right?
I do
@War But why are those in the Roles table and not in the Permissions table?
3:30 PM
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
@War I don't see what CRUD info for a role would mean
potentially you could build a matrix of all possible combinations and that would then fix the permissions table size
I would understand if it said:
RoleID, ObjectID, C, R, U, D
Yeah, that would make more sense
no remember before I said that there are many to many joins
3:32 PM
I do remember
So it would be a join table, RolePermissions.
Not a Roles table then
one for joining to objects, and one for joining to users
@AndriyM depends on your definition of a role I guess
It's basically the join between a set of a permissions, a set of users, and a set of objects
A Roles table would be just a table of all the roles created so far, a reference table, same as a Users table.
3:34 PM
could basically just be a big join table tbh ... I thought giving it a name like "Role" might help with the process a bit
@War You are confusing us.
You said there would be 2 join tables, one with Objects and one with Users
which made sense
Ok start again
there are to be 3 "Entity Tables"
User, Object, Role
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.
then there are 2 further "Join Tables"
that join User <=> Role and Object <=> Role
OK, we got that.
3:38 PM
OK. In this design I would expect the permissions to be stored in the Object <=> Role mapping table.
But by putting these CRUD flags in the Roles table, you are restricting yourself.
What AndriY said ^^^^
ok ... good ... good advice guys :)
so how would that work ?
with regards to the join tables / fields
With the CRUD flags in the Roles, you are restricting a role to have, for example CR but not UD - for all the objects.
that makes sense
so have like a permisssions table?
and join those to roles
I think you could just store the permission flags directly in the ObjectRole table: RoleID, ObjectID, C, R, U, D, as @ypercubeᵀᴹ suggested above.
3:46 PM
@AndriyM does that not limit you in the same way
scratch that
object > role collection
role > user collection
kinda keeps it simple, and if I added a Permissions table I could new permissions without the need for a recompile (was where I was going with that)
Although something would have to know about that new permission type
hmmm ... so that would be "This Object in this Role grants users these permissions"
as opposed to
"this object is in this role"
and the role has a set of permissions
I'm just trying to figure out the administration overhead here
@War Yeah a Permissions table would make things more complex but also more flexible
You could have more types of permissions, besides C,R,U,D
Yeh last time I built something like up I had separate permissions for being able to edit content and metadata
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.
but back then I was applying the permissions per component
Have you read this?
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...
3:55 PM
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 Ah, so a role in your design is supposed to always have a fixed set of permissions?
@AndriyM a fixed number as in it will always have CRUD
but the value of C could change through administration
I'm thinking the better way might be to think of roles like a means to relate a group of users to a group of resources
then I'm applying to that group the permissions
4:14 PM
@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?
4:28 PM
I've never clicked the "I'm feeling lucky" button in google, so I never noticed when it got that weird rulette-ish animation
@AndriyM exactly!!!
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"
4:50 PM
Ah, so you can preserve their current association with existing roles and deny anything at the same time.
it also means if we have a concern that an account has been comprimised we can tick one box and the entire system is shut down for it
but we still have the all the permission structure ... because this stuff will get complex
to give you an idea of what I am dealing with here ...
Yes, I think I get this model now.
So, the Object table is a self-referencing hierarchical table, correct?
Our clients our large organisations (groups of companies) each of which wants to share but gets jumpy on occasion
@AndriyM yeh, and thats the only place where I want heirarchy
the permissions / roles / users are not
but obv that relates back in such a way that you could "think of it as" heirarchical in the permission model
I'm thinking that in the UI I will render an effective permissions dialog which will show 2 sets, "inherited" and "applied from here"
then at the very root we can go in and drop ourselves as global admins in to get things started
then create a few roles for areas, then simply rip out the root permissions we have by unticking the right box
then if our clients want help they simply tick the box to grant us access in to their system
All this stuff is all about their systems and their data btw ... we just basically provide middleware that makes all the business connections work
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
5:04 PM
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
@War That's why asking at the main site would be better. You could use the discussion to formulate your question properly.
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)
5:59 PM
lol ok that was a lot of water ... the heat must have gotten to him!!!
6:14 PM
@War What DBMS?
@PaulVargas SQL Server
I'm having flash backs lol
it's been a long day lol
hi @PaulVargas :)
I could implement this in something else
I have been toying with raven
don't really know lots about it though
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
6:48 PM
All right, so assuming you implement permissions like this:
    CREATE TABLE dbo.Permission
      PermissionID int,
      PermissionName varchar(50)

    CREATE TABLE dbo.RolePermission
      RoleID int,
      PermissionID int,
      IsAllowed bit
you could query the Object table like this:
  dbo.Object AS o
  CROSS APPLY dbo.fnPermissions(o.ObjectID, @CurrentUserID, 'read') AS p
  p.IsAllowed = 1
Where dbo.fnPermissions is defined like this:
CREATE FUNCTION dbo.fnPermissions
  @ObjectID int,
  @UserID int,
  @PermissionName varchar(50)
    hierarchy AS
        Level = 0
        dbo.Object AS o
        o.ObjectID = @ObjectID


        Level = h.Level + 1
        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
@AndriyM I mean, there shouldn't be too many levels for permissions anyway, so I assume that the recursion won't need to be too levels deep
@Lamak That's my thinking as well. 20 level max I think was the figure.
One can always throw in OPTION (MAXRECURSION 0), of course :)
@AndriyM also, you really thought this through. This should've been an answer to a real question in DBA
I feel obligated to ask this question now
6:56 PM
@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)
@AndriyM dude, you created the table structure needed, the function to use, and the way to use it
@AndriyM any suggestions on where I could draw out a simple db diagram?
@War I suck at drawing diagrams. Never used any specialised tool, just the poor chart set that OpenOffice Draw's got to offer.
just seeing if i quickly put something together
I think a diagram might explain this much better ... feels like we are building a good community wiki post here
@War draw.io is a simple one
7:01 PM
oooo i like that
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.
@War go to "entity-relationship" and you'll find tables
ok i'll build one in there
oooo missus just turned up with kfc brb
7:18 PM
ok ... time to build this diagram
omfg ... do it the easy way
go to sql, create a db and have sql generate the diagram
@War that works too, I assumed you didn't have SQL Server available right now
in fact, got a better idea ... i could just build the dam thing ... I use EF code first
ok, remoted in to my work machine to do this
ok compiling
looking good so far ... adding a migration to build the db
ok that wasn't so bad ... firing it up
uh ... ew
sql connection issues don't look pretty
there we go
one generated back end stack
ok how should I change that ... or do you think that would be ok for the question?
7:46 PM
seems clear enough for me
8:03 PM
Q: Heirarchical permissions in a table stored heirarchy

WarThe 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...

feel free to improve this guys
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
@War Can youo explain this part?
false + anything = false
true + not defined = true
true + true = true
not defined + not defined = false
What do they mean?
@ypercubeᵀᴹ say I have a page and its in roles
Why do you need a "not defined"?
and I have a user thats in 3 but 2 overlap
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
8:11 PM
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 :)
With "parent permission" you mean the permission from the parent directory?
@War There seems to be a slight problem - in my opinion - with the false + anything = false.
yeh each "node / page / directory" in the hierarchy has a set of permissions applied to it
@ypercubeᵀᴹ what's the problem there?
^ yeh
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?
8:17 PM
I'm not sure, it maybe ok.
a deny explicitly overrides an allow
@War I'm seriously asking ;-), @ypercubeᵀᴹ is someone I respect immensely about this things
yeh that's cool @Lamak ... I'm working this through myself ... just as curious
@ypercubeᵀᴹ may know something we dont after all ... in my case it's quite likely lol
@War OK. Seems like it is enforcing strict security rules.
8:19 PM
good thing right ?
Yes, it seems right, on second thought.
I modified your logic table a bit.
You had not defined + not defined = false, which gives wrong impressions
not defined + defined = not defined
effective permissions can't be not defined
permissions can, but effective permissions can't
See my edit
8:23 PM
Yeh I just saw ... do you get what I mean by that ?
If we leave the not defined + not defined = false, we may fall into the trap:
oh because of the heirarchy
@War what happens if a permission is just not defined though?
go 3 deep of not defined and you get false
not defined = false
(not defined + not defined) + true = false + true = false
not defined + (not defined + true) = not defined +  true = true
8:24 PM
but if you have not defined for say 2 levels then on the third have a true then it should be true
which of the two results is correct?
it shouldn't be evaluated like that
@War yes, but according to your previous logic it could be either way, as @ypercubeᵀᴹ just showed
@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.
@War I got what you wanted to mean
8:25 PM
I think the idea is to figure out all the permissions and if any = false result = false
else if any true result = true
else false
@War well, that explains it better
Yes, that's what I understood:
If any of the permissions is false -> then false
Else if any is true -> then true
Else (all not defined) -> false
^ that's it :)
@War That what SQL implements, with 3VL
3VL ?
8:27 PM
You only need to add, that not defined as a result means the same as false: DENY
@War three valued logic
3 Valued Logic
@ypercubeᵀᴹ yeah, but it implements it like that if false is NULL, right?
TRUE, FALSE, UNKNOWN or as it's widely known:
@Lamak it's like a WHERE clause
I mean it in the sense that if something is unknown, then everything is unknown
8:29 PM
if the result is FALSE or UNKNOWN, the row is not shown
and in @War case, this happens if the value is false
@ypercubeᵀᴹ yeah, I know
if you have:
root > child > grand child
true > null  > null
result = true <-- its for that situation
also at each level say child you might have 3 null's that result in that null
@Lamak Ah yes, right.
It's different here
I added an extra sentence to the question
8:36 PM
@War why did you change back to not defined + not defined = false?
hopefully that helps clarify what we just talked about
wasn't that what we agreed?
or should it be not defined and then when effective final result is determined it becomes false
@War it's just that the way it it explained makes it seem like you evaluate 2 permissions at a time. It's better explained here
ok changed it back
As I said above. With that, somepeople will think that
i think you might be right
8:37 PM
(not defined + not defined) + true = false + true = false
which is not what you want
@ypercubeᵀᴹ I would use the "if any permission is false, then..."
instead of false + anything....
@Lamak i'll put that on there
@Lamak you know you can edit, right?
And now I feel like PaulWhite ;)
@ypercubeᵀᴹ I think my edits need to be approved
@Lamak is your function somewhere posted?
8:40 PM
so, it's faster if either of you make the edits instead
I don't see how you did but I saw your comment above ("it's not working as ...")
@ypercubeᵀᴹ is that better?
Yeah. I could edit later, to remove the backticks.
9:00 PM
Those comments are interesting guys ... have you been following them?
9:18 PM
right im gonna crash for the night later all

« first day (2071 days earlier)      last day (2819 days later) »