User Level Security in Access 2007 (1 Viewer)

[chohan78]

Hi,

during research i have found that in Access 2007 you can't create user level security, so keeping in mind i have created couple tables for users and security permissions but not sure how can i apply these permissions on whole database.... ???
thanks

 

[Some]

You can do this by having a master table which contains the username and access level of each user of the database. From there you can use some coding to get the name of the person currently logged in, DLookup their access level and change forms accordingly.


For example a record for a user could be:


username1 1
username2 1


And for admin:

admin1 2



Just say if you need more detail, I'm just on tablet at the moment.

-Some

 

[chohan78]

Hi
Thanks for the post as I said in earlier post that I have created the user login and responsibility table but I am not sure the coding to secure the database from different level of users. e.g. some users use just for view some use for data entering and obviously developer has the responsibility for design change.

 

[Some]

If you've split your database you could define a variable in the front end called AccessLevel or something.

When a form/table loads check the access level e.g.

If AccessLevel = 1 then 

MsgBox "Restricted access" 

DoCmd.Close AcForm, "Form name" 


End If

You can also restrict navigation based on this also, e.g.

If AccessLevel = 1 then 

BTN.visible = true 

End if

Will need more information on what exactly you are wanting to secure for better answer.

 

[chohan78]

Thanks but I also want to hide tool bars and manu bar when the user is logged in and all these should be visible to developer and also I don't know what's BTN stands for. If anyone can help ??
Thanks

 

[Galaxiom]

Access dropped ULS because it is so weak.

If you require real security you need the backend on a database server.

 

[cbrxxrider]

If you've split your database you could define a variable in the front end called AccessLevel or something.

When a form/table loads check the access level e.g.

If AccessLevel = 1 then 
 
MsgBox "Restricted access" 
 
DoCmd.Close AcForm, "Form name" 
 
 
End If

You can also restrict navigation based on this also, e.g.

If AccessLevel = 1 then 
 
BTN.visible = true 
 
End if

Will need more information on what exactly you are wanting to secure for better answer.

-------------------------------------------------

Seems like you've created something here that might work for the user access control I'm hoping to implement, I just need a little more detail.

I currently have a login box with row source =

SELECT [Employees Extended].ID, [Employees Extended].[Employee Name], [Employees Extended].[Job Title], [Employees Extended].[Privilege ID]
FROM [Employees Extended]
WHERE ((([Employees Extended].[Privilege ID])="M" Or ([Employees Extended].[Privilege ID])="S" Or ([Employees Extended].[Privilege ID])="A"))
ORDER BY [Employees Extended].[Employee Name];

so the privilege level will be either M- manager, S- supervisor, or
A- admin.

All forms, etc simply keep track of the current user "=[TempVars]![CurrentUserID]"

I have certain forms, such as Departments and Goals and Employees where I'd like to restrict access to those with privilege level S. I'm wondering if there's simply an If/Then statement I could put in the OnLoad of each form, report, etc that would be something like this..

If user privilege level = "S" then do not load.

Any help from those who can code well would be amazing.

:banghead:

 

[The_Doc_Man]

In essence, starting with Acc2007 and going forward, you have to "roll your own" security by coding your application to do its own thing at a fairly low level. What I have done is this.

1. Users don't log in to my database. They log in to the terminals in way that is secure (because of the domain-level security) so I just ask the terminal who they are - and that is their login name. It leads to their user record and that includes the role they play.

2. EVERY FORM was designed to be sensitive to roles, so what happens is that the form finds your role from an internal variable. NO form voluntarily "goes away" so you never see the Access Objects list. (In older parlance, you don't see the database window.)

3. The form has subroutines to change controls to take into account that some things are for public consumption but other things are not. So based on the role, the "Form Init" subroutine will look at information that I store in the TAG property to decide whether to lock the control or not. (And to make it visible or not, etc.)

I won't say it is easy - but the result can look pretty slick. What will happen is that you will quickly recognize some commonly executed things that you have to do to make the controls behave in the right way. So for example, I have a subroutine that I call to set the color and style of the control based on your role and some other factors determined by context. I have another routine to diddle with some command buttons, which led me to create a routine that moves buttons around so that they are flush with the right margin of the form (and grow to the left). These buttons vanish or appear based on context as well, all based on the idea that I won't show someone a button they aren't supposed to click, and I won't unlock a text box that they aren't supposed to modify.

OK, it takes a willingness to condemn yourself to a tedious approach at first - but as your project grows and your "toolkit" of available useful subroutines grows, you can develop serious speed.

Another trick I used is that I created a template form that had lots of common stuff already included in the class module - like FormOpen, FormLoad, FormCurrent, FormUpdate, FormClosed event code, ... and the Help, Cancel, Commit, Create, and Remove buttons were already there. Then you just add what you need and remove what you don't need. The headers, logo, form title, help/hints area ... all predefined. You just copy it and start customizing.

This sounds like a lot of work for the simple problem of trying to secure a database without putting armed guards at each terminal - but like any other project, you get out of it what you put into it.

My philosophy is that I have made most of the functions of the database easy enough through my dispatcher form and pre-fab maintenance forms so that the temptation to go behind the scenes is reduced for the users. It should also be noted, though, that with an Access Front End and Back End combination, a malicious S.O.B. will still have the ability to hose you to tears. So assure that you have a good backup regimen in place and put in auditing controls so that if nothing else, you can make a public example of the S.O.B. who hosed you.

 

[ButtonMoon]

Creating your own "role-based security" by hiding data behind forms and other application-level code is a very, very dubious practice. It's really a form of "security theatre" - a way of deceiving legitimate users into thinking their data is subject to a level of security which in reality isn't there. By doing it you may make them trust the system with data that they wouldn't have otherwise trusted it for. The people with malicious intent are unlikely to be deceived and so you are giving them the advantage.

If your intention is to simplify users' workflow by presenting them with only the features relevant to their role then that may be a legitimate reason to implement such things. Don't pretend it has anything to do with security though - not unless it's your intention to deceive users. Put the data in a DBMS with proper role-based security features (e.g. SQL Server, Oracle or pretty much any other DBMS).

 

[Galaxiom]

I would like to reinforce ButtonMoon's point.

Access Backend = Insecure. End of story.