Re: Access Limitation

Hm,

thinking a bit more about this idea generated on-the-fly, there's one more problem: the server side trigger has to know the user's password before it can add a new prefix to it. It can't be obtained from DD, because those passwords are hashed AFAIK and can't be decrypted. This mean's that the application logic has to be more complex - the application should store user passwords in the DB either in plain text or in two-way encryption format.

That makes things more complex, but should still do the job... erm... I hope. (as I told, it's late :)

Comments welcome,
Tanel.

"Tanel Poder" <tanel@@peldik.com> wrote in message news:3e59654d_2_at_news.estpak.ee...
> You could even user password protected roles in the same context, but this
> will likely give problems in multiuser environment because roles are
global.
>
> Tanel.
>
>
> "Tanel Poder" <tanel@@peldik.com> wrote in message
> news:3e596441_1_at_news.estpak.ee...
> > 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:37:37 CST