ODBC connection to Oracle.

[christshis]

Hello Guy,

We use an Oracle base software called Trapeze and Microsoft Access sometime used as a front hand to access the Trapeze oracle tables.
Due to the possible dangers of allowing the users Access, Is there a way to block the users access to Administrative Tools, to create ODBC connectivity to our databases? Trapeze's security is antiquated and uses Oracle to validate accessibility. Since a user has to be created in Oracle, with update, delete, read, write, append rights, for Trapeze to work correctly, a user can connect directly to the Db through Access and make changes directly to the data, unless the ability to prevent them from creating their own ODBC connections.".
Any ideas on how to prevent them to create that ODBC connection?Thank for your help.

 

[FoFa]

Does Trapeze use it's own internal userid to connnect to the Oracle DB, or the network account of the user? Typically they use their own account, internally, and the DB is unaware of the actual user. And your users typically are unaware of that account. If that is the case then they would not have access via their normal account.

 

[christshis]

ODBC Connecition to Oracle via MS Access

Yes, Trapeze uses its own internal userid to connect to the Oracle DB. Actually, each user has access from his/her workstation through a GUI interface. I am trying to set up some reports through MS Access, from each workstation. My concern is to prevent them from creating an ODBC connection and access the database using MS Access without me knowing. Because the way trapeze is set up, they can, with their username and password, access the table, if they know how to create an ODBC connection. Is there any way to hide this capability of accessing the “Administrative Tools than Data Source (ODBC)”.

 

[FoFa]

If Trapeze uses an internal USERID, then if they did try to setup an ODBC connection, their own ID would be passed, and that security is not setup, if I understand, so no access Oracle would allow. Try it.
In Windows admins. can lockdown certain aspects of the desktop, to include ODBC creation. Only ADMINs at our company can setup an ODBC connection via the Windows lockdown.

 

[christshis]

ODBC Connecition to Oracle via MS Access

Thank you so much for your help. I think restriction to the Windows XP level would be the best way to go. I mean only allowing admin to setup an ODBC connection and no regular users.

Thank again.

 

[namliam]

I think that a user can allways setup an ODBC connection for his user within XP...

 

[accidentalguru]

I administer Trapeze on Oracle as well, and would like to clarify this issue:

The problem is not restricting the ability to set up ODBC sources.

The problem is that the Trapeze read/write ODBC source is configured on any machine that runs Trapeze.

If you create an Access reports using the Trapeze read/write ODBC, then you have gotten users one step closer to working directly with the data outside of the Trapeze application and its built in permissions. That is likely not a good thing.

One slightly more secure option is to create a second ODBC and check the "Read Only" box in the driver configuration on the "Application" tab. That way Access would be able to retrieve, but not make changes in the data.

A semi-savvy user could still use the regular read/write Trapeze ODBC, but at least the power would be intentional, instead of giving everybody full read/write to the whole database.

It works to get my Trapeze data into Access/Excel/.csv and helps me sleep well knowing that I will not wreak havoc on my production database.

 

[Banana]

One option, though I doubt is feasible, is just to not use Trapeze at all, and connect Access directly to Oracle using Oracle's ODBC drivers. Oracle can then manage the security for the connections from Access databases.

 

[accidentalguru]

One option, though I doubt is feasible, is just to not use Trapeze at all, and connect Access directly to Oracle using Oracle's ODBC drivers. Oracle can then manage the security for the connections from Access databases.

A better option is to not use Access at all and keep Trapeze and its permissions working as designed. Trapeze has its own reporting software that uses standard SQL and the built in permissions.

 

[Banana]

That's certainly another option. I've not used either Trapeze or Oracle, but had assumed she needed Access for some reasons. Christshis will have to decide if it's easier to chuck Access or if Christshis does need it, just interface it directly to Oracle with a limited account.