Oracle-L: Re: How to keep "root" out?

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Thu, 28 Aug 2003 22:24:26 -0800
Message-ID: <F001.005CDDA0.20030828222426@fatcity.com>

Re: How to keep "root" out?Hi!

But how would you restrict an user from logging on based on OSUSER value? If you create an unhandled exception, then this works only for users without ADMINISTER DATABASE TRIGGER privilege. The ones who have this priv (like sysdba priv provides) will be able to log on without problems...

Tanel.

Original Message ----- From: Tim Gorman To: Multiple recipients of list ORACLE-L Sent: Friday, August 29, 2003 3:14 AM Subject: Re: How to keep "root" out?

Couldn't you just retrieve the column OSUSER from V$SESSION?

Perhaps something like the following:

SQL> create or replace trigger osusertrg

      2          after logon
      3          on database
      4  declare
      5          v_osuser        varchar2(30);
      6  begin
      7          dbms_output.enable(20000);
      8          select distinct decode(osuser, 'root', 'root', 'not root')
      9          into    v_osuser
     10          from    v$session
     11          where   audsid = userenv('SESSIONID');
     12          dbms_output.put_line('osuser is "'||v_osuser||'"');
     13  end osusertrg;
     14  /

Trigger created.

    SQL> show errors
    No errors.
    SQL>
    SQL> connect scott/tiger
    Connected.

    SQL> variable buffer varchar2(100)
    SQL> variable status number
    SQL> exec dbms_output.get_line(:buffer, :status)

PL/SQL procedure successfully completed.

SQL> print buffer

BUFFER

osuser is "not root"

Be aware that when you are connected as SYS then all sessions have the same AUDSID and USERENV('SESSIONID') values of 0...

Hope this helps...

-Tim

on 8/28/03 2:34 PM, Diego Cutrone at diegocutrone_at_yahoo.com.ar wrote:

>
> I don't know if this will work.
> But I'd write an external procedure (a shell) that
> checks the OS userid that's logging into the
> database...
> (may be "who am i", it works even with "su")
>
> -------------------
> bash-2.04# id
> uid=0(root) gid=0(root) groups=0(root),48(apache)
> bash-2.04# su - oracle
> oracle::/home/oracle> who am i
> costos!root pts/1 Aug 28 16:45
> oracle::/home/oracle>
> -------------------
>
> I'd put this code in the logon trigger.....
> I'm not sure if this will work with "internal" user...
>
> Greetings
> Diego Cutrone
>
>
>
>

  >> Just for grins, I'll ask this question... Is there

> any >way to keep the Unix "root" user from logging
> into the >database (i.e. connect internal or / as
> sysdba)? >Currently using 8.1.7.4 on Solaris 8 here.

  >> 
  >> We have a couple people in our Unix admin group that

> vfeel the need to "help" by writing their own DB

  >> monitoring scripts. Of course, they don't know what
  >> t>hey're talking about. They do not have formal

> logins >for the database, but since they are root
> users they >are connecting via "connect internal".
> This is not >only counterproductive but actually a
> potential >security issue--just because someone has
> root doesn't >necessarily entitle them to see the data
> in the >database. What if it is a payroll database?

  >> 
  >> So, I'm curious, is there any way to prevent access
  >> via "connect internal" or "/ as sysdba"?
  >> 
  >> Thanks in advance.

>
> W
>
> ------------
> Internet GRATIS es Yahoo! Conexión
> 4004-1010 desde Buenos Aires. Usuario: yahoo; contraseña: yahoo
> Más ciudades: http://conexion.yahoo.com.ar

-- 
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).

Received on Fri Aug 29 2003 - 01:24:26 CDT