473,390 Members | 1,673 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,390 software developers and data experts.

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

Nov 13 '05 #1
11 3523
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)
Nov 13 '05 #2
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
Nov 13 '05 #3
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
Nov 13 '05 #4
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
Nov 13 '05 #5
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


Nov 13 '05 #6
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.

Nov 13 '05 #7
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
Nov 13 '05 #8
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!

Nov 13 '05 #9
i reccomed dumping MDB and using ACCESS DATA PROJECTS against SQL
Server.

it would work great for 100 users

Nov 13 '05 #10
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.

Nov 13 '05 #11
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
Nov 13 '05 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Andrea M. Segovia | last post by:
Hello, I am a newbie to Oracle databases.... We have a visualization front-end tool connected to an Oracle back-end database on a Tru64 UNIX server. We also have clients with MS access...
13
by: BigDaDDY | last post by:
Um yeah....In case you haven't figured it out, Microsoft sucks. I'm going to be kicked back in my chair eating popcorn and watching football 10 years from now, while all you clowns are scrambling...
4
by: anand | last post by:
Hi, I have an Access 2000 database, which contains some native tables, and some linked tables which belong to an ORACLE database, through ODBC. Using VB.NET, I am trying to fetch some data by...
1
by: Andrew Arace | last post by:
I scoured the groups for some hands on code to perform the menial task of exporting table data from an Access 2000 database to Oracle database (in this case, it was oracle 8i but i'm assuming this...
4
by: ljubo lecic via AccessMonster.com | last post by:
I am an experienced ORACLE software developer who met ACCESS few months ago and I must say that ,so far, I am delighted. I rapidly developed a few aplications and everything is working extremly...
1
by: premmehrotra | last post by:
I have an access 2000 database which has several levels of security defined, i.e., certain users can only see certain forms, run certain reports. I am splitting this database in front end and...
17
by: Mell via AccessMonster.com | last post by:
Is there a way to find out where an application was created from? i.e. - work or home i.e. - if application sits on a (work) server/network, the IT people know the application is sitting...
8
by: Greg Strong | last post by:
Hello All, The short questions are 1 Do you know how to make DSN connection close in Access to Oracle 10g Express Edition? &/or 2 Do you know how to make a DSN-less pass-through query...
12
by: zwasdl | last post by:
Hi, I'm using MS Access to query against Oracle DB via ODBC. Is it possible to use HINT in Access? Thanks, Wei
0
by: Jesse Houwing | last post by:
Hey all, I've been asked for a customer to write a piece of data access using EntLib 2.0. I'd like to use the standard entlib database factory class because a transition to SQL Server is still a...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.