ADPs, Integrated Security and Selective Permissions

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.

re: ADPs, Integrated Security and Selective Permissions

"Lyle Fairfield" <LookItUp@FFDBA.Com> wrote in message
news:Xns953A6CC6985B7FFDBA@130.133.1.4...[color=blue]
>
> The next day, Jane Doe's contract is terminated.
>
> Do you have comments? Am I missing something about ADPS and BOUND forms[/color]
and[color=blue]
> their inherent vulnerability?
>[/color]

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.

re: ADPs, Integrated Security and Selective Permissions

On Tue, 3 Aug 2004 12:00:14 -0400, "John Winterbottom" <assaynet@hotmail.com>
wrote:
[color=blue]
>"Lyle Fairfield" <LookItUp@FFDBA.Com> wrote in message
>news:Xns953A6CC6985B7FFDBA@130.133.1.4...[color=green]
>>
>> The next day, Jane Doe's contract is terminated.
>>
>> Do you have comments? Am I missing something about ADPS and BOUND forms[/color]
>and[color=green]
>> their inherent vulnerability?
>>[/color]
>
>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.
>
>[/color]

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.

re: ADPs, Integrated Security and Selective Permissions

"Lyle Fairfield" <LookItUp@FFDBA.Com> wrote in message
news:Xns953A6CC6985B7FFDBA@130.133.1.4...[color=blue]
> 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[/color]
permissions[color=blue]
> to the db and SELECT permissions on the stored procedure which is the[/color]
record[color=blue]
> source for the BOUND form.
>
> To allow John Doe to "UPDATE, INSERT, DELETE" using the BOUND form, we[/color]
GRANT[color=blue]
> 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[/color]
him[color=blue]
> to UPDATING and DELETING records with that unique identifier, that is[/color]
records[color=blue]
> which he has created.
>
> SO far so good, but John Doe decides he like Access and one day, when he[/color]
has[color=blue]
> 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[/color]
the[color=blue]
> MS-SQL server in which the table, Bugs_Comments_and_Suggestions lives is
> listed. He chooses that. He chooses Windows Integrated Security, or uses[/color]
the[color=blue]
> SQL logon and the password he has for the application already described.[/color]
He[color=blue]
> 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[/color]
in[color=blue]
> datasheet view.
> We gave him "UPDATE, INSERT, DELETE" on this table and restricted his[/color]
access[color=blue]
> 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[/color]
the[color=blue]
> 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[/color]
and[color=blue]
> their inherent vulnerability?
>
> Yes, I know I could make a table for every John Doe, but multiply the[/color]
number[color=blue]
> 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[/color]
thinking[color=blue]
> is in error, and that I don't have to change my approach on security, or[/color]
stop[color=blue]
> using BOUND forms.
>
> Or maybe someone here will explain how to deal with the problem I have
> outlined.
>
> --
> Lyle[/color]


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.

re: ADPs, Integrated Security and Selective Permissions

"John Winterbottom" <assaynet@hotmail.com> wrote in
news:2n9r4rFur6vkU1@uni-berlin.de:
[color=blue]
> "Lyle Fairfield" <LookItUp@FFDBA.Com> wrote in message
> news:Xns953A6CC6985B7FFDBA@130.133.1.4...[color=green]
>>
>> The next day, Jane Doe's contract is terminated.
>>
>> Do you have comments? Am I missing something about ADPS and BOUND forms[/color]
> and[color=green]
>> their inherent vulnerability?
>>[/color]
>
> 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.[/color]

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.

re: ADPs, Integrated Security and Selective Permissions

Steve Jorgensen <nospam@nospam.nospam> wrote in
news:iqevg09s68fql13npt84u8ibeemuasomm7@4ax.com:
[color=blue]
> On Tue, 3 Aug 2004 12:00:14 -0400, "John Winterbottom"
> <assaynet@hotmail.com> wrote:
>[color=green]
>>"Lyle Fairfield" <LookItUp@FFDBA.Com> wrote in message
>>news:Xns953A6CC6985B7FFDBA@130.133.1.4...[color=darkred]
>>>
>>> The next day, Jane Doe's contract is terminated.
>>>
>>> Do you have comments? Am I missing something about ADPS and BOUND
>>> forms[/color]
>>and[color=darkred]
>>> their inherent vulnerability?
>>>[/color]
>>
>>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.
>>
>>[/color]
>
> 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.[/color]

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.

