So...No ULS in 2007...

[Montez659]

What to do? I am not in the IT dept for my company, so I don't have the option of having a front and back end to the db. I work within the department and don't have Admin priveleges on any computer. I am working with a db built using 2007 and can't roll back.

But we are in need of some ULS. I can easily create a tblUsers and set priveleges that way, but it is a matter of someone literally checking the option to "Show Hidden Items" and they can find the table of names and passwords. Is there any better way to store or hide the tblUsers table?

I am a temp here and I can't be IT troubleshooting for them if they decide to move files around, so no outside files. Any suggestions? Thanks in advance for the help!

 

[SydneyGeek]

Why can't you split the database, and put the backend onto a share?
Place it in a directory restricted to your department, put a password on the backend.
Delete all the links from the front end, relink using Data > External data > Access and choosing the Link Tables option. You will be prompted for the password.
Once linked, you can use startup forms and a password system on the front end.
Create a custom navigation group, make it the default, and hide the navigation pane. I think you can lock it too.
The users table will now be in the backend. If you have prevented direct access to the tables via the front end, and password protected the backend, you have reasonable protection.

But...
MS removed ULS because Access isn't really all that secure. You get much better security by linking the Access front end to SQL Server Express (the free version, it doesn't need to be installed on a server) or to the full version of SQL Server.
Not sure how far you'll get with IT but I think those are your options.

Denis

 

[HiTechCoach]

In additional to the excellent advice you have already received:

... so I don't have the option of having a front and back end to the db. ...

You really don't have a choice of not splitting it if this is a multi-user database application.

If you IT people need to see it from Microsft that they recomnd splitting ehn have them look at this:

Ways to share an Access database

Reliability and availability can become issues if there are multiple simultaneous users changing data. All database objects are shared.

Why would any IT Dept the practice methods that are not know to have reliability issue by saying you can;t split the database?

I am sure the Owner/CEO would think different.

 

[Montez659]

I appreciate all of your suggestions, and I understand their feasibility and purpose. However, as I stated before, I am a temp and this is a low priority on the IT dept scale. Chances are that it would not even be able to be implemented before I am gone.

I understand that this is the best and most secure way of doing it but I am not looking for something ultra secure. What I am needing is a low-level deterent at best for people with rudimentary knowledge of Access. I was just hoping that there was something a little better than storing user names and passwords in a hidden table. Seems like at this time that is the only way to go.

Thank you for all of the help though! I will definitely rely on this help for future projects.

 

[HiTechCoach]

I appreciate all of your suggestions, and I understand their feasibility and purpose. However, as I stated before, I am a temp and this is a low priority on the IT dept scale. Chances are that it would not even be able to be implemented before I am gone.

I understand that this is the best and most secure way of doing it but I am not looking for something ultra secure. What I am needing is a low-level deterent at best for people with rudimentary knowledge of Access. I was just hoping that there was something a little better than storing user names and passwords in a hidden table. Seems like at this time that is the only way to go.

Thank you for all of the help though! I will definitely rely on this help for future projects.

The critical thing splitting the database does is reduce the chances for data corruption.

The way I like to handle security if you are on a Domain is to interface with Active Directory. This way I can authinticate the user from their windows logon. No need to store any passwords.

For networks that do not have a Domain/Active Directory I store the user's password using data encryption.

 

[dogdays]

I had the same problem, elected to encrypt the password before storing it in the table. Then if a user checks hidden objects they will see nothing that they can understand.

' decrypt a string (usually a password)
Public Function utfnDecryptString(ByVal UserString As String) As String
    On Error GoTo utfnDecryptString_Err

    Dim i As Integer
    
    For i = 1 To Len(UserString)
        If Asc(Mid$(UserString, i, 1)) - 77 >= 0 Then
            Mid$(UserString, i, 1) = Chr$(Asc(Mid$(UserString, i, 1)) - 77)
        Else
            Mid$(UserString, i, 1) = Chr$((Asc(Mid$(UserString, i, 1)) + 255) - 77)
        End If
    Next i
    
    'Send back the decrypted password
    utfnDecryptString = UserString

utfnDecryptString_Exit:
    On Error Resume Next
    Exit Function

utfnDecryptString_Err:
'    Call ut_Bug(err.Description, err.Number, Erl(), Application.CurrentObjectName, "DecryptString")
    Resume utfnDecryptString_Exit
End Function

' encrypt a string (usually a password)
Public Function utfnEncryptString(ByVal UserString As String) As String
    On Error GoTo utfnEncryptString_Err
    
    Dim i As Integer
    
    For i = 1 To Len(UserString)
        If Asc(Mid$(UserString, i, 1)) + 77 > 255 Then
            Mid$(UserString, i, 1) = Chr$((Asc(Mid$(UserString, i, 1)) + 77) - 255)
        Else
            Mid$(UserString, i, 1) = Chr$(Asc(Mid$(UserString, i, 1)) + 77)
        End If
    Next i
    
    'Send back the encrypted password to check against
    'what is entered in the database
    utfnEncryptString = UserString

