RE: Disabling access of third party products

Larry,

An addition to this might be to create a login trigger that checks if the user is connecting via an authorised program.

If the user is not connecting via such a program (eg if connecting via SQL Plus) then the trigger could disconnect them.

I'm not sure but this may be only easily possible under 8i.

Someone else may be able to help with the code for such a trigger.

Regards,

Bruce Reardon
Analyst / Programmer
Comalco Aluminium (Bell Bay)
mailto:bruce.reardon_at_comalco.riotinto.com.au

-----Original Message-----

From: Larry G. Elkins [mailto:elkinsl_at_flash.net] Sent: Thursday, 6 July 2000 10:29
To: Multiple recipients of list ORACLE-L Subject: RE: Disabling access of third party products

Mahip,

One way you might want to consider is ROLES and using something like ALTER USER XXX DEFAULT ROLE NONE. You could then restrict the user to only the create session priv. Your application could then enable the appropriate role(s) upon startup using DBMS_SESSION.SET_ROLE. You can take this to extraordinary lengths. For example, on a very basic level, you might have the roles password protected. So, even if you run into a power user with MS Access, for them to enable the roles(s), they would need to (1) use a pass-through query to issue the command to enable the role, (2) know the syntax for enabling a role, and, (3) they would have to know the password for the role as well. The password could be stored in your app. Or, you can take it even further, storing the role passwords in package variables and using a DB function to return it/them, store them encrypted in a table, etc. You get the idea -- you can really go pretty far with how secure you want it to be. You can keep it pretty basic, or, you can get really complex.

There is a lot more that can be said on the subject; but, the basic idea is that, in the scenario above, by default all they can do is connect to the database. To get any priv's, to be able to select from any tables, execute functions or procedures, roles would have to be enabled on the fly. There are lots of variations on this approach, and, exactly how simple or complex you want to get with this is up to you and your needs.

Regards,

Larry G. Elkins
The Elkins Organization Inc.
elkinsl_at_flash.net
214.954.1781

-----Original Message-----

mahib.narayan_at_bt.com
Sent: Tuesday, July 04, 2000 12:06 PM
To: Multiple recipients of list ORACLE-L

Hi Lists,

Can anybody suggest a method to disable access to third party tools like toad, sql navigator etc. for users. I am using Oracle805 on Sun Solaris 2.6, I know through Product_user_profiles table you can disable access for sql*plus but not third party tools. Received on Wed Jul 05 2000 - 19:46:31 CDT