ADPs, Integrated Security and Selective Permissions

[Lyle Fairfield]

There is an MS-SQL table named Bugs_Comments_a nd_Suggestions.

There is a form named Bugs_Comments_a nd_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_a nd_Suggestions.

Our application enters some unique identifier for John Doe into the table,
Bugs_Comments_a nd_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_a nd_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_a nd_Suggestions, lives. Then he clicks O.K. and up pops the
database window with the tables, including table,
Bugs_Comments_a nd_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******@hotma il.com>
wrote:

"Lyle Fairfield" <Lo******@FFDBA .Com> wrote in message
news:Xn******* ************@13 0.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_a nd_Suggestions.

There is a form named Bugs_Comments_a nd_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_a nd_Suggestions.

Our application enters some unique identifier for John Doe into the table,
Bugs_Comments_a nd_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_a nd_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_a nd_Suggestions, lives. Then he clicks O.K. and up pops the
database window with the tables, including table,
Bugs_Comments_a nd_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******@hotma il.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.c om:

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

"Lyle Fairfield" <Lo******@FFDBA .Com> wrote in message
news:Xn****** *************@1 30.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.c om...

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

"Lyle Fairfield" <Lo******@FFDBA .Com> wrote in message
news:Xn******* ************@13 0.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_a nd_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******* ************@13 0.133.1.4>...

There is an MS-SQL table named Bugs_Comments_a nd_Suggestions.

There is a form named Bugs_Comments_a nd_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_a nd_Suggestions.

Our application enters some unique identifier for John Doe into the table,
Bugs_Comments_a nd_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_a nd_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_a nd_Suggestions, lives. Then he clicks O.K. and up pops the
database window with the tables, including table,
Bugs_Comments_a nd_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.