ADPs, Integrated Security and Selective Permissions

[Lyle Fairfield]

There is an MS-SQL table named Bugs_Comments_and_Suggestions.

There is a form named Bugs_Comments_and_Suggestions.

To allow John Doe to use this form, we GRANT him LOGIN and ACCESS permissions
to the db and SELECT permissions on the stored procedure which is the record
source for the BOUND form.

To allow John Doe to "UPDATE, INSERT, DELETE" using the BOUND form, we GRANT
John Doe "UPDATE, INSERT, DELETE" permissions on the table,
Bugs_Comments_and_Suggestions.

Our application enters some unique identifier for John Doe into the table,
Bugs_Comments_and_Suggestions, when he INSERTS a new record, and limits him
to UPDATING and DELETING records with that unique identifier, that is records
which he has created.

SO far so good, but John Doe decides he like Access and one day, when he has
a little time on his hand he decides to create his own ADP. He chooses the
option "Project, Existing Data"; a dialog box appears and he finds that the
MS-SQL server in which the table, Bugs_Comments_and_Suggestions lives is
listed. He chooses that. He chooses Windows Integrated Security, or uses the
SQL logon and the password he has for the application already described. He
finds a list box of Databases and chooses, again, the one where the table,
Bugs_Comments_and_Suggestions, lives. Then he clicks O.K. and up pops the
database window with the tables, including table,
Bugs_Comments_and_Suggestions. He double clicks on this table and it opens in
datasheet view.
We gave him "UPDATE, INSERT, DELETE" on this table and restricted his access
in the application itself.
But now John is in another application. The MS-SQL database does not know
this; it knows only that John has "UPDATE, INSERT, DELETE" permissions on the
table. John chooses one of Jane Doe's Suggestions and edits it to say
something extremely rude about the Department Head.
He then closes and deletes his little ADP.

The next day, Jane Doe's contract is terminated.

Do you have comments? Am I missing something about ADPS and BOUND forms and
their inherent vulnerability?

Yes, I know I could make a table for every John Doe, but multiply the number
of John Does by the number of required tables and one gets quite a large
number of tables, (about 10500 in the application which has prompted me to
write this), and specific permissions to be granted.

And of course, I am not so much worried about one table; many tables could
have this vulnerability.

Well, I'm going to read Robert Vieira's (WROX Professional SQL Server 2000
Programming) chapter on Security now, and I hope that I find that my thinking
is in error, and that I don't have to change my approach on security, or stop
using BOUND forms.

Or maybe someone here will explain how to deal with the problem I have
outlined.

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.

[John Winterbottom]

"Lyle Fairfield" <Lo******@FFDBA.Com> wrote in message
news:Xn*******************@130.133.1.4...

The next day, Jane Doe's contract is terminated.

Do you have comments? Am I missing something about ADPS and BOUND forms and their inherent vulnerability?

AFAIK your interpretation is correct - if you want a user to be able to
update records via a continuous form they need UPDATE permissions on the
underlying table. You could put a trigger on the table to audit who actually
did the update but other than that I don't think there's much else you can
do. The alternative is to use bound forms for vewing only and do all updates
with stored procedures. With very few exceptions that's basically what we
do.

[Steve Jorgensen]

On Tue, 3 Aug 2004 12:00:14 -0400, "John Winterbottom" <as******@hotmail.com>
wrote:

"Lyle Fairfield" <Lo******@FFDBA.Com> wrote in message
news:Xn*******************@130.133.1.4...

The next day, Jane Doe's contract is terminated.

Do you have comments? Am I missing something about ADPS and BOUND forms

and

their inherent vulnerability?

AFAIK your interpretation is correct - if you want a user to be able to
update records via a continuous form they need UPDATE permissions on the
underlying table. You could put a trigger on the table to audit who actually
did the update but other than that I don't think there's much else you can
do. The alternative is to use bound forms for vewing only and do all updates
with stored procedures. With very few exceptions that's basically what we
do.

