Sunday, April 13, 2008

Export GRANTS on tables a particular user has made

#!/bin/ksh
sqlplus -silent /nolog<<eof
connect / as sysdba
set serveroutput on
SET HEAD OFF
SET PAGESIZE 0
SET FEEDBACK OFF
SPOOL /tmp/privs_exp.sql
SELECT 'GRANT ' || PRIVILEGE || ' ON ' ||OWNER||'.'||TABLE_NAME|| ' TO '||GRANTEE|| DECODE(GRANTABLE, 'YES' , ' WITH GRANT OPTION', ' ' )||';' FROM DBA_TAB_PRIVS WHERE GRANTOR IN ('NP_TRIAL', 'NAVEEN', 'TOAD');
exit;
eof

No comments: