Adding a database to existing workgroup file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been trying to add a new database to an existing mdw file and setting
up a new group/permissions. Access to the existing database is through a
shortcut. I've logged on as the administrator, closed the existing database,
created new blank database and imported all objects from my devel database
into this new db. then set up a new group and permissions for that group.

1) After doing these steps, I find when I open Access directly (not through
the short cut to the newly "secured" db, my machine been joined to the
custom.mdw file.
2) When I rejoin my machine to the system.mdw, and then open the new
database, it, too, has been joined to the system.mdw.
3) When I rejoin the new db to the custom.mdw, I find that I can log in as
myself (e.g., as a regular user) and change any permissions I want, except
access privileges to the database itself.

In the custom.mdw, the Admin user is in the default user group and that
group has no privileges for anything.

Can anyone shed any light on what might be going wrong? The security on the
original db is working properly.
 
Susan L said:
I have been trying to add a new database to an existing mdw file and
setting
up a new group/permissions. Access to the existing database is through a
shortcut. I've logged on as the administrator, closed the existing
database,
created new blank database and imported all objects from my devel database
into this new db. then set up a new group and permissions for that group.
Click to expand...

When you imported the new database, did you remove permissions for the Users
Group and the Admin user? Double check this.

2) When I rejoin my machine to the system.mdw, and then open the new
database, it, too, has been joined to the system.mdw.
Click to expand...

Firstly, a database is not joined to a mdw file. The current Access session
is using a workgroup. Any number of mdb files can be used with that
workgroup, and also it's possible to use any number of workgroup files with
one database. If you can open the new mdb while using the system.mdw, then
the Admin User or the Users Group (common to all mdw files), has
permissions.
3) When I rejoin the new db to the custom.mdw, I find that I can log in as
myself (e.g., as a regular user) and change any permissions I want, except
access privileges to the database itself.
Click to expand...

The regular user is a member of the Users Group, and it's that Group that
has permissions it shouldn't.
In the custom.mdw, the Admin user is in the default user group and that
group has no privileges for anything.
Click to expand...

That doesn't make sense. The Admin user is not a group.

If believe you thought that by using your custom.mdw, that Users Group and
Admin User would automatically not have any permissions on imported objects.
That is not the case.
 
Joan, thanks for your response. You were (of course) right that I didn't
realize that I would need to remove privileges from the default User group
again. I did that (the Admin user was still in the User group, not the Admins
group) and everything worked as it should.

However, I still have a problem with "losing" the custom.mdw connection when
I change back my copy of Access to the system.mdw file. I closed the new db
after changing and testing permissions, opened Access from my MS Office
toolbar, rejoined the system.mdw, checked the new db, and voila! it was back
to the system.mdw. Any thoughts?
 
Susan L said:
Joan, thanks for your response. You were (of course) right that I didn't
realize that I would need to remove privileges from the default User group
again. I did that (the Admin user was still in the User group, not the
Admins
group) and everything worked as it should.

However, I still have a problem with "losing" the custom.mdw connection
when
I change back my copy of Access to the system.mdw file. I closed the new
db
after changing and testing permissions, opened Access from my MS Office
toolbar, rejoined the system.mdw, checked the new db, and voila! it was
back
to the system.mdw.
Click to expand...

What do you mean 'it was back to the system.mdw'? You just said you
rejoined the system.mdw, so what else should it be back to?

If you mean that you could open your secure db using system.mdw, then you
didn't remove all permissions for the Users Group (check the database
object). You shouldn't be able to even open the mdb while joined to
system.mdw
 
Susan said:
Joan, thanks for your response. You were (of course) right that I
didn't realize that I would need to remove privileges from the
default User group again. I did that (the Admin user was still in the
User group, not the Admins group) and everything worked as it should.

However, I still have a problem with "losing" the custom.mdw
connection when I change back my copy of Access to the system.mdw
file. I closed the new db after changing and testing permissions,
opened Access from my MS Office toolbar, rejoined the system.mdw,
checked the new db, and voila! it was back to the system.mdw. Any
thoughts?
Click to expand...

The workgroup you are joined to is the one Access will use by default when a
different one is not specified as a command line argument. It has nothing
to do with what MDB file you might try to open.

The workgroup file you are using might dictate which files you will be
*able* to open, but opening a file does not determine which workgroup file
will be used. That is already determined before any file open is even
attempted.
 
Joan: I went back and checked the User group permissions on the newly secured
database. The Users group has no permissions at all (I did check the database
permissions.) The Admin user is a member of that group, as are all other
users as well (as I read in instructions). The privileges are assigned to two
other groups.

With my original db, the only access to it was through the shortcut.
Double-clicking generated an error re not having permissions, as you pointed
out. Yet, I had rejoined the system.mdw file so I didn't have to log on to
dev versions of 4 other dbs under development. That was working fine until I
started the process of implementing security on a new db.