Actually, it's quite possible to have the trigger raise an error and roll back
the action if the user doing the update is not the same as the user indicated
in the record. ADPs are designed to move security to the back-end, so that's
where you have to do it. Fortunately, it's usually doable.

[XMVP]

"Lyle Fairfield" <Lo******@FFDBA.Com> wrote in message
news:Xn*******************@130.133.1.4...

There is an MS-SQL table named Bugs_Comments_and_Suggestions.

There is a form named Bugs_Comments_and_Suggestions.

To allow John Doe to use this form, we GRANT him LOGIN and ACCESS permissions to the db and SELECT permissions on the stored procedure which is the record source for the BOUND form.

To allow John Doe to "UPDATE, INSERT, DELETE" using the BOUND form, we GRANT John Doe "UPDATE, INSERT, DELETE" permissions on the table,
Bugs_Comments_and_Suggestions.

Our application enters some unique identifier for John Doe into the table,
Bugs_Comments_and_Suggestions, when he INSERTS a new record, and limits him to UPDATING and DELETING records with that unique identifier, that is records which he has created.

SO far so good, but John Doe decides he like Access and one day, when he has a little time on his hand he decides to create his own ADP. He chooses the
option "Project, Existing Data"; a dialog box appears and he finds that the MS-SQL server in which the table, Bugs_Comments_and_Suggestions lives is
listed. He chooses that. He chooses Windows Integrated Security, or uses the SQL logon and the password he has for the application already described. He finds a list box of Databases and chooses, again, the one where the table,
Bugs_Comments_and_Suggestions, lives. Then he clicks O.K. and up pops the
database window with the tables, including table,
Bugs_Comments_and_Suggestions. He double clicks on this table and it opens in datasheet view.
We gave him "UPDATE, INSERT, DELETE" on this table and restricted his access in the application itself.
But now John is in another application. The MS-SQL database does not know
this; it knows only that John has "UPDATE, INSERT, DELETE" permissions on the table. John chooses one of Jane Doe's Suggestions and edits it to say
something extremely rude about the Department Head.
He then closes and deletes his little ADP.

The next day, Jane Doe's contract is terminated.

Do you have comments? Am I missing something about ADPS and BOUND forms and their inherent vulnerability?

Yes, I know I could make a table for every John Doe, but multiply the number of John Does by the number of required tables and one gets quite a large
number of tables, (about 10500 in the application which has prompted me to
write this), and specific permissions to be granted.

And of course, I am not so much worried about one table; many tables could
have this vulnerability.

Well, I'm going to read Robert Vieira's (WROX Professional SQL Server 2000
Programming) chapter on Security now, and I hope that I find that my thinking is in error, and that I don't have to change my approach on security, or stop using BOUND forms.

Or maybe someone here will explain how to deal with the problem I have
outlined.

--
Lyle

You could reinstate Jane Doe's contract, but you're probably better off
without her because it's just a matter of time before she sues you.

[Lyle Fairfield]

"John Winterbottom" <as******@hotmail.com> wrote in
news:2n************@uni-berlin.de:

"Lyle Fairfield" <Lo******@FFDBA.Com> wrote in message
news:Xn*******************@130.133.1.4...

The next day, Jane Doe's contract is terminated.

Do you have comments? Am I missing something about ADPS and BOUND forms

and

their inherent vulnerability?

AFAIK your interpretation is correct - if you want a user to be able to
update records via a continuous form they need UPDATE permissions on the
underlying table. You could put a trigger on the table to audit who
actually did the update but other than that I don't think there's much
else you can do. The alternative is to use bound forms for vewing only
and do all updates with stored procedures. With very few exceptions
that's basically what we do.

Thanks, John. I find this quite troubling. If we are editing using stored
procedures, (rather than bound forms), then the user could see and run
those procedures from another ADP. I'm wondering how likely it is that he
could or would then do something foolish or deliberately destructive.

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.

