ADE Security flaw - import of database, how to prevent?

[giovi2002]

Ade files have an important security flaw when SQL server authentication is being used.
When the ade resides in a read-only directory users can still get easy access to all the data, how?
1). make a new database
2). use the import command in the file menu
3). choose the ade file

They can't import anything (no forms or code) except ALL of the table definitions and data.
How is this 'stupidity' possible, better question:
how can you keep users away from doing this?

I've tried autoexec macro but autoexec is being handled after the sql authentication event. I'm not using direct connections to my tables in forms or reports but always use views and stored procedures. I've heard that microsoft said you should revoke select permission on the tables to the user logging in on your client but then my views and sp's fail.

Please help me out

 

[danvlas]

Set the appropriate permissions on views and sp's rather than on tables?

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ESquared]

No, your views and SPs won't fail if you revoke table permission. Views do their thing no matter what the underlying table's permissions are. SPs run with dbo permissions (except dynamic SQL which runs with the user's permissions).

 

[giovi2002]

You have to use updatable views .
Simply don't grant any 'user' or 'group' permissions on the base tables.
Second make updatable views like this

Create view dbo.myview
with view_metadata
AS
etc.

Grant permissions in your views.
Use the views like you would use tables in your stored procedures.
Add execute permissions to the stored procedures.

As you see it costs some extra authorisation time but that will fix the hole