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