Imagine these tables (I\u00e2\u20ac\u2122m using PostgreSQL):<\/p>\n
CREATE<\/span> Capabilities (\n ID<\/span> serial PRIMARY<\/span> KEY<\/span>,\n Code text NOT<\/span> NULL<\/span>\n );\n\n CREATE<\/span> CapabilityPermissions (\n ID<\/span> serial PRIMARY<\/span> KEY<\/span>,\n CapabilitityID integer<\/span> NOT<\/span> NULL<\/span> REFERENCES<\/span> Capabilities(ID<\/span>),\n PermissionID integer<\/span> NOT<\/span> NULL<\/span> REFERENCES<\/span> Capabilities(ID<\/span>)\n );<\/code><\/pre>\nWhat I\u00e2\u20ac\u2122m aiming for here is the ability to make a permissions hierarchy. This will let us define, say, an ADMIN capability, and have that imply USER capabilities, EDITOR capabilities, etc. In other words, the CapabilityPermissions<\/code> table is recursive. I\u00e2\u20ac\u2122ve done this sort of thing in quite a few projects \u00e2\u20ac\u201c a flexible permissions system is surprisingly useful. Usually in your app, as soon as the user is identified, you look up their permissions from the database and assemble them into an easily-searched structure. With a recursive table like this you might do (in pseudo-python):<\/p>\n def<\/span> fetchPermissions( startPermissionID, permissionDict = None<\/span> ):\n if<\/span> permissionDict is None<\/span>:\n permissionDict = {}\n cursor = db.execute("SELECT CP.PermissionID, C.Code<\/span>\n FROM CapabilityPermissions AS CP<\/span>\n INNER JOIN Capabilities AS C ON (C.ID=CP.PermissionID)<\/span>\n WHERE CP.CapabilityID = <\/span>%s<\/span>"<\/span>, (startPermissionID,))\n for<\/span> row in cursor:\n if<\/span> row[1<\/span>] not in permissionDict:\n permissionDict[row[1<\/span>]] = row[0<\/span>]\n fetchPermissions( row[0<\/span>], permissionDict )\n\n return<\/span> permissionDict<\/code><\/pre>\nThis function recursively queries as it finds each new permission. Nothing in particular wrong with it other than it\u00e2\u20ac\u2122s making lots of queries.<\/p>\n
If you\u00e2\u20ac\u2122ve got a compliant database (PostgreSQL for example), then you can get the database to do the work with a recursive query.<\/p>\n
CREATE<\/span> VIEW<\/span> AllPermissions AS<\/span>\n WITH<\/span> RECURSIVE RecursedCP(CapabilityID,PermissionID) AS<\/span> (\n -- This is the non-recursive query, and forms the basis of the<\/span>\n -- whole sequence, each record in this result is stored into a<\/span>\n -- temporary working table, which is then available in the<\/span>\n -- recursive query (named after the recursive output name,<\/span>\n -- RecursedCP)<\/span>\n SELECT<\/span> CapabilityID,CapabilityID FROM<\/span> CapabilityPermissions\n UNION<\/span>\n -- The recursive query has the temporary table available<\/span>\n -- and will add its output to the result, and make that output<\/span>\n -- the new temporary table. For each capability then, we want to<\/span>\n -- use its permission as the new capability for the next<\/span>\n -- iteration<\/span>\n SELECT<\/span> AP.CapabilityID,CP.PermissionID\n FROM<\/span> RecursedCP AS<\/span> AP\n INNER<\/span> JOIN<\/span> CapabilityPermissions AS<\/span> CP ON<\/span> (CP.CapabilityID=AP.PermissionID)\n ) SELECT<\/span> CapabilityID, PermissionID FROM<\/span> RecursedCP;<\/code><\/pre>\nThe two parts are a query to \u00e2\u20ac\u0153seed\u00e2\u20ac\u009d the recursion, then the query that takes that seed and expands it, which becomes the next seed. The use of UNION<\/code> tells the database that the expansions should all be aggregated into the single query result.<\/p>\nWith this VIEW<\/code> in place, you can then easily convert the whole lot into a filtered query: so if you\u00e2\u20ac\u2122re user is assigned capability ID#1, you can get the full list of permissions that that capability implies like this:<\/p>\n SELECT<\/span> * FROM<\/span> AllPermissions WHERE<\/span> CapabilityID = 1<\/span>;<\/code><\/pre>\nI like to treat these top-level capabilities as \u00e2\u20ac\u0153roles\u00e2\u20ac\u009d, and the leaf permissions at the codes that are checked throughout the application. That way you get a role-based access system with fine-grained access.<\/p>\n","protected":false},"excerpt":{"rendered":"
Imagine these tables (I\u00e2\u20ac\u2122m using PostgreSQL): CREATE Capabilities ( ID serial PRIMARY KEY, Code text NOT NULL ); CREATE CapabilityPermissions ( ID serial PRIMARY KEY, CapabilitityID integer NOT NULL REFERENCES Capabilities(ID), PermissionID integer NOT NULL REFERENCES Capabilities(ID) ); What I\u00e2\u20ac\u2122m aiming for here is the ability to make a permissions hierarchy. This will let us\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[89,88,6],"_links":{"self":[{"href":"https:\/\/www.fussylogic.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1121"}],"collection":[{"href":"https:\/\/www.fussylogic.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fussylogic.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fussylogic.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fussylogic.co.uk\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1121"}],"version-history":[{"count":6,"href":"https:\/\/www.fussylogic.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1121\/revisions"}],"predecessor-version":[{"id":1143,"href":"https:\/\/www.fussylogic.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1121\/revisions\/1143"}],"wp:attachment":[{"href":"https:\/\/www.fussylogic.co.uk\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1121"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fussylogic.co.uk\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1121"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fussylogic.co.uk\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1121"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}