Very Computer

Hello-

This message is in regard to the situation described in my earlier post
of Thursday, 9/29/00, called "Changing data in View requires Table
SELECT Perm?", but I would like to resubmit the matter in a different
way.  It is not necessary to read the earlier post.  After corresponding
in some other newsgroups and speaking with several people at Microsoft
Product Support, I am led to believe that there is a major problem with
the Access 2K Project tool.  Simply put, I am told that contrary to what
common sense would would lead one to believe there is no way within the
resources of an Access Project to prevent a user from simply importing
all the raw data he or she would like to have by simply importing tables
out of any ADP or ADE file into a new Access file.  If anyone HAS
successfully prevented the import of tables from an Access Project,
PLEASE could you respond to this post?  Otherwise, this new tool has
about as much security as a cereal box, and should be marketed with a
big label on the front that says "DOES NOT WORK".

I am specifically calling out to anyone, if such people exist,  who has
first hand experience successfully deploying an ADE that cannot have its
tables extracted by simple importing.  With all due respect, if you're
not familiar with working in an Access Project, please do not respond
simply assuming that it behaves the way it should.  The standard SQL
Server method of calling data from only Views and Stored Procedures and
then denying permissions on the table DOES NOT WORK.  Microsoft Product
Support has confirmed to me that it SHOULD and that it DOES NOT.

The team here has just spent nine months porting a large database from
an MDE frontend / MDB backend format to the new ADE frontend / Sql
Server 7 backend format.  And if I'm now told that this supposedly
sophisticated, state-of-the-art, easy-to-use database tool cannot even
prevent users from extracting all raw table data as easily as peeling an
orange, then you'll understand my being, to put it mildly, upset.  If I
am forced to release a front end that anyone can extract tables from, I
could well lose my job over this.

If anyone has successfully deployed an ADE from which the tables cannot
be imported, I would appreciate your letting me know, as would those who
depend on me for their livelihood.

Thank you.

-Paul

The information you have received from Microsoft is correct, ADP/ADE does
not work...

Let me suggest that you try this with MDB/MDE.  That is what we decided to
do after I read an article by Mary Chipman in one of the trade publications.
I hate to drop names like that, but it really opened my eyes at an early
point in our development.

MDB files will respect the permissions granted/denied on the server.  That
is ONE of the biggest problems found in ADPs.

The bottom line is....don't go any further with ADP until Microsoft fixes
it, but MDB files WILL WORK.

Joe

Quote:> Hello-

> This message is in regard to the situation described in my earlier post
> of Thursday, 9/29/00, called "Changing data in View requires Table
> SELECT Perm?", but I would like to resubmit the matter in a different
> way.  It is not necessary to read the earlier post.  After corresponding
> in some other newsgroups and speaking with several people at Microsoft
> Product Support, I am led to believe that there is a major problem with
> the Access 2K Project tool.  Simply put, I am told that contrary to what
> common sense would would lead one to believe there is no way within the
> resources of an Access Project to prevent a user from simply importing
> all the raw data he or she would like to have by simply importing tables
> out of any ADP or ADE file into a new Access file.  If anyone HAS
> successfully prevented the import of tables from an Access Project,
> PLEASE could you respond to this post?  Otherwise, this new tool has
> about as much security as a cereal box, and should be marketed with a
> big label on the front that says "DOES NOT WORK".

> I am specifically calling out to anyone, if such people exist,  who has
> first hand experience successfully deploying an ADE that cannot have its
> tables extracted by simple importing.  With all due respect, if you're
> not familiar with working in an Access Project, please do not respond
> simply assuming that it behaves the way it should.  The standard SQL
> Server method of calling data from only Views and Stored Procedures and
> then denying permissions on the table DOES NOT WORK.  Microsoft Product
> Support has confirmed to me that it SHOULD and that it DOES NOT.

> The team here has just spent nine months porting a large database from
> an MDE frontend / MDB backend format to the new ADE frontend / Sql
> Server 7 backend format.  And if I'm now told that this supposedly
> sophisticated, state-of-the-art, easy-to-use database tool cannot even
> prevent users from extracting all raw table data as easily as peeling an
> orange, then you'll understand my being, to put it mildly, upset.  If I
> am forced to release a front end that anyone can extract tables from, I
> could well lose my job over this.

> If anyone has successfully deployed an ADE from which the tables cannot
> be imported, I would appreciate your letting me know, as would those who
> depend on me for their livelihood.

> Thank you.

> -Paul

