|
Home > Archive > Other Oracle database topics > October 2005 > MS Access, Oracle 9i, security, and pass-thru update queries
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
MS Access, Oracle 9i, security, and pass-thru update queries
|
|
|
| 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 2005-10-17, 1:25 pm |
| 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@x.washington.edu
(replace x with u to respond)
| |
|
| DA Morgan wrote:
> DFS wrote:
>
> 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 2005-10-17, 1:26 pm |
| 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=USE
R_NAME;PWD=< WhateverYourPassword
Is> ;DBQ=tma;DBA=W;APA=T
;PFC=1;TLO=0;DATABAS
E="
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(Orac
le 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 2005-10-17, 1:26 pm |
| 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
| |
|
| Tim Marshall wrote:
> DFS wrote:
>
>
> 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=USE
R_NAME;PWD=< WhateverYourPassword
Is> ;DBQ=tma;DBA=W;APA=T
;PFC=1;TLO=0;DATABAS
E="
>
> 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
| |
|
| Tim Marshall wrote:
> Tim Marshall wrote:
>
>
> 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 2005-10-17, 1:26 pm |
| 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.
>
> 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
| |
|
| Tim Marshall wrote:
> DFS wrote:
>
>
> I prefer Case statements, myself. 8)
>
>
> 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.
>
>
>
> 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@hotmail.com 2005-10-17, 1:26 pm |
| i reccomed dumping MDB and using ACCESS DATA PROJECTS against SQL
Server.
it would work great for 100 users
| |
|
| dbahooker@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 2005-10-20, 8:25 pm |
| 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
|
|
|
|
|