How to determine user's privs on a table?

[rtefft]

I am trying to write a function to determine if a specified user has a specific priv (ie INSERT) on an object. The caller will specify the object name, username, and priv to check. The problem I run into is that there are (at least) 5 ways a user can have rights to an object:

1. A direct grant on the object to the user.
2. A grant on the object to a role which the user has.
3. A super-priv like DBA or SELECT ANY TABLE, etc.
(these don't appear in DBA_TAB_PRIVS btw)
4. A grant to PUBLIC.
5. The specified username is the owner of the object.

I can use DBA_TAB_PRIVS, DBA_ROLE_PRIVS for 1,2, and 4. Item 5 is a simple IF statement, but #3 is the problem. Roles like SELECT ANY TABLE, INSERT ANY TABLE, and DBA show up in DBA_ROLE_PRIVS or DBA_SYS_PRIVS (user-role/priv), but I cannot find a connection from the super priv/role to the object anywhere in the data dictionary.

Other than hardcoding these "super privs", do anyone know a simple way answer the question "Does user X have privilege Y on object Z?" reliably? I have a feeling there is, but I'm just missing it.

Thanks in advance,
Rich ____________________________
Rich Tefft
PL/SQL Programmer

 

[spenglerr]

Hi,

here is a short script that is doing exactly what you want from Oracle Metalink:

--------------------------
--- Script:

--
-- gives user/table privileges, regardless of how the privilege
-- is given; either directly or through any number of roles
--
-- Parameters: Table Name
--

prompt Examining the data dictionary. Please wait....

-- whenever sqlerror stop 1;

set verify off feedback off

-- temp table user_dba_role_privs created, as CONNECT BY
-- unusable on dba_role_priv view

create table user_dba_role_privs storage (initial 100k next 100k)
as select * from sys.dba_role_privs
/

-- temp table user_dba_tab_privs created to speed up search

create table user_dba_tab_privs storage (initial 100k next 100k)
as select * from sys.dba_tab_privs where table_name = upper('&&TABLE_NAME')
/

create index user_dba_tab_privs_idx on user_dba_tab_privs (privilege, grantee)
storage (initial 100k next 100k);

set feedback off
set heading off

prompt

prompt 'Grantee list for table ' || upper('&&TABLE_NAME') from dual;
set feedback on
set heading on

column ge format a50 heading "Grantee"
column priv format a10 heading "Privilege"

break on ge
set feedback on

select grantee||' Thru role '||granted_role ge, 'SELECT' priv
from user_dba_role_privs
start with granted_role in
(select grantee from user_dba_tab_privs where privilege='SELECT' )
connect by prior grantee=granted_role
union

--

select grantee||' Thru role '||granted_role ge, 'UPDATE' priv
from user_dba_role_privs
start with granted_role in
(select grantee from user_dba_tab_privs where privilege='UPDATE' )
connect by prior grantee=granted_role
union

--

select grantee||' Thru role '||granted_role ge, 'INSERT' priv
from user_dba_role_privs
start with granted_role in
(select grantee from user_dba_tab_privs where privilege='INSERT' )
connect by prior grantee=granted_role
union

--

select grantee||' Thru role '||granted_role ge, 'DELETE' priv
from user_dba_role_privs
start with granted_role in
(select grantee from user_dba_tab_privs where privilege='DELETE' )
connect by prior grantee=granted_role
union

--

select grantee||' Thru role '||granted_role ge, 'INDEX' priv
from user_dba_role_privs
start with granted_role in
(select grantee from user_dba_tab_privs where privilege='INDEX' )
connect by prior grantee=granted_role
union

--

select grantee||' Thru role '||granted_role ge, 'ALTER' priv
from user_dba_role_privs
start with granted_role in
(select grantee from user_dba_tab_privs where privilege='ALTER' )
connect by prior grantee=granted_role
union

--

select grantee||' Thru role '||granted_role ge, 'REFERENCES' priv
from user_dba_role_privs
start with granted_role in
(select grantee from user_dba_tab_privs where privilege='REFERENCES' )
connect by prior grantee=granted_role
union

--

select grantee||' Thru role '||granted_role ge, 'EXECUTE' priv
from user_dba_role_privs
start with granted_role in
(select grantee from user_dba_tab_privs where privilege='EXECUTE' )
connect by prior grantee=granted_role
union

--

select grantee|| ' Direct' ge , privilege priv
from sys.dba_tab_privs
where table_name = upper('&&TABLE_NAME')
order by 1,2
/

set feedback off
drop table user_dba_role_privs;
drop table user_dba_tab_privs;

clear breaks
set feedback on

---------------------------------------


just use this body for your function, regards

 

[rtefft]

Thanks for the script. I will code manual recursion to handle the role-role grants (I missed that) so I can leave out the temp tables. While useful to learn from, the script misses access to an object from super-privs such as the DBA role, SELECT ANY TABLE, etc., which is my original stumbling block. I will keep working on it.

Thanks again for the info. ____________________________
Rich Tefft
PL/SQL Programmer

 

[spenglerr]

Hi again,

try this (same source)

----------- cut ---------------------- cut -------------- cut --------------

SET ECHO off
REM NAME: TFSPRIVS.SQL
REM USAGE:"@path/tfsprivs grantee_name"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT on DBA_ROLE_PRIVS, DBA_SYS_PRIVS, DBA_TAB_PRIVS, DBA_COL_PRIVS
REM ------------------------------------------------------------------------
REM PURPOSE:
REM The following script shows privileges granted to a user,
REM as well as the level, object, and object owner.
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM LVL PRIVILEGE GRA OWNER Name
REM -------- ---------- --- -------------------- ---------------
REM Column UPDATE NO SYSTEM TOOL_DEPENDENT
REM Program DELETE NO FRM45__BUFFER
REM Program DELETE NO FRM45__GRP
REM Program INSERT NO SYSTEM MENU_B_OBJ_TEXT
REM Program INSERT NO MENU_B_PARAM
REM Program SELECT NO MENU_B_APPL_GRP
REM Program UPDATE NO MENU_B_OBJ_TEXT
REM Program UPDATE NO MENU_B_PARAM
REM table INSERT NO MENU_V_GRP_PRIV
REM table SELECT NO MENU_B_APPL_GRP
REM table UPDATE NO MENU_B_OBJ_TEXT
REM
REM ------------------------------------------------------------------------
REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support.
REM The script has been tested and appears to work as intended.
REM You should always run new scripts on a test instance initially.
REM ------------------------------------------------------------------------
REM Main text of script follows:

set pause on
set pause Continues...
set verify off
set pages 20
set lines 132

select 'Column' lvl,c.privilege,c.grantable,c.owner,c.table_name,c.column_name
from dba_col_privs c
where grantee = upper('&&grantee')
union
select 'Role' GrType,r.granted_role obj,r.admin_option a, null,null,null
from dba_role_privs r
where r.grantee = upper('&grantee')
union
select 'Sys Priv',s.privilege,s.admin_option,null,null,null
from dba_sys_privs s
where s.grantee = upper('&grantee')
union
select 'table',t.privilege,t.grantable,t.owner,t.table_name,null
from dba_tab_privs t
where t.grantee = upper('&grantee')
and t.privilege !='EXECUTE'
union
select 'Program', e.privilege,e.grantable,e.owner,e.table_name,null
from dba_tab_privs e
where e.grantee = upper('&grantee')
and e.privilege !='EXECUTE'
order by 1,2,4,5,6
/
undefine grantee


----------- cut ---------------------- cut -------------- cut --------------



regards