[Gibble]

I want to try and start a brainstorming session on implementing security in an application. Specifically, in regards to the data model, and functions that would implement it. As well, as a user friendly means to administer such in the application.

Now, at it's core, we require a logins/users table, an Actions table and a permissions table.

Users
UserId
Login
Password

Actions
ActionId
Description

Permissions
PermissionId
UserId
ActionId

As well as a couple basic functions.
Bool Login(Login, Password)
Bool Logout()
Bool HasPermission(UserId, ActionId)

For your basic application, little more is needed. Problems arise, when hundreds and thousands of user permissions must be managed in a rather large application. At which time, we logically start grouping users.

Groups
GroupId
UserId
Name

And setting Permissions on the Group...rather than the user level, possibly still keeping user level permissions for refinement. For that we can simply add a column to our Permissions Table for granting to a group and checking for a value in either column for that row.

Permissions
PermissionId
GroupId
UserId
ActionId

This again, often falls short, when two people both have permission to perform the same action in an application, but one should only be for a specific set of data, and the other, for different data. For example company A and company B...but how then do we refine access at this point while still keeping an efficient database, and manageable permissions? Where do we extend this basic data model we have built?

[Gibble]

After some thought...I've come with a workable data model...I think, that's flexible enough to allow/disallow data access even though a person has role access to perform the task at hand...


When setting up and defining Actions, you also define Data Access Filters, that are basic templates for setting up what a person has access to, for later granting them.

I envision tables similar too the following.

DataAccessFilters
DataAccessFilterId
ActionId
Name <--Filter on Courses
Description
Table <--Courses
Column <--CourseType
Comparison <--Between
ValueDataType <--Integer

When granting a person/group an Action, you also grant based upon the data and store that permission in a Data Access Grants table...similar to the following.

DataAccessGrants
DataAccessGrantId
PermissionId
Value1 <--17
Value2 <--20
CRUD <--RU


This would allow the person/group given role permissions in the Permissions table (referenced by PermissionId) to Read or Update if the CourseType column of the Courses table is between 17 and 20.


The trick now, is when writing the function in code that is associated with this action to read these tables and determine if in fact the person is trying to access data they have been granted access too.


...and the turtle takes another step towards solving his conundrum.