/* Formatted on 6/23/2014 10:33:45 AM (QP5 v5.115.810.9015) */
DECLARE
CURSOR cur_grants (p_user varchar2
)
IS
SELECT 'GRANT '
|| DECODE (db.object_type,
'TABLE', 'SELECT',
'VIEW', 'SELECT',
'EXECUTE')
|| ' ON '
|| DECODE (db.owner, 'PUBLIC', '', db.owner || '.')
|| '"'
|| db.object_name
|| '"'
|| ' TO '
|| p_user
sql_stmt
FROM all_objects db
WHERE db.object_type IN
('TABLE',
'PACKAGE',
'PACKAGE BODY',
'PROCEDURE',
'VIEW',
'FUNCTION')
UNION
SELECT 'GRANT '
|| DECODE (ao2.object_type,
'TABLE', 'SELECT',
'VIEW', 'SELECT',
'EXECUTE')
|| ' ON '
|| DECODE (ao.owner, 'PUBLIC', '', ao.owner || '.')
|| '"'
|| ao.object_name
|| '"'
|| ' TO '
|| p_user
sql_stmt
FROM all_objects ao, all_objects ao2, dba_synonyms ds
WHERE ao.object_type = 'SYNONYM'
AND ao.object_name = ds.synonym_name
AND ao2.object_name = ds.table_name
AND ao2.object_type IN
('TABLE',
'PACKAGE',
'PACKAGE BODY',
'PROCEDURE',
'VIEW',
'FUNCTION');
v_user VARCHAR2 (20) := 'USER_NAME';
BEGIN
FOR i IN cur_grants (v_user)
LOOP
EXECUTE IMMEDIATE i.sql_stmt;
DBMS_OUTPUT.put_line ('Command : ' || i.sql_stmt);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
END;
/
-- Please give the User Name in v_user variable
DECLARE
CURSOR cur_grants (p_user varchar2
)
IS
SELECT 'GRANT '
|| DECODE (db.object_type,
'TABLE', 'SELECT',
'VIEW', 'SELECT',
'EXECUTE')
|| ' ON '
|| DECODE (db.owner, 'PUBLIC', '', db.owner || '.')
|| '"'
|| db.object_name
|| '"'
|| ' TO '
|| p_user
sql_stmt
FROM all_objects db
WHERE db.object_type IN
('TABLE',
'PACKAGE',
'PACKAGE BODY',
'PROCEDURE',
'VIEW',
'FUNCTION')
UNION
SELECT 'GRANT '
|| DECODE (ao2.object_type,
'TABLE', 'SELECT',
'VIEW', 'SELECT',
'EXECUTE')
|| ' ON '
|| DECODE (ao.owner, 'PUBLIC', '', ao.owner || '.')
|| '"'
|| ao.object_name
|| '"'
|| ' TO '
|| p_user
sql_stmt
FROM all_objects ao, all_objects ao2, dba_synonyms ds
WHERE ao.object_type = 'SYNONYM'
AND ao.object_name = ds.synonym_name
AND ao2.object_name = ds.table_name
AND ao2.object_type IN
('TABLE',
'PACKAGE',
'PACKAGE BODY',
'PROCEDURE',
'VIEW',
'FUNCTION');
v_user VARCHAR2 (20) := 'USER_NAME';
BEGIN
FOR i IN cur_grants (v_user)
LOOP
EXECUTE IMMEDIATE i.sql_stmt;
DBMS_OUTPUT.put_line ('Command : ' || i.sql_stmt);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
END;
/
-- Please give the User Name in v_user variable
No comments:
Post a Comment