Hi Joe-

A team of myself and two other programmer have just finished porting our whole
database from from MDE front / MDB back to ADP front / SQL Server back.  The
database is large and intricate, and it took us nine months of full time work.
It is due to be released in a few weeks, and I was just starting to package it
up to distribute for testing and wide-spread use, when I discovered this
surprise.

As you know, code written for an MDB is not the same as code written for an
ADE.  Our reason for being for the last nine months was to translate our
database.  Now our excellently written database looks... well... worthless.
What am I going to tell my supervisor?  Will I have time to tell him anything
before I lose my job?

I would like to thank the developers of Microsoft Access Project for detonating
this nuclear device in my office.  It's really a swell thing you've done.

-Paul

Tell your supervisor YOU discovered this security problem BEFORE it got out
in general use.  Tell him it's Microsoft's fault.

Then sit down and put the front end into an MDB.  Your SQL Server back-end
is fine.  Import the modules from the ADP file into the new MDB.  Link to
your views in SQL Server (You can even rename the links if that helps.)
I'll bet you will find 80%+ of your code from the broken project can be
reused.  It's been a while since I even looked at ADP, but your program's
logic shouldn't have to change moving to MDB.

Compile the modules, then begin importing forms one at a time, fixing things
like recordsource or rowsource until the each one runs correctly.  All this
may take a lot less time than you think.

Joe

Dear Joe,

Thank you for your words of encouragement.  To three people who have suddenly
found themselves in a very distressing situation, they are truly much
appreciated.  You're absolutely right- our best and perhaps only hope for really
setting things right again is to attempt to convert the front end ADP to an
MDB.  Just before reading your e-mail, I had come to the same conclusion and met
with my team-members about it.  We're going to give it all we've got, and with a
little help from the cosmos, we might just make it.  Thank you again.

Sincerely,

Paul

Paul,
Where is the security breach, exactly?
I wish you can explain in some deep detail, giving a scenario involving
fictitious names with details regarding the view, the table, etc.
We might be able to provide some help without the need to re-write
everything.
M.A.

I can explain in more detail, although object names don't really come into the
picture here, even as an example.  Here is a description of the problem.  Since
I don't know other people's knowledge level of Access in general and Access
Projects in particular, forgive me if I state some things that may already be
obvious.

1)  Access MDB/MDEs (traditional Access databases) and Access ADP/ADEs (Access
Projects) both have a convenient feature for easily IMPORTING (i.e. copying)
tables from one MDB to another, from one ADP to another, or between MDBs and
ADPs.  To import tables from an MDB or ADP file, all one needs to be able to do
is browse to the file holding the tables and, in the case of an MDB only, know
the Database Password if one has been put on the MDB.  (ADPs do not have a
Database Password function available.)  I believe, though I do not have
first-hand experience, that importing the tables from an MDB is usually blocked
by use of Access' User Level Security system.

2)  An Access Project is a new kind of Access file that is made specifically
for use with a SQL Server backend.  Whereas an MDB can potentially be used with
any of several kinds of backends (or combination thereof), by definition an
Access Project always has a SQL server backend.

3)  I believe the traditional way to prevent users from importing tables out of
sensitive front end MDE files is through Access' User Level Security system.
In the case of the current MDE front / MDB back version of our database (which
is currently still being used), we used a simple, adequate system involving a
combination of a Database Password on the actual front end, and a little
"launcher" MDE that the user clicked on to pass the Database Password to the
actual front end and open it up (without the user knowing the password.)

4)  What I have found out in the last few days is that the Database Password
feature does not exist on Access Projects, User Level Security also does not
exist, and that the one means by which an Access Project IS supposed to be able
to be secured from unauthorized table importing simply DOES NOT PREVENT
IMPORTING.

5)  We are instructed to secure the tables as follows:  In the front end, do
not call any data directly from the tables.  Instead, use only SqlServer's
Views and Stored Procedures.  Then, in SqlServer, grant the user only INSERT,
UPDATE, and DELETE permissions (i.e. not SELECT) on the tables.  Then grant the
user full permissions on all the Views and Stored Procedures.

6)  However, once these permission settings are all correctly in place, if you
load data that is based on a View or Stored Procedure into a form in the
Project front end, make any change to any existing data (e.g. change the
spelling of a name) and then attempt to save the changes, an error message is
generated stating that SELECT permission is denied on the underlying table, and
the changes cannot be saved.

