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