Question Database Security / User Permissions?

[Gardinia]

I’m wondering what the best way to go about restricting access to certain forms / tables / reports in Access 2013?

I have some Databases that were created back in Access 97 and have managed to upgrade these databases to work in Access 2013, fixing any modules and forms which broke when upgrading the databases.

User access and permissions within the database were controlled by User Level Security (the Workgroup.mdw file) which is not secure anyway I guess, as someone could easily copy the entire database.

The database as it stands now works perfectly in Access 2013, however anyone can do anything with the database which is my main issue. I’m wanting to limit access for certain forms / tables / reports to certain users, then out right deny other users from accessing the database.

I’ve seen the SQL Server 2014 Migration Assistant for Access, which looks to store the tables on SQL server then map these to the Access database, so i guess i could deny certain users from accessing tables that way, or only allow them to read the tables. However i'm not sure how that would help me lock them out of certain forms / reports and queries in the databases?

Anything from here on will be pretty new to me, so any advice at all would be most welcome.

Thanks in advance!

 

[Christos99]

Hi, You should be looking at a front end / back end split at least if you stay with Access for the data as opposed to SQL Server. The front end should be released to the users as an executable (accde) which will prevent them changing the design. There are other ways to further protect the accde by disabling the shift key etc.

To prevent users 'using' specific forms etc. you could implement access depending on users WindowsId. Requires some programming effort though.

SQL Server is preferable to secure the data. Depends on your resources and how much effort you want to make......

 

[burrina]

Security preferences should be enabled when you Log Into the db. Example:

If Me.frmName = "frmSomeForm" Then 'On Form Security Clearance
Call MsgBox("Access Denied, You do Not Have Security Level Clearance to view this form.", vbExclamation, Application.Name)
End If

If Me.txtstatus = 1 Then 'Some Command Button on Main Menu
Call MsgBox("Sorry, you do not have access to this form, Please Contact your System Administrator for details.", vbExclamation, Application.Name)
Exit Sub
End If

Me.txtUserID = Forms![frmLogin]![txtUserID] 'Main Menu
Me.txtstatus = DLookup("[fAccess]", "tblfrmList", "[userID]='" & Me.txtUserID.Value & "'")

tblfrmList
frmListID PK
EmpID FK
userID Text User Name
frmName Text Name of form user does not have access to
fAccess Number User has access to this form? -1 is True, 1 is False

HTH