Permissions Problem

Erik · Apr 6, 2004

Hello All!

I am trying to secure a database that is accessed by many
users. I have implemented user-level security and it
appears to be working fine. The bulk of the users we need
to interact with the DB only through the Switchboard, but
there are a couple of users that will need to be able to
hit the tables, queries, etc directly. The question I have
is this:
Since most of the options in the Switchboard run update,
insert and delete queries - how can I keep the users from
manually opening and editing the records in the table
while still giving the permissions needed to allow the
Switchboard to do it's job?
Any help would be greatly appreciated!

Thanks,

Erik

Eric Butts [MSFT] · Apr 7, 2004

Hi Erik,

For the queries take a look at the property "Run Permissions" and set it to
Owners.

Then set the necessary permissions on the tables.

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.

Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights

--------------------
| Content-Class: urn:content-classes:message
| From: "Erik" <[email protected]>
| Sender: "Erik" <[email protected]>
| Subject: Permissions Problem
| Date: Tue, 6 Apr 2004 10:51:50 -0700
| Lines: 19
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcQb/9baGsqNODBJTSWS/yPFeYbNeQ==
| Newsgroups: microsoft.public.access.security
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.security:9741
| NNTP-Posting-Host: tk2msftngxa09.phx.gbl 10.40.1.161
| X-Tomcat-NG: microsoft.public.access.security
|
| Hello All!
|
| I am trying to secure a database that is accessed by many
| users. I have implemented user-level security and it
| appears to be working fine. The bulk of the users we need
| to interact with the DB only through the Switchboard, but
| there are a couple of users that will need to be able to
| hit the tables, queries, etc directly. The question I have
| is this:
| Since most of the options in the Switchboard run update,
| insert and delete queries - how can I keep the users from
| manually opening and editing the records in the table
| while still giving the permissions needed to allow the
| Switchboard to do it's job?
| Any help would be greatly appreciated!
|
| Thanks,
|
| Erik
|

Erik · Apr 7, 2004

Hi Eric,

Thanks for the reply. I set all of the queries "Run
Permissions" (found in the properties in query design
view) to "Owners". I then set the permissions for the
tables to "Read" and "Read Design" for the main user group.
The main user group can still not open the forms from the
switchboard that have buttons to "delete" or "Add new" in
them. In order to allow the users to open the form, I have
to allow insert and delete permissions on the tables -
which, of course, undermines the purpose of securing the
db in the first place. Basically, I have three groups:
MCA_Admins, MCA_Users, and MCA_Accounting. The MCA_Users
must be able to use the forms on the Switchboard to add,
delete and update records - but must not be able to edit
the records in the tables directly. The MCA_Accounting
group is sort of a hybrid between the users and the admins
in that they should be able to edit the records of only
certain tables directly. Is there no way to allow access
to these tables only through a form?

Thanks again,

Erik

Eric Butts [MSFT] · Apr 7, 2004

TC · Apr 8, 2004

Eric Butts said:
Hi Erik,

I miss read your initial post. Thought the queries in question where
Update, Append, Delete queries. RunPermissions property is only used for
those queries.

Not so, surely. RWOP has a defined use for Select queries also. It lets the
current user select data from tables to which he has no direct access.

The only way I see you doing this is using VBA code. What would be
involved is binding your Form to a Recordset rather binding it directly to
a table/query. Use code to Update/Append data as needed utilizing
DAO.WorkSpaces.

He could do it using RWOP queries. But he has not been advised on the
critical step: ensuring that the ownership of the tables & RWOP queries has
been established correctly!

HTH,
TC

Erik · Apr 8, 2004

Hi guys,

Thanks again for all of the help! I did make sure that all
of the queries and tables were owned by "MCA_Admins" (my
admin group) before trying the RWOP method. Yes - the
MCA_Admins group has full permissions on every object in
the DB. As this recordset method seems somewhat daunting
to me, would you have any further suggestions on how I
could allow users to work with the switchboard forms
(doing the update,delete, and insert actions) while not
having to open up the database objects to the users? It
feels as though I have done all I could as far as working
with RWOP is concerned...perhaps I am missing something.

Thanks again,

Erik

-----Original Message-----

Not so, surely. RWOP has a defined use for Select queries also. It lets the
current user select data from tables to which he has no direct access.

He could do it using RWOP queries. But he has not been advised on the
critical step: ensuring that the ownership of the tables & RWOP queries has
been established correctly!

HTH,
TC

<http://www.microsoft.com/security/security_bulletins/ms03-
026.asp> and/or

TC · Apr 9, 2004

Hi Erik

I may have misunderstood what you have or have not done. For an RWOP query
to work as expected:

- the RWOP query must be owned by a user who has appropriate access to the
table(s) referenced by that query;

- the other (normal) users must have read-access to the RWOP query, and

- those other (normal) users must not have *any* access to the table(s)
referenced by the RWOP query.

Then, the other ("normal") users can only reference the relevant table(s)
via the RWOP query. They can not reference the table(s) directly.

To check the owner of a query:

(untested)
debug.print dbengine(0)(0).querydefs![query_name_here].owner