[Lyle Fairfield]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:iq********************************@4ax.com:

On Tue, 3 Aug 2004 12:00:14 -0400, "John Winterbottom"
<as******@hotmail.com> wrote:

"Lyle Fairfield" <Lo******@FFDBA.Com> wrote in message
news:Xn*******************@130.133.1.4...

The next day, Jane Doe's contract is terminated.

Do you have comments? Am I missing something about ADPS and BOUND
forms

and

their inherent vulnerability?

AFAIK your interpretation is correct - if you want a user to be able to
update records via a continuous form they need UPDATE permissions on the
underlying table. You could put a trigger on the table to audit who
actually did the update but other than that I don't think there's much
else you can do. The alternative is to use bound forms for vewing only
and do all updates with stored procedures. With very few exceptions
that's basically what we do.

Actually, it's quite possible to have the trigger raise an error and
roll back the action if the user doing the update is not the same as the
user indicated in the record. ADPs are designed to move security to the
back-end, so that's where you have to do it. Fortunately, it's usually
doable.

But from the new ADP, the user could create a new record using whatver
identity he wanted?

I guess I'll study the trigger solution a bit more intensely, regardless.

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.

[John Winterbottom]

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:iq********************************@4ax.com...

On Tue, 3 Aug 2004 12:00:14 -0400, "John Winterbottom" <as******@hotmail.com> wrote:

"Lyle Fairfield" <Lo******@FFDBA.Com> wrote in message
news:Xn*******************@130.133.1.4...

The next day, Jane Doe's contract is terminated.

Do you have comments? Am I missing something about ADPS and BOUND formsand

their inherent vulnerability?

AFAIK your interpretation is correct - if you want a user to be able to
update records via a continuous form they need UPDATE permissions on the
underlying table. You could put a trigger on the table to audit who actuallydid the update but other than that I don't think there's much else you cando. The alternative is to use bound forms for vewing only and do all updateswith stored procedures. With very few exceptions that's basically what we
do.

Actually, it's quite possible to have the trigger raise an error and roll

back the action if the user doing the update is not the same as the user indicated in the record.

Yes that's another option, but it adds a lot of complexity. And it means you
need a user column in every table. Better to rely on sql server permissions
and use stored procedures exclusively for updating data IMO.

Also, just my personal preference, but I try to avoid triggers for enforcing
business rules and especially for enforcing security. I've found they're not
very "visible" and you can end up forgetting about them - I have spent many
hours debugging problems that were caused by a sneaky hidden trigger.

[Nicole]

Clearly your users are much smarter than mine. It helps to be
reminded occassionally that curious and dangerously informed users can
be MUCH worse than babysitting :)

I think you are correct that relying on anything in Access for
security is leaving loopholes open. You may want to consider using
Views and granting permissions to those instead of to the tables
themselves. This does increase your security options. I don't think
that this is a security issue with bound forms, since it doesn't stop
a user from connecting directly to the tables via Access.

It does make one think that runtime versions would be better, but that
doesn't prevent a user from installing Access (if they have insall
priviledges) and doing the same thing.

To resolve this particular issue: First, is there a good reason for
the users to have UPDATE and DELETE priviledges, even if only on their
own comments?

If so, then I would consider a separate table which tracks
modification dates and times and the user ID via UPDATE trigger on the
Bugs_Comments_and_Suggestions table. You may also wish to use the
same insert statement into the tracking table to track INSERTs and
DELETEs with triggers. These tables should be restricted to SA only.

I have many tables with critical information that have a DELETE
trigger which saves the deleted info to a near duplicate table (i.e.
Table and Table_Deleted) that only the sa has priviledges to. It has
saved my butt a few times since it is quick and easy to extract the
data -- and who deleted it -- when the situation arises.

Alternately, you could assign permissions at the column level, and
include a modification date and time column that the users do not have
access to. This only stores the last change, though.

