access security

[ariansman]

is there a way to securely block the access of user to tables/queries, else in Microsoft Access?
thank you

[ItsMe]

Yes. There are a few ways to go about it. The first thing I do is to develop the app so it does not depend on anything but forms and can run, exclusively, in Runtime. Ensure your app is designed as a Front End. Publish your app as an executable. Encrypt the Back End.

The other things would be... get rid of the Ribbon using DoCmd.ShowToolbar. Use settings within Access to hide Nav Pane, etc. Now you can disable the Shift Bypass using a custom function.
https://support.microsoft.com/en-us/kb/826765

[ariansman]

Yes. There are a few ways to go about it. The first thing I do is to develop the app so it does not depend on anything but forms and can run, exclusively, in Runtime. Ensure your app is designed as a Front End. Publish your app as an executable. Encrypt the Back End.

The other things would be... get rid of the Ribbon using DoCmd.ShowToolbar. Use settings within Access to hide Nav Pane, etc. Now you can disable the Shift Bypass using a custom function.
https://support.microsoft.com/en-us/kb/826765

Thank you Itsme,
about the first solution:
do you mean that i change the ms access file to an application? how can i do that? is there a software to do this for me?

[ariansman]

and for the second solution, i went through that, but the User can still go and see the tables through File/access options. so is not secure at all.

[CJ_London]

there are plenty of ways to secure it, but nothing is totally secure - a determined and sophisticated user will always find a way. Suggest you google 'secure access database' or similar to find a solution that meets your needs. Have you considered using SQL server or MySQL for example for the back end. Did you encrypt the back end - and kept the password secure? Have you designed your front end to not have linked tables?

[ariansman]

there are plenty of ways to secure it, but nothing is totally secure - a determined and sophisticated user will always find a way. Suggest you google 'secure access database' or similar to find a solution that meets your needs. Have you considered using SQL server or MySQL for example for the back end. Did you encrypt the back end - and kept the password secure? Have you designed your front end to not have linked tables?

it seemed to me that the user will need a password to be able to open the file if it is encrypted. so i didn't go through that. am i right about that? i have no idea what are " back end" or "front end". i need to google them i think.

well, I have already made my file in access and now i see how insecure is MS Access. is there a way that i change my file into another DB program, such as "oracle" or " file maker"? i dont know much DB programs. any suggestion is appreciated.
thank you

[Perceptus]

Make a database, split that database into a front end and back end. convert the front end to ACCDE. make the back end encrypted with password. Then remove the shift bypass. I dont see how this is insecure.

[ItsMe]

do you mean that i change the ms access file to an application?

Access has design tools to create applications. The most common tool is the Form editor. If your users see nothing but forms when they use your application, well, you have an application.

You can use Access to publish your app as an accde file. This will help to remove some access to some options within Access. In order for this to be affective, the application needs to be able to run during Runtime and not be dependent on Designtime. In other words, no dependency of user interaction with query, table, etc. objects and no execution of any code that makes design changes, like saving the design of a form.

the User can still go and see the tables through File/access options.

That is because you did not change settings in the Options. You can hide many features via options. I hide the Ribbon via VBA code.

Like you mentioned, the first step is probably wrapping your head around a split DB. Use the Front End file for your application side of things. Use the Back End file as the DB side of things.

[Rawb]

Keep in mind that, if you want to restrict users to only certain Tables, you cannot expect to do this reliably in Access. If the user has access to the backend file, they have access to EVERYTHING in that file. Any attempt to limit that access can be circumvented if the user has Access programming/design experience.

Also, if you decide to go with the encrypted backend option, every user will have to be given the same password, and this password will provide unlimited access to that backend file.

For the majority of cases, this level of security is enough. However, if you need "true" security, access restrictions, and user tracking, you will need to use a real RDBMS for the backend, such as MS SQL Server, MySQL, etc.

[CJ_London]

Also, if you decide to go with the encrypted backend option, every user will have to be given the same password, and this password will provide unlimited access to that backend file.

Not so. At the least secure level, your front end is linked to the backend, the password is required at the time of linking so the user does not need to know the password at the time of running. However if they know what they are doing they can find the password by looking in the mySysObjects table in the front end.

To get round that, the developer does not provide linked tables (or queries) but instead uses recordsets defined in code, which in turn is compiled to accde/accdr so the only way a user can get to the password is to reverse engineer the code - and that is a specialised skill which few people have. There are websites out there professing to be able to do this - for a price - but from what I hear, the reverse engineered code is difficult to interpret (different variable names, all annotation lost), although given enough time it can be clarified. The question the OP has to ask is, 'how much is the data worth?'

Keep in mind that, if you want to restrict users to only certain Tables, you cannot expect to do this reliably in Access

you can (to a fair extent) by having multiple back ends - I regularly do this for my customers, there are typically three backends, one with the customer data, one with 'system data' concerned with the operation of the application and a temporary backend for temporary tables (actually one for each user) to prevent bloat. Sometimes there is another for user specific data held locally, sometimes the customer data is split into more that one backend, either because of space limitations or because of different functional requirements.

[ItsMe]

I thought I would bump this thread. Since many people have strong opinions about the security of Access and what its capabilities are, I would like to offer a challenge. I believe nothing is impossible. But, it seems Access has a black eye, even though there have been advancements in its security. Perhaps I am wrong. But, I do not think it is as easy to crack as some believe it is. For those, I offer the following example.

To prove you cracked the sample provided, post the string contained within PK 5 of the table. In the FE, there is a form linked to the table that resides in the BE. The form will display records PK 1 through PK 4. PK 5 has the magic string.

In order for the linked BE to work, place the unzipped folder on your C drive.
C:\Secure_DB_Example

[CJ_London]

@Itsme: Looks like you do something similar to what I do! I've PM'ed you with the answer

[ItsMe]

Yup, that is the correct answer. Nice work.

[CJ_London]

Here is my version of securing a db from prying eyes.

Same instructions per ItsMe.

I know the backend can be broken by breaking the password - but with 128 bit encryption (from 2010), it is pretty safe

And the front end can be broken by reverse engineering the code, but takes time and/or money.

So the challenge is to find out what is in the 6th record - without using one of the above methods

[ItsMe]

Rawb Pm'd me with the correct answer, too.

Ajax, I was unaware there was a third option. I figured you would have to get the code out of the FE file to get anywhere. So I will not be able to get in.