Securing a Database

Susan Bricker

Greetings. I am looking for some advice on making a database secure.
By secure, I mean that I want only certain people to have write access
to the database and I want the updates to be permitted while other
read-only users may be browsing and generating reports.

I am working with MS/ACCESS 2000 and would like to split the database
into a front-end and back-end and then put the back-end onto a shared
drive that all of my group has access to. My goal is that all of my
team members (colleagues) will have a shortcut icon on their desktop and
some of them will have more 'permissions' than others. Some will have
read-only access and others will have read/update/delete access.

I have been reading some information trying to self-educate but really
would like some guidance before I really screw this up.

1. Should I split the database first?
2. Shoud I set the ADMIN password next?
3. Should I use the Security Wizard? If so, any pointers? I have
created some test databases trying to do test runs and have failed
miserably.

Thanks, in advance, for all your help.
Sue

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #1

Jim Allensworth

Study this white paper/FAQ...
http://support.microsoft.com/default...ent/secfaq.asp

- Jim

On 24 May 2004 19:06:32 GMT, Susan Bricker <sb****@att.net> wrote:

Greetings. I am looking for some advice on making a database secure.
By secure, I mean that I want only certain people to have write access
to the database and I want the updates to be permitted while other
read-only users may be browsing and generating reports.

I am working with MS/ACCESS 2000 and would like to split the database
into a front-end and back-end and then put the back-end onto a shared
drive that all of my group has access to. My goal is that all of my
team members (colleagues) will have a shortcut icon on their desktop and
some of them will have more 'permissions' than others. Some will have
read-only access and others will have read/update/delete access.

I have been reading some information trying to self-educate but really
would like some guidance before I really screw this up.

1. Should I split the database first?
2. Shoud I set the ADMIN password next?
3. Should I use the Security Wizard? If so, any pointers? I have
created some test databases trying to do test runs and have failed
miserably.

Thanks, in advance, for all your help.
Sue

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #2

Susan Bricker

Thank you for the information. I have been studying ... and taking test
runs with test versions of my database. I still have some questions,
though.

My application has forms that have separate command buttons to browse
(view), edit, add, save a record. I would like to disable the
appropriate buttons when a user with read-only access logon into the
application. I have been searching the online help and the FAQ list and
have not been successful in figuring out, programmatically, how to
determine ...

1. Who is logged on? (user name)
2. What group is the user in? (group name) which will imply the
permissions assigned to the user.

How should I go about this task? I was thinking something like putting
a call to a new function in the initial Switchboard open event function.
The new function would find out who the user is and the group name and
then set a global variable (I, already, have a global Collection of
variables defined and could another variable to the collection). Then
whenever a new form opens I could inspect the global variable and
disable/enable the various buttons at will.

What do you think?

Thanks.
Sue


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #3

Susan Bricker

Thank you for the information. I have been studying ... and taking test
runs with test versions of my database. I still have some questions,
though.

My application has forms that have separate command buttons to browse
(view), edit, add, save a record. I would like to disable the
appropriate buttons when a user with read-only access logon into the
application. I have been searching the online help and the FAQ list and
have not been successful in figuring out, programmatically, how to
determine ...

1. Who is logged on? (user name)
2. What group is the user in? (group name) which will imply the
permissions assigned to the user.

How should I go about this task? I was thinking something like putting
a call to a new function in the initial Switchboard open event function.
The new function would find out who the user is and the group name and
then set a global variable (I, already, have a global Collection of
variables defined and could another variable to the collection). Then
whenever a new form opens I could inspect the global variable and
disable/enable the various buttons at will.

What do you think?

Thanks.
Sue


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #4

hamking

Just finished securing my own database after many failed attempts.
Follow the steps as described here. Worked like a charm.
http://support.microsoft.com/default...NoWebContent=1

"Jim Allensworth" <ji****@Notdatacentricsolutions.com> wrote in message news:<40*****************@news.west.earthlink.net> ...

Study this white paper/FAQ...
http://support.microsoft.com/default...ent/secfaq.asp

- Jim

On 24 May 2004 19:06:32 GMT, Susan Bricker <sb****@att.net> wrote:

Greetings. I am looking for some advice on making a database secure.
By secure, I mean that I want only certain people to have write access
to the database and I want the updates to be permitted while other
read-only users may be browsing and generating reports.