Does any of that help?
TC

Erik · Apr 9, 2004

Hi TC,

I actually have it setup exactly as you specified:
Admins have full access to every table and query (every
object, actually), and Users have read access to the
queries. The problem is that when a user clicks one of the
buttons on the switchboard to open a form - if the Users
group doesn't have at least read access to the table that
the form uses, when the user clicks the button to open
that form he gets: "An error has occured" - once I grant
read access to the table - the error doesn't come up, but
instead the form comes up blank. When I allow insert
permissions for Users - the form comes up fine. What do
you think I am doing wrong?

Thanks again,

Erik

-----Original Message-----
Hi Erik

I may have misunderstood what you have or have not done. For an RWOP query
to work as expected:

- the RWOP query must be owned by a user who has appropriate access to the
table(s) referenced by that query;

- the other (normal) users must have read-access to the RWOP query, and

- those other (normal) users must not have *any* access to the table(s)
referenced by the RWOP query.

Then, the other ("normal") users can only reference the relevant table(s)
via the RWOP query. They can not reference the table(s) directly.

To check the owner of a query:

(untested)
debug.print dbengine(0)(0).querydefs! [query_name_here].owner

Does any of that help?
TC

Hi guys,

Thanks again for all of the help! I did make sure that all
of the queries and tables were owned by "MCA_Admins" (my
admin group) before trying the RWOP method. Yes - the
MCA_Admins group has full permissions on every object in
the DB. As this recordset method seems somewhat daunting
to me, would you have any further suggestions on how I
could allow users to work with the switchboard forms
(doing the update,delete, and insert actions) while not
having to open up the database objects to the users? It
feels as though I have done all I could as far as working
with RWOP is concerned...perhaps I am missing something.

Thanks again,

Erik
in
question where queries
also. It lets the tables
& RWOP queries has Microsoft
strongly advises
<http://www.microsoft.com/security/security_bulletins/ms03-
026.asp> and/or
user
group. from
the form, I
have securing
the questions
on installed
the
<http://www.microsoft.com/security/security_bulletins/ms03 patches,
not warranties,
and
accessed
by and
it users
we allow
the

Click to expand...

.

Erik · Apr 9, 2004

Oh - I forgot to mention on the previous post - the owner
of all objects is the "Admins" group...and all of the
queries are set to "Run Permissions" = "Owner's".

Thanks,

Erik

-----Original Message-----
Hi TC,

I actually have it setup exactly as you specified:
Admins have full access to every table and query (every
object, actually), and Users have read access to the
queries. The problem is that when a user clicks one of the
buttons on the switchboard to open a form - if the Users
group doesn't have at least read access to the table that
the form uses, when the user clicks the button to open
that form he gets: "An error has occured" - once I grant
read access to the table - the error doesn't come up, but
instead the form comes up blank. When I allow insert
permissions for Users - the form comes up fine. What do
you think I am doing wrong?

Thanks again,

Erik

-----Original Message-----
Hi Erik

I may have misunderstood what you have or have not done. For an RWOP query
to work as expected:

- the RWOP query must be owned by a user who has appropriate access to the
table(s) referenced by that query;

- the other (normal) users must have read-access to the RWOP query, and

- those other (normal) users must not have *any* access to the table(s)
referenced by the RWOP query.

Then, the other ("normal") users can only reference the relevant table(s)
via the RWOP query. They can not reference the table(s) directly.

To check the owner of a query:

(untested)
debug.print dbengine(0)(0).querydefs! [query_name_here].owner

Does any of that help?
TC

Hi guys,

Thanks again for all of the help! I did make sure that all
of the queries and tables were owned by "MCA_Admins" (my
admin group) before trying the RWOP method. Yes - the
MCA_Admins group has full permissions on every object in
the DB. As this recordset method seems somewhat daunting
to me, would you have any further suggestions on how I
could allow users to work with the switchboard forms
(doing the update,delete, and insert actions) while not
having to open up the database objects to the users? It
feels as though I have done all I could as far as working
with RWOP is concerned...perhaps I am missing something.

Thanks again,

Erik

-----Original Message-----

in message
Hi Erik,

I miss read your initial post. Thought the queries in
question where
Update, Append, Delete queries. RunPermissions
property is only used for
those queries.

Not so, surely. RWOP has a defined use for Select queries
also. It lets the
current user select data from tables to which he has no
direct access.

The only way I see you doing this is using VBA code.
What would be
involved is binding your Form to a Recordset rather
binding it directly to
a table/query. Use code to Update/Append data as
needed utilizing
DAO.WorkSpaces.

He could do it using RWOP queries. But he has not been
advised on the
critical step: ensuring that the ownership of the tables
& RWOP queries has
been established correctly!

HTH,
TC

Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed
the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft
strongly advises
you to review the information at the following link
regarding Microsoft
Security Bulletin MS03-026

Click to expand...

<http://www.microsoft.com/security/security_bulletins/ms03 -

026.asp> and/or
to visit Windows Update at
<http://windowsupdate.microsoft.com/> to
install
the patch. Running the SCAN program from the Windows
Update site will
help
to insure you are current with all security patches,
not just MS03-026."

