Re: User has group permissions to object, but still denied access.

After a bit more testing, I've been able to rule out some of the variables.
For now, discard the questions of network quirks, Active Directory
securities, and file locations. There is a set of 13 queries out of the 81
that she is never able to edit, regardless of where the database files are
located at. She's also unable to edit them when given explicit full
permissions to those query objects.

Here's the kicker though - the owner, sysadm, can edit the query, but it
doesn't have the ability to change the owner of the object to someone else.
The owners for the other 68 queries can be changed.

I have a gut feeling, based on the names of the queries, that these queries
may have been some of the original queries created in the database. I
thought that perhaps they were corrupted somehow in all of the upgrades and
such. I did a compact and repair on the database, but that didn't resolve
the issue.

Any ideas based on the new information?

"Project Orca" wrote:

Thanks for the heads-up on the file names! That fixed my second issue.

I'm still scratching my head over the first issue though. She's not using
different databases - I've made sure that I'm using the same database and
workgroup file in my tests under her username. I test them on the mapped
drive and she's unable to edit a few specific queries (security on those
specific queries is identical to queries that she does have the ability to
edit). I copy the files (mdb and mdw) to the local PC, reopen Access, attach
to the local mdw file, close Access, reopen Access and open the local mdb
file and I can edit all queries.

I checked the object dependencies for those queries, thinking that they may
be required for the reports that she couldn't edit (due to the mdb and mdw
file names being identical), but there was no common dependency among the
queries that she couldn't edit. The problem also continued after I changed
the name of the workgroup file.

"Chris O'C via AccessMonster.com" wrote:

Since the permissions are applied to the db file and she has different
permissions depending on where the file is, it means she's either opening the
file as a different user or opening it with a different workgroup when she
opens it on her PC. Or it means she's not opening an exact copy of the db
file. (Maybe an older copy before she had permissions to these queries?)

She doesn't have exclusive access to make changes to the db because someone
accidentally named the workgroup file the same as the db file and placed both
files in the same folder. DBName.mdw and DBName.mdb will both create a
locking db file, DBName.ldb, when they open unless that file already exists.
If DBName.ldb already exists, the db will be opened in shared mode, not
exclusively.

Either change the name of one of the files or move one of the files to
another folder.

Chris
Microsoft MVP


Project Orca wrote:
The user is attempting to change some queries on a database stored on a
server and accessed by a mapped drive, but gets the following error message
when trying to save the changes: "You do not have the necessary permissions
to use the '<query>' object. Have your administrator or the person who
created this object establish the appropriate permissions for you." (replace
the <query> with the name of the query currently being edited). There are
about 80 queries in the database, and I haven't been able to find a pattern
for which queries she is able to edit and which ones she is not.

If she copies or moves the database to her local machine, she's able to make
changes to any of the queries.

Regarding the security, the database was converted from an Access 97
database with no security to an Access 2000 (when connected to a workgroup
file - database.mdw as user "sysadm" ) and then to Access 2003 format (again
with the database.mdw and user "sysadm"). At that point, all of the objects
in the database had an owner of "sysadm".

Then the security permissions were set on the database. The Users group was
only given permission. Another group was created for basic read/write
(GroupRW). The Admins group was given full permissions. The Admin user was
removed from the Admins group. Sysadm was already in the Admins group, the
GroupRW and Users group. User1 had the same groups (Admins, GroupRW, and
Users).

The user has full control of the folder on the server where the database is
stored, as well as propagating that full control to the database files (mdb,
ldb, and mdw).

The way I understand the security as it is set up, even though the
individual users don't have permissions to database objects, they should
still get the permissions that were assigned to their group - in this case
full permissions for User1 since she is part of the Admins group and the
Admins group has full permissions to all database objects.

Can anyone see a reason why she wouldn't be allowed to edit a query? Can
anyone see a reason why she can edit some queries but not others (when they
all have the same security structure)?

I don't want to muddy the waters but it may be important to note that there
35 forms, 45 reports, and 2 macros. She's unable to gain exclusive access to
change reports and forms when the database is located on the server - even if
she's the only one in the database at the time and the server reports no
other users with the database open. This is another issue that I need to
resolve, but I'm trying to tackle one area at a time...

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-security/200806/1


.

Relevant Pages

  • Re: Creating security for MS Access application
    ... I wanted to create user ids and grant permissions based on user ids. ... You've not properly secured your database if that's the case. ... of User Level Security before mucking around with it too much more. ... with instructions on how to properly secure a database without the Security Wizard: ...
    (microsoft.public.access.security)
  • Re: User has group permissions to object, but still denied access.
    ... specific queries is identical to queries that she does have the ability to ... permissions depending on where the file is, it means she's either opening the ... about 80 queries in the database, and I haven't been able to find a pattern ... The Admins group was given full permissions. ...
    (microsoft.public.access.security)
  • Re: Secured database problems
    ... User Level Security *properly* is a challenging task to ... In the BE remove all permissions for the tables except ... my other saved queries reference those RWOP queries. ... or import your tables into another database. ...
    (microsoft.public.access.security)
  • Re: A note to add to ASPFAQ.com for database compacting
    ... I set the database directory permission to give full access to ... then checked "Allow inheritable permissions from parent to ... Thus your problem is that you have to high of security set ... > the files needed and also a compact & repair operation will not cause ...
    (microsoft.public.inetserver.asp.db)
  • Sorting out security
    ... MS Access security. ... created a new workgroup, added a password for the Admin role, added groups, ... user IDs and passwords for users and allocated permissions on the ... remote logins to a secured database. ...
    (microsoft.public.access.security)