473,739 Members | 2,355 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 3564
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.wash ington.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 cUserOracleConn ect =
"ODBC;DSN=XXX;U ID=USER_NAME;PW D=<WhateverYour PasswordIs>;DBQ =tma;DBA=W;APA= T;PFC=1;TLO=0;D ATABASE="

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

set qdf = dbs.CreateQuery Def ("")

'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 = cUserOracleConn ect

'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 cUserOracleConn ect =
"ODBC;DSN=XXX;U ID=USER_NAME;PW D=<WhateverYour PasswordIs>;DBQ =tma;DBA=W;APA= T
;PFC=1;TLO=0;DA TABASE="
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

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

Similar topics

10
3533
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 databases who would like to share their data using this visualization tool but do not want to import their data into the Oracle server back-end.
13
3322
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 to rewrite all your code because Microsoft upgraded all their crap and nothing you wrote 10 years earlier works. It doesn't take a rocket scientist to figure out that Microsoft is unreliable. Try opening an Excel 95 spreadsheet you wrote in...
4
14473
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 joining two tables, one table native to the mdb file, and another belonging to ORACLE. I am using a connection string to connect to mdb file:
1
9198
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 will work for 9i and even 10g ) No one had what I needed, so I wrote it myself. I Rule. This code isn't going for efficiency, and isn't trying to be dynamic. It doesn't create the table structure in Oracle, that's up to you. (I
4
2042
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 good. Now, I am offered to develop something more complex. And It must be done in ACCESS.I feel so comfortable in ACCESS programming that I am about to accept the offer.But before my final response I URGENTLY need some advices and opinions from...
1
1340
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 back end. I want to move back end to Oracle. I don't now where security is stored in Access. Will access users see only he forms/reports hey have been authorized
17
4415
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 there, but is there a way they can find out if that application was put there from a CD or email or created at work? Hint: It's not on a client/server database, just native jet database mdb created on Access 2003 (default 2000)...
8
9636
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 work from
12
5284
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
1313
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 viable option in the near future. The problem is that the project will use Oracle till then, and there is no integrated security activated on the Oracle Server (and it won't be in the near future). For compliance reasons every user must connect
0
8794
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9341
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9269
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6756
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6056
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4572
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4826
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3282
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2195
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.