MS Access Security User Levels 6

[assets]

I am trying to add security level to my database. I want customers to see only data that applies to them. All customers data is stored in the same area. My problem is I need to setup accounts that user need to login to. These account will need to have differewnt level of security. Any idea's would be appreciated. Thanks

 

[lameid]

JeremyNYC,

I'm not sure that having the passwords in the DB is an all bad thing... Long as Access security is used to lock down the application (developer and all other users).

While I agree that a table is difficult to appropriately secure, it is not a fundamentally flawed idea.

Case in point. Imagine that first thing this morning I copied the MDW file. Then about 9:30 you (John Q User) change your password... I say, "What was your old password?" After a quick exchange I convince you that once changed I can't use your old password. You then give me your password. Then I proceed to use my early copy of the MDW to Logon as You and go muck everything up with your credentials. Conversely, if only the developer has access to the MDW, and Administrator (default User) has permissions that are ok for everybody, then you don't have to worry about special shortcuts to open a MDW or have each user join the correct workgroup. Once the data in the table changes it is done globally. Unless of course someone copies the database, but even then they can't hose the live data. Hmmm... Finally a real reason to use UNC's in linked tables instead of mapped drives. The authentication table MUST be in the same file as the rest of the data or similar stealing of passwords would be possible. At anyrate, if you want something truly secure, give up on JET.

 

[assets]

Thanks Lameid,

I agree having passwords in the table is not a concern for security as it is only going to be used to filter records, it is not ment as high level security. That is why I am not using workgroups. If a section has 50 staff, they only need to see information on computers etc, and employees details for those's 50 staff and not the 800 other staff. The next stage once this works will be to have web page where staff can find special items eg. colour printer for publications (this may be in any division). but that is problem in the future. My concern is to get this working and I will try you suggestions.

Thanks.

 

[JeremyNYC]

Oh, I didn't realize you were planning on not using user-level security. I'll bow out of the conversation, as I thing that is a wrong decision for many, many reasons.


Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[lameid]

I still hold that if a password is stored in a table that is only accessible to administrator via a RWOP query in a database that administrator does not have administrator permission to the database object, has strartup properties set to not allow defualt menus, not allow special keys and has the the AllowBypasskey property set to false, there really is not a problem. Naturally the RWOP query is only runable via the authentication process and the change password function.

While pilfering the entire MDB and using an old password to get at a customer list is a little easier in this scenario, I think it is a sufficiently paranoid approach if you want to live with JET.

Give me one way to obtain a password from a properly secured table that is not possible with a password in a workgroup and I'll concede my point of view.

 

[scottian]

I think a good idea would be to create a login table that will store the following fields perhaps.

UserID
Password
Department

Now have a first form prompt the user to input a 'username' and 'password', on the click of another button open a second hidden form. The data for this second form is obtained from a select query pulling info from the login table with the criteria set to that which the user entered in the login form (an option here would be to have the database shut down if no records are retrieved)this data will include his or her department ID. This form now remains open for the duration that the user has the database open. It this department ID field which would be used as criteria in all queries, thus stopping the user from seeing anything but records which relate to them. This is also a good idea if a user were to move departments, as you just need to alter that particular users record. I use this myself, the best two peices of code ive found on this site have been the 'disable shift key' and the 'user login'
i still have them if you want them or i can send you an example db with them in.

 

[psemianonymous]

I've never heard of anyone using custom security on top of JET security. The only question is why? Your passwords certainly aren't more secure than if they were stored in the MDW.

I just don't see the benefit of doing it that way.


Pete

faq181-3893 - pros and cons of Access/JET security and its alternatives

 

[assets]

scottian,lameid

Thanks I have used a similar idea to yours. excep all information is stored in the employee table. The employee and assets main form are filted on the department ID/. This now works. There is only one minor problem on a form that looks at assets catagories (it is based on queries) that list all assets, but if you try to open one that is not your deptment ID it only opens your department records.

I am leaving this as the next stage is to have a web form on the intranet where people can choose eg colour printer and find a location not in there department where on is available for general use. This raises some more security issues as the batabase will need be available to all uses including remote access.

The use of code (the two peices of code 'disable shift key' and the 'user login) make it hard for user to get in to data. The issues raised by lameiud are valid. The only problem lameid I have had was somebody copying the data and changing records.

thanks

 

[danvlas]

ftp://ftp.artrom.ro/StartupOptions.zip

That allows controlling most usual startup options of a mdb file, including enabling/disabling Shift key in such a way that only Admins can modify it.

However, this is not really security. Any user that has the right to view data in the tables can link those tables to a new database.

Best way, as Jeremy said: read, understand and apply user-level security.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[scottian]

I have read a number of interesting threads on the subject of access security, it is true that the Access security is good. However to do this requires replacing the system.mdw file. This file is required by the application and usually resides on the C: drive with the rest of the system. The problem is when a database is stored on a server the system.mdw file needs to be stored in the same folder as the database, which is a potential risk as any user with access to the folder can delete the file and or replace it with one which they created. I have a feeling that this would render the database unusable or worse actually allow a user into your database with admin access. I have a feeling that 'assets' works in a similar environment to mine in that the database is shared with a number of users with access to the same server. I have found that the best security level is the security that you yourself create and only you know how it works. How deep you go depends on how secure you require your database to be, after all I believe its only a matter of time before some clever Access user writes some code that will be able to access the system.mdw file and reset all users with Admin access.

 

[danvlas]

scottian:
It is true that someone may delete the workgroup information file from the system. That's why backups are needed for everything.

It is not true that replacing the mdw file with a 'virgin' or a custom one will give other users Admin access to the database.
Security is stored in the database itself, not in the mdw file.

The mdw file has the only purpose to identify the user to the database.

The most frequent mistake when securing a database is that default user Admin is left as administrator.
The second: user Admin remains the owner of the objects created in the database. And the owner has the right to do anything, right?

Again: read Jeremy's posts in this and other threads. But very carefully.


HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html