utfnEncryptString_Exit:
    On Error Resume Next
    Exit Function

utfnEncryptString_Err:
'    Call ut_Bug(err.Description, err.Number, Erl(), Application.CurrentObjectName, "EncryptString")
    Resume utfnEncryptString_Exit
End Function

Jack

 

[Montez659]

Ok, So I have been reading up and playing around a bit with splitting the database and I can see how it is much more effective. But I have one question - what can be done to keep a user that is using the front-end from seeing certain info in the back end? For instance, I have a password field and the info for that is currently stored in tblUsers. A user can still click on "Show hidden objects" and see that this table (as well as another table that my supervisor doesn't want people to get access to) is there and can still click on it and open it.

Am I not setting something properly or am I missing something? There are a ton of resources out there, I know, but I am having a hard time de-coding them to get what I am needing.

 

[SydneyGeek]

Encrypt the passwords (use the function dogdays provided) as a start.

You can create a custom navigation group, containing only the items you want your users to see; follow this link.

You can also have a menu form that displays different options depending on user permissions. I wrote a tutorial several years ago; see here.

There are other options but this should get you started.

Denis

 

[HiTechCoach]

Ok, So I have been reading up and playing around a bit with splitting the database and I can see how it is much more effective. But I have one question - what can be done to keep a user that is using the front-end from seeing certain info in the back end? For instance, I have a password field and the info for that is currently stored in tblUsers. A user can still click on "Show hidden objects" and see that this table (as well as another table that my supervisor doesn't want people to get access to) is there and can still click on it and open it.

One of the advantages to a split database is that you can use a table in a back end that is not a linked table. You use a recordset or query that reads from the back end. This way it is not visible to the anyone sniffing around in the front end.

TIP (See code at end of post):
There are ways to hide a table that make then total not visible in the navigation pane (Database Container is prior versions). You can use VBA code to hide the table and also unhide the table.

Am I not setting something properly or am I missing something? There are a ton of resources out there, I know, but I am having a hard time de-coding them to get what I am needing.

[/CODE]After 15+ years working with Access and trying to make it sure this is what I have learned from experience that securing an Access data is impossible but you can get close.

I have found that a single file (unsplit) databases are least secure. This is just one of the reason I start every database split.

They key to security is putting up as many roadblocks as possible to discourage anyone from cracking your database.

Public Sub HideTable(strTablename As String, Optional db As DAO.Database)
'Hide a table from the database window
'Note: This is different than the Access Object 'hidden'
'attribute that can be set with SetHiddenAttribute
    
    Dim lngAttributes As Long
    
    'Point to a current database if one is not passed
    If db Is Nothing Then
        Set db = CurrentDb
    End If
    
    'Modify the Attribute of the TableDef
    With db
        With .TableDefs(strTablename)
            
            'Take a snapshot of the current bit fields
            lngAttributes = .Attributes
            
            'Now UNSET the ReadOnly bits in the SnapShot
            'so we don't try to write to them.
            lngAttributes = lngAttributes Or dbAttachedODBC
            lngAttributes = lngAttributes Or dbAttachedTable
            lngAttributes = lngAttributes - (dbAttachedODBC + dbAttachedTable)
            
            'Now set the hidden bit, while maintaining the other
            'writable bits
            .Attributes = lngAttributes Or dbHiddenObject
            
        End With
    End With
    
    'Reflect the change in the database window/nav pane
    RefreshDatabaseWindow
    
End Sub
    
        
Public Sub UnHideTable(strTablename As String, Optional db As DAO.Database)
'Un hide a table that was hidden with "HideTable"
'Note: This is different than the Access Object 'hidden'
'attribute that can be set with SetHiddenAttribute
    
    Dim lngAttributes As Long
    
    'Point to a current database if one is not passed
    If db Is Nothing Then
        Set db = CurrentDb
    End If
    
    'Set the TableDef attribute
    With db
        With .TableDefs(strTablename)
            If dbHiddenObject = (.Attributes And dbHiddenObject) Then
                
                'Take a snap shot of the current bit fields
                lngAttributes = .Attributes
                
                'Now UNSET the ReadOnly bits in the SnapShot, so we
                'don't try to write to them
                lngAttributes = lngAttributes Or dbAttachedODBC
                lngAttributes = lngAttributes Or dbAttachedTable
                lngAttributes = lngAttributes - (dbAttachedODBC + dbAttachedTable)
                
                'Now set the UN-SET the hidden bit, while maintaining the other
                'writable bits
                .Attributes = lngAttributes - dbHiddenObject
            End If
        End With
    End With
    
    'Reflect the change in the database window/nav pane
    RefreshDatabaseWindow
    
End Sub

 

[Montez659]

Thanks for all of the help guys. I appreciate your time, even though I am probably rehashing an old topic with quite a bit of info on it. Sometimes all that info gets overwhelming. You have given great pointers and I have a good idea of what I need to do. Thanks again!