re: ADPs, Integrated Security and Selective Permissions

"Steve Jorgensen" <nospam@nospam.nospam> wrote in message
news:iqevg09s68fql13npt84u8ibeemuasomm7@4ax.com...[color=blue]
> On Tue, 3 Aug 2004 12:00:14 -0400, "John Winterbottom"[/color]
<assaynet@hotmail.com>[color=blue]
> wrote:
>[color=green]
> >"Lyle Fairfield" <LookItUp@FFDBA.Com> wrote in message
> >news:Xns953A6CC6985B7FFDBA@130.133.1.4...[color=darkred]
> >>
> >> The next day, Jane Doe's contract is terminated.
> >>
> >> Do you have comments? Am I missing something about ADPS and BOUND forms[/color]
> >and[color=darkred]
> >> their inherent vulnerability?
> >>[/color]
> >
> >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[/color][/color]
actually[color=blue][color=green]
> >did the update but other than that I don't think there's much else you[/color][/color]
can[color=blue][color=green]
> >do. The alternative is to use bound forms for vewing only and do all[/color][/color]
updates[color=blue][color=green]
> >with stored procedures. With very few exceptions that's basically what we
> >do.
> >
> >[/color]
>
> Actually, it's quite possible to have the trigger raise an error and roll[/color]
back[color=blue]
> the action if the user doing the update is not the same as the user[/color]
indicated[color=blue]
> in the record.[/color]

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.

re: ADPs, Integrated Security and Selective Permissions

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 <LookItUp@FFDBA.Com> wrote in message news:<Xns953A6CC6985B7FFDBA@130.133.1.4>...[color=blue]
> 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[/color]

re: ADPs, Integrated Security and Selective Permissions

Lyle Fairfield <LookItUp@FFDBA.Com> wrote in
news:Xns953A6CC6985B7FFDBA@130.133.1.4:
[color=blue]
> 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.[/color]

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.

re: ADPs, Integrated Security and Selective Permissions

> I'm going to:[color=blue]
>
> 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.[/color]

Nice solution. Let us know how it works out.

re: ADPs, Integrated Security and Selective Permissions

"Lyle Fairfield" <LookItUp@FFDBA.Com> wrote in message
news:Xns953A6CC6985B7FFDBA@130.133.1.4[color=blue]
> 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[/color]


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

re: ADPs, Integrated Security and Selective Permissions

"Lyle Fairfield" <LookItUp@FFDBA.Com> wrote in message
news:Xns953A85B313712FFDBA@130.133.1.4[color=blue]
> "John Winterbottom" <assaynet@hotmail.com> wrote in
> news:2n9r4rFur6vkU1@uni-berlin.de:
>[color=green]
>> "Lyle Fairfield" <LookItUp@FFDBA.Com> wrote in message
>> news:Xns953A6CC6985B7FFDBA@130.133.1.4...[color=darkred]
>>>
>>> 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?
>>>[/color]
>>
>> 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.[/color]
>
> 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[/color]

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

re: ADPs, Integrated Security and Selective Permissions

nicolec@namg.com (Nicole) wrote in
news:7faf54fd.0408041051.3a79f385@posting.google.c om:
[color=blue]
> Nice solution. Let us know how it works out.[/color]

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.

re: ADPs, Integrated Security and Selective Permissions

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

re: ADPs, Integrated Security and Selective Permissions

John Winterbottom wrote:
[color=blue]
> 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.[/color]

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

re: ADPs, Integrated Security and Selective Permissions

Lyle Fairfield <LookItUp@FFDBA.Com> wrote in
news:Xns953C111A403FFDBA@130.133.1.4:
[color=blue]
> 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".[/color]

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.

re: ADPs, Integrated Security and Selective Permissions

> Well, now the next hurdle... It seems that sometimes, Access can use form[color=blue]
> 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."[/color]

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.