Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Restricting access via sqlplus

RE: Restricting access via sqlplus

From: <>
Date: Thu, 10 Jul 2003 03:31:41 -0700
Message-ID: <>

We used a similar idea on an in-house application - hard-coded the role password into the application.

Neither approach is perfect, if someone wanted to try hard enough they could reverse-engineer the app, or try and pick up the password by spying on the sql*net connection.

For practical security, you need to back these schemes up with auditing failed logons, audit triggers to record who made changes to data, etc.

Simon Anderson

"Jacques Kilchoer" <[EMAIL PROTECTED]>
10/07/2003 01:29
Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        Subject:        RE: Restricting access via sqlplus

To prevent these kinds of problems, the only solution I've ever thought was feasible was how we implemented it at a previous company of mine.

Our solution:
Each user would be granted a role, but the role would not be a default role. The role was protected by a password. The application would connect to the database, read the encrypted password for the role from a database table, use a two-way decryption scheme to decrypt the password, and then use the "set role" command to give the user the role that would allow insert/update/delete to be done from inside the application.

The same could not be accomplished from inside SQL*Plus or Toad because the user would not know the password to use for the "set role" command. The role password could be changed as often as deemed necessary, except that the new password would have to be stored in the database in an encrypted form.

This scheme has an obvious flaw, that anyone able to crack the encryption scheme could decrypt the role password, do a "set role" and then issue DML commands from inside a SQL client. And it would only work if you are building and deploying your own in-house application. But at the time we could not think of anything better. This was using Oracle 8.0.


Please see the official ORACLE-L FAQ:


Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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 Thu Jul 10 2003 - 05:31:41 CDT

Original text of this message