USER or ROLE에 대한 TABLE Privileges 확인[오라클자바커뮤니티/빅데이터/오라클/PLSQL/SQL쿼리/SQL튜닝실무교육/SQL기초교육]

-- 
-- 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 


  
-------------------  
Sample Output 
------------------- 

SQL> @utp.sql 

    Examining the data dictionary. Please wait.... 

Enter value for table_name:  DEPT 

Grantee list for table DEPT 

Grantee                  Privilege 
------------------------ ------ 
JAY Direct SELECT 
SCOTT Thru role TIMMY    INSERT 
                        SELECT 
                        UPDATE 
SYS Thru role TIMMY      INSERT 
                        SELECT 
                        UPDATE 
SYSTEM Direct            INSERT 
TIMMY Direct            INSERT 
                        SELECT 
                        UPDATE 
  
11 rows selected. 


METALINK 자료 입니다. 
  

	[100%환급,실무전문]SQL/빅데이터/자바/스프링/웹퍼블리싱/안드…	오라클자바…	12-27	2850
	[채용확정무료교육]오라클자바개발잘하는신입뽑기2개월과정,교육…	오라클자바…	12-11	2079
53	[평일100%환급7건]웹퍼블리싱,자바&JSP,안드로이드,C#닷넷,SQL기…	오라클자바…	03-15	1917
52	[주말]C#,ASP.NET마스터	오라클자바…	01-31	2069
51	[기업100%환급,평일주간]SQL기초에서스키마오브젝트,PLSQL,힌트…	오라클자바…	01-31	3032
50	[주말주간]자바&웹,jQUERY,스프링프레임워크,마이바티스	오라클자바…	01-31	1595
49	[평일주간/야간,주말주간/야간]Spring,MyBatis,Hibernate개발자…	오라클자바…	01-19	1923
48	[평일주간/야간,주말주간/야간]안드로이드개발자과정(Adnroid 교…	오라클자바…	01-11	1783
47	[평일야간,주말주간야간]JAVA,Network&JSP&Spring,MyBatis,Hiber…	오라클자바…	01-03	2383
46	[100%환급,실무전문]SQL/빅데이터/자바/스프링/웹퍼블리싱/안드…	오라클자바…	12-27	2850
45	[주말야간]개발자를위한PLSQL,SQL튜닝,힌트(토/일)	오라클자바…	12-19	2003
44	[평일주간/야간,주말주간/야간]웹퍼블리싱 마스터(HTML5,CSS3,jQ…	오라클자바…	12-14	1975
43	[채용확정무료교육]오라클자바개발잘하는신입뽑기2개월과정,교육…	오라클자바…	12-11	2079
42	[주말야간]JAVA,JSP,Spring,PLSQL,힌트,웹퍼블리싱,안드로이드,…	오라클자바…	12-09	1616
41	[평일야간,주말야간]닷넷(C#,Network,ADO.NET,ASP.NET)마스터	오라클자바…	12-01	1836
40	[기업100%환급]오라클&자바웹스프링신입과정3주(SQL,JAVA,JSP,Se…	오라클자바…	12-01	2076
39	[평일야간,주말]SQL기초에서실무까지(SQL기초,PLSQL,힌트,튜닝)	오라클자바…	12-01	1492