Record level permissions (1 Viewer)

[SunWuKung]

Could somebody give me some help on how record level permissions for users are usually handled?

I have ideas how to check if a user could or couldn't execute a function in general, but I couldn't figure out how to check if a user could do something with a certain record - like modifying a specific record.
I wold like to implement checks like this on many different tables.
How would you do this?
Link a permission table to each table? But than I would need to have a different permission check function for each table as well - there must be a better way.

Thanks for the help.
SWK

 

[FoFa]

Access does not have row level security. But there is a way around it. Usually we create a permissions table with what ever data you need to define permissions. Than base everything on queries that use the security table to return just those rows by the permissions contained in that table.

 

[SunWuKung]

Do you create a separate permissions table for each table that you want to assign permissions or have a single permission table for all permissions?

 

[Robert Dunstan]

Hi,

What I do is I have a table (tblUsers) which stores the user name, password and permission level. I force the user to login to the database by a login form and when they click OK there's a bit of code that uses the DLookup function to return their permission level and their username. These are then stored in public variables.

I can then filter records by the username so that the user can then only edit his/her records. This is achieved by setting the criteria of my underlying queries to a public function that returns the value of username variable:

Public Function UserName()

UserName = LoggedInAs

End Function

The criteria setting for the queries would be
UserName()

 

[FoFa]

We usually try to keep it in one table (K.I.S.S.) and use check boxes (binary) values if possible. This is ok for table permissions, but you asked about ROW LEVEL permissions which is more complicated. ROW LEVEL is usually controled by some data values. With this we usually use 2 tables. One for the basic permissions and one linking field values to the permissions table. Than we use an IN for the queries against this table with a subselect.

MainTbl
ItemID
CompanyID
QOH

Permissions
UserID
UserKey

RowLevel
UserKey
Datavalue

SELECT * FROM mainTbl
WHERE CompanyID in
(SELECT Datavalue from Rowlevel inner join Permissions on Permissions.userKey = RowLevel.UserKey Where permissions.Userid = [supplyuseridhere])

So if User Joe has 2 records with a datavalue of Co1 and Co2 he would get all the data with those CompanyID's with that query, but not Co3 or Co4 etc.

 

[SunWuKung]

Thanks Fofa and Dunstan,
that was very useful for me.

SWK