Now when I set up the new db to use the custom.mdw, 1) I can open the db
directly by double-clicking the file, which I should not be able to do. 2) As
of 1/2 hour ago, I can now also open my original db by double-clicking the
file, which I could not do earlier today, because I checked. 3) when I close
these dbs and Access as well, then reopen Access to rejoin the system.mdw
file, close Access, then reopen the two databases, and check their mdw file
in Security Manager, they are rejoined to the system.mdw file.

Perhaps my next step should be to recreate the custom.mdw file and start over.
 
Susan L said:
Joan: I went back and checked the User group permissions on the newly
secured
database. The Users group has no permissions at all (I did check the
database
permissions.) The Admin user is a member of that group, as are all other
users as well (as I read in instructions). The privileges are assigned to
two
other groups.
OK.


With my original db, the only access to it was through the shortcut.
Double-clicking generated an error re not having permissions, as you
pointed
out. Yet, I had rejoined the system.mdw file so I didn't have to log on to
dev versions of 4 other dbs under development. That was working fine until
I
started the process of implementing security on a new db.

OK.


Now when I set up the new db to use the custom.mdw, 1) I can open the db
directly by double-clicking the file, which I should not be able to do.
Click to expand...

Maybe, maybe not. It depends on what workgroup file you are joined to by
default. If you are joined to your custom.mdw by default, then it should
let you in. If you are joined to the system.mdw by default and aren't using
a desktop shortcut, it shouldn't let you in.

2) As
of 1/2 hour ago, I can now also open my original db by double-clicking the
file, which I could not do earlier today, because I checked.
Click to expand...

Again what workgroup file are you joined to by default? If it really is the
system.mdw, then you didn't secure it properly to begin with (check to see
who the owner of the database object is - that'll provide a hint). All this
time, your original mdb may not have been secured properly.

3) when I close
these dbs and Access as well, then reopen Access to rejoin the system.mdw
file,
Click to expand...

(see that suggests that you were joined to some other mdw)

close Access, then reopen the two databases, and check their mdw file
in Security Manager, they are rejoined to the system.mdw file.
Click to expand...

You may not understand that what you see in Tools, security, workgroup
administrator. That is your default workgroup file. It isn't necessarily
the one currently in use. It simply tells you which mdw will be used by
default. You can over-ride the default by using a desktop shortcut that
points to a different workgroup file. For that session only, the other
workgroup file will be used. Even if you are using a custom.mdw and you go
to the workgroup administrator, it will show you that your default is
system.mdw. If you want to really verify the workgroup file that is being
used in the current session of Access then hit Ctrl-G and type
?dbengine.systemdb

I have a feeling from your description that neither your original mdb, nor
your new one are truly secure. A good test to see if they are, is to try to
open them while using the system.mdw workgroup.
 
Joan: Thanks for your patience. The owner of my db object is in both dbs my
administrative user, "sradmin". Typed the code you sent in the Immediate
window - custom.mdw was being used. Thanks for explaining that the Workgroup
Administrator doesn't necessarily show the file in use, rather the default. I
think that's what Rick was trying to get across to me. (Brain is a little low
on gas.)

Somehow, the "problem" has now gone away. I rejoined the system.mdw after
the last round of checking the security and making sure neither User group
had privileges and then tried double-clicking the dbs themselves. I could not
get in to either of them, as it should be.

Now I can only get in via the shortcuts. Don't understand what was going
wrong (after I removed the User group privileges-- that was a problem), but I
did restart my computer. Maybe that cleared something out. Since I can't get
in using system.mdw, does it sound as if they are secure? If not, I'l better
redo, using your instructions, which were a lifesaver when I first set up
security.
 
Susan L said:
Since I can't get
in using system.mdw, does it sound as if they are secure? If not, I'l
better
redo, using your instructions, which were a lifesaver when I first set up
security.
Click to expand...

Yes it does; I wouldn't be redoing anything.
 
Thanks again. Two final questions. 1) I'd like to be able to periodically
monitor the status of tables in the databases. Do you have any methods to
recommend in addition to the code to identify the workgroup file? (We have an
"experimenter" on staff.) I found some code in a post, but I get an error
"Method or data member not found." Here's the code:
debug.print dbengine(0)(0).tabledefs("tblMyTableName").Owner
debug.print hex$(dbengine(0)(0).tabledefs("tblMyTableName").AllPermissions)
Is the syntax OK?
2) Last. Can I put the custom.mdw in a folder to which users do not have
read/write privileges or must they have privileges to use the file? (Am
thinking "out of sight/out of mind")

Appreciate the time you've spent.
 
Susan L said:
Thanks again. Two final questions. 1) I'd like to be able to periodically
monitor the status of tables in the databases. Do you have any methods to
recommend in addition to the code to identify the workgroup file? (We have
an
"experimenter" on staff.) I found some code in a post, but I get an error
"Method or data member not found." Here's the code:
debug.print dbengine(0)(0).tabledefs("tblMyTableName").Owner
Click to expand...

