ADP Security Best Practices

[Stefan]

The last days, I was thinking about how to implement Security for my
ADP application.

I was searching this group a bit for best practices regarding security,
but unfortunately didn't find very much. The most interesting threads
for me were these two:
http://groups-beta.google.com/group...w+WITH_METADATA&rnum=1&hl=de#82380dee74da33ff
http://groups-beta.google.com/group....sqlserver/browse_frm/thread/b2a4ae7278ab1e68

Before reading these threads I thought that one would only need to base
the forms on SP's and everything would be nice. But unfortunately
Access updates/inserts the data directly in the table. Well, after a
bit of thinking it seems reasonable that access does it that way. But
it's bad for security

In these threads i think 3 ways of implementing security with adp's
were mentioned:
1)Allow user access to the tables the forms based on via sp's, views or
directly.
-->It seems to me, that this is the easiest to implement, but it
provides no 'real' security.
OK, after all it's as good as the .mdb solution to the problem
2)create updatable views that provide their metadata (WITH METADATA).
Base the forms on these views.
-->I'm not really sure if this one works in all cases. This provides a
bit a better security than 1). Could this also work if the form is
based on a SP that is itself based on the view? Would the form then
store the data through the view?
3)Base forms on sp's and update the data via other sp's
--->looks to me like the de-luxe security implementation. From an
architectural point of view the
only way to go, but it seems to be a real pain to implement this
(only possible with unbound
forms (?)). This somehow kills the whole speed advantage of
developing with access
(correct me if I'm wrong!)


OK, now my question (besides the ones hidden in the text ):
Dear experienced adp - developers, what security mechanisms do you use
for your projects? What are pro's/con's and are there any pitfalls? Did
I forget a possibility to secure the Database?

Thanks a lot for your response! I think this could also help other adp
developers to secure their databases...

 

[aaron.kempf]

sprocs don't help to make it easier/safer.. for the most part

I would just reccomending making sure that people don't get in the db
window-- and they can't build their own views / sprocs for example.

ADP Security = SQL Server Security

I agree, unbound forms is laughable; and I would avoid it like the
plague... I've had some stability problems for sure.

I dont think that updating tables is a security hole; I just think that
you need to either: a) trust your users or b) not build RAD solutions.

Using ASP.net and VB.net and all that other stuff; all it will do is
take 4 times as long to develop.. and you'll probably be left with the
same problems.
To be honest; I usually only bind each for to a single record and a
single table at a time.

I set the recordsource for a form = 'Select * from Employees WHERE
employeeID = 2345'
and then I set 'allowadditons = false' and 'allowDeletions = false' and
'AllowFilters = false' if you really must.

-Aaron

 

[aaron.kempf]

sprocs don't help to make it easier/safer.. for the most part

I would just reccomending making sure that people don't get in the db
window-- and they can't build their own views / sprocs for example.

ADP Security = SQL Server Security

I agree, unbound forms is laughable; and I would avoid it like the
plague... I've had some stability problems for sure.

I dont think that updating tables is a security hole; I just think that
you need to either: a) trust your users or b) not build RAD solutions.

Using ASP.net and VB.net and all that other stuff; all it will do is
take 4 times as long to develop.. and you'll probably be left with the
same problems.
To be honest; I usually only bind each for to a single record and a
single table at a time.

I set the recordsource for a form = 'Select * from Employees WHERE
employeeID = 2345'
and then I set 'allowadditons = false' and 'allowDeletions = false' and
'AllowFilters = false' if you really must.

-Aaron

 

[Sylvain Lafontaine]

Like you said, ADP doesn't really offer more than MDB about security and has
many of the same limitations, like the absence of any INSERT, DELETE or
UPDATE command. There is a Resync command but you are limited to only the
primary key as the parameter and you can't add other parameters (but I might
be wrong on this, as it has been a long time since the last time that I've
tested this). The use of things like a VIEW with either the WITH CHECK
OPTION or with VIEW_METADATA can be used in some situations but there is
nothing there to amaze you; especially when you can do the same with MDB
with linked Tables/Views/Building your own recordset to be used as the
record source.

