How can password protect all data tabels

[deepaksharma]

My greetings to all of you.
I want to password protect all the data tables present in my database file located on the backend. Is this possible? if so, how?


When I Googled to find a solution to this problem, then I found that it is not possible and instead of password protecting the tables, we can protected the whole database file.

[Minty]

If your database is split, the the Backend would only have the tables in it, so I don't understand the problem of password protecting the whole thing?

[ArviLaanemets]

Use SQL Server database as backend;
For every BE DB is a separate database on SQL Server;
For every BE Database on SQL Server, separate user group is defined in your LAN (or several of such user groups, when e.g. some users can add/edit data, some can only read data, and some data may be accessible for specific users only). Additionally, there must be an user group (or several of them) for database administrator(s).

Only database administrators (and SQL Server adminstrators) have direct access to SQL Server. Regular users can access data only through Front-ends, and only when they are the member of user group for this database (and are limited by rights determined for this user group). When user is removed from user group, he/she can't access this DB anymore.

In FE's, users have access to data only through forms - direct access e.g. to tables must be unavailable.

[CJ_London]

I want to password protect all the data tables

Would help to know what you want to protect them from.
Table design changes or deletion? modifying data when not using the front end? Someone copying all the data and taking it away? Users seeing data they should not be able to see? etc

[Vstar920AZ♣]

You can lock all forms to AllowEdits = False, AllowAdditions = False, AllowDelete = False (in code or in the form properties)
I use the following code to detect the user. Then I lookup their preset user level in a table. All users under a certain level will have their edit access (like above) set to false.

Option Compare Database

Public PsUserNm As String  'These are public so the user kind of carries them around with them, like a badge, while they are in the DB as they enter any objects (forms, reports ...)
Public PbytUserLvl As Byte

Public Sub QuickUserNm()  'This sub is mainly for me in design mode as design mode often drops my public variable values - - - - - - -
 PsUserNm = Environ("UserName")
End Sub

Public Function PublicVarSID()
 Dim DBnm As String
 Dim rs As Recordset
 Dim sSQL As String
 Dim vbANS As VbMsgBoxResult
PsUserNm = StrConv(Environ$("username"), 2)
Debug.Print
'******ALTERNATE DBA USER HIJACK*********************

If PsUserNm = "MyEnviron UserName"  Then
    
    vbANS = MsgBox("Do you want to mimic a user session?", vbYesNo, "SESSION TYPE")
    
    If vbANS = vbYes Then
    
        Set rs = CurrentDb.OpenRecordset("SELECT PC_UserNm " & _
                                         "FROM AssignedRoles_t;")
        Do While rs.EOF = False
            sSQL = sSQL & vbCrLf & "-" & rs.Fields(0)
            rs.MoveNext
        Loop
            
        PsUserNm = InputBox("Enter User SID." & sSQL, "DEFINE SESSION")  'This is so I can mimic another users Environ$ to see what they might be seeing.
        sSQL = ""
    End If
End If
'******END CODE FOR ALTERNATE DBA USER HIJACK*********************


 'This is the user level lookup to my AssignedRoles_t.

 If DCount("[UserLevel]", "AssignedRoles_t", "[PC_UserNm]='" & PsUserNm & "'") <> 0 Then
    PbytUserLvl = DLookup("[UserLevel]", "AssignedRoles_t", "[PC_UserNm]='" & PsUserNm & "'")
 Else:  'Exit DB is user is not in AssignedRoles_t - - - -
    MsgBox "User Not Found.  See Administrator.", vbOKOnly, "CLOSING DATABASE:"
    Set rs = Nothing
    DoCmd.Quit 
    Exit Function
 End If
   
End Function

'------in any form or object, this or some variation to control objects visible or active  Generally in the OnOpen Action
 If PbytUserLvl = 5 Then
    Me.(some button or field).Visible = True
    Me.AllowEdits = True
    Me.AllowDeletion = True
    Me.AllowAdditions = True
'----by default these would be set to False in the form and turned only if User Level is > Level you choose
 End If
'-------
AssignedRoles field names  
Employee,
First_Name,
Middle_Initial,
Last_Name,
Supervisor,
PC_UserNm,
UserLevel

[isladogs]

If its really important to safeguard your data, you can also encrypt the data in the backend tables then view the decrypted data in Access forms. See Encrypted Split No Strings (isladogs.co.uk)
However, users should NEVER have direct access to the data tables anyway

[Vstar920AZ♣]

Another solution, if you truly want the tables to be read only when access in forms is here.
https://www.devhut.net/ms-access-make-tables-read-only/

[deepaksharma]

I apologize to all of you, I was actually out of my city so it took me some time to come back to this forum.

[deepaksharma]

If your database is split, the the Backend would only have the tables in it, so I don't understand the problem of password protecting the whole thing?

Thanks for replying minty,
Actually, I want that no other user should tamper with the data written in the table of the backend file, so I want to make it password protected.

[deepaksharma]

Use SQL Server database as backend;

Only database administrators (and SQL Server adminstrators) have direct access to SQL Server. Regular users can access data only through Front-ends, and only when they are the member of user group for this database (and are limited by rights determined for this user group). When user is removed from user group, he/she can't access this DB anymore.

Thank you Arvilaaements for replying,
Your method seems to work to a great extent but the problem is that I do not have complete knowledge of SQL Server. So when I Googled about it, I found its usage and installation process quite complicated. If you can provide me a detailed article written in simple language about this, it would be greatly appreciated.

[deepaksharma]

Would help to know what you want to protect them from.
Table design changes or deletion? modifying data when not using the front end? Someone copying all the data and taking it away? Users seeing data they should not be able to see? etc

hello cj london
I want that no user should be able to copy, view, or edit the data of the backend file without permission.
Thank you.

[deepaksharma]

I use the following code to detect the user. Then I lookup their preset user level in a table. All users under a certain level will have their edit access (like above) set to false.

hello vstar,
Thank you very much for giving me so much time and explaining in such detail. You probably want to put edit, delete and other types of restrictions on backend file access through the form whereas I want to restrict backend file access directly.
Thank you very much again.

[deepaksharma]

If its really important to safeguard your data, you can also encrypt the data in the backend tables then view the decrypted data in Access forms. See Encrypted Split No Strings (isladogs.co.uk)
However, users should NEVER have direct access to the data tables anyway

hello isladogs,
As per your advice, I encrypted the backend file with password and linked it to the frontend. This solved my problem but made the frontend program very slow. Please tell why this is happening and how can I come out of this problem.
Thank you.

[Welshgasman]

hello isladogs,
As per your advice, I encrypted the backend file with password and linked it to the frontend. This solved my problem but made the frontend program very slow. Please tell why this is happening and how can I come out of this problem.
Thank you.

Well I would expect it has to decrypt the data?

[isladogs]

Decrypting the data is almost instantaneous in my example database from post #6
Probably any noticeable slowdown is due to whatever you put in your code

You can also make tables read only using constraints - you can do a Google search for this but there is very little documentation available
However, as already stated the question is really irrelevant as your users should never be able to access tables or queries directly
All interaction should only be vis forms and these are easy to lock