-
Notifications
You must be signed in to change notification settings - Fork 2
Expand file tree
/
Copy pathchange_all_privs_code.sql
More file actions
executable file
·62 lines (56 loc) · 2.95 KB
/
change_all_privs_code.sql
File metadata and controls
executable file
·62 lines (56 loc) · 2.95 KB
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
whenever sqlerror exit sql.sqlcode
set verify off
BEGIN EXECUTE IMMEDIATE 'ALTER TABLE T_TAB_PRIVS_F ADD (TABLE_NAME_COMP VARCHAR2(128))'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
DECLARE
VNAME VARCHAR2(128);
V_IS_11204 BOOLEAN;
FUNCTION fc_adapt_type (V_STR_IN IN VARCHAR2) RETURN VARCHAR2 IS
V_FINAL_PART varchar2(30);
begin
-- Ira truncar o código até o início da parte de dígito em X casas e concatenar o restante, variando o tamanho de acordo com o tamanho da própria string final e qtd de dígitos.
-- Ex: PERFORMED_PROCEDURE_STE123_T -> PERFORMED_PROCEDURE123_T (24) | PREDICATES_DEFINITIO499_COLL -> PREDICATES_DEFIN499_COLL (24) | MEDIA_STORAGE_SOP_INSTA88_T -> MEDIA_STORAGE_SOP_I88_T (23)
IF LENGTH(V_STR_IN)>23 THEN
V_FINAL_PART := REGEXP_SUBSTR(V_STR_IN,'[[:digit:]]+_(T|COLL)$');
RETURN SUBSTR(V_STR_IN,1,LEAST(24-(3-LENGTH(SUBSTR(V_FINAL_PART,1,INSTR(V_FINAL_PART,'_')-1))),LENGTH(V_STR_IN))-LENGTH(V_FINAL_PART)) || V_FINAL_PART;
ELSE
RETURN V_STR_IN;
END IF;
end;
FUNCTION fc_remove_digits (V_STR_IN IN VARCHAR2) RETURN VARCHAR2 IS
begin
RETURN REGEXP_REPLACE(V_STR_IN,'[[:digit:]]+',''); -- Remove digits
end;
BEGIN
FOR I IN (SELECT T_TAB_PRIVS_F.ROWID,T_TAB_PRIVS_F.* FROM T_TAB_PRIVS_F)
LOOP
IF (I.ORAVERSION = '11.2.0.4') THEN
V_IS_11204 := TRUE;
ELSE
V_IS_11204 := FALSE;
END IF;
VNAME := '';
IF I.OWNER IN ('MDSYS','ORDSYS','SYS','XDB') AND REGEXP_LIKE(I.TABLE_NAME,'^([[:alpha:]]|_|-)+[[:digit:]]+_(T|COLL)$') AND (I.TYPE = 'TYPE' OR V_IS_11204) AND I.GRANTEE IN ('XDB','PUBLIC') THEN
VNAME := fc_remove_digits(fc_adapt_type(I.TABLE_NAME));
ELSIF I.OWNER = 'XDB' AND REGEXP_LIKE(I.TABLE_NAME,'^X\$PT.*') AND (I.TYPE = 'TABLE' OR V_IS_11204) AND I.GRANTEE IN ('DBA','SYSTEM') THEN
VNAME := REGEXP_REPLACE(I.TABLE_NAME,'^X\$PT.*','X$PT');
ELSIF I.OWNER = 'SYS' AND REGEXP_LIKE(I.TABLE_NAME,'^QT([[:digit:]])+_BUFFER$') AND (I.TYPE = 'VIEW' OR V_IS_11204) THEN
VNAME := REGEXP_REPLACE(I.TABLE_NAME,'^QT([[:digit:]])+_BUFFER$','QT_BUFFER');
ELSIF I.OWNER = 'SYS' AND REGEXP_LIKE(I.TABLE_NAME,'^SYST.*==$') AND (I.TYPE = 'TYPE' OR V_IS_11204) AND I.GRANTEE = 'PUBLIC' THEN
VNAME := REGEXP_REPLACE(I.TABLE_NAME,'^SYST.*==$','SYST==');
ELSIF I.OWNER = 'XDB' AND REGEXP_LIKE(I.TABLE_NAME,'^SYS_NT') AND (I.TYPE = 'TABLE' OR V_IS_11204) AND I.GRANTEE = 'SELECT_CATALOG_ROLE' THEN
VNAME := REGEXP_REPLACE(I.TABLE_NAME,'^SYS_NT.*','SYS_NT');
ELSIF I.OWNER = 'XDB' AND REGEXP_LIKE(I.TABLE_NAME,'^X\$(NM|PT|QN)') AND (I.TYPE = 'TABLE' OR V_IS_11204) AND I.GRANTEE = 'SELECT_CATALOG_ROLE' THEN
VNAME := REGEXP_REPLACE(I.TABLE_NAME,'^(X\$(NM|PT|QN)).*','\1');
END IF;
IF VNAME IS NOT NULL THEN
UPDATE T_TAB_PRIVS_F
SET TABLE_NAME_COMP = VNAME
WHERE ROWID = I.ROWID;
END IF;
END LOOP;
COMMIT;
END;
/
whenever sqlerror continue
set verify on