Lyle Fairfield <Lo******@FFDBA.Com> wrote in message news:<Xn*******************@130.133.1.4>...

There is an MS-SQL table named Bugs_Comments_and_Suggestions.

There is a form named Bugs_Comments_and_Suggestions.

To allow John Doe to use this form, we GRANT him LOGIN and ACCESS permissions
to the db and SELECT permissions on the stored procedure which is the record
source for the BOUND form.

To allow John Doe to "UPDATE, INSERT, DELETE" using the BOUND form, we GRANT
John Doe "UPDATE, INSERT, DELETE" permissions on the table,
Bugs_Comments_and_Suggestions.

Our application enters some unique identifier for John Doe into the table,
Bugs_Comments_and_Suggestions, when he INSERTS a new record, and limits him
to UPDATING and DELETING records with that unique identifier, that is records
which he has created.

SO far so good, but John Doe decides he like Access and one day, when he has
a little time on his hand he decides to create his own ADP. He chooses the
option "Project, Existing Data"; a dialog box appears and he finds that the
MS-SQL server in which the table, Bugs_Comments_and_Suggestions lives is
listed. He chooses that. He chooses Windows Integrated Security, or uses the
SQL logon and the password he has for the application already described. He
finds a list box of Databases and chooses, again, the one where the table,
Bugs_Comments_and_Suggestions, lives. Then he clicks O.K. and up pops the
database window with the tables, including table,
Bugs_Comments_and_Suggestions. He double clicks on this table and it opens in
datasheet view.
We gave him "UPDATE, INSERT, DELETE" on this table and restricted his access
in the application itself.
But now John is in another application. The MS-SQL database does not know
this; it knows only that John has "UPDATE, INSERT, DELETE" permissions on the
table. John chooses one of Jane Doe's Suggestions and edits it to say
something extremely rude about the Department Head.
He then closes and deletes his little ADP.

The next day, Jane Doe's contract is terminated.

Do you have comments? Am I missing something about ADPS and BOUND forms and
their inherent vulnerability?

Yes, I know I could make a table for every John Doe, but multiply the number
of John Does by the number of required tables and one gets quite a large
number of tables, (about 10500 in the application which has prompted me to
write this), and specific permissions to be granted.

And of course, I am not so much worried about one table; many tables could
have this vulnerability.

Well, I'm going to read Robert Vieira's (WROX Professional SQL Server 2000
Programming) chapter on Security now, and I hope that I find that my thinking
is in error, and that I don't have to change my approach on security, or stop
using BOUND forms.

Or maybe someone here will explain how to deal with the problem I have
outlined.

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date

[Lyle Fairfield]

Lyle Fairfield <Lo******@FFDBA.Com> wrote in
news:Xn*******************@130.133.1.4:

Well, I'm going to read Robert Vieira's (WROX Professional SQL Server
2000 Programming) chapter on Security now, and I hope that I find that
my thinking is in error, and that I don't have to change my approach on
security, or stop using BOUND forms.

Thanks to everyone who replied. Here's my current plan.

I'm going to:

1. revoke all John Doe's permissions, but not his login or db access;
2. create an application role, "Whomever" with a password, "Whatever";
3. grant, to the application role, the permissions I removed from John Doe;
4. when John Doe opens the ADP, run code that sets (or activates) the
approle,(this restricts the CONNECTION to the context of the approle),
together with the encrypted password (I'm planning to use the CryptAPI in
the code so that the password is not openly avilable using some hex
browser);
5. Now John Doe will have the permissions of the approle, but only while he
is using the ADP which in itself determines what John can do; if he opens
the db in another context, say another ADP, he has no permissions at all.

That's it folks; maybe I'll be able to sleep tonight and get going on this
in the morning. Unless of course someone has an "UH OH" for me.

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.

[Nicole]

> I'm going to:

1. revoke all John Doe's permissions, but not his login or db access;
2. create an application role, "Whomever" with a password, "Whatever";
3. grant, to the application role, the permissions I removed from John Doe;
4. when John Doe opens the ADP, run code that sets (or activates) the
approle,(this restricts the CONNECTION to the context of the approle),
together with the encrypted password (I'm planning to use the CryptAPI in
the code so that the password is not openly avilable using some hex
browser);
5. Now John Doe will have the permissions of the approle, but only while he
is using the ADP which in itself determines what John can do; if he opens
the db in another context, say another ADP, he has no permissions at all.

That's it folks; maybe I'll be able to sleep tonight and get going on this
in the morning. Unless of course someone has an "UH OH" for me.

Nice solution. Let us know how it works out.

[Bradley.-.]

"Lyle Fairfield" <Lo******@FFDBA.Com> wrote in message
news:Xn*******************@130.133.1.4

There is an MS-SQL table named Bugs_Comments_and_Suggestions.

There is a form named Bugs_Comments_and_Suggestions.

To allow John Doe to use this form, we GRANT him LOGIN and ACCESS
permissions to the db and SELECT permissions on the stored procedure
which is the record source for the BOUND form.

To allow John Doe to "UPDATE, INSERT, DELETE" using the BOUND form,
we GRANT John Doe "UPDATE, INSERT, DELETE" permissions on the table,
Bugs_Comments_and_Suggestions.

Our application enters some unique identifier for John Doe into the
table, Bugs_Comments_and_Suggestions, when he INSERTS a new record,
and limits him to UPDATING and DELETING records with that unique
identifier, that is records which he has created.

SO far so good, but John Doe decides he like Access and one day, when
he has a little time on his hand he decides to create his own ADP. He
chooses the option "Project, Existing Data"; a dialog box appears and
he finds that the MS-SQL server in which the table,
Bugs_Comments_and_Suggestions lives is listed. He chooses that. He
chooses Windows Integrated Security, or uses the SQL logon and the
password he has for the application already described. He finds a
list box of Databases and chooses, again, the one where the table,
Bugs_Comments_and_Suggestions, lives. Then he clicks O.K. and up pops
the database window with the tables, including table,
Bugs_Comments_and_Suggestions. He double clicks on this table and it
opens in datasheet view.
We gave him "UPDATE, INSERT, DELETE" on this table and restricted his
access in the application itself.
But now John is in another application. The MS-SQL database does not
know this; it knows only that John has "UPDATE, INSERT, DELETE"
permissions on the table. John chooses one of Jane Doe's Suggestions
and edits it to say something extremely rude about the Department
Head.
He then closes and deletes his little ADP.

The next day, Jane Doe's contract is terminated.

Do you have comments? Am I missing something about ADPS and BOUND
forms and their inherent vulnerability?

Yes, I know I could make a table for every John Doe, but multiply the
number of John Does by the number of required tables and one gets
quite a large number of tables, (about 10500 in the application which
has prompted me to write this), and specific permissions to be
granted.

And of course, I am not so much worried about one table; many tables
could have this vulnerability.

Well, I'm going to read Robert Vieira's (WROX Professional SQL Server
2000 Programming) chapter on Security now, and I hope that I find
that my thinking is in error, and that I don't have to change my
approach on security, or stop using BOUND forms.

Or maybe someone here will explain how to deal with the problem I have
outlined.

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date

First, I'd be using stored procedures and views... no table access at
all.

Next, what identifier are you storing with the record?

The easiest way I can think of is to use a stored procudure to add new
records that automatically stores the current username in the record
(SUSER_NAME()).

The stored procedure/view to view/edit the data then check this record
against the current username.

eg. SELECT tblMyTable.Field1, tblMyTable.Field2, tblMyTable.Field3 FROM
tblMyTable WHERE tblMyTable.MyUser = SUSER_NAME()

No matter how these stored procedure/views are accessed the security
works.