7)  If one goes ahead and grants SELECT permission on the underlying SqlServer
table, sure, the record can be saved, and all other operations work fine, too.
HOWEVER, THE TABLES ARE NOW AVAILABLE FOR IMPORT by anyone.  Security just
became non-existant.

8)  Microsoft Technical Support has confirmed to me that this behavior is real,
and that, as of last Friday, there is no apparant way to avoid exposing the
tables for import.  They say that this is because of the way ADO interacts with
SqlServer.  Now if one had the option to use ODBC in an an Access Project, this
would be forgivable.  But by definition, an Access Project automatically uses
only ADO and interacts only with SqlServer.  There is no choice in the matter.

9)  The sum result of all this is that tables cannot be secured in an Access
Project.  And of course a database tool which cannot secure its tables is about
as useful for holding data as a tin can with the bottom cut out.

Why is this fraudulant Access Project product even on the market?  I don't
know.  And at this point there isn't time to think about it.  I'm too busy
trying to avoid getting sacked Monday morning.

Paul

This is troubling, but there's no way that an Access ADP is secure in any
case.
Here's why:
    When you create an ADP, you can choose to protect the database either
with NT security or with SQL security.  If you choose the former, it is a
simple matter for the user to create another ADP to connect to the same
database and do whatever she wants.
    If you pick SQL security, the seemingly safest way is to create a dummy
account with a dummy password that can only get to the target database.
Then you either have to tell Access to save the password or not.  If you do,
the user logs in seamlessly; if you don't save the password, the user sees
the User ID and must type the password to open the ADP.  It's a little like
deciding whether you should leave your keys in the ignition or hand them to
the first person you see at the mall.
    If you decide to have the user type the password, then it's still
trivial to create another ADP on the same database.  It only takes 2 simple
lines of  VBA code to read it because Access exposes it through the
Application.ADOConnectString property of the Access.Application object.  And
once the user has the connection string, creating another ADP or deleting or
altering your data through VBA code is equally trivial.
           -- Hardin

Like most environments, the one where this DB needs to be deployed has a
particular comfort zone of security that is required, no more and no less.  This
is a printing company, and virtually the only people we need to protect the raw
data from is THE USERS THEMSELVES.  For example, salespeople must not be allowed
to view the customer lists of other salespeople, and no one must be allowed into
the Personnel data without permission.  Our users are not programmers, and I can
tell you after working with them that they will not be pulling out the
connection string programatically, easy as it may be.  However, they sell
printing services, which involve lots of mail-merge sort of lists.  The
salespeople are used to receiving data from the customers and some are very
familiar with Access at a USER level.  Because of this, many know how to import
tables.  That is the level of security that I have to program for.  So it's not
Fort Knox.

-Paul

I hate the idea of going from ADPs to MDBs (I have noticed that ADPs before
much better)

No idea if this will help but I am aware of a product called "Access Project
Security Manager" from www.databasecreations.com

HTH

Adam

--------------------------------------------------------
Check out these HOT UTILITIES FOR ACCESS AND VB DEVELOPERS....
www.ssw.com.au
* SSW Data PRO  - Version Control for your data.mdb
* SSW Data Renovator  - Compare the differences between two data.mdb's
* SSW Upsize PRO! - Don't UPSIZE to SQL Server without it
--------------------------------------------------------

To explain in brief:
Users log-in to SQL Server.
Then the app *logs-in* after the user. The app login is special kind of
login, called application role login.

The specific app gets full access, but if the user uses simple user log-in
he gets ... no access.

Hope This Helps
Ivan Arjentinski
-----------------------------------------------
Please answer only to the newsgroups.
I'll not answer any direct emails.
-----------------------------------------------

Quote:> Hello-

> This message is in regard to the situation described in my earlier post
> of Thursday, 9/29/00, called "Changing data in View requires Table
> SELECT Perm?", but I would like to resubmit the matter in a different
> way.  It is not necessary to read the earlier post.  After corresponding
> in some other newsgroups and speaking with several people at Microsoft
> Product Support, I am led to believe that there is a major problem with
> the Access 2K Project tool.  Simply put, I am told that contrary to what
> common sense would would lead one to believe there is no way within the
> resources of an Access Project to prevent a user from simply importing
> all the raw data he or she would like to have by simply importing tables
> out of any ADP or ADE file into a new Access file.  If anyone HAS
> successfully prevented the import of tables from an Access Project,
> PLEASE could you respond to this post?  Otherwise, this new tool has
> about as much security as a cereal box, and should be marketed with a
> big label on the front that says "DOES NOT WORK".

