DBAPack/privobj.sql at master · flvani/DBAPack · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
SET VERIFY OFF FEED OFF DEFINE ON LINES 200 PAGESIZE 0
COL PRIVILEGIOS FORMAT A200
COL SINONIMO FORMAT A200

DEFINE P1=UPPER('%&1.')

BREAK ON OWNER SKIP 1

COL OWNER NOPRINT

PROMPT
PROMPT Gerando Scripts de Grants (&p_temp_path.Grant.Privs.SQL)

SPOOL &p_temp_path.Grant.Privs.SQL

SELECT
  'GRANT ' || STRAGG(PRIVILEGE)    ||
  ' ON ' || OWNER || '.' || TABLE_NAME ||
  ' TO ' || GRANTEE || DECODE( GRANTABLE, 'YES', ' WITH GRANT OPTION' ) || ';' "PRIVILEGIOS"
FROM DBA_TAB_PRIVS
WHERE OWNER ||'.'||TABLE_NAME LIKE &P1.
AND TABLE_NAME NOT LIKE 'BIN$%'
GROUP BY OWNER, GRANTEE, TABLE_NAME, GRANTABLE
ORDER BY 1
/

PROMPT
SELECT
  DECODE( OWNER, 'PUBLIC', 'CREATE PUBLIC SYNONYM ', 'CREATE SYNONYM ' || OWNER || '.' ) ||
  SYNONYM_NAME ||' FOR ' || TABLE_OWNER || '.' || TABLE_NAME  || DECODE( DB_LINK, NULL, '', '@'||DB_LINK ) || ';' SINONIMO
FROM DBA_SYNONYMS WHERE TABLE_OWNER||'.'||TABLE_NAME LIKE &P1.
/

SPOOL OFF

PROMPT
PROMPT Gerando Scripts de Revogação (&p_temp_path.Revoke.Privs.SQL)

SPOOL &p_temp_path.Revoke.Privs.SQL

SELECT
  '--REVOKE ' || STRAGG(PRIVILEGE)    ||
  ' ON ' || OWNER || '.' || TABLE_NAME ||
  ' FROM ' || GRANTEE || ';' "PRIVILEGIOS"
FROM DBA_TAB_PRIVS
WHERE OWNER ||'.'||TABLE_NAME LIKE &P1.
AND TABLE_NAME NOT LIKE 'BIN$%'
GROUP BY OWNER, GRANTEE, TABLE_NAME, GRANTABLE
ORDER BY 1
/

PROMPT
SELECT
  DECODE( OWNER, 'PUBLIC', '--DROP PUBLIC SYNONYM ', '--DROP SYNONYM ' || OWNER || '.' ) || SYNONYM_NAME || ';' SINONIMO
FROM DBA_SYNONYMS WHERE TABLE_OWNER||'.'||TABLE_NAME LIKE &P1.
/

SPOOL OFF

PROMPT
PROMPT @privobj &1.
PROMPT

UNDEFINE P1
UNDEFINE 1
SET FEED 6

CLEAR BREAK
COL OWNER CLEAR