access security

[ItsMe]

@Ajax



With some tips from you I was able to get into my own DB. I think what you have done to yours, that is different, is not to use linked tables. If an ODBC connection is created and connection strings are created via VBA, it will be more difficult to get in. If you are creating connection strings in VBA, an added level of security can be added by using a custom function to scramble and unscramble your connection string. It is not encryption but it makes it difficult to find the connection string.

There may be another way to thwart the technique used to get into the DB I posted. There may be a way that is intrinsic to Access and could reduce development time.

[ariansman]

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.

1- What is the good of this if a user with a basic knowledge can go to “files/options/ current database” and make the navigation pane and ribbon visible again. They can still go to tables and change data.
2- If the ribbon is hidden through a VBA code, will “files/options/ current database” get disabled? I mean I want the “option” to be out of reach of the user. I mean user should only be restricted to work through forms and buttons.

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.

1- I did split it. The user opens the front end. But user can still make the tables visible in the navigation pane and ribbon and change data tables. It doesn’t matter if the user is working in the main access file or FE.
2- I attempted to open the backend file. But it didn’t open. So I changed the extension in order to open it and encrypt it. After encryption, I returned the BE extension. But the front end is not working and asks for a password. I am totally confused

[ItsMe]

1- What is the good of this if a user with a basic knowledge can go to “files/options/ current database” and make the navigation pane and ribbon visible again. They can still go to tables and change data.

If you hide the Ribbon using VBA, you will not be bale to go to Options.

DoCmd.ShowToolbar "ribbon", acToolbarNo
DoCmd.ShowToolbar "ribbon", acToolbarYes

2- If the ribbon is hidden through a VBA code, will “files/options/ current database” get disabled? I mean I want the “option” to be out of reach of the user. I mean user should only be restricted to work through forms and buttons.

Yes, the user will only see the forms. You can create custom Ribbons and leave the Ribbon visible but, I do not believe you will be able to hide the Backstage (File Tab) in a custom Ribbon (I could be wrong).

1- I did split it. The user opens the front end. But user can still make the tables visible in the navigation pane and ribbon and change data tables. It doesn’t matter if the user is working in the main access file or FE.

If you encrypt the backend (Make A Copy First), you can then link your FE file. If you link prior to encrypting the BE, it will not work. Also, as mentioned in this thread, someone with a real desire to get access to the Data Tables can.

2- I attempted to open the backend file. But it didn’t open. So I changed the extension in order to open it and encrypt it. After encryption, I returned the BE extension. But the front end is not working and asks for a password. I am totally confused

I do not understand why you would change the file extension of your BE file. It should remain accdb. In your FE file, delete the linked tables. Then, start the Import wizard to Link the tables again. The Wizard will ask for the pass one time.

[ariansman]

thank you Itsme
in my access file there is a form which is loaded as the main file is opened. i put your code in the the onload of this form. did i do a right job? can we instead put the code somewhere not dependent on a form? i mean in the VBA of the main access file. does such a thing exist?
i am thinking to disable the shift bybass and also F11. so i am thinking how can i myself get to the database to edit it or make new forms and reprts it in future!!


And,
i could not open the BE because it says it needs a digital signature, that i don't know what it is!
it seems to me i should make a manual BE, encrypt it and then link it to the manually made FE. does this make sense to you?

[ItsMe]

in my access file there is a form which is loaded as the main file is opened. i put your code in the the onload of this form. did i do a right job?

That should do just fine. I suppose you could create a function and place it in a Standard Module. Then, call the function from an AutoExec macro. A macro with the name Auto Exec will fire before anything else. However, this seems like a lot of work for ????

so i am thinking how can i myself get to the database to edit it or make new forms and reprts it in future!!

You don't. That is why you create folders to Archive, Work On, and isolate master copies. I will even have a separate folder to do the final lockdown steps and accde conversion. Copy your Master FE file and distribute copies.

i could not open the BE because it says it needs a digital signature

Not understanding why. I did not even think digital certs were still being used. It sounds like a security warning and you simply click OK to open. If not, there may be some Active Directory stuff going on with your network. You can adjust Microsoft Office Trusted Locations via the Machine's Registry Settings.

it seems to me i should make a manual BE, encrypt it and then link it to the manually made FE. does this make sense to you?

That sounds like the way. Identify the directories where your BE and FE will reside. Split the DB. Encrypt the BE. Delete any previously linked tables from the FE. Use the Wizard to link to the BE. Use UNC to link tables by navigating through the server "Entire Network".

[ariansman]

I suppose you could create a function and place it in a Standard Module .

can you please help me how can I write that function? Shall I copy and paste that DoCmd code in the module? Where should I past that?

And
I made the BE manually: I made a copy of the same file and named it BE. I deleted everything except tables. In the FE I deleted tables. Then I encrypted the BE and lastly made connection to tables in BE through external data. Is this way fine? The FE is supposed to be smaller but both FE and BE have the same size. Isn’t this a problem? Secondly if I have many many forms and tables, this will be a huge job.

And lastly, do you think if it is wise and feasible to keep BE in another type of database such as SQL? Will the FE still work?

[ItsMe]

can you please help me how can I write that function?

Here is a DB file that contains one form and no VBA in the On Load event. There is a standard module with the VBA to Hide the Ribbon and show the Ribbon. You can call the either function from the immediate window to test it. There is an Autoexec macro. The Macro calls the function during startup and hides the ribbon.

SampleAutoExec.zip

I made the BE manually:

That sounds fine. You can use the wizard to import objects like forms, tables, etc. Also, there is a wizard to Split the data base. I believe it is under Database Tools (never used it). If you use the wizard to split, you will have to delete the linked tables and then Encrypt. At least, I would encrypt after the split.

do you think if it is wise and feasible to keep BE in another type of database such as SQL? Will the FE still work?

You can. It is whatever you are comfortable with during development and maintenance. The FE design will have to be based on the type of BE used.

[ariansman]

Thank you very much ItsMe. you were really helpful ,