I am working with MS/ACCESS 2000 and would like to split the database
into a front-end and back-end and then put the back-end onto a shared
drive that all of my group has access to. My goal is that all of my
team members (colleagues) will have a shortcut icon on their desktop and
some of them will have more 'permissions' than others. Some will have
read-only access and others will have read/update/delete access.

I have been reading some information trying to self-educate but really
would like some guidance before I really screw this up.

1. Should I split the database first?
2. Shoud I set the ADMIN password next?
3. Should I use the Security Wizard? If so, any pointers? I have
created some test databases trying to do test runs and have failed
miserably.

Thanks, in advance, for all your help.
Sue

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #5

hamking

Just finished securing my own database after many failed attempts.
Follow the steps as described here. Worked like a charm.
http://support.microsoft.com/default...NoWebContent=1

"Jim Allensworth" <ji****@Notdatacentricsolutions.com> wrote in message news:<40*****************@news.west.earthlink.net> ...

Study this white paper/FAQ...
http://support.microsoft.com/default...ent/secfaq.asp

- Jim

On 24 May 2004 19:06:32 GMT, Susan Bricker <sb****@att.net> wrote:

Greetings. I am looking for some advice on making a database secure.
By secure, I mean that I want only certain people to have write access
to the database and I want the updates to be permitted while other
read-only users may be browsing and generating reports.

I am working with MS/ACCESS 2000 and would like to split the database
into a front-end and back-end and then put the back-end onto a shared
drive that all of my group has access to. My goal is that all of my
team members (colleagues) will have a shortcut icon on their desktop and
some of them will have more 'permissions' than others. Some will have
read-only access and others will have read/update/delete access.

I have been reading some information trying to self-educate but really
would like some guidance before I really screw this up.

1. Should I split the database first?
2. Shoud I set the ADMIN password next?
3. Should I use the Security Wizard? If so, any pointers? I have
created some test databases trying to do test runs and have failed
miserably.

Thanks, in advance, for all your help.
Sue

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #6

Susan Bricker

Very good step by step instructions. I ran into one problem, though.
Step 13 .. "import all the objects from your original database into the
new database". I thought that I did that, but the VBA code behind the
forms and reports did not get imported. Good thing I have a few copies
of this Application. Any ideas what I did wrong?

Sue

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #7

Bradley

Susan Bricker wrote:

Thank you for the information. I have been studying ... and taking
test runs with test versions of my database. I still have some
questions, though.

My application has forms that have separate command buttons to browse
(view), edit, add, save a record. I would like to disable the
appropriate buttons when a user with read-only access logon into the
application. I have been searching the online help and the FAQ list
and have not been successful in figuring out, programmatically, how to
determine ...

1. Who is logged on? (user name)
CurrentUser()
2. What group is the user in? (group name) which will imply the
permissions assigned to the user.
You have to loop through the group collection (a person may belong to
more than one group)

Dim ws As Workspace, usr As User, i As Integer
Set ws = DBEngine.Workspaces(0)
Set usr = ws.Users(CurrentUser) 'current username
For i = 0 To usr.Groups.Count - 1 'loop through user's groups
myGroup = usr.Groups(i).Name
If myGroup = "Admins"
' Code to run if in Admin group
End If
Next i
How should I go about this task? I was thinking something like
putting a call to a new function in the initial Switchboard open
event function. The new function would find out who the user is and
the group name and then set a global variable (I, already, have a
global Collection of variables defined and could another variable to
the collection). Then whenever a new form opens I could inspect the
global variable and disable/enable the various buttons at will.

What do you think?

Thanks.
Sue

--
regards,

Bradley

Nov 13 '05 #8

Susan Bricker

Bradley,
Thank you for your help. If I can get past the 'securing and setting of
permissions' then I will definitely use your suggested source code.

But I have MAJOR PROBLEMS with this entire process.
I am following the steps outlined in one of the links mentioned on a
previous reply. Of course, I think that I am doing everything
correctly. But ...

Here's what I have ...

Original application: relutil.mdb
Copy of application: TESTrelutil.mdb

After I have created the new user (for me) in the Admins group and I
have moved the Admin user to the Users group, I open a new db and name
it TTrelutil.mdb. Then I create another group called RelCoords and
assign all the necessary permissions. I then add 1 user to RelCoords
group and 1 user to Users group. Then I import the TESTrelutil objects
(all of them) to TTrelutil.mdb. Then I exit MS/ACCESS. Here are the
problems:

