msystables? / users saving their own queries?

[tw]

I want the users of this system to be able to save custom queries based on
the permissions that they have. So the user can take the queries and/or
tables that they have permission to and run and save a query. However, the
users can create a query and run it, but when they try to save it, they get
an error message that they do not have the permission to "msystables" that
they need.

When I view hidden objects I can see the following tables

MSysAccessObjects ->owner is me?
MSysAccessXML -> owner is Admin (Admin has no rights to anywhere in system -
except here I guess)
MSysACEs -> owner is Engine
MSysObjects -> owner is Engine
MSysQueries -> owner is Engine
MSysRelationships -> owner is Engine

1. I did not change the owner of MSysAccessObjects to myself so how is this
set to me as the owner?

2. Why is MSysAccessXML's owner Admin when Admin should have no rights at
all, and that is how I thought I had set it up.

3. What user name should be the owner of these objects?

4. I do not see MSysTables. Why do not?

5. How can I give my users permission to save custom queries and reports?

I'm using a form that lists the following code in a combo box to allow users
to see the queries that they have saved provided they save them correctly.
"SELECT msysobjects.Name, msysobjects.Type FROM msysobjects WHERE
msysobjects.Name Like "<*" And msysobjects.Type=5" I have a custom menu
that allows them access to the query builder to create the queries. I have
similar code for custom reports. These work fine for me (I am a member of
the Admins group), but for users with limited permissions, they can create
and run a query, but they cannot save the query.

6. Even as a member of the Admins group, I do not have access to view or
modify the tables where Engine is the Owner. Can this be changed so that I
can accomplish what I need to accomplish?

7. Users that don't have administrative permissions can see the list created
by the SQL statement above, and can select a query from the combo box. But
when they click on the button to run the query which uses the code below to
run the query
DoCmd.OpenQuery Me.cmbQuery.Value, acViewNormal, acReadOnly
they get the message that they do not have the necessary permission to run
the query 'queryname' even though the query has RWOP and Owner (me) has
full access. And in fact, the user also has access to the table that the
query was based on. It runs fine when I try it. Why?

 

[Joan Wild]

I want the users of this system to be able to save custom queries

By the security wizard.


By the security wizard.


Don't change them. They are as they should be. You should not
change the permission of any system object.


I assume that you are using Access 2003 and that you used the security
wizard to implement security. If that's not correct then ignore the
following.


It will also show them queries saved by other users.


No, you can't change that. Nor should you view or modify those
tables.


Yes but do the users have permission on the RWOP query (not the
tables, but the query)

As for the MSysTables error, that will happen if you create a new
group (not one that the wizard offers) and want this group to be able
to save queries (only in Access 2003 though). The workarounds are:

Don't use the security wizard if you want to create extra groups.
Secure it manually in this case.
Or
Make users in your custom group also be a member of any of the Wizard
supplied groups (read-only will do).

Another possibility for that error. If you have code that modifies the SQL
property of a saved query, you need to give users permission to modify the
query, but that doesn't sound as though it's the problem in your case.

 

[tw]

Other comments inline
Access 2002, used security wizard, but closed the holes as suggested in this
newgroup and on the security faqs.

I'm using Access 2002, and I used the security wizard to create the
workgroup file, but I then made the necessary modifications according to
this user group and the security faqs to complete implementation of
security.
This is OK, users will get a polite message if they do not have permission
to run a specific query. Also, users will be modifying their front end
only. If the query is something they want access to permanently, the users
are instructed to let me know in order to include it in future updates of
the front end.the wizard created groups I allowed are the following...
Admins
Backup Operators
Full Data Users
Users
I don't want the users in any of these groups, except maybe backup operators
which doesn't give access to see any objects, but give exclusive access so
maybe this isn't a good idea anyway. Is there option for Access 2002?

Another possibility for that error. If you have code that modifies the
SQL property of a saved query, you need to give users permission to modify
the query, but that doesn't sound as though it's the problem in your case.

Is there a workaround in my case?

 

[tw]

Response to #7 response... This is the problem here. I gave permission to
my "BaseUser" group and the queries can now run for other users. Is there a
way when a new custom query is saved that it be added to the BaseUser groups
list of permitted queries (reports also), but only those custom queries
created and saved by the users?

Question 5. Is there a way (access 2002) to give my users permission to
save their own custom queries and reports?

 

[Joan Wild]

Other comments inline
Access 2002, used security wizard, but closed the holes as suggested in
this newgroup and on the security faqs.
I'm using Access 2002, and I used the security wizard to create the
workgroup file, but I then made the necessary modifications according to
this user group and the security faqs to complete implementation of
security.

My apologies. This bug occurs in 2002 as well.

the wizard created groups I allowed are the following...
Admins
Backup Operators
Full Data Users
Users

You realize that giving permissions to the Users Group gives permission for
anyone in the world who has Access. The Users Group is common to all
workgroups.

I don't want the users in any of these groups, except maybe backup
operators which doesn't give access to see any objects, but give exclusive
access so maybe this isn't a good idea anyway. Is there option for Access
2002?

That makes no sense to me. You don't want the users in any of these groups
to do what?

Did you create a group after running the wizard?

 

[Joan Wild]

Response to #7 response... This is the problem here. I gave permission to
my "BaseUser" group and the queries can now run for other users. Is there
a way when a new custom query is saved that it be added to the BaseUser
groups list of permitted queries (reports also), but only those custom
queries created and saved by the users?

You shouldn't need to do this. The user that creates and saves the query,
owns it. You don't need to modify the permissions for them, as they'll have
full access.

Question 5. Is there a way (access 2002) to give my users permission to
save their own custom queries and reports?

You could run the security wizard again, and add the Read-Only Group. Make
the users that are members of your BaseUser group, also members of the
Read-Only Group. They'll be able to save queries then.

 

[TC]

There's no point worrying about the presence, absence or ownership of
any of the MSys* objects. Those objects are all managed by Jet (or
Access), in ways that Jet (or Access) deem appropriate.

If you're just interested - fine. But if you're asking because you're
worried about them or think they are not working properly - forget it,
just let Jet & Access do whatever they will, with them.

HTH,
TC

 

[tw]

You shouldn't need to do this. The user that creates and saves the query,
owns it. You don't need to modify the permissions for them, as they'll
have full access.

You are right, I got caught up in testing and created a sample query, then
logged in as another user to see if it would run. But since, I couldn't
save as a normal user, I couldn't test a query that a user would have
created. So the problem remains how to let every user save a query, without
giving them keys to the car.

You could run the security wizard again, and add the Read-Only Group.
Make the users that are members of your BaseUser group, also members of
the Read-Only Group. They'll be able to save queries then.

How would a read-only group have permission to save?
There is sensitive data that we don't want all the users to have access to
even at a read-only level. This includes employee salaries, social security
numbers etc. that not everone needs to see.

 

[tw]

comments in-line

My apologies. This bug occurs in 2002 as well.



You realize that giving permissions to the Users Group gives permission
for anyone in the world who has Access. The Users Group is common to all
workgroups.

I don't have any users in the "Users Group" and I won't put any there.

That makes no sense to me. You don't want the users in any of these
groups to do what?

Not sure of your question. Other than the users that are in these groups
already, I don't want any other users in these groups. That's like giving
the keys to your Ferrari to your teenager.

I have made several custom groups where most of the users are.

 

[tw]

I was just wondering because of the error message received referred to
permission of MSysTables and I'm trying to resolve this problem of allowing
users to be able to save queries that they create.

 

[Joan Wild]

How would a read-only group have permission to save?

It's a bug in the security wizard. Your other alternative is to not use the
wizard. Start over and secure it manually. Or do as i suggested. If you
add the Read-only Group using the wizard, you'll find that they can save a
query. Why shouldn't they be able to? If they have read data permission on
a table, they can create and save a query. So the group you created after
the fact, that you want to save queries, also make the members of this group
members of the read-only group.

 

[Joan Wild]

comments in-line

Not sure of your question. Other than the users that are in these groups
already, I don't want any other users in these groups.

OK, that's fine.

I have made several custom groups where most of the users are.

I realize that. That is where the bug is. If you create custom groups,
after running the wizard, none of these groups will be able to save queries.
However, if you also made these users a member of any Wizard created group,
they will be able to.

The alternative is to start from scratch and don't use the wizard at all.
Secure it manually. You will not have the problem then.

 

[TC]

Ok. I'm sure that Joan will help you with that.

Cheers,
TC

 

[tw]

As I said before, I don't want all users to have even read only permissions
to some of the data because they shouldn't even be able to see employee
salaries, SSNs etc. But, if I were to user the security wizard to create a
read only group, then remove permissions from most tables for that "wizard
created" group, then add all the users to the group, will that accomplish
the same thing? I'd like to avoid having to start over again, because there
are many groups, not just a few.

 

[Joan Wild]

As I said before, I don't want all users to have even read only
permissions to some of the data because they shouldn't even be able to see
employee salaries, SSNs etc.

OK, but you want some users (the ones where you are having an issue) to be
able to create and save queries. They must have at least Read Data
permissions on the underlying tables to be able to do this. So how do you
expect them to be able to create queries?

But, if I were to user the security wizard to create a read only group,
then remove permissions from most tables for that "wizard created" group,
then add all the users to the group, will that accomplish the same thing?

You don't need to add *all* users to the read-only group; just the users
that you want to be able to create/save queries.

 

[tw]

They have read/write access to the specific table that affect their job,
which are the same table that they would be creating queries on.

BTW, I ran the query wizard to attempt what you are saying. The screen had
all the objects selected and requested me to leave it that way or unselect
the items to leave the security as is on those tables. I unselected the
items, because I didn't want to change the permissions on those items, I
just wanted to add the readonly group that you selected. It took all
permissions away from every group. I had to restore from the backup I made
prior to making this attempt. Can I run the wizard to add a group without
modifying the current groups and permissions? If so, how do I answer the
questions from the wizard on each window?

 

[Joan Wild]

BTW, I ran the query wizard to attempt what you are saying. The screen
had all the objects selected and requested me to leave it that way or
unselect the items to leave the security as is on those tables. I
unselected the items, because I didn't want to change the permissions on
those items, I just wanted to add the readonly group that you selected.
It took all permissions away from every group.

That's because you unselected them. Just leave it as is.

Can I run the wizard to add a group without modifying the current groups
and permissions? If so, how do I answer the questions from the wizard on
each window?

I don't use the wizard myself, but I believe you leave things as they are
(don't uncheck anything). Add the Read-Only Group. After it finishes, go
into Tools, Security, Accounts and add your users to this group.

If this doesn't work, you'll have to start over. If you don't use the
wizard, but secure it manually, you won't have this problem.

 

[tw]

Well it turns out that I have some corruption in the database as well, so
because of that I'm going to have to start with a blank database and import
all the objects which will remove permissions anyway. So I'll go with your
suggestion and not use the security wizard. (Ever again!) Thanks for all
the help. I'll post back if I have problems, but I understand what to do so
I should be OK.

Thanks.