Disallowing Direct Table Access

When a user launches an invoice form, it may be best for both security and performance to create a (local) 'copy' of the invoice table (with only one, new record). This way, the user can cancel/delete the record without impacting the real table on the network. Also, the user's permissions may not allow the user to delete records in the real table - to protect invoices previously created.

Currently, my users have direct access to the table... Question: Is there a standard methodology for the solution described above?
icAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JimMorganCommented:
ic:  Welcome to EE.

There a couple of points to review in your question.

When you say your users have direct access to the table, can I read from that the data tables are not separate from the rest of the DB and users have more or less free rein to go in the DB outside of program control?

It is quite customary to break up a DB into a back end (data tables - located on the server) and front end (everything but the data tables - located on each user's workstation).  I go one step further and grant no permissions for a user to have direct access to the data tables.  All access to the tables are done through queries with owner access.

In addition, I turn off all capabilities for any user to do anything but stay within the program controlled application - no Access menus, toolbars, database, etc.

There is no real reason to make a local copy of the invoice table as nothing is really added to the table until the new record is saved.  If it is canceled, no problem.

What I do for new entries is to put them all into a temporary table until the user either closes the table or clicks a button to 'process' the records.  They really are in the main table but flagged as new so they don't impact the ongoing records.

You can easily set it up so that the user presses a command button for deletion and then you can handle the deletion by code - allow deletes on new records but not old records.

Anything else that I can provide you, don't hesitate to ask.

Jim
0
JimMorganCommented:
JimMorgan changed their proposed answer to a comment
0
icAuthor Commented:
Thanks Jim!!,

The database tables are seperate .mde files that sit on the server (invoices included) as opposed to what I think of as the user's "shell" (user's pc). Towards the end of your answer, you wrote about generating a temporary table... this is it!

I'm wondering if there is a "standard" method for this temporary table?

....do you create this table each time the form is launched?

....is it simply a table that's been created and (perhaps) resides in the user's "shell" program (i.e., local).

When you say "flagged as new" - is this done with a field in this table?

I've used the "WITH OWNERACCESS OPTION" in my sql (only works with saved queries, not when written into vba code, oh well, I suppose that's no big deal).
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

icAuthor Commented:
Replace "standard" with "recommended".

-ic
0
JimMorganCommented:
ic:  The easiest way to make a temp table is to go to the tables database,  highlight the table, then export to external database.  Access will ask for the database name (the local DB) and if you want to save definition and data or definition only.  Don't save the data.

Give it a new name like ttmpInvoices or tblNewInvoices.  If you need any ancillary tables, you can setup the same relationships as the original table.

I also make a copy of the form which is used to view/enter invoices.  Call the new one frmNewInvoices.  The query behind this one uses the temp table.  Add a button to process and accept all new invoices.  Do whatever you need to do to verify that the invoices are correct and then run an append query to add them to the master invoice table and the run a delete query to delete the ones loaded over.

You can add another field to the temp table to indicate if the invoices are ready to be accepted.  You only append and delete the accepted invoices.  So if an invoice is in progress, it will remain in the temp table until it is either correct or finished.

I have hundreds of saved queries.  Except for some special functions which we have to build in code and which will not take parameters, all queries are saved queries.  They are optimized by Jet and run faster.

Jim
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gavindewCommented:
No standard method I know of, however a simple solution is as follows :

Allow user insert, no delete priveleges on table.

If user want to delete, code will verify if this is possible, then code uses DAO or ADO object to delete record, using a priviledged account. Of course, the account information needs to be safeguarded, either hard coded, and code removed, else encryted in the DB.

For updates of the record, simply copy the record to the same table, with a status indicating temporary record. When edit is compete, once again, code will update original record using the privileged account.

Note, if privileged account is not used, and only code is used, then the user could simply open the tables using a full version of access of some other query tool, and perform all the edits they want.

Access doesn't yet handle security very well..

Hope this helps ..
0
JimMorganCommented:
gavindew:  Welcome to EE.

Actually, with the proper setup, Access security is quite good.  It is just not possible, for example, for a user to get into or see anything in our apps except the structure of queries.  That is the only item you cannot keep a user from viewing, regardless of what you do.  They can not make changes to a query or run the queries outside of program control.

We started out using what we called a 'covert' account to do various tasks but wound up not needing it once I finally figured out how to develop some fool-proof security.  Since our apps are single user, we don't even use custom workgroups.  We found out that they were more trouble than they were worth.

