Back End Security Questions

[Guest]

Access 2000

- The Back End database is secured using User Level Security
- The Front End is secured through the same User Level Security file as the
back end
- Security on the linked tables in the FE is quite open (everyone can
Read Design, Read Data, Update Data, Insert Data, Delete Data)
- Security on the BE actual tables is locked down to my various
groups, as appropriate
- Admin user has no rights to anything and instead I have a separate dbadmin
account which is the only account in the 'Admins' group and is also the
owner of both halves of the database
- The 'Users' group has no rights to anything and instead I have separate
groups for each department etc.
- I've made an MDE of my FE
- I've disabled the shift key on both my front and back end (but see below)
- All my VBA code is password protected

My BE only contains the data tables, A single form (that autostarts) with a
single button on it (that quits Access) and a module that contains Michael
Kaplans Dis/enable shift key code

So with the shift key disabled it is impossible for my more Access savvy
users to edit data in the BE (and so bypassing my lovely audit trail software)

- How can I prevent my users from accessing my BE while still leaving myself
the flexibility to make changes to it?
- Should I remove the Module contaning the dis/enable shift key code from
the back end? And If I remove the code how would I be able to enable the
shift key again?
- Should I make an MDE of my back end as well as my FE?


Thanks in advance for any help

Simon

 

[Joan Wild]

Access 2000

- I've disabled the shift key on both my front and back end (but see
below)

My BE only contains the data tables, A single form (that autostarts)
with a single button on it (that quits Access) and a module that
contains Michael Kaplans Dis/enable shift key code

You don't need a form, really. Just an autoexec macro that throws up a
message, followed by the Quit action.

- How can I prevent my users from accessing my BE while still leaving
myself the flexibility to make changes to it?
- Should I remove the Module contaning the dis/enable shift key code
from
the back end? And If I remove the code how would I be able to enable
the shift key again?

You can put that code in another mdb; and run it from there while logged in
as your dbadmin user. Just change the code to turn the shiftkey bypass back
on. Once you're done you'd run the code in the backend to turn it off.

- Should I make an MDE of my back end as well as my FE?

That would serve no purpose.

 

[John Welch]

What I do is put a checkbox on the autostart form in the back end (I do this
in the front end too) that allows a user to toggle the dis/enable shift key
property. When the form opens, this checkbox's visible property is set
depending on whether the current user is in the admins group, and the
checkbox's state is set depending on the current value of that property. The
checkbox's after update event changes the property, which takes effect the
next time the database is opened.
I use the very useful "IsUserInGroup" function, which I got from the
brilliant and generous TC:

Public Function IsUserInGroup(strGroup As String, strUser As String) As
Boolean
Dim s As String
On Error Resume Next
s = DBEngine(0).Users(strUser).Groups(strGroup).Name
IsUserInGroup = (Err.Number = 0)
End Function

HTH
-John

 

[TC]

I use the very useful "IsUserInGroup" function, which I got from the
brilliant and generous TC:

Can I have that in writing?

TC

 

[Guest]

You can put that code in another mdb; and run it from there while logged in
as your dbadmin user. Just change the code to turn the shiftkey bypass back
on. Once you're done you'd run the code in the backend to turn it off.

Thanks for that Joan but I don't quite follow you

1) How do you call the code from another mdb?
2) Does that mean that anyone who reads these forums can enable the shift
key on any protected db?

Simon

 

[Rick Brandt]

Thanks for that Joan but I don't quite follow you

1) How do you call the code from another mdb?
2) Does that mean that anyone who reads these forums can enable the
shift key on any protected db?

Simon

Yep. If it wasn't done with User Level Security applied as well. With ULS
applied you can disable the shift key with an additional argument that would
require anyone attempting to change the property back to be a user with
administrator permissions on the database. Without ULS anyone who knows how can
re-enable the shift key on any file.

 

[Guest]

Thanks for that Rick

But how do you call the code from another mdb?

 

[Joan Wild]

If I'm not mistaken, MichKa's function includes arguments (one being supply
the path to the mdb). If not, you'd change the code to reference the path
to the mdb, rather than CurrentDb(). Actually ,the function provided in the
FAQ does this.

http://support.microsoft.com/?id=207793

Function faq_DisableShiftKeyBypass(strDBName as String, fAllow as Boolean)

You'd put this in another mdb. You'd open that mdb using your secure mdw,
and logging in as a member of the Admins Group. That will ensure that only
other members of that Admins Group can switch it back.

 

[Guest]

Thanks for your help Joan, Rick & John

I now have a nice tight back end ;-)