Re: Access Limitation

Hello!

You might code your application to add a prefix or suffix to the username/password which a user types in and then send this information to Oracle.

When users don't know the prefix/suffix, they can't log on to db either. Of course the users can monitor network traffic and find out the secret.

For that you could use some kind of complex algorithm for changing the password prefix in DB every time a user logs on, using AFTER LOGON trigger. You have to use autonomous transaction for that. When using trigger to change the password, sniffing sql*net wont help, because the alter user clause isn't issued on client side. Of course the application has to be aware of the same algorithm, that it knows which prefix to add next time.

Issues:

1. *any* client side application can still be debugged/reverse-engineered, thus revealing the algorithm
2. since every DDL issues an implicit commit, heavy commiting can occur in (web) environments, where logon is made for every request (actually I'm not sure whether a commit record is written to logs and lgwr posted when transaction is zero size - hasn't even started. It's too late here to test either. Anyone?)
3. if sql*net is traced on client side and logon *doesn't* succeed for some reason, then the afterupgrade trigger doesn't fire - the same password remains and the user can log on with that password once (once is enough to be worried about).

Actually this problem could be relieved by auditing failed logon attempts and creating a trigger for AUD$ table - when a non successful logon is tried, the trigger automatically changes the user's password to next one, of course the application has to be aware of that as well.

Because encrypting is the *only* real protection for information when one has physical access to the hardware (client + network connection to server), it might be reasonable to use private-public key encryption to propagate the new password to client. But that would require writing the algorithms (maybe obfuscation toolkit could be used) for both server side triggers and client side. Also it would require a mechanism for getting the encrypted password to the client, this could be achieved with a dummy account with create session privilege and one table for storing the encrypted passwords. This means, two Oracle logins have to be made when a user logs on - first one for getting the new encrypted password to client (and setting the appropriate pwd for user in Oracle) and second is the actual login to the right schema.

But anyway, novadays most users aren't directly connecting to Oracle, there usually is some kind of application logic in middleware, in application servers or as PL/SQL packages in DB.

Hope it helps,
Tanel.

"DA Morgan" <damorgan_at_exesolutions.com> wrote in message news:3E591290.4B4D0F7_at_exesolutions.com...
> Pete Finnigan wrote:
>
> > Hi Daniel
> >
> > a comment in-line:
> >
> > In article <3E56AAD1.E6B908E4_at_exesolutions.com>, DA Morgan
> > <damorgan_at_exesolutions.com> writes
> > >Life Learner wrote:
> > >
> > >> Hi there,
> > >>
> > >> I'd like to know how to prevent end users from accessing system
directly via
> > >> sqlplus, toad etc, while let them do their work only in application
level
> > >> like forms, proc etc.
> > >>
> > >> thx.
> > >
> > >There are a number of methods. Among them:
> > >
> > >An after logon trigger that checks the connecting software ...
> > >
> > >SELECT program
> > >FROM v_$session;
> > >
> >
> > This cannot be easily tricked by just renaming the sqlplus binary to the
> > same name as the binary for the application as the following shows:
> >
> > oracle:venus> sqlplus sys/change_on_install
> >
> > SQL*Plus: Release 8.1.7.0.0 - Production on Sat Feb 22 20:47:13 2003
> >
> > (c) Copyright 2000 Oracle Corporation. All rights reserved.
> >
> > Connected to:
> > Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
> > With the Partitioning option
> > JServer Release 8.1.7.0.0 - Production
> >
> > SQL> create user pete identified by pete;
> >
> > User created.
> >
> > SQL> grant create session to pete;
> >
> > Grant succeeded.
> >
> > SQL> grant select on v_$session to pete;
> >
> > Grant succeeded.
> >
> > SQL> connect pete/pete
> > Connected.
> > SQL> select program from v$session
> > 2 where username='PETE';
> >
> > PROGRAM
> > ------------------------------------------------
> > sqlplus_at_venus (TNS V1-V3)
> >
> > SQL> !cp $ORACLE_HOME/bin/sqlplus ./someapp
> >
> > SQL> exit
> > Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 -
> > Production
> > With the Partitioning option
> > JServer Release 8.1.7.0.0 - Production
> > oracle:venus> ./someapp pete/pete
> >
> > SQL*Plus: Release 8.1.7.0.0 - Production on Sat Feb 22 20:47:13 2003
> >
> > (c) Copyright 2000 Oracle Corporation. All rights reserved.
> >
> > Connected to:
> > Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
> > With the Partitioning option
> > JServer Release 8.1.7.0.0 - Production
> >
> > SQL> select program from v$session
> > 2 where username='PETE';
> >
> > PROGRAM
> > ------------------------------------------------
> > sqlplus_at_venus (TNS V1-V3)
> >
> > SQL>
> >
> > I don't have a copy of toad here at present to check if renaming it has
> > the same result.
> >
> > Of course someone a bit more skilled could trick the database as without
> > looking into TNS in more detail i suspect that the name of the program
> > is sent as part of a TNS packet to the RDBMS, this could be changed, so
> > relying on v$session is not 100% foolproof but it would certainly stop
> > casual and normal business users.
> >
> > I just thought i would share that little experiment to see how reliable
> > v$session is as a checking mechanism for the source of program
> >
> > Kind regards
> >
> > Pete
> > --
> > Pete Finnigan
> >
> > Email : pete_at_peterfinnigan.demon.co.uk
> > Email : pete_at_petefinnigan.com
> >
> > Web site: http://www.petefinnigan.com
> >
> > Independent consultant specialising in Oracle security. Pete Finnigan is
the
> > author of the recently published book about Oracle security from the
SANS
> > Institute "Oracle security Step-by-step (A survival guide for Oracle
security)"
> > - see http://store.sans.org for details.
> >
> > Some recently published articles include:
> >
> > http://online.securityfocus.com/infocus/1644 - "SQL injection and
Oracle - part
> > one"
> >
> > http://online.securityfocus.com/infocus/1646 - "SQL injection and
Oracle - part
> > two"
>
> There are ways to trick v_$session, no question about it. But the chances
of an
> end-user realizing that are slim to none. Especially if the first time
they try to
> connect the indication they get that there is a problem comes from a
manager that
> informs them the next time they try they can pick up their final paycheck
on the
> way out the door. That greatly changes the risk-reward ratio.
>
> The most secure way is a required role that is invoked in the application
itself.
>
> Daniel Morgan
>
Received on Sun Feb 23 2003 - 18:18:41 CST