Hope that gives you a good start...
--
regards,

Bradley

[Bradley.-.]

"Lyle Fairfield" <Lo******@FFDBA.Com> wrote in message
news:Xn*******************@130.133.1.4

"John Winterbottom" <as******@hotmail.com> wrote in
news:2n************@uni-berlin.de:

"Lyle Fairfield" <Lo******@FFDBA.Com> wrote in message
news:Xn*******************@130.133.1.4...

The next day, Jane Doe's contract is terminated.

Do you have comments? Am I missing something about ADPS and BOUND
forms and their inherent vulnerability?

AFAIK your interpretation is correct - if you want a user to be able
to update records via a continuous form they need UPDATE permissions
on the underlying table. You could put a trigger on the table to
audit who actually did the update but other than that I don't think
there's much else you can do. The alternative is to use bound forms
for vewing only and do all updates with stored procedures. With very
few exceptions that's basically what we do.

Thanks, John. I find this quite troubling. If we are editing using
stored procedures, (rather than bound forms), then the user could see
and run those procedures from another ADP. I'm wondering how likely
it is that he could or would then do something foolish or
deliberately destructive.

--
Lyle

Build your stored prociedure so that it won't matter... they should be
able to be called from anywhere and still protect the data.

Else, maybe look at using Application Roles?

--
regards,

Bradley

[Lyle Fairfield]

ni*****@namg.com (Nicole) wrote in
news:7f**************************@posting.google.c om:

Nice solution. Let us know how it works out.

Well, it's been a bit of a struggle but I'm hanging in there.

The good news is that if John Doe has no permissions other than login and
dbaccess he can't see any SQL objects in the database window, not only when
he logs in, but also after the application sets the approle.

The bad news is that not only can't John see any objects, neither can
Access forms or reports see any SQL objects. It seems that when we use
"spGetSchools" (stored procedure) as the record source of an Access form or
report, Access checks the database window for the existence of
"spGetSchools" and it doesn't find it. Arggggggggggggggggh. This seems to
be because Access maintains three(I thought it was two but it seems that it
is three) connections to the db, one for the db window, one for sql
execution and one for combo and list boxes. And setting the approle does
not replace the first one, for the dbwindow, it simply removes or disables
it, while it does replace the second two. Well, maybe this is a FEATURE!,
because, as I pointed out previously, John Doe cannot see any SQL objects
now, (although a form with a connection than can populate a list box but
not with a connection that can populate the form itself seems a bit
strange.)

So how to make the bound forms and reports work (my original pursuit)?
Well, this I just discovered. We use Connection 2. How? It's pretty simple.
Use an SQL statement instead of the bald SQL object name. For
"spGetSchools" that would be "EXEC spGetSchools". For a View, Table, or
Table Returning Function it would be "SELECT * FROM Table, View or UDF".

Well great, it's midnight and I THINK I have my problem solved. Actually I
think it may turn out to be a terrific solution, but I sure wish I had
known about and planned for this from the beginning.
I'll try putting it all together tomorrow.
Ain't Access GRAND?

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.

[Trevor Best]

Lyle Fairfield wrote:

[snip]

Or you could google:

http://groups.google.com/groups?as_e...=lang_en&hl=en

http://tinyurl.com/4zfap

Not sure what that turns up, maybe more questions than answers (maybe
more people telling other people to google <g>)

My suggestion would be to store username on insert and check that
against logged in user name (system_user) in update and delete triggers.

Or, the preferred (by many SQL Server gurus) is to use views for viewing
data and stored procedures for updating, John Doe would have no
permissions on the table itself.

In Access you can set "with owner option" on a query to
query/update/delete a table that the user doesn't have permission. In
the same sense, a stored procedure runs in the context of the user who
created it, if you wanted table operations to run in the context of the
user running it you have to use dynamic SQL in the SP, e.g.

exec sp_executesql 'update blah'

HTH

