Best Way to implement security?

[jazminecat]

Hi all - I have a simple access db on the network, that exists primarily to generate a report for our users, and which gets it's data from our AS400. This db contains maybe 4 tables, which are updated by code run from another program. It dumps the info into a txt file, and there are macros which run on open in the access db which grab the txt file and update the tables, run a report, print it, and then close the db. This all happens in the background.

The problem I have is this: I have a user who is in management (you know where this is going already i'm sure) who has been poking around in there and making his own queries.

I have been asked to make him a front end so he can poke around and make queries without compromising the backend data. I have made a frontend and linked the tables, but this still allows him to change the data in the tables. What is the simplest way to give him the ability to make and run queries, without the ability to change the data in the linked tables?

Thanks in advance!

 

[Remou]

If this database is being generated, is it not possible to give the manager an identical copy?

 

[jazminecat]

The tables are updated all day, throughout the day, each time they run this process on the AS400. I can give him a copy of the db, but have to use linked tables in order for him to have up-to-date data. And it seems that he can add and delete data from these tables, which I want to prevent. Any ideas?

 

[Remou]

There are a lot of arguments for snapshot data if this guy wants to create reports. It can be quite annoying to build a report and find that you cannot reconcile the figures because the data has been updated. Would it be possible to provide a daily or even weekly snapshot?

Alternatively, if you use Distinct in queries the queries will not be updateable. You could provide such queries rather than tables.

 

[jazminecat]

He wants to build his own queries, and run them. I know...but I can't change that part of the equation - he's upper management.

 

[Remou]

You can build queries from queries.

 

[jazminecat]

So how could I go about giving him a front end that has only these queries that he can build from, and doesn't have tables that he would edit? We need to provide a solution that doesn't require daily or weekly updating - he needs real time information, and we don't have the staff to constantly maintain it. Any thoughts? Thanks for your help!

 

[Remou]

You can use In. For example:

[tt]SELECT DISTINCT tblMembers.Member, tblMembers.MembersKey, tblMembers.Address
FROM tblMembers IN 'C:\Docs\Tek-Tips.mdb'[/tt]

 

[jazminecat]

Very cool! I will give that a shot and let you know. Thanks Remou!! I honestly had no clue I could do queries against another db.

 

[jazminecat]

Hm, well, I'm making progress, but have run into a snag.

What I have done is copy all of the queries from the backend into this new frontend for the manager to mess with. I have used your code, using SELECT DISTINCT and IN pointing to the backend, to update these queries so that it looks just like the backend he is oh so fond of poking around in.

One problem though, with the DISTINCT - on some of these copied queries, it returns fewer records than the original. This is because not all of the queries use the "rundate" which is a field in the table, and probably the only reliably unique field in every record. There are often nearly identical records because a transaction may have happened many times - thus the only unique character is the rundate. So...if I remove the DISTINCT from the query in the new frontend, it runs fine and returns an identical recordset as the original. But it's my understanding that it is the DISTINCT that makes the returned records uneditable - so I need it. If I add in the rundate, I get the right number of records. If I uncheck the 'show' box in the query design view for rundate, it again shows a smaller recordset. Any ideas? thanks!!

 

[lespaul]

have you had your AS400 administrator make sure that the proper permissions have been set on the Database side?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Remou]

I was sure I had posted a note to the effect that the query could, of course, be tampered, but perhaps it failed.
Another way to prevent queries being updated is with a join. If you have a table called, say, Hold with no index and a single record:

[tt]SELECT DISTINCT tblMembers.MembersKey
FROM tblMembers LEFT JOIN Hold ON tblMembers.MembersKey = Hold.HoldField IN 'C:\Docs\Tek-Tips.mdb'[/tt]