Re: How to keep "root" out?

From: Tim Gorman <tim_at_sagelogix.com>
Date: Thu, 28 Aug 2003 16:14:26 -0800
Message-ID: <F001.005CDD11.20030828161426@fatcity.com>

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: Tim Gorman
  INET: tim_at_sagelogix.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 Aug 28 2003 - 19:14:26 CDT