--
Error reading sig - A)bort R)etry I)nfluence with large hammer

[Trevor Best]

John Winterbottom wrote:

Also, just my personal preference, but I try to avoid triggers for enforcing
business rules and especially for enforcing security. I've found they're not
very "visible" and you can end up forgetting about them - I have spent many
hours debugging problems that were caused by a sneaky hidden trigger.

Yes they are forgettable, triggers on views in particular, I scripted a
database using "SQL Compare" and had inadvertently left the "use SQL
Server 7" syntax checked, consequently the new database lost all SQL
Server 2000 features including triggers in views, in this case an
"instead of" trigger that redirected the updates to the base tables as
SQL Server didn't allow updates to the view as it affacted multiple tables.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer

[Lyle Fairfield]

Lyle Fairfield <Lo******@FFDBA.Com> wrote in
news:Xn*****************@130.133.1.4:

So how to make the bound forms and reports work (my original pursuit)?
Well, this I just discovered. We use Connection 2. How? It's pretty
simple. Use an SQL statement instead of the bald SQL object name. For
"spGetSchools" that would be "EXEC spGetSchools". For a View, Table, or
Table Returning Function it would be "SELECT * FROM Table, View or UDF".

Well, now the next hurdle... It seems that sometimes, Access can use form
and report input parameters with "EXEC Some_Stored_Procedure" and sometimes
it cannot. mostly, in my very limited experience it cannot.
This erratic behaviour isn't wonderfully helpful.
In its paper discussing App Roles and Access MS says

"Unlike with other database objects, Access does not always use the same
connection to retrieve the data source of a subform. Access frequently (but
not always) creates a new connection to SQL Server just to handle the
subform recordset, or to retrieve the linking field data that connects the
subform to the main form. Because this new connection does not have the
application role applied, the user may receive a permissions error if the
user does not have explicit permissions to the database object.
Unfortunately, this means that there is no reliable way to use bound
subforms when application roles are applied. The only effective workaround
is to have completely unbound subforms, with the data manipulation handled
programmatically. This is the most serious limitation when you use
application roles in Access."

I don't use subforms per se, preferring to roll my own "sub" forms. But
this sometimes one connection, sometime another, really idiotic behaviour
seems to occur in those roll your own sub forms and in forms in general. I
have two almost identical forms with input parameters. One works fine. The
other won't accept the input parameters. I'm beginning to think, after a
couple of days wasted on this that App Roles are quite a bit like something
that rhymes. And I'm getting less and less enthusiastic about ADPs.

Maybe tomorrow will be brighter.

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.

[Nicole]

> Well, now the next hurdle... It seems that sometimes, Access can use form

and report input parameters with "EXEC Some_Stored_Procedure" and sometimes
it cannot. mostly, in my very limited experience it cannot.
This erratic behaviour isn't wonderfully helpful.
In its paper discussing App Roles and Access MS says

"Unlike with other database objects, Access does not always use the same
connection to retrieve the data source of a subform. Access frequently (but
not always) creates a new connection to SQL Server just to handle the
subform recordset, or to retrieve the linking field data that connects the
subform to the main form. Because this new connection does not have the
application role applied, the user may receive a permissions error if the
user does not have explicit permissions to the database object.
Unfortunately, this means that there is no reliable way to use bound
subforms when application roles are applied. The only effective workaround
is to have completely unbound subforms, with the data manipulation handled
programmatically. This is the most serious limitation when you use
application roles in Access."

Although I do like ADP's because they do many things well and make
them easy, the above says in a nutshell the basic problem with them --
inconsistant behavior. This could be acceptable if you knew WHEN
Access was going to behave one way and when it was going to behave the
other, but you never do. Notice that MS doesn't outline above when it
will create a new connection and when it will not.

Nor is there the ability to explicitly set all the available options
for the default connection, which would solve this particular issue
with Application Roles. (At least not that I know of.)

Thanks for keeping us posted, Lyle.