Securing the backend

[DBSSP]

I'm trying to secure the backend of my database. I was wondering how I could code a function to pass the backend password from the frontend so that records can be written. Any ideas?

Jay
"If the words up and down were reversed, would you trip and fall or trip and fly?"

 

[oharab]

What sort of security are you using and how are you linking the front end to the back?
I have set up my latest system with user level security. The back end tables have the required permissions set (making sure the admin user has no permissions at all!) as do all the linked tables, forms, etc in the front end.

The net result is users can only access the files if they are using the correct mdw file and that will mean they only have access if they know the passwords.

It seems to work really well & is about as secure as you can get with Access, especially as I have 2 versions of the mdw file, a development & a production version which does not have an account with design rights!

hth

Ben


----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[thargrav]

If you are only concerned about is securing the contents of the back end database from prying eyes, then try this:


Setup a back end database password.
Re-link the front end to the back end, which will prompt you for the password.
Make the front end into a MDE file and only distribute the MDE to your users.

The result will be that the users will only be able to access the back end with the MDE (direct access will prompt a database password). And it's difficult to find the imbedded password in the MDE file.

Tom

 

[DBSSP]

I was looking to do that second option. Passing the DB Password to the back end. My database development has to remain on public drives, IT loves me so much, I'm sure. As for the Access User Config Wizard or Security Wizard, whatever it is, I can't use it here because there are too many changes that go on, and again IT would probably come down hard about it.

Jay
"If the words up and down were reversed, would you trip and fall or trip and fly?"

 

[thargrav]

In that case, you could forget about a password and make both the back & and front end MDE files. Making the back end a MDE will not prevent someone from connecting with another front end but it will keep users from casual viewing.

Tom

 

[DBSSP]

How would I go about passing the password to the backend via the frontend?

Jay
"If the words up and down were reversed, would you trip and fall or trip and fly?"

 

[Lenvdb]

Hi Jay
How did you pass the password in code from the front end to the back end?
No one replied to your last question...
I am now having to do the same.

Rgds
Len

 

[oharab]

What do you mean by pass the password? If your tables are linked to the back end, then the password is remembered.

B

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[Lenvdb]

Every time I distribute a new update of the front end, I run code to link to a Password Protected backend.
So far I have been running the code:
Dim dbPath as String
dbPath = "c:/somefolder/MyDB.mdb"
DoCmd.TransferDatabase acLink,"Microsoft Access",dbPath,acTable,"tblClients","tblClients"

This I also run for all the other tables. So far I have not had a Password Protected Backend. Now I do and I get the error message:"Not a valid Password" every time I relink the tables.
Any ideas on how I must do this?
Rgds
Len

 

[psemianonymous]

