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

[DFS]

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

[DA Morgan]

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
da******@x.washington.edu
(replace x with u to respond)

[DFS]

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

[Tim Marshall]

DFS wrote:

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.

Then use a temporary query (see air code example, below). You can save
the admin password in VBA code as part of the ODBC connect string - I
would save it as a constant in a standard module. Get the connect
string expression from the connect property of any saved PTQ you
currently have. For example, in a module called Mod_Constants (watch
for wrap):

Option Compare Database
Option Explicit

'*******Connect String*************

Public Const cUserOracleConnect =
"ODBC;DSN=XXX;UID=USER_NAME;PWD=<WhateverYourPassw ordIs>;DBQ=tma;DBA=W;APA=T;PFC=1;TLO=0;DATABASE="

If this app is distributed as an mde, you wouldn't need to worry about
this getting out. You're scuppered if it's to be issued as an mdb, though.

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. That way, damage is
limited if, for whataever reason, the user password gets out and you
don't need to worry about the Admins.

Dealing with your actual question,

Here's an example of a temporary query using DAO methods (air code):

Function fUpdateWhatever(strS as string) as Boolean

'This function creates a temporary PTQ that is executed
'And then discarded. It will never be saved.
'
'Called by something like:
' If fUpdateWhatever(Oracle Update SQL) = True then
'if False is returned, the calling procedure will
'know the update did not occur.
'
'strS is the Oracle SQL update or other action statement
'Develop strS in the calling sub/function and pass here
'I include a database variable here, though I always prefer
'to use the David Fenton dbLocal database variable function
'to avoid clean-up

dim dbs as DAO.Database
dim qdf as DAO.QueryDef

On Error GoTo Err_Proc

'Set value of this function to true. Any irrecoverable
'errors will st value to false.

fUpdateWhatever = True

set dbs = access.CurrentDb

set qdf = dbs.CreateQueryDef ("")

'The empty string => life of the query is for the duration of this
'procedure only - it won't be saved as a query on the database
'window, ie, it cannot be exported.

with qdf

'Connect string constant defined above

.connect = cUserOracleConnect

'Give the temp Query the Oracle SQL

.SQL = strS

'The following is necessary or Access will
'throw an error/advisory

.ReturnsRecords = False

'Execute the SQL - I don't include error
'Handling, though you can trap Oracle errors
'and not just Access errors

.Execute, dbFailOnError

.Close

end with

Exit_Proc:

Set qdf = Nothing

dbs.close

Set Dbs = nothing

Exit Function

Err_Proc:

'Error handling - set fUpdateWhatever = False if
'error cannot be recovered

....

End Function
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

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

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

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(

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[DFS]

Tim Marshall wrote:

DFS wrote:

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.
Then use a temporary query (see air code example, below).

I have done those temp pass-thru queries, many times in the past. But it's
just so kludgey I was wondering if anybody has smoother solutions to storing
and executing pass-thrus, but still protecting the queries. I guess the
temp queries is the way to go.

You can
save the admin password in VBA code as part of the ODBC connect
string - I would save it as a constant in a standard module. Get the
connect
string expression from the connect property of any saved PTQ you
currently have. For example, in a module called Mod_Constants (watch
for wrap):

Option Compare Database
Option Explicit

'*******Connect String*************

Public Const cUserOracleConnect =
"ODBC;DSN=XXX;UID=USER_NAME;PWD=<WhateverYourPassw ordIs>;DBQ=tma;DBA=W;APA=T
;PFC=1;TLO=0;DATABASE="
If this app is distributed as an mde, you wouldn't need to worry about
this getting out.
True, but if you open an .mdb file you can import queries from .mde files
(tables too). Depending on security settings in place.


You're scuppered if it's to be issued as an mdb, though. 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. That way, damage is
limited if, for whataever reason, the user password gets out and you
don't need to worry about the Admins.
Not a bad idea.

Dealing with your actual question,

Here's an example of a temporary query using DAO methods (air code):

<snip example>

Thanks Tim

[DFS]

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.

[Tim Marshall]

DFS wrote:

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 prefer Case statements, myself. 8)
Did you consider a Select-only ROLE? That's what I use sometimes.

Hmmm, I started with users when I began working on the Oracle app I
manage. Haven't had a need to look at anything else.

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?

The same thing. The unfortunate thing about PTQ versus linked tables is
that a PTQ Select is not updateable.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[DFS]

Tim Marshall wrote:

DFS wrote:

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 prefer Case statements, myself. 8)

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

Hmmm, I started with users when I began working on the Oracle app I
manage. Haven't had a need to look at anything else.

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?

The same thing. The unfortunate thing about PTQ versus linked tables
is that a PTQ Select is not updateable.

So if you have a datasheet where the User needs to...

1) choose a Status from a combobox
2) fill in a date
3) choose a name from a combo box
4) enter a comment

and they have to do this for 100 records, you execute 400 update queries
(passing in a record ID, field name, and update value), rather than use a
linked table?

Scary!

[dbahooker]

i reccomed dumping MDB and using ACCESS DATA PROJECTS against SQL
Server.

it would work great for 100 users

[DFS]

db*******@hotmail.com wrote:

i reccomed dumping MDB and using ACCESS DATA PROJECTS against SQL
Server.

it would work great for 100 users

Sure, except for a few things:

1) you can't house local queries or code - as I understand it, all
processing and objects are server side

2) my client standardizes on Oracle.

[Tim Marshall]

DFS wrote:

So if you have a datasheet where the User needs to...

1) choose a Status from a combobox
2) fill in a date
3) choose a name from a combo box
4) enter a comment

and they have to do this for 100 records, you execute 400 update queries
(passing in a record ID, field name, and update value), rather than use a
linked table?
Personally, I would execute 100 update statements if I designed it and
go by record/row (row and column are the appropriate terminology in
Oracle, BTW).
Scary!

Not according to E F Codd, it's not. What do you think Access is doing
to Jet when you do the above?

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me