| 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
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
-----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" <ORACLE-L_at_fatcity.com>
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: peter.schauss_at_ngc.com
>
> 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: ListGuru_at_fatcity.com (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: tanel.poder.003_at_mail.ee 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: ListGuru_at_fatcity.com (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: Jacques.Kilchoer_at_quest.com 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: ListGuru_at_fatcity.com (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: Mark Brooks INET: mark_at_foxhillgroup.com 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: ListGuru_at_fatcity.com (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 - 21:29:24 CDT
 |
 |