Created a the get grants scripts and added the possibility of passing parameter to the other 2

David FRANCO · David FRANCO · commit 262a2499bd17 · 2018-02-20T12:34:50.000-03:00
diff --git a/createUser.sql b/createUser.sql
@@ -1,10 +1,14 @@
 set serveroutput on size unlimited
 set feedback on
 
+var v_owner varchar2(30);
+
+exec :v_owner := '&1';
+
 DECLARE
     TYPE NamesList IS TABLE OF VARCHAR2(30);  -- nested table type
     usernames NamesList := NamesList(
-      'USER_847448','USER_928866','USER_993233','USER_886618','USER_A34104'
+      :v_owner
     );
     tempTbsName VARCHAR2(50);
     v_count NUMBER;
@@ -55,4 +59,4 @@ BEGIN
 END;
 /
 
---exit;
+exit;
diff --git a/getDesyncSequences.sql b/getDesyncSequences.sql
@@ -4,7 +4,7 @@ SET FEEDBACK ON
 
 var v_owner varchar2(30);
 
-exec :v_owner := '&1';
+exec :v_owner := upper('&1');
 
 DECLARE
     DIFF NUMBER;
diff --git a/getGrants.sql b/getGrants.sql
@@ -0,0 +1,21 @@
+-- Get all grants of a specific user, could be done with the get_ddl to, but this way is easier to 
+-- adapt from getting the grants of a user to a table, role or etc.
+
+var v_owner varchar2(30);
+
+exec :v_owner := '&1';
+
+select 'grant '||granted_role||' to '||grantee||';' 
+  from dba_role_privs 
+ where grantee in (:v_owner)
+union all
+select 'grant '||privilege||' to '||grantee||';' 
+ from dba_sys_privs 
+where grantee in (:v_owner)
+union all
+select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' 
+  from dba_tab_privs 
+ where grantee in (:v_owner)
+;
+
+exit