Try a variant of what was suggested above: instead of re-linking tables, delete the table links and recreate them to the "new" password-protected back-end. This will store the password in the table's connect string, so that you don't have to remember it (and so that your users won't look for it).



To see your table connect string, do the following in the immediate window (CTRL-G to get to immediate window):

?CurrentDb.Tabledefs("YOUR_TABLE_NAME_HERE&quot.Connect

 

[Lenvdb]

Guys
this whole linking/unlinking/relinking to a Password Protected backend seems like a lot of bulls...to me. I looked at all te codesamples. I looked at all the possible threads on this topic, and you people as a Access Dev community are also not too sure about this whole process.
Now seriously, how many of you out there actually do this?
Foolio, I cannot recreate table links without passing a password in code.

I first have to specify the CurrentDb, which is the one I am linking to in code, without passing the magic password I cannot specify the CurrentDB, which I need to get the TableDefs from.
Set sDB = DBEngine(0).OpenDatabase("MyDatabase.mdb",,,"MS Access;PWD=magicpassword;DATABASE=MyDatabase.mdb&quot
I cannot get past this...
Once I get that right, I can do the little bit of code above:
For i = 0 to sDB.TableDefs.count - 1
sDB.TableDefs(i).Connect = "magic connect string"
sDB.TableDefs(i).Refreshlink
Next

Rgds
Desperado
Len
What am I doing wrong here?

 

[psemianonymous]

OK, let me start over then. This is what you have described:

1. Database password on backend database (i'll call it "TABLES.MDB"
2. front-end linked via table links to the backend (I'll call it "FORMSRPTS.MDB"
3. Attempting to update table links to "take" new database password for the production backend


I have done this once before. I use Access built-in security, so database passwords are redundant in my case, and so I don't deal with them.

Now, for my polite answer:

1. You don't need to do an OpenDatabase unless you are running this relinking code from a separate database. I run my relinking code (sans database password) from inside the database. So which database are you attempting to open with the OpenDatabase method? The frontend or the backend? I assume the front-end, but then why are you passing a database password to open the front-end...it just doesn't make sense. Is your front-end password protected as well? Explain.

2. Be nicer.

Pete

 

[oharab]

I have a totaly secured database, front end and back end are secured using Access Userleve security.
The links to the backend are set when I roll out the database, but if the backend file does not exist, the file is due to move soon, then I have some code that relinks the tables from the front end to the new back end.
As soon as IT recover my files (they lost 2gb of my data this morning!) I will post the code here.

B

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[DaOtH]

LenvdB

Just some snippets of code, might be useful.

How to get the password from your already linked tables :

Function GetLinkedDbPassWord() As String
Dim tbl As TableDef
Dim str As String

Set dbCurrent = CurrentDb
Set tbl = dbCurrent.TableDefs("Table1&quot
str = tbl.Connect
GetLinkedDbPassWord = Mid(str, InStr(str, "PWD=&quot + Len("PWD=&quot, & _
InStr(str, ";DATABASE=&quot - (InStr(str, "PWD=&quot + Len("PWD=&quot))
End Function


And how to specify the password (e.g. when compacting the backend) :

<snip>
DBEngine.CompactDatabase &quot;C:\Original.mdb&quot;, &quot;C:\Compacted.mdb&quot;, , , &quot;;pwd=&quot; & GetLinkedDbPassWord()
<snip>

&quot;In three words I can sum up everything I've learned about life: it goes on.&quot;
- Robert Frost 1874-1963

 

[Lenvdb]

Hi Pete
My apologies...I did not mean to be snappy.
I have some user access login control over the frontend. I link to the backend mdb file through the Frontend by starting the mde file while holding the Shift key down.
However, some users still access the backend of the mde file by starting the mde file while holding the Shift key down. And messed up my data. I need to shut them out of the backend.

OK - so I found the correct code for the linking process and I link to the backend OK with code in the front end.

Anyone keen on a sample, I will let you have it...

But, users can still hold the shift key down and get into the tables in the front end. The goal is to keep them out of the tables, but allow them to work merrily on the forms I designed.

Seems like this is not going to be the case...

Rgds
Thanks for your quick response guys...
Len

 

[Lenvdb]

Please disregard the fist paragraph of the aboce confusing reply:
Revised it should read as follows:

Hi Pete
My apologies...I did not mean to be snappy.
I have some user access login control over the frontend. I link to the backend mdb file through the Frontend.
However, some users still access the backend of the mde file by starting the mde file while holding the Shift key down. And messed up my data. I need to shut them out of the backend.
.......
The rest is the same

Rgds
Len

 

[oharab]

What I had in another system was a similar problem.
What my code does now is delete all the linked tables when the file shuts down, then when the main form repopens it reads through a table of tablenames and uses a stored value from another table telling where the back end is to recreate the linked tables.
As soon as I get my files back I will modify the code to include passwords and post it here.

B

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

&quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;
NoFX-The Decline
----------------------------------------------

 

[Lenvdb]

Here goes :
Sample code of linking to a secured back end:
gsDBPath is a global variable holding the mdb file path.


Public Sub LinkTablesNew()
Dim sBackEndDB As Database
Dim ws As Workspace
Dim i As Integer
Dim iTblCount As Integer
Dim sTable As String
On Error GoTo EH

TestTablecount iTblCount ' a method to check if I 'havent already linked the tables=======

If Not iTblCount > 7 Then
DoCmd.Hourglass True
Forms![frmLogin]!lblLinking.Visible = True
Forms![frmLogin]!lblLinking.Caption = &quot;Linking tables...&quot;
Forms![frmLogin].Refresh
DoEvents

Set sBackEndDB = DBEngine(0).OpenDatabase(gsDBPath, False, False, &quot;MS Access;PWD=nogo&quot
With sBackEndDB
For i = 0 To .TableDefs.Count - 1
sTable = .TableDefs(i).Name
If Left$(sTable, 4) <> &quot;MSys&quot; Then 'Don't link the System Tables
DoCmd.TransferDatabase acLink, &quot;Microsoft Access&quot;, gsDBPath, acTable, sTable, sTable
End If

Next
End With
Set sBackEndDB = Nothing
DoCmd.Hourglass False
End If
Exit Sub
EH:
MsgBox Err.Number & &quot; - &quot; & Err.Description, vbExclamation, &quot;Linking Tables Error&quot;
Resume Next

End Sub

 

[DaOtH]

LenvdB

If they are using the Shift-key trick, switching off the shift key might help you.
(dbCurrent.Properties(&quot;AllowBypassKey&quot = False)

You can then create some kind of password protected button in your .mde which sets the property to True for you to allow access for whatever you need to do in there.

Obviously users would still be able to set this property to true using another database and linking to this one.


&quot;In three words I can sum up everything I've learned about life: it goes on.&quot;
- Robert Frost 1874-1963