Question Securing Access 2010 Database

[BlackOnBlack]

I have a database that I want to put on my firm's network. The database is proprietary to me and I want to secure it. I would like to make it such that 1) the database will only work from a set network location and cannot be copied onto someone's hard drive and 2) no one can modify or export the queries, edit the macros, change table design, etc. I don't care about security of the data, just the queries, marcos, etc. that process it. Suggestions? The database is used on a project basis, so when someone starts a new project, they need to copy the database. Because of this, I don't think splitting the database is pragmatic. Thanks in advance to those who reply!

 

[gemma-the-husky]

you can't easily protect the queries, or the table design. the rest you can do.
I don't think these are the end of the world personally. queries on their own aren't so much use.

what you want is a licensing system where you provide a license key to validate an install.

 

[BlackOnBlack]

This is really unfortunate that Access stripped its security protections. The queries and macros are what I'm looking to protect.

 

[RainLover]

If you have all of your Queries running under your SQL would help.

I am a bit lost for the correct terminology.

Convert you Queries to SQL and run them behind your Forms and Reports. This is not a complete fix but it does at least hide some of your code

 

[gemma-the-husky]

if you want to be fancy, you could store the sql for your queries in an encrypted manner, and get your app to unencrypt them on the fly.

you will lose some efficiency as your app won't be able to build query plans, but you may get the security you want.

Seriously, though, a user won't get very far seeing details of 100's of queries

 

[RainLover]

Have a look at this.

Public Sub SetDefaultDirectory()
    Dim vntDir As Variant
    
    If (conHandleErrors) Then On Error GoTo ErrorHandler
    
    vntDir = DLookup("BackupFolder", "tblConfiguration")
    
    If Len(vntDir) Then
        ' Something in table... check if valid on this machine.
        If Dir(vntDir, vbDirectory) = "" Then
            ' Not valid... set the default.
            vntDir = GetDefaultPathForTransfer()
        End If
    Else
        ' Nothing in table... set the default.
        vntDir = GetDefaultPathForTransfer()
    End If
    
    If Right(vntDir, 1) <> "\" Then vntDir = vntDir & "\"
    
    [B][U]CurrentDb.Execute " UPDATE tblConfiguration" & _
                      " SET BackupFolder = " & Chr(34) & vntDir & Chr(34), conDbFailOnError
    
[/U][/B]ExitProcedure:
    Exit Sub
 ErrorHandler:
    DisplayError "SetDefaultDirectory", conModuleName
    Resume ExitProcedure
    
End Sub

The Bold is SQL and is totally hidden from the user.

I must ask this question. How many people have you got there that can break into this code. If you have one or more why don't they help with the problem. Just curiosity on my part.

BTW Ignore the Error trapping. It requires more than what you are seeing here.

 

[RainLover]

if you want to be fancy, you could store the sql for your queries in an encrypted manner, and get your app to unencrypt them on the fly.

you will lose some efficiency as your app won't be able to build query plans, but you may get the security you want.

Seriously, though, a user won't get very far seeing details of 100's of queries

Dave

I think you are correct in your approach. However I doubt if this will slow things down. Some people use this approach as standard.

 

[Minty]

You could distribute an MDE version of it?
Not totally foolproof, but it locks everything down from memory?

(I could be way off here....)

 

[RainLover]

You can't import, export, modify, or rename any forms, reports, pages or modules in an MDE database.

You can do as you wish with tables and queries.

So use your SQL behind a form or report where it can't be touched by naughty people.

I am inclined to believe that the Original ULS which was last available in 2003 is not as good as the security you can write yourself.

Just remember that security is not strong in Access.

The best method is to use SQL Server if you have it available to you and that more than one person in your shop knows how to use it. It requires maintenance.

The last thing that I can suggest is MySQL. It is easy to use within reason and it is free.

The other thing is that MySQL is available to you free on 90% (Approx. figure) of all servers around the world. Therefore if you want to share over the net you are half way there.

 

[BlackOnBlack]

So far, the best solution that I came up with was to convert back to a mdb and restrict user options. I'm not thrilled with this approach. I'll read some of the other suggestions and see if I can come up with something that works for me.

 

[CJ_London]

There is no easy answer to securing your db. Code, forms and reports are easy in principle - convert to mde or accde but there are facilities out there to break the compilation if someone really wants to spend the money.

To protect queries you need to do as suggested by rainlover - or a variation is one of the things I do which is to store the query sql in a table in a separate encrypted db and have some code that opens the db, returns the appropriate query sql and then runs it.

To protect tables, again, put them in a separate encrypted db and link to them.

However anyone who is access savvy will know where to look to find the password and for that matter the location of the tables db so perhaps does not go far enough. Enter SQL server etc. Or...

You can bury the linking data in VBA code which if encrypted provides pretty good protection from your average hacker.

So, user opens the db and sees no tables or queries, only compiled forms, reports and modules. To break into the system they need to be able to decompile the code and dencrypt another db or two - are they going to consider it is worth the effort?

Another thing you can do is hide the access window, but this will only work if all your forms are popup and modal and again a user could just create a blank db and then copy all tables, forms etc across -but not if the db is encrypted/compiled, so back to this as a solution. As a full time developer I only hide the access window to enhance the user experience, not as a security device.

Going back to your original post - not splitting the db is likely to be fraught with problems and if anything makes your objective harder to achieve.

With regards a set network location, depending on the size of the organisation this can be difficult to achieve. Look at the environ data - so you could for example identify a user login and/or machine id and limit access to those on the 'approved' list. With regards networks, you could perhaps put an empty file (could be hidden) on the network in a suitable location and have your application try to find it - if it can't then the application closes - implication being the user is not on the network. This may not be appropriate if users are not always connected to the network.

You cannot prevent someone copying the db to their hard drive, but again, if you have front end/back end you can hide the back end pretty well - you can even change the file type in the name so it looks like a text file, pdf, zip file whatever. You can also put some code into the front end which determines where it is, and if it is on a C drive it just closes and can even delete itself.

 

[RainLover]

There are a Lot of Solutions here.

I don" t have one in my library.

Such is life.