Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Disabling access of third party products

RE: Disabling access of third party products

From: Larry G. Elkins <>
Date: Wed, 5 Jul 2000 18:20:42 -0500
Message-Id: <>


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.


Larry G. Elkins
The Elkins Organization Inc.

-----Original Message-----
From: []On Behalf Of
Sent: Tuesday, July 04, 2000 12:06 PM
To: Multiple recipients of list ORACLE-L Subject: Disabling access of third party products

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.



Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Wed Jul 05 2000 - 18:20:42 CDT

Original text of this message