1. No users can log onto the TTrelutil application. The error occurs
after hitting the OK key on the logon window. It says that there may
have been a loss of Network connection. Mind you I am only working in
my personal PC (no network involved yet ... I am only testing).

2. The id that is in the Admins group is unable to bypass the startup
logic, by holding the shift key down. I do not know how to disable the
'shift-key', yet, for start-up. So I did not do anything intentionally
to do so. The result, I can't get into the development windows for
Access.

The only way I figured out to rectify this was to run the WRKGADM.EXE
and 'join' the original SYSTEM.MDW (Original Workgroup settings) and go
back to the beginning.
HELP!!!!

Thanks.
Sue

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #9

Joan Wild

"Susan Bricker" <sb****@att.net> wrote in message
news:40*********************@news.frii.net...

But I have MAJOR PROBLEMS with this entire process.
I am following the steps outlined in one of the links mentioned on a
previous reply. Of course, I think that I am doing everything
correctly. But ...
No you are not following the steps correctly. more....

Original application: relutil.mdb
Copy of application: TESTrelutil.mdb

After I have created the new user (for me) in the Admins group
That is not the first step. If this is what you did first, then it doesn't
matter what you did after this.
and I
have moved the Admin user to the Users group, I open a new db and name
it TTrelutil.mdb.
Again, you did this while logged in as Admin and so TTrelutil.mdb is owned
by Admin.
Then I create another group called RelCoords and
assign all the necessary permissions. I then add 1 user to RelCoords
group and 1 user to Users group. Then I import the TESTrelutil objects
(all of them) to TTrelutil.mdb.

How could you assign permissions to objects, before they were imported?
<rest snipped>

I would suggest you go back to relutil.mdb and start over.

Make a copy of it again TESTrelutil.mdb.

You are using Access 2000, so don't use the security wizard.

It is essential that you follow every step (every phrase in fact) in order.

You can try the detailed steps I have outlined for 2000 at
http://www.jmwild.com/security97.htm

--
Joan Wild
Microsoft Access MVP

Nov 13 '05 #10

Bradley

Susan Bricker wrote:

Bradley,
Thank you for your help. If I can get past the 'securing and setting
of permissions' then I will definitely use your suggested source code.

But I have MAJOR PROBLEMS with this entire process.
I am following the steps outlined in one of the links mentioned on a
previous reply. Of course, I think that I am doing everything
correctly. But ...

Here's what I have ...

Original application: relutil.mdb
Copy of application: TESTrelutil.mdb

After I have created the new user (for me) in the Admins group and I
have moved the Admin user to the Users group, I open a new db and name
it TTrelutil.mdb.
Then I create another group called RelCoords and
assign all the necessary permissions. I then add 1 user to RelCoords
group and 1 user to Users group. Then I import the TESTrelutil
objects (all of them) to TTrelutil.mdb. Then I exit MS/ACCESS. Here
are the problems:
How can you assign permissions before you import?
1. No users can log onto the TTrelutil application. The error occurs
after hitting the OK key on the logon window. It says that there may
have been a loss of Network connection. Mind you I am only working in
my personal PC (no network involved yet ... I am only testing).
2. The id that is in the Admins group is unable to bypass the startup
logic, by holding the shift key down. I do not know how to disable
the 'shift-key', yet, for start-up. So I did not do anything
intentionally to do so. The result, I can't get into the development
windows for Access.
Make sure your user who belongs to the Admins group has permissions to
Administer the database and all objects..?
The only way I figured out to rectify this was to run the WRKGADM.EXE
and 'join' the original SYSTEM.MDW (Original Workgroup settings) and
go back to the beginning.
HELP!!!!

Thanks.
Sue

Not sure, sounds a bit odd! :)

--
regards,

Bradley

Nov 13 '05 #11

hamking

Not sure why that is. All of my VBA code were imported with no
problems. If that's the only case just copy and paste all the code
over.
Susan Bricker <sb****@att.net> wrote in message news:<40*********************@news.frii.net>...

Very good step by step instructions. I ran into one problem, though.
Step 13 .. "import all the objects from your original database into the
new database". I thought that I did that, but the VBA code behind the
forms and reports did not get imported. Good thing I have a few copies
of this Application. Any ideas what I did wrong?

Sue

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #12