DatabaseProcApplicationCreatedLinks
sybsystemprocssp_jdbc_stored_procedures  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Altered from the ODBC sp_jdbc_procedures defined in sycsp11.sql.
4     **
5     ** New column 'PROCEDURE_TYPE' was added to support JDBC spec. This
6     ** column is to indicate if the procedure returns a result. If 0,
7     ** column will be evalued as DatabaseMetadata.procedureResultUnknown;
8     ** this means that the procedure MAY return a result.
9     */
10    /*
11    ** Messages for 'sp_jdbc_stored_procedures'	18041
12    **
13    ** 18041, 'Stored Procedure qualifier must be name of current database.'
14    **
15    */
16    create or replace procedure sp_jdbc_stored_procedures
17        @sp_qualifier varchar(32) = null, /* stored procedure qualifier; 
18        ** For the SQL Server, the only valid
19        ** values are NULL or the current 
20        ** database name
21        */
22        @sp_owner varchar(771) = null, /* stored procedure owner */
23        @sp_name varchar(771) = null, /* stored procedure name */
24        @version int = null, /* Conform to JDBC 4.0 spec if @version is not null*/
25        @functions int = 0 /* Call from getFunctions()? */
26    as
27    
28        declare @msg varchar(90)
29        declare @uid int
30        declare @protecttype tinyint
31        declare @id int
32        declare @action smallint
33        declare @number smallint
34        declare @sequence int
35    
36    
37        if @@trancount = 0
38        begin
39            set chained off
40        end
41    
42        set transaction isolation level 1
43    
44        /* this will make sure that all rows are sent even if
45        ** the client "set rowcount" is differect
46        */
47    
48        set rowcount 0
49    
50    
51    
52        /* If qualifier is specified */
53        if @sp_qualifier is not null
54        begin
55            /* If qualifier doesn't match current database */
56            if db_name() != @sp_qualifier
57            begin
58                /* If qualifier is not specified */
59                if @sp_qualifier = ''
60                begin
61                    /* in this case, we need to return an empty 
62                    ** result set because the user has requested a 
63                    ** database with an empty name 
64                    */
65                    select @sp_name = ''
66                    select @sp_owner = ''
67                end
68    
69                /* qualifier is specified and does not match current database */
70                else
71                begin
72                    /* 
73                    ** 18041, 'Stored Procedure qualifer must be name of
74                    ** current database'
75                    */
76                    exec sp_getmessage 18041, @msg out
77                    raiserror 18041 @msg
78                    return (1)
79                end
80            end
81        end
82    
83        /* If procedure name not supplied, match all */
84        if @sp_name is null
85        begin
86            select @sp_name = '%'
87        end
88    
89        /* If procedure owner not supplied, match all */
90        if @sp_owner is null
91            select @sp_owner = '%'
92    
93        /* 
94        ** Retrieve the stored procedures and associated info on them
95        */
96        /*
97    
98        ** get rows for public, current users, user's groups
99        */
100   
101       if @functions = 0
102       begin
103           if (@version is not null)
104           begin
105               select PROCEDURE_CAT = db_name(),
106                   PROCEDURE_SCHEM = user_name(o.uid),
107                   PROCEDURE_NAME = o.name + ';' + ltrim(str(p.number, 5)),
108                   num_input_params = - 1, /* Constant since value unknown */
109                   num_output_params = - 1, /* Constant since value unknown */
110                   num_result_sets = - 1, /* Constant since value unknown */
111                   REMARKS = convert(varchar(254), null), /* Remarks are NULL */
112                   PROCEDURE_TYPE = 0,
113                   SPECIFIC_NAME = o.name + ';' + ltrim(str(p.number, 5))
114               from sysobjects o, sysprocedures p /*__FORCEDINDEX125x__*/, sysusers u
115               where o.name like @sp_name ESCAPE '\'
116                   and p.sequence = 0
117                   and user_name(o.uid) like @sp_owner ESCAPE '\'
118                   and o.type = 'P' /* Object type of Procedure */
119                   and p.id = o.id
120                   and u.uid = user_id() /* constrain sysusers uid for use in 
121                   ** subquery 
122                   */
123                   and (suser_id() =
124                           (select uid from sysusers where suid = suser_id()) /* User is the System Administrator */
125                       or o.uid = user_id() /* User created the object */
126                       /* here's the magic..select the highest 
127                       ** precedence of permissions in the 
128                       ** order (user,group,public)  
129                       */
130   
131                       /*
132                       ** The value of protecttype is
133                       **
134                       **      0  for grant with grant
135                       **      1  for grant and,
136                       **      2  for revoke
137                       **
138                       ** As protecttype is of type tinyint, protecttype/2 is
139                       ** integer division and will yield 0 for both types of
140                       ** grants and will yield 1 for revoke, i.e., when
141                       ** the value of protecttype is 2.  The XOR (^) operation
142                       ** will reverse the bits and thus (protecttype/2)^1 will
143                       ** yield a value of 1 for grants and will yield a
144                       ** value of zero for revoke.
145                       **
146                       ** Normal uids have values upto 16383, roles have uids
147                       ** from 16384 upto 16389 and uids of groups start from
148                       ** 16390 onwards.
149                       **
150                       ** If there are several entries in the sysprotects table
151                       ** with the same Object ID, then the following expression
152                       ** will prefer an individual uid entry over a group entry
153                       ** and prefer a group entry over a role entry.
154                       **
155                       ** For example, let us say there are two users u1 and u2
156                       ** with uids 4 and 5 respectiveley and both u1 and u2
157                       ** belong to a group g12 whose uid is 16390.  procedure p1
158                       ** is owned by user u0 and user u0 performs the following
159                       ** actions:
160                       **
161                       **      grant exec on p1 to g12
162                       **      revoke grant on p1 from u1
163                       **
164                       ** There will be two entries in sysprotects for the object
165                       ** p1, one for the group g12 where protecttype = grant (1)
166                       ** and one for u1 where protecttype = revoke (2).
167                       **
168                       ** For the group g12, the following expression will
169                       ** evaluate to:
170                       **
171                       **      (((+)*abs(16390-16383))*2) + ((1/2)^1))
172                       **      = ((14) + (0)^1) = 14 + 1 = 15
173                       **
174                       ** For the user entry u1, it will evaluate to:
175                       **
176                       **      (((+)*abs(4-16383)*2) + ((2/2)^1))
177                       **      = ((abs(-16379)*2 + (1)^1)
178                       **      = 16379*2 + 0 = 32758
179                       **
180                       ** As the expression evaluates to a bigger number for the
181                       ** user entry u1, select max() will chose 32758 which,
182                       ** ANDed with 1 gives 0, i.e., sp_jdbc_stored_procedures will
183                       ** not display this particular procedure to the user.
184                       **
185                       ** When the user u2 invokes sp_jdbc_stored_procedures, there is
186                       ** only one entry for u2, which is the entry for the group
187                       ** g12, and so this entry will be selected thus allowing
188                       ** the procedure in question to be displayed.
189                       **
190                       ** Notice that multiplying by 2 makes the number an
191                       ** even number (meaning the last digit is 0) so what
192                       ** matters at the end is (protecttype/2)^1.
193                       **
194                       */
195   
196                       or ((select distinct max(((sign(p.uid) * abs(p.uid - 16383)) * 2)
197                           + ((p.protecttype / 2) ^ 1))
198                           from sysprotects p, sysusers u
199                           where action in (193, 224)
200                               and u.uid = user_id()
201                               and (p.uid = 0 /* get rows for public */
202                                   or p.uid = user_id() /* current user */
203                                   or p.uid = u.gid)
204                               and p.id = o.id /* outer join to correlate 
205                           ** with all rows in sysobjects 
206                           */
207                           ) & 1 /* more magic...normalize GRANT */
208                       ) = 1) /* final magic...compare Grants */
209               order by PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME, SPECIFIC_NAME
210           end /* End of if @version is not null */
211           else
212           begin
213               select PROCEDURE_CAT = db_name(),
214                   PROCEDURE_SCHEM = user_name(o.uid),
215                   PROCEDURE_NAME = o.name + ';' + ltrim(str(p.number, 5)),
216                   num_input_params = - 1, /* Constant since value unknown */
217                   num_output_params = - 1, /* Constant since value unknown */
218                   num_result_sets = - 1, /* Constant since value unknown */
219                   REMARKS = convert(varchar(254), null), /* Remarks are NULL */
220                   PROCEDURE_TYPE = 0
221               from sysobjects o, sysprocedures p /*__FORCEDINDEX125x__*/, sysusers u
222               where o.name like @sp_name ESCAPE '\'
223                   and p.sequence = 0
224                   and user_name(o.uid) like @sp_owner ESCAPE '\'
225                   and o.type = 'P' /* Object type of Procedure */
226                   and p.id = o.id
227                   and u.uid = user_id() /* constrain sysusers uid for use in 
228                   ** subquery 
229                   */
230                   and (suser_id() =
231                           (select uid from sysusers where suid = suser_id()) /* User is the System Administrator */
232                       or o.uid = user_id() /* User created the object */
233                       /* here's the magic..select the highest 
234                       ** precedence of permissions in the 
235                       ** order (user,group,public)  
236                       */
237   
238                       /*
239                       ** The value of protecttype is
240                       **
241                       **      0  for grant with grant
242                       **      1  for grant and,
243                       **      2  for revoke
244                       **
245                       ** As protecttype is of type tinyint, protecttype/2 is
246                       ** integer division and will yield 0 for both types of
247                       ** grants and will yield 1 for revoke, i.e., when
248                       ** the value of protecttype is 2.  The XOR (^) operation
249                       ** will reverse the bits and thus (protecttype/2)^1 will
250                       ** yield a value of 1 for grants and will yield a
251                       ** value of zero for revoke.
252                       **
253                       ** Normal uids have values upto 16383, roles have uids
254                       ** from 16384 upto 16389 and uids of groups start from
255                       ** 16390 onwards.
256                       **
257                       ** If there are several entries in the sysprotects table
258                       ** with the same Object ID, then the following expression
259                       ** will prefer an individual uid entry over a group entry
260                       ** and prefer a group entry over a role entry.
261                       **
262                       ** For example, let us say there are two users u1 and u2
263                       ** with uids 4 and 5 respectiveley and both u1 and u2
264                       ** belong to a group g12 whose uid is 16390.  procedure p1
265                       ** is owned by user u0 and user u0 performs the following
266                       ** actions:
267                       **
268                       **      grant exec on p1 to g12
269                       **      revoke grant on p1 from u1
270                       **
271                       ** There will be two entries in sysprotects for the object
272                       ** p1, one for the group g12 where protecttype = grant (1)
273                       ** and one for u1 where protecttype = revoke (2).
274                       **
275                       ** For the group g12, the following expression will
276                       ** evaluate to:
277                       **
278                       **      (((+)*abs(16390-16383))*2) + ((1/2)^1))
279                       **      = ((14) + (0)^1) = 14 + 1 = 15
280                       **
281                       ** For the user entry u1, it will evaluate to:
282                       **
283                       **      (((+)*abs(4-16383)*2) + ((2/2)^1))
284                       **      = ((abs(-16379)*2 + (1)^1)
285                       **      = 16379*2 + 0 = 32758
286                       **
287                       ** As the expression evaluates to a bigger number for the
288                       ** user entry u1, select max() will chose 32758 which,
289                       ** ANDed with 1 gives 0, i.e., sp_jdbc_stored_procedures will
290                       ** not display this particular procedure to the user.
291                       **
292                       ** When the user u2 invokes sp_jdbc_stored_procedures, there is
293                       ** only one entry for u2, which is the entry for the group
294                       ** g12, and so this entry will be selected thus allowing
295                       ** the procedure in question to be displayed.
296                       **
297                       ** Notice that multiplying by 2 makes the number an
298                       ** even number (meaning the last digit is 0) so what
299                       ** matters at the end is (protecttype/2)^1.
300                       **
301                       */
302   
303                       or ((select distinct max(((sign(p.uid) * abs(p.uid - 16383)) * 2)
304                           + ((p.protecttype / 2) ^ 1))
305                           from sysprotects p, sysusers u
306                           where action in (193, 224)
307                               and u.uid = user_id()
308                               and (p.uid = 0 /* get rows for public */
309                                   or p.uid = user_id() /* current user */
310                                   or p.uid = u.gid)
311                               and p.id = o.id /* outer join to correlate 
312                           ** with all rows in sysobjects 
313                           */
314                           ) & 1 /* more magic...normalize GRANT */
315                       ) = 1) /* final magic...compare Grants */
316               order by PROCEDURE_SCHEM, PROCEDURE_NAME
317           end /* End of else of "if @version is not null" */
318       end /* End of "if @functions =0" */
319       else
320       begin
321           select FUNCTION_CAT = db_name(),
322               FUNCTION_SCHEM = user_name(o.uid),
323               FUNCTION_NAME = user_name(o.uid) + '.' + o.name,
324               REMARKS = convert(varchar(254), null), /* Remarks are NULL */
325               FUNCTION_TYPE = 1, /*functionNoTable*/
326               SPECIFIC_NAME = o.name + ';' + ltrim(str(p.number, 5))
327           from sysobjects o, sysprocedures p /*__FORCEDINDEX125x__*/, sysusers u
328           where o.name like @sp_name ESCAPE '\'
329               and user_name(o.uid) like @sp_owner ESCAPE '\'
330               and o.type = 'SF' /* Object type of functions */
331               and p.id = o.id
332               and u.uid = user_id() /* constrain sysusers uid for use in 
333               ** subquery 
334               */
335               and p.sequence = 0
336   
337               and (suser_id() =
338                       (select uid from sysusers where suid = suser_id()) /* User is the System Administrator */
339                   or o.uid = user_id() /* User created the object */
340                   /* here's the magic..select the highest 
341                   ** precedence of permissions in the 
342                   ** order (user,group,public)  
343                   */
344   
345                   /* refer the logic for protecttype in the if part above */
346                   or ((select max(((sign(p.uid) * abs(p.uid - 16383)) * 2)
347                       + ((p.protecttype / 2) ^ 1))
348                       from sysprotects p, sysusers u
349                       where action in (193, 224)
350                           and (p.uid = 0 /* get rows for public */
351                               or p.uid = user_id() /* current user */
352                               or p.uid = u.gid)
353                           and p.id = o.id /* outer join to correlate 
354                       ** with all rows in sysobjects 
355                       */
356                       ) & 1 /* more magic...normalize GRANT */
357                   ) = 1) /* final magic...compare Grants */
358           order by FUNCTION_CAT, FUNCTION_SCHEM, FUNCTION_NAME, SPECIFIC_NAME
359       end /* End of else "if @functions =0" */
360   