debug.print
dbengine(0)(0).Containers("tables").documents("tblMyTableName").owner

I'm not sure what you hope to find wrt monitoring the status of the table.
You can deny all permissions for all tables to your users, and instead use
RWOP (run with owner permissions) queries as the recordsources for all forms
and reports (more information in the security FAQ). Further you can lock
down the interface so that they don't see the database window.
2) Last. Can I put the custom.mdw in a folder to which users do not have
read/write privileges or must they have privileges to use the file? (Am
thinking "out of sight/out of mind")
Click to expand...

Users need read/write/create/delete permissions on the folder. However, if
you want to achieve out of sight, you can put it in a hidden folder.

\\server\share$ rather than \\server\share
That will hide it in windows explorer, however a savvy user can still get to
it, if they know the path.

What would be the concern if they could 'see' the mdw?
 
You can deny all permissions for all tables to your users, and instead use
RWOP (run with owner permissions) queries as the recordsources for all forms
and reports (more information in the security FAQ). I'll review the FAQ again.
What would be the concern if they could 'see' the mdw?
Click to expand...
I read in another post that people could use hacking software on the
workgroup file to obtain passwords. As background, two days ago, I entered my
original db as administrator and tried to make a change. (This was still when
I could not get in to the db by directly double-clicking, so presumably it
was secure.) I received a message that one of our users had the db open as
exclusive. No users except the administrative user have this permission. This
led me to believe that perhaps the mdw had been hacked. I have checked, and
neither that individual nor the group he belongs has exclusive privileges --
at least not when I checked.
-
susan
 
I read in another post that people could use hacking software on the
workgroup file to obtain passwords.
Click to expand...

Just an FYI, but there are security crackers on the internet that can break
Access security. Some don't even need the mdw to do so.
As background, two days ago, I entered my
original db as administrator and tried to make a change. (This was still
when
I could not get in to the db by directly double-clicking, so presumably it
was secure.) I received a message that one of our users had the db open as
exclusive. No users except the administrative user have this permission.
Click to expand...

That message could come up for a number of reasons. First your database
should be split, with the backend (tables only) on the server, and a copy of
the frontend (all other objects) on each person's PC. The frontend contains
links to the tables in the backend. If you are going to do this, don't use
the database splitter on a secure mdb - the backend will end up unsecured.
Instead split it manually - see http://www.jmwild.com/SplitSecure.htm on how
to do it.

Secondly, if users don't have create permission on the folder where the
backend (or your current mdb) is on the server, then the first person in
will open it exclusively.

Thirdly, if the message was about needing exclusive access, that is true on
recent versions of Access, you need exclusive access to the mdb in order to
make design changes (another reason to split; you can work on your copy of
the frontend exclusively to make design changes.)
 
No, if you are referring to the default Admin user, I did not define a
password. I did define one for my custom admin user, "sradmin." Are you
suggesting I assign one?
 
Susan L said:
No, if you are referring to the default Admin user, I did not define a
password. I did define one for my custom admin user, "sradmin." Are you
suggesting I assign one?
Click to expand...


Giving the default Admin user a password is what causes the login dialog to
be displayed.
 
I was reading about splitting a database on your site just before I opened
your post.
Re the open exclusive issue: Yes, I was denied access to whatever design
changes I wanted to make because the other user (who was named in the
message) had the DB open exclusively. Second, the users do have full
privileges on the folder that houses the DB, so if that means that the first
user doesn't open the DB with exclusive privileges, then maybe there is a
problem.

I think I'm going to have to study Access security in more depth using the
Microsoft FAQ and the resources/ links on your site and others. Rather than
trying to figure out what happened in the past, I'll plan for the future
using the information and resources you've provided. I have learned a
tremendous amount from you and really appreciate the time you've spent
helping me. After my "crash course" in security, I may have more questions to
post. Many thanks, Joan.
 
Re the open exclusive issue: Yes, I was denied access to whatever design
changes I wanted to make because the other user (who was named in the
message) had the DB open exclusively.
Click to expand...

Is that what the message said? Or did it say that you needed exclusive
access to make changes?

Perhaps that user is opening exclusively (File, Open, Open Exclusively or
using the /excl switch in a shortcut, or the database is set to open
exclusively - Tools Options).

Also, you didn't name your mdw the same as the mdb, did you? That could
cause the message as well.
I think I'm going to have to study Access security in more depth using the
Microsoft FAQ and the resources/ links on your site and others. Rather
than
trying to figure out what happened in the past, I'll plan for the future
using the information and resources you've provided. I have learned a
tremendous amount from you and really appreciate the time you've spent
helping me. After my "crash course" in security, I may have more questions
to
post. Many thanks, Joan.
Click to expand...

You're welcome; come back when you've got more questions.
 
Back
Top