This posting is provided "AS IS" with no warranties,
and confers no rights

Click to expand...

| References: <194a301c41bff$d6da2ed0 [email protected]>
<[email protected]>
| Subject: RE: Permissions Problem
| Date: Wed, 7 Apr 2004 08:33:56 -0700
| Lines: 116
| Message-ID: <19bfc01c41cb5$bd2121e0 [email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE
V5.50.4910.0300
| Thread-Index: AcQctb0hp6xE3GdcTAa00dBGZgAkMA==
| Newsgroups: microsoft.public.access.security
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl
microsoft.public.access.security:9772
| NNTP-Posting-Host: tk2msftngxa09.phx.gbl 10.40.1.161
| X-Tomcat-NG: microsoft.public.access.security
|
| Hi Eric,
|
| Thanks for the reply. I set all of the queries "Run
| Permissions" (found in the properties in query design
| view) to "Owners". I then set the permissions for the
| tables to "Read" and "Read Design" for the main user
group.
| The main user group can still not open the forms from
the
| switchboard that have buttons to "delete" or "Add
new" in
| them. In order to allow the users to open the form, I
have
| to allow insert and delete permissions on the tables -
| which, of course, undermines the purpose of securing
the
| db in the first place. Basically, I have three groups:
| MCA_Admins, MCA_Users, and MCA_Accounting. The
MCA_Users
| must be able to use the forms on the Switchboard to
add,
| delete and update records - but must not be able to
edit
| the records in the tables directly. The MCA_Accounting
| group is sort of a hybrid between the users and the
admins
| in that they should be able to edit the records of
only
| certain tables directly. Is there no way to allow
access
| to these tables only through a form?
|
| Thanks again,
|
| Erik
|
|
| >-----Original Message-----
| >Hi Erik,
| >
| >For the queries take a look at the property "Run
| Permissions" and set it to
| >Owners.
| >
| >Then set the necessary permissions on the tables.
| >
| >I hope this helps! If you have additional questions
on
| this topic, please
| >respond back to this posting.
| >
| >
| >Regards,
| >
| >Eric Butts
| >Microsoft Access Support
| >[email protected]
| >"Microsoft Security Announcement: Have you installed
the
| patch for
| >Microsoft Security Bulletin MS03-026? If not
Microsoft
| strongly advises
| >you to review the information at the following link
| regarding Microsoft
| >Security Bulletin MS03-026
|

Click to expand...

<http://www.microsoft.com/security/security_bulletins/ms0 3

| -026.asp> and/or
| >to visit Windows Update at
| <http://windowsupdate.microsoft.com/> to install
| >the patch. Running the SCAN program from the Windows
| Update site will help
| >to insure you are current with all security patches,
not
| just MS03-026."
| >
| >This posting is provided "AS IS" with no warranties,
and
| confers no rights
| >
| >--------------------
| >| Content-Class: urn:content-classes:message
| >| From: "Erik"

Click to expand...

| >| Sender: "Erik"
<[email protected]>
| >| Subject: Permissions Problem
| >| Date: Tue, 6 Apr 2004 10:51:50 -0700
| >| Lines: 19
| >| Message-ID: <194a301c41bff$d6da2ed0
[email protected]>
| >| MIME-Version: 1.0
| >| Content-Type: text/plain;
| >| charset="iso-8859-1"
| >| Content-Transfer-Encoding: 7bit
| >| X-Newsreader: Microsoft CDO for Windows 2000
| >| X-MimeOLE: Produced By Microsoft MimeOLE
V5.50.4910.0300
| >| Thread-Index: AcQb/9baGsqNODBJTSWS/yPFeYbNeQ==
| >| Newsgroups: microsoft.public.access.security
| >| Path: cpmsftngxa06.phx.gbl
| >| Xref: cpmsftngxa06.phx.gbl
| microsoft.public.access.security:9741
| >| NNTP-Posting-Host: tk2msftngxa09.phx.gbl
10.40.1.161
| >| X-Tomcat-NG: microsoft.public.access.security
| >|
| >| Hello All!
| >|
| >| I am trying to secure a database that is accessed
by
| many
| >| users. I have implemented user-level security and
it
| >| appears to be working fine. The bulk of the users
we
| need
| >| to interact with the DB only through the
Switchboard,
| but
| >| there are a couple of users that will need to be
able
| to
| >| hit the tables, queries, etc directly. The
question I
| have
| >| is this:
| >| Since most of the options in the Switchboard run
| update,
| >| insert and delete queries - how can I keep the
users
| from
| >| manually opening and editing the records in the
table
| >| while still giving the permissions needed to allow
the
| >| Switchboard to do it's job?
| >| Any help would be greatly appreciated!
| >|
| >| Thanks,
| >|
| >| Erik
| >|
| >
| >.
| >
|

.

Click to expand...

.

Click to expand...

.

Permissions Problem

Erik

Eric Butts [MSFT]

Erik

Eric Butts [MSFT]

TC

Erik

TC

Erik

Erik