exec sp_procxmode 'sp_jdbc_stored_procedures', 'AnyMode'
go

Grant Execute on sp_jdbc_stored_procedures to public
go
RESULT SETS
sp_jdbc_stored_procedures_rset_001
sp_jdbc_stored_procedures_rset_003
sp_jdbc_stored_procedures_rset_002

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 59
 MEST 4 Empty String will be replaced by Single Space 65
 MEST 4 Empty String will be replaced by Single Space 66
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysprotects sybsystemprocs..sysprotects
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysobjects o and [sybsystemprocs..sysusers u], 5 tables with rc=1 105
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysobjects o and [sybsystemprocs..sysusers u], 5 tables with rc=1 213
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysobjects o and [sybsystemprocs..sysusers u], 5 tables with rc=1 321
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {sequence}
116
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype, predid)
Intersection: {uid}
Uncovered: [id, grantor, protecttype, predid]
203
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {sequence}
223
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype, predid)
Intersection: {uid}
Uncovered: [id, grantor, protecttype, predid]
310
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {sequence}
335
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype, predid)
Intersection: {uid}
Uncovered: [id, grantor, protecttype, predid]
352
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 119
 MAW1 3 Warning message on %name% sybsystemprocs..sysprocedures.id: Warning message on sysprocedures 119
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 204
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 204
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 226
 MAW1 3 Warning message on %name% sybsystemprocs..sysprocedures.id: Warning message on sysprocedures 226
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 311
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 311
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 331
 MAW1 3 Warning message on %name% sybsystemprocs..sysprocedures.id: Warning message on sysprocedures 331
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 353
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 353
 MGTP 3 Grant to public sybsystemprocs..sp_jdbc_stored_procedures  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysprocedures  
 MGTP 3 Grant to public sybsystemprocs..sysprotects  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check return value of exec 76
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 196
 MUCO 3 Useless Code Useless Brackets 197
 MUCO 3 Useless Code Useless Brackets 303
 MUCO 3 Useless Code Useless Brackets 304
 MUCO 3 Useless Code Useless Brackets 346
 MUCO 3 Useless Code Useless Brackets 347
 QCRS 3 Conditional Result Set 105
 QCRS 3 Conditional Result Set 213
 QCRS 3 Conditional Result Set 321
 QDIS 3 Check correct use of 'select distinct' 196
 QDIS 3 Check correct use of 'select distinct' 303
 QGWO 3 Group by/Distinct/Union without order by 196
 QGWO 3 Group by/Distinct/Union without order by 303
 QISO 3 Set isolation level 42
 QNAJ 3 Not using ANSI Inner Join 114
 QNAJ 3 Not using ANSI Inner Join 198
 QNAJ 3 Not using ANSI Inner Join 221
 QNAJ 3 Not using ANSI Inner Join 305
 QNAJ 3 Not using ANSI Inner Join 327
 QNAJ 3 Not using ANSI Inner Join 348
 QNUA 3 Should use Alias: Table sybsystemprocs..sysusers 124
 QNUA 3 Should use Alias: Column action should use alias p 199
 QNUA 3 Should use Alias: Table sybsystemprocs..sysusers 231
 QNUA 3 Should use Alias: Column action should use alias p 306
 QNUA 3 Should use Alias: Table sybsystemprocs..sysusers 338
 QNUA 3 Should use Alias: Column action should use alias p 349
 QPNC 3 No column in condition 123
 QPNC 3 No column in condition 196
 QPNC 3 No column in condition 230
 QPNC 3 No column in condition 303
 QPNC 3 No column in condition 337
 QPNC 3 No column in condition 346
 QTJ1 3 Table only appears in inner join clause 348
 VUNU 3 Variable is not used @uid 29
 VUNU 3 Variable is not used @protecttype 30
 VUNU 3 Variable is not used @id 31
 VUNU 3 Variable is not used @action 32
 VUNU 3 Variable is not used @number 33
 VUNU 3 Variable is not used @sequence 34
 MRST 2 Result Set Marker 105
 MRST 2 Result Set Marker 213
 MRST 2 Result Set Marker 321
 MSUB 2 Subquery Marker 124
 MSUB 2 Subquery Marker 231
 MSUB 2 Subquery Marker 338
 MSUC 2 Correlated Subquery Marker 196
 MSUC 2 Correlated Subquery Marker 303
 MSUC 2 Correlated Subquery Marker 346
 MTR1 2 Metrics: Comments Ratio Comments: 65% 16
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 9 = 9dec - 2exi + 2 16
 MTR3 2 Metrics: Query Complexity Complexity: 102 16
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 105
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, u=sybsystemprocs..sysusers} 0 196
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 213
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, u=sybsystemprocs..sysusers} 0 303
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 321
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, u=sybsystemprocs..sysusers} 0 346

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
reads table sybsystemprocs..sysprocedures  
reads table sybsystemprocs..sysusers  
writes table sybsystemprocs..sp_jdbc_stored_procedures_rset_001 
reads table sybsystemprocs..sysprotects  
writes table sybsystemprocs..sp_jdbc_stored_procedures_rset_003 
writes table sybsystemprocs..sp_jdbc_stored_procedures_rset_002