| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_oledb_stored_procedures | | 31 Aug 14 | Defects Dependencies |
1 2 3 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 4 5 /* 6 ** Messages for "sp_oledb_stored_procedures" 18041 7 ** 8 ** 18041, "Stored Procedure qualifier must be name of current database." 9 ** 10 */ 11 create procedure sp_oledb_stored_procedures 12 @sp_name varchar(771) = null, /* stored procedure name */ 13 @sp_owner varchar(32) = null, /* stored procedure owner */ 14 @sp_qualifier varchar(32) = null, /* stored procedure qualifier; 15 ** For the SQL Server, the only valid 16 ** values are NULL or the current 17 ** database name 18 */ 19 @type varchar(2) = null, /* used for ado.net2 and do nothing here */ 20 @is_ado int = 1 21 22 23 as 24 25 26 if @@trancount = 0 27 begin 28 set chained off 29 end 30 31 set transaction isolation level 1 32 33 /* If qualifier is specified */ 34 if @sp_qualifier is not null 35 begin 36 /* If qualifier doesn't match current database */ 37 if db_name() != @sp_qualifier 38 begin 39 /* If qualifier is not specified */ 40 if @sp_qualifier = '' 41 begin 42 /* in this case, we need to return an empty 43 ** result set because the user has requested a 44 ** database with an empty name 45 */ 46 select @sp_name = '' 47 select @sp_owner = '' 48 end 49 50 /* qualifier is specified and does not match current database */ 51 else 52 begin 53 /* 54 ** 18041, "Stored Procedure qualifer must be name of 55 ** current database" 56 */ 57 raiserror 18041 58 return (1) 59 end 60 end 61 end 62 63 /* If procedure name not supplied, match all */ 64 if @sp_name is null 65 begin 66 select @sp_name = '%' 67 end 68 else 69 begin 70 /* If owner name is not supplied, but procedure name is */ 71 if (@sp_owner is null) and (charindex('%', @sp_name) = 0) 72 begin 73 /* If procedure exists and is owned by the current user */ 74 if exists (select * 75 from sysobjects 76 where uid = user_id() 77 and name = @sp_name 78 and type = 'P') /* Object type of Procedure */ 79 begin 80 /* Set owner name to current user */ 81 select @sp_owner = user_name() 82 end 83 end 84 end 85 86 /* If procedure owner not supplied, match all */ 87 if @sp_owner is null 88 select @sp_owner = '%' 89 90 /* 91 ** Retrieve the stored procedures and associated info on them 92 */ 93 select PROCEDURE_CATALOG = db_name(), 94 PROCEDURE_SCHEMA = user_name(o.uid), 95 PROCEDURE_NAME = o.name + ';' + ltrim(str(p.number, 5)), 96 PROCEDURE_TYPE = case when o.type = 'P' then convert(smallint, 1) when o.type = 'F' then convert(smallint, 2) end, 97 PROCEDURE_DEFINITION = convert(varchar(254), null), 98 DESCRIPTION = convert(varchar(254), null), /* Remarks are NULL */ 99 DATE_CREATED = case when @is_ado = 2 then convert(datetime, o.crdate) when @is_ado = 1 then convert(datetime, null) end, 100 DATE_MODIFIED = case when @is_ado = 2 then convert(datetime, o.expdate) when @is_ado = 1 then convert(datetime, null) end 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 in ('P', 'F') /* Object type of Procedure or Function */ 106 and p.id = o.id 107 and u.uid = user_id() /* constrain sysusers uid for use in 108 ** subquery 109 */ 110 111 and (suser_id() = 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_oledb_stored_procedures will 167 ** not display this particular procedure to the user. 168 ** 169 ** When the user u2 invokes sp_oledb_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 distinct 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 222 order by PROCEDURE_CATALOG, PROCEDURE_SCHEMA, PROCEDURE_NAME 223
exec sp_procxmode 'sp_oledb_stored_procedures', 'AnyMode' go Grant Execute on sp_oledb_stored_procedures to public go
| RESULT SETS | |
| sp_oledb_stored_procedures_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..sysroles reads table master..syssrvroles (1)  reads table sybsystemprocs..sysprotects reads table sybsystemprocs..sysusers reads table sybsystemprocs..sysprocedures reads table sybsystemprocs..sysobjects |