> I am specifically calling out to anyone, if such people exist,  who has
> first hand experience successfully deploying an ADE that cannot have its
> tables extracted by simple importing.  With all due respect, if you're
> not familiar with working in an Access Project, please do not respond
> simply assuming that it behaves the way it should.  The standard SQL
> Server method of calling data from only Views and Stored Procedures and
> then denying permissions on the table DOES NOT WORK.  Microsoft Product
> Support has confirmed to me that it SHOULD and that it DOES NOT.

> The team here has just spent nine months porting a large database from
> an MDE frontend / MDB backend format to the new ADE frontend / Sql
> Server 7 backend format.  And if I'm now told that this supposedly
> sophisticated, state-of-the-art, easy-to-use database tool cannot even
> prevent users from extracting all raw table data as easily as peeling an
> orange, then you'll understand my being, to put it mildly, upset.  If I
> am forced to release a front end that anyone can extract tables from, I
> could well lose my job over this.

> If anyone has successfully deployed an ADE from which the tables cannot
> be imported, I would appreciate your letting me know, as would those who
> depend on me for their livelihood.

> Thank you.

> -Paul

Winglift

I have followed this up with Microsoft.... Here is the answer I got from
them, I would like to know the current status/workaround used for this
problem.

   When Access binds to Views for update scenarios, it goes through the MDAC
Windows Cursor engine.  This piece of software goes against the base tables
for SQL Server 6.5 and SQL Server 7.0 servers all the time.  This means that
if you want security on your forms to work (you don't want to grant access
to base tables), then you have to use unbound forms against the View and do
the data posting yourself.  With SQL Server 2000, views now have a WITH
VIEW_METADATA option.  If your view has this option set, the Windows Cursor
engine will actually go directly to the View for the updates.  You should
read the SQL 2000 BOL topic for CREATE VIEW with all the details.

Hope this helps.

Adam
* Keep up-to-date by attending the monthly meetings at the 'Access/ASP/SQL
Server User Group, Sydney"   www.ssw.com.au/AccessUG
--------------------------------------------------------
Check out these HOT UTILITIES FOR ACCESS AND VB DEVELOPERS....
www.ssw.com.au
* SSW Data PRO  - Version Control for your data.mdb
* SSW Data Renovator  - Compare the differences between two data.mdb's
* SSW Upsize PRO! - Don't UPSIZE to SQL Server without it
--------------------------------------------------------

Quote:> Hello-

> This message is in regard to the situation described in my earlier post
> of Thursday, 9/29/00, called "Changing data in View requires Table
> SELECT Perm?", but I would like to resubmit the matter in a different
> way.  It is not necessary to read the earlier post.  After corresponding
> in some other newsgroups and speaking with several people at Microsoft
> Product Support, I am led to believe that there is a major problem with
> the Access 2K Project tool.  Simply put, I am told that contrary to what
> common sense would would lead one to believe there is no way within the
> resources of an Access Project to prevent a user from simply importing
> all the raw data he or she would like to have by simply importing tables
> out of any ADP or ADE file into a new Access file.  If anyone HAS
> successfully prevented the import of tables from an Access Project,
> PLEASE could you respond to this post?  Otherwise, this new tool has
> about as much security as a cereal box, and should be marketed with a
> big label on the front that says "DOES NOT WORK".

> I am specifically calling out to anyone, if such people exist,  who has
> first hand experience successfully deploying an ADE that cannot have its
> tables extracted by simple importing.  With all due respect, if you're
> not familiar with working in an Access Project, please do not respond
> simply assuming that it behaves the way it should.  The standard SQL
> Server method of calling data from only Views and Stored Procedures and
> then denying permissions on the table DOES NOT WORK.  Microsoft Product
> Support has confirmed to me that it SHOULD and that it DOES NOT.

> The team here has just spent nine months porting a large database from
> an MDE frontend / MDB backend format to the new ADE frontend / Sql
> Server 7 backend format.  And if I'm now told that this supposedly
> sophisticated, state-of-the-art, easy-to-use database tool cannot even
> prevent users from extracting all raw table data as easily as peeling an
> orange, then you'll understand my being, to put it mildly, upset.  If I
> am forced to release a front end that anyone can extract tables from, I
> could well lose my job over this.

> If anyone has successfully deployed an ADE from which the tables cannot
> be imported, I would appreciate your letting me know, as would those who
> depend on me for their livelihood.

> Thank you.

> -Paul