Re: Access Limitation

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:23:14 CST