RE: Restricting access via sqlplus
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] Sent by: [EMAIL PROTECTED] 10/07/2003 01:29 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: 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: http://www.orafaq.net -- Author: 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).
Re: Restricting access via sqlplus
Hi Jacques, It sounds like the encrypted password is read by the client? and decrypted on the client? or in the database as a package procedure?. If it was decrypted in the client and then the set role command was sent to the database the password could be read from the network with a tool such as snoop on Unix or using SQL*Net support level trace as that shows packet contents in the SQL trace. The latter could be setup by a user on his PC attempt a logon to the database and then read the password from the trace file. If the decryption and set role were to be done in a package and the password is not passed over the network then its better. You could also encrypt the network traffic of course. But as you say there is still a risk from someone discovering the encryption scheme. thanks for sharing your solution kind regards Pete -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan 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).
RE: Restricting access via sqlplus
The problem is that if the decryption and set role were done inside a database package, then that means that the Oracle database user needs execute privilege on the package, and so the user could call the package from inside SQL*Plus on the client. At my old company they were talking about encrypting the network traffic but I left before they implemented that, and I don't know if they ever did. -Original Message- From: Pete Finnigan [mailto:[EMAIL PROTECTED] It sounds like the encrypted password is read by the client? and decrypted on the client? or in the database as a package procedure?. If it was decrypted in the client and then the set role command was sent to the database the password could be read from the network with a tool such as snoop on Unix or using SQL*Net support level trace as that shows packet contents in the SQL trace. The latter could be setup by a user on his PC attempt a logon to the database and then read the password from the trace file. If the decryption and set role were to be done in a package and the password is not passed over the network then its better. You could also encrypt the network traffic of course. But as you say there is still a risk from someone discovering the encryption scheme. -- 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).
RE: Restricting access via sqlplus
We use a similar process for several of our in-house applications. The users get an Oracle id that only has create session. The application prompts for the users username and password and attempts to login to the database. If the login succeeds the application closes the connection and then logs in behind the scenes using an application account. This way if the user connects to the database with any tool (Toad, Navigator, Access, ...) they have no object rights and cannot do anything. Downside is the DBA sees a lot of application connections in the session table. Some of the applications maintain a table that maps user names to sessions to make support easier. Works pretty well most of the time. YMMV. Mark -Original Message- Jacques Kilchoer Sent: Wednesday, July 09, 2003 5:29 PM To: Multiple recipients of list ORACLE-L 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 password -Original Message- Sent: mercredi, 9. juillet 2003 14:59 To: Multiple recipients of list ORACLE-L Hi! 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). Cheers, Tanel. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Wednesday, July 09, 2003 11:39 PM Tanel, If I change TOAD.EXE to TODD.EXE, this scheme fails instantly ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Wednesday, July 09, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Hi! I think sqlplus product profile isn't a good idea, because some smarter ones might be using TOAD, SQL Navigator or SQL Worksheet... What you might want to do is to write an after logon trigger which checks the app name from v$session and allows logon if and only if app name (v$session.program) is your 3rd party one. If app name isn't correct, then your trigger raises an exception. But of course, it only protects you from dumb users. Another way would be playing with roles, but since you have 3rd party app, it might be problematic. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 10:29 PM Is there a way to prevent end users from connecting directly to the database via sqlplus without restricting access of those same users via application code. The application is a third party package which prompts for an id and password and then uses that id/password to connect to the database. I found a note the the archives which suggested making an entry into the SQLPLUS_PRODUCT_PROFILE table, but I have not been able to make this work. Oracle version: 8.1.7 AIX 4.3.3 Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City
RE: Restricting access via sqlplus
Title: RE: Restricting access via sqlplus
Something what I have used in past ...
DROP TABLE SYSTEM.SQLPLUS_COMMANDS CASCADE CONSTRAINTS
/
CREATE TABLE SYSTEM.SQLPLUS_COMMANDS
(
COMMAND VARCHAR2(20 BYTE) NULL
)
TABLESPACE TOOLS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 40K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL
MONITORING
/
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'COPY');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'EDIT');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'EXECUTE');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'GET');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'HOST');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'PASSWORD');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'RUN');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'SAVE');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'SET');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'SPOOL');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'START');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'ALTER');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'ANALYZE');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'AUDIT');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'CONNECT');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'CREATE');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'DELETE');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'DROP');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'GRANT');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'INSERT');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'LOCK');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'NOAUDIT');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'RENAME');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'REVOKE');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'SELECT');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'SET ROLE');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'SET TRANSACTION');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'TRUNCATE');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'UPDATE');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'BEGIN');
INSERT INTO SQLPLUS_COMMANDS ( COMMAND ) VALUES ( 'DECLARE');
COMMIT;
PROCEDURE restrict_sqlplus
(SPLUS_USERID VARCHAR2)
IS
--
CURSOR C1 IS SELECT B.COMMAND CMD_TXT FROM SQLPLUS_COMMANDS B;
--
BEGIN
--
FOR C1REC IN C1 LOOP
EXIT WHEN C1%NOTFOUND;
EXECUTE IMMEDIATE 'INSERT INTO PRODUCT_USER_PROFILE VALUES(''SQL*Plus'','||
||SPLUS_USERID||||','||||C1REC.CMD_TXT||||
', NULL, NULL, ''DISABLED'', NULL, NULL)';
END LOOP;
COMMIT;
--
END restrict_sqlplus;
--
/
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-Original Message-
From: Schauss, Peter [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 09, 2003 3:30 PM
To: Multiple recipients of list ORACLE-L
Subject: Restricting access via sqlplus
Is there a way to prevent end users from connecting directly
to the database via sqlplus without restricting access of those
same users via application code. The application is a third party
package which prompts for an id and password and then uses that
id/password to connect to the database.
I found a note the the archives which suggested making an entry into
the SQLPLUS_PRODUCT_PROFILE table, but I have not been able to make
this work.
Oracle version: 8.1.7
AIX 4.3.3
Thanks,
Peter Schauss
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Schauss, Peter
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).
*This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank
you.*1
Re: Restricting access via sqlplus
Hi! I think sqlplus product profile isn't a good idea, because some smarter ones might be using TOAD, SQL Navigator or SQL Worksheet... What you might want to do is to write an after logon trigger which checks the app name from v$session and allows logon if and only if app name (v$session.program) is your 3rd party one. If app name isn't correct, then your trigger raises an exception. But of course, it only protects you from dumb users. Another way would be playing with roles, but since you have 3rd party app, it might be problematic. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 10:29 PM Is there a way to prevent end users from connecting directly to the database via sqlplus without restricting access of those same users via application code. The application is a third party package which prompts for an id and password and then uses that id/password to connect to the database. I found a note the the archives which suggested making an entry into the SQLPLUS_PRODUCT_PROFILE table, but I have not been able to make this work. Oracle version: 8.1.7 AIX 4.3.3 Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).
RE: Restricting access via sqlplus
INSERT INTO system.sqlplus_product_profile (userid, attribute, char_value) VALUES (oracle_user_name, 'CONNECT', 'DISABLED') / Works for us. Most people can't connect via SQL*Plus. I can connect to the production database under my own username, but not perform other DML such as updates, because there is an entry for me in the product profile for attribute = 'UPDATE'. When I type an UPDATE command the following happens: PROD update cn_people SP2-0544: invalid command: update Leslie -Original Message- Sent: Wednesday, July 09, 2003 3:30 PM To: Multiple recipients of list ORACLE-L Is there a way to prevent end users from connecting directly to the database via sqlplus without restricting access of those same users via application code. The application is a third party package which prompts for an id and password and then uses that id/password to connect to the database. I found a note the the archives which suggested making an entry into the SQLPLUS_PRODUCT_PROFILE table, but I have not been able to make this work. Oracle version: 8.1.7 AIX 4.3.3 Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tierstein, Leslie 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).
RE: Restricting access via sqlplus
Title: RE: Restricting access via sqlplus Tanel, If I change TOAD.EXE to TODD.EXE, this scheme fails instantly ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Tanel Poder [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 09, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Subject: Re: Restricting access via sqlplus Hi! I think sqlplus product profile isn't a good idea, because some smarter ones might be using TOAD, SQL Navigator or SQL Worksheet... What you might want to do is to write an after logon trigger which checks the app name from v$session and allows logon if and only if app name (v$session.program) is your 3rd party one. If app name isn't correct, then your trigger raises an exception. But of course, it only protects you from dumb users. Another way would be playing with roles, but since you have 3rd party app, it might be problematic. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 10:29 PM Is there a way to prevent end users from connecting directly to the database via sqlplus without restricting access of those same users via application code. The application is a third party package which prompts for an id and password and then uses that id/password to connect to the database. I found a note the the archives which suggested making an entry into the SQLPLUS_PRODUCT_PROFILE table, but I have not been able to make this work. Oracle version: 8.1.7 AIX 4.3.3 Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: Restricting access via sqlplus
Title: RE: Restricting access via sqlplus Hi! 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). Cheers, Tanel. - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Wednesday, July 09, 2003 11:39 PM Subject: RE: Restricting access via sqlplus Tanel, If I change TOAD.EXE to TODD.EXE, this scheme fails instantly ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Tanel Poder [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 09, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Subject: Re: Restricting access via sqlplus Hi! I think sqlplus product profile isn't a good idea, because some smarter ones might be using TOAD, SQL Navigator or SQL Worksheet... What you might want to do is to write an after logon trigger which checks the app name from v$session and allows logon if and only if app name (v$session.program) is your 3rd party one. If app name isn't correct, then your trigger raises an exception. But of course, it only protects you from dumb users. Another way would be playing with roles, but since you have 3rd party app, it might be problematic. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 10:29 PM Is there a way to prevent end users from connecting directly to the database via sqlplus without restricting access of those same users via application code. The application is a third party package which prompts for an id and password and then uses that id/password to connect to the database. I found a note the the archives which suggested making an entry into the SQLPLUS_PRODUCT_PROFILE table, but I have not been able to make this work. Oracle version: 8.1.7 AIX 4.3.3 Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).
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).
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 password -Original Message- Sent: mercredi, 9. juillet 2003 14:59 To: Multiple recipients of list ORACLE-L Hi! 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). Cheers, Tanel. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Wednesday, July 09, 2003 11:39 PM Tanel, If I change TOAD.EXE to TODD.EXE, this scheme fails instantly ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Wednesday, July 09, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Hi! I think sqlplus product profile isn't a good idea, because some smarter ones might be using TOAD, SQL Navigator or SQL Worksheet... What you might want to do is to write an after logon trigger which checks the app name from v$session and allows logon if and only if app name (v$session.program) is your 3rd party one. If app name isn't correct, then your trigger raises an exception. But of course, it only protects you from dumb users. Another way would be playing with roles, but since you have 3rd party app, it might be problematic. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 10:29 PM Is there a way to prevent end users from connecting directly to the database via sqlplus without restricting access of those same users via application code. The application is a third party package which prompts for an id and password and then uses that id/password to connect to the database. I found a note the the archives which suggested making an entry into the SQLPLUS_PRODUCT_PROFILE table, but I have not been able to make this work. Oracle version: 8.1.7 AIX 4.3.3 Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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). -- 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
