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