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

DA Morgan wrote:
> 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.

Your first idea is a bad one.

MS Access is the best database front-end and db-client dev environment available, bar none. It's also one of the best query and report writers.

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

LOL!!!!!!! You're out of your uninformed mind. I've worked on the Access front-end for about 8 weeks.

A good Forms developer couldn't duplicate it in 12 weeks, even if he had the source code. And even then it wouldn't have nearly the functionality and ease of use I provide in Access.

> 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.

That's a possibility. I'll look at it.

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

Thanks Received on Thu Oct 13 2005 - 11:33:34 CDT