| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_ijdbc_stored_procedures | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** sp_ijdbc_stored_procedures 4 */ 5 6 7 /* 8 ** Altered from the ODBC sp_ijdbc_procedures defined in sycsp11.sql. 9 ** 10 ** New column 'PROCEDURE_TYPE' was added to support JDBC spec. This 11 ** column is to indicate if the procedure returns a result. If 0, 12 ** column will be evalued as DatabaseMetadata.procedureResultUnknown; 13 ** this means that the procedure MAY return a result. 14 */ 15 /* 16 ** Messages for 'sp_ijdbc_stored_procedures' 18041 17 ** 18 ** 18041, 'Stored Procedure qualifier must be name of current database.' 19 ** 20 */ 21 22 create procedure sp_ijdbc_stored_procedures 23 @sp_qualifier varchar(32) = null, /* stored procedure qualifier; 24 ** For the SQL Server, the only valid 25 ** values are NULL or the current 26 ** database name 27 */ 28 @sp_owner varchar(32) = null, /* stored procedure owner */ 29 @sp_name varchar(261) = null /* stored procedure name */ 30 as 31 32 declare @msg varchar(90) 33 34 if @@trancount = 0 35 begin 36 set chained off 37 end 38 39 set transaction isolation level 1 40 41 delete #tmp_stored_procedures 42 43 /* If qualifier is specified */ 44 if @sp_qualifier is not null 45 begin 46 /* If qualifier doesn't match current database */ 47 if db_name() != @sp_qualifier 48 begin 49 /* If qualifier is not specified */ 50 if @sp_qualifier = '' 51 begin 52 /* in this case, we need to return an empty 53 ** result set because the user has requested a 54 ** database with an empty name 55 */ 56 select @sp_name = '' 57 select @sp_owner = '' 58 end 59 60 /* qualifier is specified and does not match current database */ 61 else 62 begin 63 /* 64 ** 18041, 'Stored Procedure qualifer must be name of 65 ** current database' 66 */ 67 exec sp_getmessage 18041, @msg out 68 raiserror 18041 @msg 69 return (1) 70 end 71 end 72 end 73 74 /* If procedure name not supplied, match all */ 75 if @sp_name is null 76 begin 77 select @sp_name = '%' 78 end 79 80 /* If procedure owner not supplied, match all */ 81 if @sp_owner is null 82 select @sp_owner = '%' 83 84 /* 85 ** Retrieve the stored procedures and associated info on them 86 */ 87 88 insert #tmp_stored_procedures 89 select PROCEDURE_CAT = db_name(), 90 PROCEDURE_SCHEM = user_name(o.uid), 91 PROCEDURE_NAME = o.name + ';' + ltrim(str(p.number, 5)), 92 num_input_params = - 1, /* Constant since value unknown */ 93 num_output_params = - 1, /* Constant since value unknown */ 94 num_result_sets = - 1, /* Constant since value unknown */ 95 REMARKS = convert(varchar(254), null), /* Remarks are NULL */ 96 PROCEDURE_TYPE = 0 97 from sysobjects o, sysprocedures p, sysusers u 98 where o.name like @sp_name ESCAPE '\' 99 and p.sequence = 0 100 and user_name(o.uid) like @sp_owner ESCAPE '\' 101 and o.type = 'P' /* Object type of Procedure */ 102 and p.id = o.id 103 and u.uid = user_id() /* constrain sysusers uid for use in 104 ** subquery 105 */ 106 107 and (suser_id() = 1 /* User is the System Administrator */ 108 or o.uid = user_id() /* User created the object */ 109 /* here's the magic..select the highest 110 ** precedence of permissions in the 111 ** order (user,group,public) 112 */ 113 114 /* 115 ** The value of protecttype is 116 ** 117 ** 0 for grant with grant 118 ** 1 for grant and, 119 ** 2 for revoke 120 ** 121 ** As protecttype is of type tinyint, protecttype/2 is 122 ** integer division and will yield 0 for both types of 123 ** grants and will yield 1 for revoke, i.e., when 124 ** the value of protecttype is 2. The XOR (^) operation 125 ** will reverse the bits and thus (protecttype/2)^1 will 126 ** yield a value of 1 for grants and will yield a 127 ** value of zero for revoke. 128 ** 129 ** Normal uids have values upto 16383, roles have uids 130 ** from 16384 upto 16389 and uids of groups start from 131 ** 16390 onwards. 132 ** 133 ** If there are several entries in the sysprotects table 134 ** with the same Object ID, then the following expression 135 ** will prefer an individual uid entry over a group entry 136 ** and prefer a group entry over a role entry. 137 ** 138 ** For example, let us say there are two users u1 and u2 139 ** with uids 4 and 5 respectiveley and both u1 and u2 140 ** belong to a group g12 whose uid is 16390. procedure p1 141 ** is owned by user u0 and user u0 performs the following 142 ** actions: 143 ** 144 ** grant exec on p1 to g12 145 ** revoke grant on p1 from u1 146 ** 147 ** There will be two entries in sysprotects for the object 148 ** p1, one for the group g12 where protecttype = grant (1) 149 ** and one for u1 where protecttype = revoke (2). 150 ** 151 ** For the group g12, the following expression will 152 ** evaluate to: 153 ** 154 ** (((+)*abs(16390-16383))*2) + ((1/2)^1)) 155 ** = ((14) + (0)^1) = 14 + 1 = 15 156 ** 157 ** For the user entry u1, it will evaluate to: 158 ** 159 ** (((+)*abs(4-16383)*2) + ((2/2)^1)) 160 ** = ((abs(-16379)*2 + (1)^1) 161 ** = 16379*2 + 0 = 32758 162 ** 163 ** As the expression evaluates to a bigger number for the 164 ** user entry u1, select max() will chose 32758 which, 165 ** ANDed with 1 gives 0, i.e., sp_ijdbc_stored_procedures will 166 ** not display this particular procedure to the user. 167 ** 168 ** When the user u2 invokes sp_ijdbc_stored_procedures, there is 169 ** only one entry for u2, which is the entry for the group 170 ** g12, and so this entry will be selected thus allowing 171 ** the procedure in question to be displayed. 172 ** 173 ** Notice that multiplying by 2 makes the number an 174 ** even number (meaning the last digit is 0) so what 175 ** matters at the end is (protecttype/2)^1. 176 ** 177 */ 178 179 or ((select max(((sign(uid) * abs(uid - 16383)) * 2) 180 + ((protecttype / 2) ^ 1)) 181 from sysprotects p 182 where p.id = o.id /* outer join to correlate 183 ** with all rows in sysobjects 184 */ 185 /* 186 ** get rows for public, current users, user's groups 187 */ 188 and (p.uid = 0 /* get rows for public */ 189 or p.uid = user_id() /* current user */ 190 or p.uid = u.gid) /* users group */ 191 192 /* 193 ** check for SELECT, EXECUTE privilege. 194 */ 195 and (action in (193, 224)) /* check for SELECT,EXECUTE 196 ** privilege 197 */ 198 ) & 1 /* more magic...normalize GRANT */ 199 ) = 1 /* final magic...compare Grants */ 200 /* 201 ** If one of any user defined roles or contained roles for the 202 ** user has permission, the user has the permission 203 */ 204 or exists (select 1 205 from sysprotects p1, 206 master.dbo.syssrvroles srvro, 207 sysroles ro 208 where p1.id = o.id 209 and p1.uid = ro.lrid 210 and ro.id = srvro.srid 211 and has_role(srvro.name, 1) > 0 212 and p1.action = 224)) 213 214 order by PROCEDURE_SCHEM, PROCEDURE_NAME 215 216
exec sp_procxmode 'sp_ijdbc_stored_procedures', 'AnyMode' go Grant Execute on sp_ijdbc_stored_procedures to public go
| DEFECTS | |
 MEST 4 Empty String will be replaced by Single Space | 50 |
