| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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.
I think this kind of solution was discussed on the list many months ago.
Problem description at my previous company: We had a two-tier application written that required users to sign on with their own Oracle userid/password. (We implemented password expiration rules etc...) The application, while connected to the database as the user, would issue insert/update/delete statements against database tables in a common "APPLICATION" schema.
We wanted to prevent a user from being able to connect to the database using another application (SQL*Plus, Toad, Excel using ODBC, etc.) and issue the same insert/update/delete statements that the application could do.
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.
-----Original Message-----
No, if you code your trigger to check if the program is your apps name, then renaming
TOAD to TODD doesn't change anything.
But of course if you change TOAD to your apps name, then this scheme fails. But as I
stated, these kinds of methods only help against dumb users.
If you want true security you have to have some kind of middle layer enforcing
security and business/data rules.. (could be implemented inside database as well,
through PL/SQL packages and no direct access to tables for example).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com 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 Wed Jul 09 2003 - 18:35:48 CDT
 |
 |