All in all and excerpt for the fact that it's a little easier to use a SP -
with or without parameters - as the record source of a form or a report and
the better integration between SP and subforms/subreports in comparaison of
using sql pass-through queries; ADP and MDB are practically identical in
there capabilities to interface with SQL-Server and ADP doesn't offer any
real advantage. (Bof, maybe a little speedier because of an heavier use of
SPs but that's all.)

If you take this with the fact that MS is now in the process of cancelling
any tool based on COM/DCOM/ActiveX technologies in favor of the use of .NET
and Web Services, then you can see why MS has aborted any further
development of ADP & ADO and is now suggesting to either use MDB with linked
tables for any basic interfacing against a SQL-Server or to use .NET and Web
Services for more serious, secure or complicated stuff.

In your case, if you need a real security then you will have to use .NET.

 

[aaron.kempf]

there IS a new version of ADO; for the record!!!

and Office Web Components are sure going to be popular for the next 20
years and there isnt' a goddamn thing MS can do to kill it

Microsoft HASNT stopped development of ADP... Office 2007 is going to
support changes to a SQL 2005 database.
Microsoft ISNT reccomending the use of linked tables

and to be honest; linked tables are a maintenance NIGHTMARE.
you have to use the linked table manager; in addition to updating
connection strings for SQL passthrough-- what a ****ing joke.

I just think that your whole premise-- that sprocs make things more
secure-- is ****ing hogwash
We dont need an insert, delete, update COMMAND-- all this does is make
things more complex... with no real benefit

Interfacing with ADP over MDB DOES HAVE MANY ADVANTAGES
Dont listen to these MDB script kiddies

most of the MVPs around here don't know jack shit about SQL Server; and
I would discount 90% of what they say regarding ADP
ADP has a bright future.

MDB is the one that is going away


-Aaron

 

[Stefan]

Thanks for the answers so far.

Moving to .net is no option at the moment. First we move the data to
the server and redefine the datamodel, keeping at least the layout of
forms and reports. The security part is not _that_ important, as it is
an application for internal use only. And we'll be certainly better
off, than with the current solution (unencrypted mdb on a network
drive) .

I've seen many discussions here pro/contra adp. This was already
decided in favor of adp. Therefore I'm not that much interested in
discussing pro/contra adp, but more about making the best of an adp
solution.

OK, you both favor to let forms do their modifications directly in the
tables. Is there anybody who has a different approach?

stefan

 

[Sylvain Lafontaine]

If you're really interested to keep ADP and have a secure environment, then
another solution would be to use unbound forms and make the update yourself.

However, if you are to use unbound forms, I don't see the point of keeping
ADP along; unless you want to have a mixed environment, where both bound and
unbound forms will be used. (But if you are going this last way, I cannot
say that a mixed environment will have the same level of security as one
where only unbound forms will be used; so we might say that going this way
will set you back at the starting case.)

 

[aaron.kempf]

Sylvain

why dont you shut your ****ing trap you ****ing ****

stop trying to discourage people from ADP; it's not 'going away'-- it's
got a better / stronger / brighter future than MDB does

and VB.net takes FOUR TIMES AS LONG TO DEVELOP

ADP is the best environment anywhere.
If you don't agree; why don't you STFU and go to a different channel

-Aaron
ADP Nationalist

 

[Stefan]

Aaron,

I understand Sylvain's point and he is right. If i'd use unbound forms,
I could use .net as well.

But as we don't have the time rewrite the whole application in this
manner,
I guess the only way to go with adp is to grant user access on the
tables.

As I assume there's no secret trick to circumvent this, I can now go on
and
implement the solution with table based rights without thinking that I
do it in
a bad way

Thanks Guys!

Stefan

 

[aaron.kempf]

is Sylvain a GIRL or just a GIRLIE-MAN?

he's trying to steer you away from ADP; he/she can **** himself /
herself

-Aaron

 

[Vadim Rapp]

(4) use application role feature of sql server. Once ADP application starts,
it logs on as application using sp_setapprole, and then has all permissions,
while users don't have them. ADP is compiled and does not show the database
table. Users only have Access Runtime.

This way, only your code is allowed to work with the database; users' hands
are not.

Vadim Rapp

 

[Malcolm Cook]

There are problem with this approach - the main one that does not have a workaround to my knowledge and has precluded me from taking
this approach is that subforms wont work

see http://support.microsoft.com/kb/308312

 

[Sylvain Lafontaine]

I never tried them personally but many people have reported problems trying
to use application role with ADP in this newsgroup in the past (you can
search this newsgroup on Google for some interesting advice, in particular
in those messages posted by Lyle Fairfield).

However, like Mary Chipman has said, using an application role is not really
more secure than using an obfuscated SQL Server Login because in both cases,
the login and the password must be stored in client code and there's
decompilers (and network packets analysers) around here for everything.

Also, the trouble of using AR with ADP (in particular for subforms, filtered
comboboxes and list boxes, reports and subreports) seriously diminish their
usefulness in comparaison with the use of other technologies. You will
chose to use ADP against a SQL-Server because it's simple, not because it's
complicated.

 

[aaron.kempf]

this 'AppRole' uses the 'Application' node in the connectionString.. so
we need to setup a role for 'Microsoft Office' right?

or can you give more information about this.

I've personally never had a drop of problems with permissions; and I
love ADP.

-Aaron