| MEST 4 Empty String will be replaced by Single Space | 56 |
| MEST 4 Empty String will be replaced by Single Space | 57 |
| MINU 4 Unique Index with nullable columns sybsystemprocs..sysprotects | sybsystemprocs..sysprotects |
| QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered (id, number, type, sequence) Intersection: {sequence} | 99 |
| QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysprotects.csysprotects unique clustered (id, action, grantor, uid, protecttype) Intersection: {uid} Uncovered: [id, grantor, protecttype] | 190 |
| QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered (id, action, grantor, uid, protecttype) Intersection: {action} | 212 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 212 |
 MGTP 3 Grant to public master..syssrvroles | |
| MGTP 3 Grant to public sybsystemprocs..sp_ijdbc_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..sysroles | |
| MGTP 3 Grant to public sybsystemprocs..sysusers | |
| MNER 3 No Error Check should check @@error after delete | 41 |
| MNER 3 No Error Check should check return value of exec | 67 |
| MNER 3 No Error Check should check @@error after insert | 88 |
| MUCO 3 Useless Code Useless Brackets | 69 |
| MUCO 3 Useless Code Useless Brackets | 179 |
| MUCO 3 Useless Code Useless Brackets | 180 |
| QISO 3 Set isolation level | 39 |
| QNAJ 3 Not using ANSI Inner Join | 97 |
| QNAJ 3 Not using ANSI Inner Join | 205 |
| QPNC 3 No column in condition | 107 |
| QPNC 3 No column in condition | 179 |
 MSUC 2 Correlated Subquery Marker | 179 |
| MSUC 2 Correlated Subquery Marker | 204 |
| MTR1 2 Metrics: Comments Ratio Comments: 68% | 22 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 6 = 6dec - 2exi + 2 | 22 |
| MTR3 2 Metrics: Query Complexity Complexity: 54 | 22 |
| PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 | 89 |
| PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, u=sybsystemprocs..sysusers} 0 | 179 |
| PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, r=sybsystemprocs..sysroles, s=master..syssrvroles} 0 | 204 |
| DEPENDENCIES |
| PROCS AND TABLES USED writes table tempdb..#tmp_stored_procedures (1) reads table sybsystemprocs..sysprotects reads table sybsystemprocs..sysusers reads table sybsystemprocs..sysroles reads table sybsystemprocs..sysprocedures reads table master..syssrvroles (1)  reads table sybsystemprocs..sysobjects calls proc sybsystemprocs..sp_getmessage  calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages reads table master..sysmessages (1) |