| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Table access
Tracy Rahmlow wrote:
>
> I am looking for a script that I can supply a table name and it
> returns all users that have access to it (either directly, thru system
> priveleges or thru roles) and what the access is. Does anybody have
> something like this that I can use? Thanks
>
> American Express made the following
> annotations on 01/13/2004 08:16:14 AM
> ------------------------------------------------------------------------------
> ******************************************************************************
>
> "This message and any attachments are solely for the intended
> recipient and may contain confidential or privileged information. If
> you are not the intended recipient, any disclosure, copying, use, or
> distribution of the information included in this message and any
> attachments is prohibited. If you have received this communication in
> error, please notify us by reply e-mail and immediately and
> permanently delete this message and any attachments. Thank you."
>
> ******************************************************************************
>
> ==============================================================================
Not exactly what you request, but pretty close ...
rem
rem whocan.sql rem rem Copyright (C) Oriole Software, 2003 rem rem Downloaded from http://www.oriolecorp.com rem rem This script for Oracle database administration is free software; you rem can redistribute it and/or modify it under the terms of the GNU General rem Public License as published by the Free Software Foundation; either rem version 2 of the License, or any later version. rem rem This script 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 You should have received a copy of the GNU General Public License rem along with this program; if not, write to the Free Software rem Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.rem
--
-- This scripts allows you to check who can either SEE (i.e. SELECT
from)
-- or MODIFY (INSERT, UPDATE or DELETE) a given table or view.
-- This is fairly easy to check when the right was directly granted,
much
-- less when rights are inherited through roles.
--
-- Check your sensitive data ...
--
-- Usage : @whocan SEE|MODIFY [owner.]tablename
--
-- Example : @whocan see scott.emp
--
-- No row returned means that the object doesn't exist (SYS and SYSTEM
-- could otherwise access it). If no owner is specified, it defaults
-- to the current schema.
--
-- For DBAs only, as usual.
--
--
-------------------------------------------------------------------------
--
set verify off
select u.name "USERNAME"
from sys.user$ u
where u.type# = 1
and (exists (select null
from sys.sysauth$
where privilege# in (select privilege
from sys.system_privilege_map
where (upper('&1') = 'SEE'
and name = 'SELECT ANY TABLE')
or (upper('&1') = 'MODIFY'
and name in ('INSERT ANY
TABLE',
'UPDATE ANY
TABLE',
'DELETE ANY
TABLE')))
connect by grantee# = prior privilege#
start with grantee# = u.user#
union all
select null
from sys.objauth$
where privilege# in (select privilege
from sys.table_privilege_map
where (upper('&1') = 'SEE'
and name = 'SELECT')
or (upper('&1') = 'MODIFY'
and name in ('INSERT',
'UPDATE',
'DELETE')))
and obj# = (select o.obj#
from sys.obj$ o,
sys.user$ u
where o.owner# = u.user#
and u.name = decode(instr('&2', '.'),
0,
sys_context('USERENV',
'CURRENT_SCHEMA'),
upper(substr('&2', 1,
instr('&2','.')-1)))
and o.name = decode(instr('&2', '.'),
0, upper('&2'),
upper(substr('&2',
instr('&2','.')+1))))
and grantee# in (select 1
from dual
union all
select privilege#
from sys.sysauth$
connect by grantee# = prior privilege#
start with grantee# = u.user#
union
select u.user#
from dual))
or u.name = decode(instr('&2', '.'),
0, sys_context('USERENV',
'CURRENT_SCHEMA'),
upper(substr('&2', 1,
instr('&2','.')-1))))
-- Check that the object exists !
and exists (select null
from sys.obj$ o,
sys.user$ u
where o.owner# = u.user#
and u.name = decode(instr('&2', '.'),
0, sys_context('USERENV',
'CURRENT_SCHEMA'),
upper(substr('&2', 1,
instr('&2','.')-1)))
and o.name = decode(instr('&2', '.'),
0, upper('&2'),
upper(substr('&2',
instr('&2','.')+1))))
/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: sfaroult_at_oriole.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 Tue Jan 13 2004 - 16:14:42 CST
 |
 |