Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h7T1kQK03784 for ; Thu, 28 Aug 2003 20:46:26 -0500 X-ClientAddr: 66.27.56.212 Received: from www3.fatcity.com (rrcs-west-66-27-56-212.biz.rr.com [66.27.56.212]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id h7T1kN303776 for ; Thu, 28 Aug 2003 20:46:23 -0500 Received: (from root@localhost) by www3.fatcity.com (8.11.6/8.11.6) id h7SNFVR12080 for oracle-l@orafaq.net; Thu, 28 Aug 2003 16:15:31 -0700 Received: by fatcity.com (05-Jun-2003/v1.0g-b73/bab) via fatcity.com id 005CDD11; Thu, 28 Aug 2003 16:14:26 -0800 Message-ID: Date: Thu, 28 Aug 2003 16:14:26 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Tim Gorman Sender: ml-errors@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Tim Gorman Subject: Re: How to keep "root" out? Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 73; ListGuru (c) 1996-2003 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-type: multipart/alternative; boundary="B_3144935545_5423111" --B_3144935545_5423111 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable 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 =3D userenv('SESSIONID'); > 12 dbms_output.put_line('osuser is "'||v_osuser||'"'); > 13 end osusertrg; > 14 / >=20 > Trigger created. >=20 > SQL> show errors > No errors. > SQL>=20 > SQL> connect scott/tiger > Connected. > SQL> variable buffer varchar2(100) > SQL> variable status number > SQL> exec dbms_output.get_line(:buffer, :status) >=20 > PL/SQL procedure successfully completed. >=20 > SQL> print buffer >=20 > BUFFER > -------------------------------------------------------------------------= ----- > -- > osuser is "not root" Be aware that when you are connected as SYS then all sessions have the same AUDSID and USERENV(=8CSESSIONID=B9) values of 0... Hope this helps... -Tim on 8/28/03 2:34 PM, Diego Cutrone at diegocutrone@yahoo.com.ar wrote: >=20 > 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") >=20 > ------------------- > bash-2.04# id > uid=3D0(root) gid=3D0(root) groups=3D0(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> > ------------------- >=20 > I'd put this code in the logon trigger..... > I'm not sure if this will work with "internal" user... >=20 > Greetings=20 > Diego Cutrone >=20 >=20 >=20 >=20 >> 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. >>=20 >> 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? >>=20 >> So, I'm curious, is there any way to prevent access >> via "connect internal" or "/ as sysdba"? >>=20 >> Thanks in advance. >=20 > W >=20 > ------------ > Internet GRATIS es Yahoo! Conexi=F3n > 4004-1010 desde Buenos Aires. Usuario: yahoo; contrase=F1a: yahoo > M=E1s ciudades: http://conexion.yahoo.com.ar --B_3144935545_5423111 Content-type: text/html; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable Re: How to keep "root" out? Couldn't you just retrieve the column OSUSER from V$SESS= ION?

Perhaps something like the following:

SQL> create or replace trigger o= susertrg
  2          after l= ogon
  3          on data= base
  4  declare
  5          v_osuse= r        varchar2(30);
  6  begin
  7          dbms_ou= tput.enable(20000);
  8          select = distinct decode(osuser, 'root', 'root', 'not root')
  9          into &n= bsp;  v_osuser
 10          from  &= nbsp; v$session
 11          where  =  audsid =3D 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@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=3D0(root) gid=3D0(root) groups=3D0(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 questio= n... Is there
> any >way to keep the Unix "root&q= uot; 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 wri= ting their own DB
>> monitoring scripts. Of course, they d= on't know what
>> t>hey're talking about. They do not have formal
> logins >for the database, but since th= ey 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
 --B_3144935545_5423111-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: tim@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@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).