Rem sql92_security_chk.sql
Rem ---------------------------------------------------------------------------
Rem       Author: Simon Fletcher, Pentest Limited
Rem      Program: SQL92_SECURITY check
Rem               This query checks for potential issues prior to setting 
Rem               the initialization parameter SQL92_SECURITY to true.
Rem               Basically is queies for users with UPDATE/DELETE privileges
Rem               on tables but without SELECT privileges.
Rem      Release: 15 February 2010
Rem    $Revision: 30 $
Rem        $Date: 2010-02-12 13:34:00 +0000 (Fri, 12 Feb 2010) $
Rem ----------------------------------------------------------------------------
Rem Copyright 2010, Simon Fletcher, email: simon.fletcher@pentest.co.uk
Rem
Rem    This program is free software: you can redistribute it and/or modify
Rem    it under the terms of the GNU General Public License as published by
Rem    the Free Software Foundation, either version 3 of the License, or
Rem    (at your option) any later version.
Rem
Rem    This program is distributed in the hope that it will be useful,
Rem    but WITHOUT ANY WARRANTY; without even the implied warranty of
Rem    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
Rem    GNU General Public License for more details.
Rem
Rem    For a copy of the GNU General Public License see
Rem    <http://www.gnu.org/licenses/>.
Rem ----------------------------------------------------------------------------

set timing on
spool sql92_security_chk

select u1.name grantee,
       tpm.name privilege,
       u2.name owner,
       o.name table_name
from   sys.user$ u1, 
       sys.user$ u2, 
       sys.obj$ o,
       sys.table_privilege_map tpm,
       ( select nvl( r.user#, oa.grantee# ) user#,
                oa.obj#,
                oa.privilege#
         from   sys.objauth$ oa,
                ( select distinct CONNECT_BY_ROOT grantee# user#, 
                         grantee#, 
                         privilege# role#, 
                         option$
                  from   sys.sysauth$ sa
                  where  privilege# > 0
                  connect by prior sa.privilege# = sa.grantee#
                     start with grantee# in(select user# from sys.user$ where type# = 1) 
                     or grantee#=1
                ) r
         where  oa.grantee# = r.role# (+)
         and    oa.privilege# in(select privilege from sys.table_privilege_map where name in('UPDATE', 'DELETE'))
         and    nvl( r.user#, oa.grantee# ) not in(
                   select u.user#
                   from   sys.user$ u,
                          ( select distinct sa.grantee#
                            from   sys.sysauth$ sa
                            connect by prior sa.grantee# = sa.privilege#
                               start with sa.privilege# 
                                  in( select privilege from sys.system_privilege_map spm where spm.name = 'SELECT ANY TABLE')
                           ) x
                   where  u.user# = x.grantee#
                   and    u.type# = 1)
         and    oa.obj# not in( 
                   select oa2.obj#
                   from   sys.objauth$ oa2,
                          ( select distinct CONNECT_BY_ROOT grantee# user#, 
                                   grantee#, 
                                   privilege# role#
                            from   sys.sysauth$ sa
                            where  privilege# > 0
                            connect by prior sa.privilege# = sa.grantee#
                            start with grantee#=1
                           ) x
                   where  oa2.grantee# = x.role#
                   and    oa2.privilege# in(select privilege from sys.table_privilege_map where name = 'SELECT')
                   union 
                   select oa2.obj#
                   from   sys.objauth$ oa2
                   where  oa2.grantee# = 1
                   and    oa2.privilege# in(select privilege from sys.table_privilege_map where name = 'SELECT')
                )
         minus
         select nvl( r.user#, oa.grantee# ) user#,
                oa.obj#,
                tpm.privilege
         from   sys.objauth$ oa,
                sys.table_privilege_map tpm,
                ( select distinct CONNECT_BY_ROOT grantee# user#, 
                         grantee#, 
                         privilege# role#, 
                         option$
                  from   sys.sysauth$ sa
                  where  privilege# > 0
                  connect by prior sa.privilege# = sa.grantee#
                     start with grantee# in(select user# from sys.user$ where type# = 1) 
                     or grantee#=1
                ) r
         where  oa.grantee# = r.role# (+)
         and    oa.privilege# in(select privilege from sys.table_privilege_map where name = 'SELECT')
         and    tpm.name in('UPDATE', 'DELETE')
       ) ud
where  u1.user# = ud.user# 
and    u2.user# = o.owner#
and    o.obj# = ud.obj# 
and    tpm.privilege = ud.privilege#
union
( select u.name,
         s.name privilege,
         null,
         null
  from   sys.user$ u,
         sys.system_privilege_map s,
         ( select distinct CONNECT_BY_ROOT sa.privilege# root_privilege#, 
                  sa.grantee#,
                  sa.privilege#
           from   sys.sysauth$ sa
           connect by prior sa.grantee# = sa.privilege#
              start with sa.privilege# 
                 in( select privilege from sys.system_privilege_map spm where spm.name in( 'UPDATE ANY TABLE', 'DELETE ANY TABLE'))
         ) t
  where  u.user# = t.grantee#
  and    u.type# = 1
  and    s.privilege = t.root_privilege#
  minus
  select u.name,
         x.name privilege,
         null, 
         null
  from   sys.user$ u,
         sys.system_privilege_map s,
         sys.system_privilege_map x,
         ( select distinct CONNECT_BY_ROOT sa.privilege# root_privilege#, 
                  sa.grantee#,
                  sa.privilege#
           from   sys.sysauth$ sa
           connect by prior sa.grantee# = sa.privilege#
              start with sa.privilege# 
                 in( select privilege from sys.system_privilege_map spm where spm.name = 'SELECT ANY TABLE')
         ) t
  where  u.user# = t.grantee#
  and    u.type# = 1
  and    s.privilege = t.root_privilege#
  and    x.name in( 'UPDATE ANY TABLE', 'DELETE ANY TABLE')
)
/
spool off