Re: MS Access, Oracle 9i, security, and pass-thru update queries

Tim Marshall wrote:
> Tim Marshall wrote:
>
>> If the tables to which updates are being performed by non-admin
>> people are limited, ie, not all the tables in the database, it might
>> also be advisable to consider a new user with grant update on
>> TABLE_NAME to UserName and use THAT in the constant above.
>
> Another thing to keep in mind with development. I only use PTQs and
> Oracle SQL, largely because I prefer the theta join methods versus the
> ansi joins Access against linked tables. I find this much, much
> easier to write procedures to construct a select or other statement
> based on criteria chosen/specified by the user in the forms.

I use a mixture. I often create Oracle views and use them as linked tables. But I like the Access iif() better than the Oracle CASE WHEN END.

> I will always make sure I have a user name with grant select on the
> tables I require and no other insert, delete, update, etc privileges.

> It's against that user that I write a DSN and save my PTQs, which you
> will need for things like combo boxes and list boxes for your forms.
> Even if you use linked tables, it's still advisable to set up the
> links with the select only user DSN.

Did you consider a Select-only ROLE? That's what I use sometimes.

> Any and all action statements are done as per my example in my first
> post.

What do you do when you have datasheets that users need to update?

> You can use ADO without the need for saving querydefs and without the
> need for DSNs, something I was very excited about UNTIL I discovered
> Access has no support ofr ADO in report writing in mdbs/mdes. 8(

Didn't know that. I still write DAO code, exclusively. Received on Thu Oct 13 2005 - 16:27:09 CDT