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

DFS wrote:
> Architecture: Access 2003 client, Oracle 9i repository, no Access security
> in place, ODBC linked tables.
>
> 100 or so users, in 3 or 4 groups (Oracle roles actually): Admins, Updaters
> and ReadOnly. Each group sees a different set of menu options when they
> open the client and login to Oracle.
>
> For the sake of speed I use pass-through queries here and there for updates
> and deletes. I update their SQL property in code and execute them.
>
> Since the pass-thru queries need Admin priveleges, I could/would like to
> store the Admin password with the query. But that's Security Breach #1,
> since you can import the query into another .mdb (even from the .mde), and
> see the Admin password.
>
> To get around the breach, I don't store the password with the query, and
> when Admins login I create a pass-thru query in code, and connect to the db
> with the Admin password, thus setting their permissions for the session.
>
> This works fine, but it's kind of kludgey. I'd like to protect the queries
> (and ODBC table links) from prying eyes, and I definitely won't be using
> native Access security.
>
> Ideas?
>
> Thanks

My first idea is to dump MS Access and get a real front-end.

A good developer could likely duplicate your front-end using Oracle Forms in a week.

Why don't you take a look at using the SYS_CONTEXT function to identify the end-user from their operating system login and skip the passwords entirely.

You can find a demo of SYS_CONTEXT in Morgan's Library at www.psoug.org.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)