brasiman
asked on
Setup specific permissions in Microsoft Access
I have a Microsoft Access database that has multiple tables, queries, forms, reports, macros and modules. I am looking for a way to password protect each of those tables, queries, reports, etc individually. A question on top of that, is there a way I can use the windows credentials to password protect the files. For example, maybe i have a form/table setup with all the Windows usernames and passwords, in that same form i have a list of all the tables, queries, etc, where i just check a box whether or not they have access to that table, query, etc. I don't know, just a thought. Any ideas how i can accomplish password protecting each individual table, query, etc and setup user logins with different permissions to these areas? Thanks in advance!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The NavPain is a pain.
You can hide it on the second time an app opens.
You can then keep the user from opening it.
Access will fly it open for various reasons.
The only security for data therefore is a back end like SQL Server (Express is free)
Forms & Reports are fairly simple
Put all of this in a code module
You now have ReturnUserName to give you the user's Windows login name
You now have ReturnComputerName to give you the machine's name
In the Form_Open() or Report_Open() event, you build a nice Select Case True structure
Distribute a compiled (.mde or .accde) front-end and only folks/machines who meet the conditions so that the Select Case True evaluates true are going to open that object.
But the advent of the Ribbon REALLY put a crimp in developers ability to keep the users out of the underpinnings. If you really need to secure things, change platforms.
You can hide it on the second time an app opens.
You can then keep the user from opening it.
Access will fly it open for various reasons.
The only security for data therefore is a back end like SQL Server (Express is free)
Forms & Reports are fairly simple
Put all of this in a code module
Option Compare Database
Option Explicit
Private Type USER_INFO_2
usri2_name As Long
usri2_password As Long ' Null, only settable
usri2_password_age As Long
usri2_priv As Long
usri2_home_dir As Long
usri2_comment As Long
usri2_flags As Long
usri2_script_path As Long
usri2_auth_flags As Long
usri2_full_name As Long
usri2_usr_comment As Long
usri2_parms As Long
usri2_workstations As Long
usri2_last_logon As Long
usri2_last_logoff As Long
usri2_acct_expires As Long
usri2_max_storage As Long
usri2_units_per_week As Long
usri2_logon_hours As Long
usri2_bad_pw_count As Long
usri2_num_logons As Long
usri2_logon_server As Long
usri2_country_code As Long
usri2_code_page As Long
End Type
Public Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function GetComputerName Lib "kernel32" _
Alias "GetComputerNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function apiNetGetDCName _
Lib "netapi32.dll" Alias "NetGetDCName" _
(ByVal servername As Long, _
ByVal DomainName As Long, _
bufptr As Long) As Long
' function frees the memory that the NetApiBufferAllocate
' function allocates.
Private Declare Function apiNetAPIBufferFree _
Lib "netapi32.dll" Alias "NetApiBufferFree" _
(ByVal buffer As Long) _
As Long
' Retrieves the length of the specified wide string.
Private Declare Function apilstrlenW _
Lib "kernel32" Alias "lstrlenW" _
(ByVal lpString As Long) _
As Long
Private Declare Function apiNetUserGetInfo _
Lib "netapi32.dll" Alias "NetUserGetInfo" _
(servername As Any, _
username As Any, _
ByVal Level As Long, _
bufptr As Long) As Long
' moves memory either forward or backward, aligned or unaligned,
' in 4-byte blocks, followed by any remaining bytes
Private Declare Sub sapiCopyMem _
Lib "kernel32" Alias "RtlMoveMemory" _
(Destination As Any, _
Source As Any, _
ByVal Length As Long)
Private Declare Function apiGetUserName Lib _
"advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, _
nSize As Long) _
As Long
Private Const MAXCOMMENTSZ = 256
Private Const NERR_SUCCESS = 0
Private Const ERROR_MORE_DATA = 234&
Private Const MAX_CHUNK = 25
Private Const ERROR_SUCCESS = 0&
'******** Code Start ********
'This code was originally written by Dev Ashish.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Function fGetFullNameOfLoggedUser(Optional strUserName As String) As String
'
' Returns the full name for a given UserID
' NT/2000 only
' Omitting the strUserName argument will try and
' retrieve the full name for the currently logged on user
'
On Error GoTo ErrHandler
Dim pBuf As Long
Dim dwRec As Long
Dim pTmp As USER_INFO_2
Dim abytPDCName() As Byte
Dim abytUserName() As Byte
Dim lngRet As Long
Dim i As Long
' Unicode
abytPDCName = fGetDCName() & vbNullChar
If (Len(strUserName) = 0) Then strUserName = fGetUserName()
abytUserName = strUserName & vbNullChar
' Level 2
lngRet = apiNetUserGetInfo( _
abytPDCName(0), _
abytUserName(0), _
2, _
pBuf)
If (lngRet = ERROR_SUCCESS) Then
Call sapiCopyMem(pTmp, ByVal pBuf, Len(pTmp))
fGetFullNameOfLoggedUser = fStrFromPtrW(pTmp.usri2_full_name)
End If
Call apiNetAPIBufferFree(pBuf)
ExitHere:
Exit Function
ErrHandler:
fGetFullNameOfLoggedUser = vbNullString
Resume ExitHere
End Function
Private Function fGetUserName() As String
' Returns the network login name
Dim lngLen As Long, lngRet As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngRet = apiGetUserName(strUserName, lngLen)
If lngRet Then
fGetUserName = Left$(strUserName, lngLen - 1)
End If
End Function
Function fGetDCName() As String
Dim pTmp As Long
Dim lngRet As Long
Dim abytBuf() As Byte
lngRet = apiNetGetDCName(0, 0, pTmp)
If lngRet = NERR_SUCCESS Then
fGetDCName = fStrFromPtrW(pTmp)
End If
Call apiNetAPIBufferFree(pTmp)
End Function
Private Function fStrFromPtrW(pBuf As Long) As String
Dim lngLen As Long
Dim abytBuf() As Byte
' Get the length of the string at the memory location
lngLen = apilstrlenW(pBuf) * 2
' if it's not a ZLS
If lngLen Then
ReDim abytBuf(lngLen)
' then copy the memory contents
' into a temp buffer
Call sapiCopyMem( _
abytBuf(0), _
ByVal pBuf, _
lngLen)
' return the buffer
fStrFromPtrW = abytBuf
End If
End Function
Function ReturnUserName() As String
' returns the NT Domain User Name
Dim rString As String * 255, sLen As Long, tString As String
tString = ""
On Error Resume Next
sLen = GetUserName(rString, 255)
sLen = InStr(1, rString, Chr(0))
If sLen > 0 Then
tString = Left(rString, sLen - 1)
Else
tString = rString
End If
On Error GoTo 0
ReturnUserName = UCase(Trim(tString))
End Function
Function ReturnComputerName() As String
Dim rString As String * 255, sLen As Long, tString As String
tString = ""
On Error Resume Next
sLen = GetComputerName(rString, 255)
sLen = InStr(1, rString, Chr(0))
If sLen > 0 Then
tString = Left(rString, sLen - 1)
Else
tString = rString
End If
On Error GoTo 0
ReturnComputerName = UCase(Trim(tString))
End Function
You now have ReturnUserName to give you the user's Windows login name
You now have ReturnComputerName to give you the machine's name
In the Form_Open() or Report_Open() event, you build a nice Select Case True structure
Dim MyBoolean as Boolean
MyBoolean = false
Select case True
Case ReturnUserName = "Nicky" 'he gets to open this
MyBoolean = False
Case ReturnUserName = "Scotty" 'he gets to open this
MyBoolean = False
Case ReturnUserName = "Brian" 'he gets to open this
MyBoolean = False
Case ReturnUserName = "brasiman" 'he gets to open this
MyBoolean = False
Case Else ' not you!
MyBoolean = True
End select
Cancel = MyBoolean
Distribute a compiled (.mde or .accde) front-end and only folks/machines who meet the conditions so that the Select Case True evaluates true are going to open that object.
But the advent of the Ribbon REALLY put a crimp in developers ability to keep the users out of the underpinnings. If you really need to secure things, change platforms.
Read through ALL OF THIS
https://www.experts-exchange.com/questions/27231448/Access-2010-disable-or-modify-Quick-Access-Toolbar.html
Not much has changed in the years since A2007 was released.
https://www.experts-exchange.com/questions/27231448/Access-2010-disable-or-modify-Quick-Access-Toolbar.html
Not much has changed in the years since A2007 was released.
ASKER
Thanks everyone! Nick67, I will read through that whole link. I can create a form for usernames/passwords to the database, I just don't like how if you know what you are doing, you can still unhide the tables. I am hoping there is a way to only be able to do that with an admin password, or something like that.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nick I REALLY appreciate your input and suggestions! Unfortunately I need to stay with Access for now. But you have been great at setting my expectation with the security in Access. I am looking through your links now.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER