1 PROCEDURE xoq_validate IS
2 compat VARCHAR2(30);
3 dummy_num NUMBER;
4 dummy_out_1_str VARCHAR2(100);
5 dummy_out_2_str VARCHAR2(100);
6 ok BOOLEAN := TRUE;
7 v_Value varchar2(64);
8 BEGIN
9
10 begin
11 SELECT value INTO v_Value FROM v$option WHERE parameter = 'OLAP';
12 if v_Value = 'FALSE' then
13 -- set status OPTION OFF
14 sys.dbms_registry.Option_Off('XOQ');
15 return;
16 end if;
17 exception
18 when OTHERS then
19 null;
20 end;
21
22 -- check compatible
23 SELECT value INTO compat FROM v$parameter WHERE name='compatible';
24 IF NOT (substr(compat,1,3) >= '9.2' OR substr(compat,1,2) >= '10') THEN
25 ok := FALSE;
26 END IF;
27
28 IF ok THEN
29 --check for errors during installation/upgrade
30 BEGIN
31 SELECT 0 INTO dummy_num from sys.registry$error
32 WHERE identifier='XOQ'AND rownum <=1;
33 -- at least one install error was found so component is invalid
34 ok := FALSE;
35 EXCEPTION
36 WHEN NO_DATA_FOUND THEN
37 -- no install errors were found so component remains valid
38 NULL;
39 END;
40 END IF;
41
42 IF ok THEN
43 -- check that dependent component XDB is valid
44 IF dbms_registry.is_valid('XDB', dbms_registry.release_version) != 1 THEN
45 ok := FALSE;
46 END IF;
47 END IF;
48
49 IF ok THEN
50 -- check that expected XDB resources are there
51 IF NOT (dbms_xdb.existsresource('/olap_data_security/public/acls') AND
52 dbms_xdb.existsresource('/xds/dsd')) THEN
53 ok := FALSE;
54 END IF;
55 END IF;
56
57 IF ok THEN
58 -- check that installed library is valid
59 BEGIN
60 SELECT 0 INTO dummy_num FROM DBA_LIBRARIES
61 WHERE STATUS = 'INVALID' AND rownum <=1 AND
62 OWNER='SYS' AND LIBRARY_NAME = 'DBMS_OLAPI_LIB';
63 -- at least one object is invalid so component is invalid
64 ok := FALSE;
65 EXCEPTION
66 WHEN NO_DATA_FOUND THEN
67 -- no invalid objects were found so component remains valid
68 NULL;
69 END;
70 END IF;
71
72 IF ok THEN
73 -- check very basic OLAP API function (including load of shared library)
74 BEGIN
75 dummy_num := OlapiBootstrap2(compat, dummy_out_1_str, dummy_out_2_str);
76 EXCEPTION
77 WHEN OTHERS THEN
78 ok := FALSE;
79 END;
80 END IF;
81
82 IF ok THEN
83 -- check that Java classes are loaded successfully
84 BEGIN
85 SELECT 0 INTO dummy_num FROM dba_objects
86 WHERE owner = 'SYS' AND
87 status = 'INVALID' AND
88 object_type = 'JAVA CLASS' AND
89 object_name LIKE 'oracle/AWXML/%';
90 -- at least one class is invalid so component is invalid
91 ok := FALSE;
92 EXCEPTION
93 WHEN NO_DATA_FOUND THEN
94 -- no invalid components were found so component remains valid
95 NULL;
96 END;
97 END IF;
98
99 IF ok THEN
100 -- check that installed types, packages, and procedures are valid
101 BEGIN
102 SELECT 0 INTO dummy_num FROM DBA_OBJECTS
103 WHERE STATUS = 'INVALID' AND rownum <=1 AND
104 OWNER='SYS' AND OBJECT_NAME IN
105 ('DBMS_CUBE_ADVISE','DBMS_CUBE_ADVISE_SEC','DBMS_CUBE',
106 'DBMS_CUBE_EXP','GENDATABASEINTERFACE','GENCONNECTIONINTERFACE',
107 'GENSERVERINTERACE','GENMDMPROPERTYIDCONSTANTS',
108 'GENMDMCLASSCONSTANTS','GENMDMOBJECTIDCONSTANTS',
109 'GENMETADATAPROVIDERINTERFACE','GENCURSORMANAGERINTERFACE',
110 'GENDATATYPEIDCONSTANTS','GENDEFINITIONMANAGERINTERFACE',
111 'GENDATAPROVIDERINTERFACE','DBMS_AW_XML','DBMS_CUBE_UTIL',
112 'COAD_ADVICE_T','COAD_ADVICE_REC','GENOLAPIEXCEPTION',
113 'GENINTERFACESTUB', 'GENINTERFACESTUBSEQUENCE',
114 'GENRAWSEQUENCE','GENWSTRINGSEQUENCE',
115 'DBMS_CUBE_UTIL_EXT_MD_T','DBMS_CUBE_UTIL_EXT_MD_R',
116 'OLAPIHANDSHAKE2','OLAPIBOOTSTRAP2');
117 -- at least one object is invalid so component is invalid
118 ok := FALSE;
119 EXCEPTION
120 WHEN NO_DATA_FOUND THEN
121 -- no invalid objects were found so component remains valid
122 NULL;
123 END;
124 END IF;
125
126 IF ok THEN
127 -- check for expected role
128 BEGIN
129 SELECT 0 INTO dummy_num FROM DBA_ROLES
130 WHERE ROLE = 'OLAP_XS_ADMIN';
131 EXCEPTION
132 WHEN NO_DATA_FOUND THEN
133 ok := FALSE;
134 END;
135 END IF;
136
137 IF ok THEN
138 -- Address bug 17997122 by carefully checking OLAP_XS_ADMIN role
139 -- privileges against the DBA_TAB_PRIVS view.
140 -- check for privileges granted as local granted privileges, COMMON='NO'
141
142 -- Bug 21856522 As of 12.2 Privs on sys owned pl/sql packages can be common = no or
143 -- common = yes
144 SELECT COUNT(*) INTO dummy_num
145 FROM DBA_TAB_PRIVS
146 WHERE GRANTEE='OLAP_XS_ADMIN' AND
147 PRIVILEGE='EXECUTE' AND
148 OWNER='SYS' AND
149 TABLE_NAME='DBMS_XDS';
150
151 IF dummy_num = 0 THEN
152 -- Missing grant to pl/sql package, so invalid
153 ok := FALSE;
154 END IF;
155 END IF;
156
157 IF ok THEN
158 SELECT COUNT(*) INTO dummy_num
159 FROM DBA_TAB_PRIVS
160 WHERE GRANTEE='OLAP_XS_ADMIN' AND
161 ((PRIVILEGE='SELECT' AND OWNER='SYS' AND
162 TABLE_NAME='XS$OLAP_POLICY' AND COMMON='NO') OR
163 (PRIVILEGE='SELECT' AND OWNER='SYS' AND
164 TABLE_NAME='DBA_ROLES' AND COMMON='NO'));
165
166 IF dummy_num = 0 THEN
167 -- No local granted privileges
168 -- check to see if grants are common granted privileges, COMMON = 'YES'
169 SELECT COUNT(*) INTO dummy_num
170 FROM DBA_TAB_PRIVS
171 WHERE GRANTEE='OLAP_XS_ADMIN' AND
172 ((PRIVILEGE='SELECT' AND OWNER='SYS' AND
173 TABLE_NAME='XS$OLAP_POLICY' AND COMMON='YES') OR
174 (PRIVILEGE='SELECT' AND OWNER='SYS' AND
175 TABLE_NAME='DBA_ROLES' AND COMMON='YES'));
176 IF dummy_num != 2 THEN
177 -- Incomplete set of common granted privileges granted, so invalid
178 ok := FALSE;
179 END IF;
180
181 ELSIF dummy_num = 2 THEN
182 -- Grants are valid for local granted privileges.
183 -- Now grants may also be a common granted privilege, COMMON = 'YES'
184 SELECT COUNT(*) INTO dummy_num
185 FROM DBA_TAB_PRIVS
186 WHERE GRANTEE='OLAP_XS_ADMIN' AND
187 ((PRIVILEGE='SELECT' AND OWNER='SYS' AND
188 TABLE_NAME='XS$OLAP_POLICY' AND COMMON='YES') OR
189 (PRIVILEGE='SELECT' AND OWNER='SYS' AND
190 TABLE_NAME='DBA_ROLES' AND COMMON='YES'));
191 IF dummy_num = 0 THEN
192 -- No Common granted privileges granted,
193 -- but still valid because of valid local granted privileges
194 ok := TRUE;
195 ELSIF dummy_num != 2 THEN
196 -- Incomplete set of common granted privileges granted, so invalid
197 ok := FALSE;
198 END IF;
199 ELSE
200 -- Incomplete set of local granted privileges granted, so invalid
201 ok := FALSE;
202 END IF;
203 END IF;
204
205 IF ok THEN
206 sys.dbms_registry.valid('XOQ');
207 ELSE
208 sys.dbms_registry.invalid('XOQ');
209 END IF;
210 END;
[Home] [Help]