Jim
0
icAuthor Commented:
I'm satisfied my question(s) have been answered. I'm adding additional comments for the heck of it; I'm enjoying the discussion.

Jim, with the two forms, would it not be easier to use one form and change the "data entry" (form.DataEntry = False) property and disable any necessary controls (e.g., a delete button) at the same time? (I'm only saying because the file size of the shell is 4MB +/- (over 50% of which I blame on the form objects).

gavindew, where you state: "using a privileged account..." in reference to using ADO or DAO, I'm wondering, "How?". SQL uses the "WITH OWNERACCESS OPTION" to get around the security restrictions on a user, but what's the method for DAO and ADO.
0
JimMorganCommented:
ic:

I distinctly remembered responding to your last comment but it doesn't seem to be here today.

Two forms is not a problem.  I don't think that forms take up that much room.  There is more room taking up with the code than the form.  The objects are stored in internal tables (Access is written with Access - everything is table based and stored in Access hidden tables).

To give you an idea, I have a commercial app which has three DB's linked together.  The front-end had 21 tables, 433 queries, 93 forms, 31 modules, and a few macros and reports.  The total DB size is 9.5MB.  Repaired and compacted, it drops to 7.8MB.  Converted to an MDE, the size drops to 5.4MB.  Zipped, makes it only 1.4MB.

The difference in the 7.8 and 5.4 is code.  When you have an MDB, you have both versions of code - text based and compiled.  Compiled is tokenized with comments removed.  The MDE removes the text based code.

The backend data side has 58 tables and is 1.95MB is size.  These are empty tables so the table structure takes up a bit of space.  Zipped, reduces the DB to 284KB.

To find out how much space the forms take up, make a copy of the DB, remove the forms, and compact to see the final size.

If you are going to have multiple forms, it would be better to place redundant code as functions in a module and place the calls in the event property line.  This will reduce the class module size and cut down on space requirements.  The extra code for turning on and off features may take more room than the form.

When gavindew was talking about 'privileged accounts', he was referring to the same item I was with my 'covert account'.  The problem with these accounts is if you do not encrypt your DB, someone can debug the code and discover how to set up that account.  Then everything in your DB is wide open.  The encrypting only keeps out casual users.

If you are using Access 97, you are currently using DAO.  ADO is available in 97 but few use it as 97 is not really structured for ADO.  In Access 2000, ADO is the default structure and you have to preface DAO structures with DAO to use them.  I don't believe the 'OwnerAccess Option' is changed in the ADO structure.

Jim
0
gavindewCommented:
ic, you mention access security is foolproof. If you can tell me how, It would be a great help.

How do you prevent users from running queries from outside of program control. If a programmer opens your program using a full version of access, he/she shuould easily be able to open the query, and perform any operations on it that they can, so long as their security permissions allow it. I have not been able to obtain security without using a 'covert account' as you put it.

0
gavindewCommented:
Oops, that comment was directed at JimMorgan
0
gavindewCommented:
ic, DAO and ADO can both be used to access standard access queries, thus access security is still in place. Using a privileged account simply allows a further level of security, however with the problem of maintaining the secrecy of the account password.
0
JimMorganCommented:
Gavin:  A full version of access and all permissions still gives you only 'ADMIN' priviledges on my DB's.  During the development phase, I use an MDW and have a developer's group with all permissions.  I change the ADMIN user to give that user the same permissions as the final user - essentially minimal.

Before I ship the apps, I remove the ability to bypass the startup routines using the shift key.  I also remove all Access development capabilities - menu bars, toolbars, database windows, etc.  I also put in an AutoExec macro which runs code to check security and run the program.  If everything is not right, they are tossed from the program.

All they can do is create a blank DB and inport the queries.  But they only have the rights to see the SQL statements, which I can't get around, but not see the Design mode or run the queries - they aren't the owner and have other rights.

The product is shipped without an MDW so no matter what the smart user or programmer tries to do, they will never be able to setup an exact copy of the owner state and MDW we use for development.

The database has no password and any user can use it but not do anything outside the permissions we allow.

Jim

0
gavindewCommented:
Jim, How about a user who accesses your queries via ODBC. Can't he run the queries using MS Query or something like that, and then do all the direct data editing he wants to.

This is the problem I have.

gavin
0
JimMorganCommented:
I don't think so.  He still would not have the permissions to user the tables that the queries are based on.

Can you be a little more explicit about how this is done and I'll see if I can duplicate it on my app?  I'd be very interested to see if my security holds up.  No one has been able to break it so far, even us.

Jim
0
gavindewCommented:
I have tables which the user has no permissions on, then I have a query with owner access permissions to read the tables, and allow insert and update priviledges.

The user is free via an enabled ODBC application to open the queries (not the tables), and update or insert records at will. However, my application code restricts access via the user interface.

I can solve the problem using a priviledged account, where the account performs record updates, and changes record status flags to indicate valid records in the table. However, without using another more priviledged account, if the user via the access user interface, or code can insert / update records, then via ODBC connection has the same permissions, however, can do so without my user interface enforces restrictions.

One more problem is the problem of using OLE automation to create an access instance, load the specified project, and then invoke all the code function which are declared as public as one wants, including using the CurrentDB object to open queries, which then actually popup in the access user interface.

How to overcome this is another problem, since my stealth accounts normally declare an object called CN, which is the protected connection to the database, however is made public so that all the code module have access to it. A user using OLE automation, can simply extract the password by obtaining the DSN connection information directly from the CN object using OLE automation, and Accesses Eval method.

Is this enough information, need more, just let me know.
0
JimMorganCommented:
Gavin:

Ok, we have the same table and query setups.

Who cares is the user can open the queries via ODBC to insert and update records as will?  I certainly don't.  I don't know how many users of Access apps written by someone else are sophisticated enough to even try this.  You want to screw up the database by playing around with inserting records via the query directly, go ahead.  I hope that you have good backups.

A question?  If the queries are in one DB and the tables are in another, it seems that the ODBC would have to open both DBs, which I don't believe they could - at least not the data DB, trying to run the queries would seem to be an exercise in futility, right?

I guess what I'm saying about ODBC connections is I don't feel that they are an issue here.  If the data was sensitive enough, put it in a network folder with passworded user permissions so the hacker can't get to it.

OLE automation - I've not played with.  Can you give me an example of how this would come about?

Jim
0
gavindewCommented:
Jim,

The reason for the question is this. We tendered for a governemnt contract, to use our accounting system, and a database consultant came around to talk about secuirty. These governement contracts are all picky about audit trails, especially on financial info. They pointed out things such as field level security, row level security, store procedure buffers between queries and tables etc. etc. and made the point that the information as we put it just was not secure enough. I was hoping that you knew something which could help us overcome this. We are now forced to consider re-writing our apps in delphi - just becuase of security! Not good for us!!

By the way :

It is quite possible to open a query in one access MDB, which references linked tables in another MDB or even SQL sever or something. The wonders of Microsoft JET. Only problem is queries which access VBA script. These will not run. However, using a column which references a VBA function, whilst safeguarding the query, unfortunately places too much of a performance burden on things.

Ole automation is also quite simple. Just create an Access.Application object

Dim a as Access.Application OR from another applicatiom, such a VB, simply

Dim a as NEW Access.Application

and using the access help you will be able to navigate and do almost anything in access via programatic control that you can think of.

Gavin
0
JimMorganCommented:
That's the problem working on a government contract.  I actually worked with the CIA once in which they wanted to give the user the idea that they could change information in a record.  When the user finished, they wanted the information put back the way it was before the changes were made and to put those initial changes away along with the date/time and user name for further review.

Access was never designed to handle this kind of security.  Not without writing a lot of code.  I not sure that Delphi is the answer either.  There is one more attribute which you can assign to an object: Private, Public, or Protected but I feel that most of those are already available in Access.  You can always develop your own class to take care of that.

You could also create a security table that allows you to define which fields, records and such have what security rules.  We use a table like this form dynamic opening of generic popup forms based on a rules table.

I not sure what they mean by 'store procedure buffers' between queries and tables.  It might be easier to develop some security rules tables and a slight modification of your data structure to accomodate the lack of security they are talking about than to rewrite the entire apps in Delphi.  Even going outside to VB would be easier.

I've been involved with a lot of these 'special' requirements, which, once properly defined, can be solved.  I know it's a pain in the rear but this is one of the fun things about database development.

I think that once you and I get on the same level for terminology, that there may be more common ground than it appears.  90% of my work has been grounded in the 'real world', although Condor, the database I spent 15 years working with, was sold primarily only to government agencies.

Let's move this conversation to email.  My address is in my profile.

Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.