1 package body CDBView is
2
3 type tabs_array is varray(11) of dbms_id;
4 sens_table_array tabs_array;
5 type sens_tabs is table of boolean index by dbms_id;
6 sens_table_list sens_tabs;
7
8 function isLegalOwnerViewName(owner IN varchar2, oldview IN varchar2,
9 newview IN varchar2) return boolean;
10 -- Create the cdb view
11 -- private helper procedure to create the cdb view
12 -- Note that quotes should not be added around owner, oldview_name and
13 -- newview_name before create_cdbview is invoked since all three are used
14 -- as literals to query dictionary views.
15 procedure create_cdbview(chk_upgrd IN boolean, owner IN varchar2,
16 oldview_name IN varchar2, newview_name IN varchar2) as
17 sqlstmt varchar2(4000);
18 col_name varchar2(128);
19 comments varchar2(4000);
20 col_type number;
21 upper_owner varchar2(128);
22 upper_oldview varchar2(128);
23 quoted_owner varchar2(130); -- 2 more than size of owner
24 quoted_oldview varchar2(130); -- 2 more than size of oldview_name
25 quoted_newview varchar2(130); -- 2 more than size of newview_name
26 table_not_found EXCEPTION;
27 PRAGMA exception_init(table_not_found, -942);
28 insuff_privilege EXCEPTION;
29 PRAGMA exception_init(insuff_privilege, -1031);
30
31
32 cursor tblcommentscur is select c.comment$
33 from sys.obj$ o, sys.user$ u, sys.com$ c
34 where o.name = upper_oldview and u.name = upper_owner
35 and o.obj# = c.obj# and o.owner#=u.user# and o.type# = 4
36 and c.col# is null;
37
38 cursor colcommentscur is select c.name, co.comment$, c.type#
39 from sys.obj$ o, sys.col$ c, sys.user$ u, sys.com$ co
40 where o.name = upper_oldview and u.name = upper_owner
41 and o.owner# = u.user# and o.type# = 4 and o.obj# = c.obj#
42 and c.obj# = co.obj# and c.intcol# = co.col#
43 and bitand(c.property, 32) = 0;
44
45 begin
46
47 if (not isLegalOwnerViewName(owner, oldview_name, newview_name)) then
48 RAISE table_not_found;
49 end if;
50
51 -- convert owner and view names to upper case
52 upper_owner := upper(owner);
53 upper_oldview := upper(oldview_name);
54
55 --
56 -- Bug 27445727 : Certain SYS owned tables like USER$, LINK$ etc. are out
57 -- of bounds even for DBA level users. So we should not allow cloning such
58 -- tables into a Container_Data view and be able to bypass this protection.
59 --
60 -- Since this procedure does not support quoted identifiers, we need not
61 -- worry about canonicalizing the owner or oldview_name arguments before
62 -- comparing against SYS and the array of sensitive tables.
63 --
64 if (upper_owner = 'SYS' and sens_table_list.exists(upper_oldview)) then
65 RAISE insuff_privilege;
66 end if;
67
68 quoted_owner := '"' || upper_owner || '"';
69 quoted_oldview := '"' || upper_oldview || '"';
70 quoted_newview := '"' || upper(newview_name) || '"';
71
72 -- Create cdb view
73 sqlstmt := 'CREATE OR REPLACE VIEW ' ||
74 quoted_owner || '.' || quoted_newview ||
75 ' CONTAINER_DATA AS SELECT * FROM CONTAINERS(' ||
76 quoted_owner || '.' || quoted_oldview || ')';
77
78 --dbms_output.put_line(sqlstmt);
79 execute immediate sqlstmt;
80
81 -- table and column comments
82 open tblcommentscur;
83 fetch tblcommentscur into comments;
84 comments := replace(comments, '''','''''');
85 sqlstmt := 'comment on table ' || quoted_owner || '.' || quoted_newview ||
86 ' is ''' || comments || ' in all containers''';
87 -- dbms_output.put_line(sqlstmt);
88 execute immediate sqlstmt;
89 close tblcommentscur;
90
91 sqlstmt := 'comment on column ' || quoted_owner || '.' || quoted_newview ||
92 '.CON_ID is ''container id''';
93 -- dbms_output.put_line(sqlstmt);
94 execute immediate sqlstmt;
95 open colcommentscur;
96 loop
97 fetch colcommentscur into col_name, comments, col_type;
98 exit when colcommentscur%NOTFOUND;
99
100 comments := replace(comments, '''','''''');
101 if comments is not NULL and
102 col_type <> 8 and
103 col_type <> 123 then
104 sqlstmt := 'comment on column ' ||
105 quoted_owner || '.' || quoted_newview || '.' ||
106 col_name || ' is ''' || comments || '''';
107 -- dbms_output.put_line(sqlstmt);
108 execute immediate sqlstmt;
109 end if;
110 end loop;
111 close colcommentscur;
112 end;
113
114 -- This function is created to prevent SQL injection. We couldn't use
115 -- dbms_assert because catcdbviews.sql is called before dbms_assert
116 -- is created
117 function isLegalOwnerViewName(owner IN varchar2, oldview IN varchar2,
118 newview IN varchar2) return boolean as
119 cCheck number;
120 ownerId number;
121 cleanOldview varchar2(128);
122 cleanNewview varchar2(128);
123 begin
124
125 -- Check if owner already exist
126 -- USER$ contains both users and roles, exclude roles from the check
127 begin
128 execute immediate 'SELECT USER# FROM USER$ WHERE TYPE# = 1 AND NAME = :1'
129 into ownerId using upper(owner);
130 exception
131 when no_data_found then return false;
132 end;
133
134 -- Check if oldview already exist, qualify with owner# to avoid ORA-1422
135 -- error,if two dictionary view with the same name exists across different
136 -- schemas like DBA_DV_STATUS which exists in both SYS and DVSYS schemas.
137 -- Bug 29339155: Check for remoteowner being null to avoid ORA-1422 error
138 -- if there were remote PL/SQL objects depending on oldview.
139 begin
140 execute immediate 'SELECT 1 FROM OBJ$ WHERE NAME = :1' ||
141 ' AND TYPE# in (2, 4) and owner# = :2' ||
142 ' AND REMOTEOWNER is null'
143 into cCheck using upper(oldview), ownerId;
144 exception
145 when no_data_found then return false;
146 end;
147
148 -- Check for appropriate newview name
149 -- The following is allowed for newview name
150 -- 1. Substitute 'DBA' with 'CDB'
151 -- 2. Substitute 'AWR_PDB' with 'CDB_HIST'
152 -- 3. Substitute 'ATTRIBUTES' with 'ATTRIB'
153 -- 4. Substitute 'DATABASE' with 'CDB'
154 -- 5. Remove 'REDUCED'
155 -- 6. Add 'AWRI$_CDB'
156 cleanOldview := REGEXP_REPLACE(upper(oldview),
157 'DBA|DATABASE|_| |HIST|ATTRIB(UTE)?S?|CDB|AWR_PDB|REDUCED');
158 cleanNewview := REGEXP_REPLACE(upper(newview),
159 'CDB|DATABASE|_| |HIST|ATTRIB(UTE)?S?|AWRI\$');
160
161 if (cleanOldview = cleanNewview) then
162 RETURN TRUE;
163 end if;
164
165 RETURN FALSE;
166
167 end isLegalOwnerViewName;
168
169 --
170 -- Initialize the list of SYS owned sensitive tables which should
171 -- not be allowed to be shadow-copied even within Create_CDBView.
172 --
173 -- Once ER 24598663 gets implemented, these entries will be part of a
174 -- SYS owned metadata table and instead of hard-coding the list, we will
175 -- be populating the list by fetching it from the table.
176 --
177 begin
178 sens_table_array := tabs_array('ENC$', 'LINK$', 'USER$', 'DEFAULT_PWD$',
179 'XS$VERIFIERS', 'USER_HISTORY$',
180 'HIST_HEAD$', 'HISTGRM$','CDB_LOCAL_ADMINAUTH$',
181 'PDB_CREATE$','PDB_SYNC$');
182
183 for i in 1..11 loop
184 sens_table_list(sens_table_array(i)) := TRUE;
185 end loop;
186
187 end CDBView;
[Home] [Help]