Database | Proc | Application | Created | Links |
sybsystemprocs | sp_oledb_statistics ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 3 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 4 /* 10.0 1.1 06/16/93 sproc/tables */ 5 6 /* 7 ** Messages for "sp_oledb_statistics" 18039 8 ** 9 ** 17676, "This may be a temporary object. Please execute procedure from tempdb." 10 ** 11 ** 18039, "Table qualifier must be name of current database" 12 ** 13 */ 14 create procedure sp_oledb_statistics 15 @table_catalog varchar(32) = null, 16 @table_schema varchar(32) = null, 17 @table_name varchar(255) = null 18 19 as 20 21 declare @type1 varchar(3) 22 declare @tname varchar(255) 23 declare @startedInTransaction bit 24 25 if (@@trancount > 0) 26 select @startedInTransaction = 1 27 else 28 select @startedInTransaction = 0 29 30 if @@trancount = 0 31 begin 32 set chained off 33 end 34 35 set transaction isolation level 1 36 37 if (@startedInTransaction = 1) 38 save transaction oledb_keep_temptable_tx 39 40 /* temp table */ 41 if (@table_name like "#%" and 42 db_name() != db_name(tempdb_id())) 43 begin 44 /* 45 ** Can return data about temp. tables only in tempdb 46 */ 47 raiserror 17676 48 return (1) 49 end 50 51 create table #oledb_results_table 52 ( 53 54 TABLE_CATALOG varchar(32) null, 55 TABLE_SCHEMA varchar(32) null, 56 TABLE_NAME varchar(255) null, 57 CARDINALITY int null 58 ) 59 60 61 62 /* 63 ** Special feature #1: enumerate databases when owner and name 64 ** are blank but catalog is explicitly '%'. 65 */ 66 if @table_catalog = '%' and 67 @table_schema = '' and 68 @table_name = '' 69 begin 70 71 /* 72 ** If enumerating databases 73 */ 74 insert #oledb_results_table 75 select 76 TABLE_CATALOG = name, 77 TABLE_SCHEMA = null, 78 TABLE_NAME = null, 79 CARDINALITY = null 80 from master..sysdatabases 81 82 /* 83 ** eliminate MODEL database 84 */ 85 where name != 'model' 86 order by TABLE_CATALOG 87 end 88 89 /* 90 ** Special feature #2: enumerate owners when qualifier and name 91 ** are blank but owner is explicitly '%'. 92 */ 93 else if @table_catalog = '' and 94 @table_schema = '%' and 95 @table_name = '' 96 begin 97 98 /* 99 ** If enumerating owners 100 */ 101 insert #oledb_results_table 102 select distinct 103 TABLE_CATALOG = null, 104 TABLE_SCHEMA = user_name(uid), 105 TABLE_NAME = null, 106 CARDINALITY = null 107 from sysobjects 108 order by TABLE_CATALOG 109 end 110 else 111 begin 112 113 /* 114 ** end of special features -- do normal processing 115 */ 116 if @table_catalog is not null 117 118 begin 119 if db_name() != @table_catalog 120 begin 121 if @table_catalog = '' 122 begin 123 124 /* 125 ** If empty qualifier supplied 126 ** Force an empty result set by 127 ** going directly to select 128 */ 129 goto SelectClause 130 end 131 end 132 end 133 134 select @type1 = 'SUV' 135 136 137 if @table_name is null 138 139 begin 140 141 /* 142 ** If table name not supplied, match all 143 */ 144 select @table_name = '%' 145 end 146 else 147 begin 148 if (@table_schema is null) and 149 (charindex('%', @table_name) = 0) 150 begin 151 152 /* 153 ** If owner not specified and table is specified 154 */ 155 if exists (select * from sysobjects 156 where uid = user_id() 157 and id = object_id(@table_name) 158 and (type = 'U' or type = 'V' 159 or type = 'S')) 160 begin 161 162 /* 163 ** Override supplied owner w/owner of table 164 */ 165 select @table_schema = user_name() 166 end 167 end 168 end 169 170 /* 171 ** If no owner supplied, force wildcard 172 */ 173 if @table_schema is null 174 select @table_schema = '%' 175 /* 176 ** If no catalog supplied, force wildcard 177 */ 178 if @table_catalog is null 179 select @table_catalog = '%' 180 181 182 insert #oledb_results_table 183 select 184 TABLE_CATALOG = db_name(), 185 TABLE_SCHEMA = user_name(o.uid), 186 TABLE_NAME = o.name, 187 -- CARDINALITY = rowcnt(x.doampg) 188 CARDINALITY = row_count(db_id(), x.id) 189 190 from sysusers u, sysobjects o, sysindexes x 191 where 192 /* Special case for temp. tables. Match ids */ 193 (o.name like @table_name or o.id = object_id(@table_name)) 194 and user_name(o.uid) like @table_schema 195 196 /* 197 ** Only desired types 198 */ 199 and charindex(substring(o.type, 1, 1), @type1) != 0 200 and o.name = x.name 201 202 /* 203 ** constrain sysusers uid for use in subquery 204 */ 205 and u.uid = user_id() 206 and ( 207 suser_id() = 1 /* User is the System Administrator */ 208 or o.uid = user_id() /* User created the object */ 209 /* here's the magic..select the highest 210 ** precedence of permissions in the 211 ** order (user,group,public) 212 */ 213 214 /* 215 ** The value of protecttype is 216 ** 217 ** 0 for grant with grant 218 ** 1 for grant and, 219 ** 2 for revoke 220 ** 221 ** As protecttype is of type tinyint, protecttype/2 is 222 ** integer division and will yield 0 for both types of 223 ** grants and will yield 1 for revoke, i.e., when 224 ** the value of protecttype is 2. The XOR (^) operation 225 ** will reverse the bits and thus (protecttype/2)^1 will 226 ** yield a value of 1 for grants and will yield a 227 ** value of zero for revoke. 228 ** 229 ** For groups, uid = gid. We shall use this to our advantage. 230 ** 231 ** If there are several entries in the sysprotects table 232 ** with the same Object ID, then the following expression 233 ** will prefer an individual uid entry over a group entry 234 ** 235 ** For example, let us say there are two users u1 and u2 236 ** with uids 4 and 5 respectiveley and both u1 and u2 237 ** belong to a group g12 whose uid is 16390. table t1 238 ** is owned by user u0 and user u0 performs the following 239 ** actions: 240 ** 241 ** grant select on t1 to g12 242 ** revoke select on t1 from u1 243 ** 244 ** There will be two entries in sysprotects for the object t1, 245 ** one for the group g12 where protecttype = grant (1) and 246 ** one for u1 where protecttype = revoke (2). 247 ** 248 ** For the group g12, the following expression will 249 ** evaluate to: 250 ** 251 ** ((abs(16390-16390)*2) + ((1/2)^1) 252 ** = ((0) + (0)^1) = 0 + 1 = 1 253 ** 254 ** For the user entry u1, it will evaluate to: 255 ** 256 ** (((+)*abs(4-16390)*2) + ((2/2)^1)) 257 ** = (abs(-16386)*2 + (1)^1) 258 ** = 16386*2 + 0 = 32772 259 ** 260 ** As the expression evaluates to a bigger number for the 261 ** user entry u1, select max() will chose 32772 which, 262 ** ANDed with 1 gives 0, i.e., sp_oledb_statistics will not display 263 ** this particular table to the user. 264 ** 265 ** When the user u2 invokes sp_oledb_statistics, there is only one 266 ** entry for u2, which is the entry for the group g12, and 267 ** so the group entry will be selected thus allowing the 268 ** table t1 to be displayed. 269 ** 270 ** ((select max((abs(uid-u.gid)*2) 271 ** + ((protecttype/2)^1)) 272 ** 273 ** Notice that multiplying by 2 makes the number an 274 ** even number (meaning the last digit is 0) so what 275 ** matters at the end is (protecttype/2)^1. 276 ** 277 **/ 278 279 or ((select max((abs(p.uid - u2.gid) * 2) + ((p.protecttype / 2) ^ 1)) 280 from sysprotects p, sysusers u2 281 where p.id = o.id /* outer join to correlate 282 ** with all rows in sysobjects 283 */ 284 and u2.uid = user_id() 285 /* 286 ** get rows for public, current users, user's groups 287 */ 288 and (p.uid = 0 or /* public */ 289 p.uid = user_id() or /* current user */ 290 p.uid = u2.gid) /* users group */ 291 292 /* 293 ** check for SELECT, EXECUTE privilege. 294 */ 295 and (p.action in (193, 224))) & 1 296 297 /* 298 ** more magic...normalise GRANT 299 ** and final magic...compare 300 ** Grants. 301 */ 302 ) = 1) 303 order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME 304 305 end 306 307 SelectClause: /* Adaptive Server has expanded all '*' elements in the following statement */ 308 select #oledb_results_table.TABLE_CATALOG, #oledb_results_table.TABLE_SCHEMA, #oledb_results_table.TABLE_NAME, #oledb_results_table.CARDINALITY from #oledb_results_table where TABLE_CATALOG like @table_catalog 309 AND TABLE_SCHEMA like @table_schema 310 AND TABLE_NAME like @table_name order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME 311 312 313 if (@startedInTransaction = 1) 314 rollback transaction oledb_keep_temptable_tx 315 316 return (0) 317
exec sp_procxmode 'sp_oledb_statistics', 'AnyMode' go Grant Execute on sp_oledb_statistics to public go
RESULT SETS | |
sp_oledb_statistics_rset_001 |
DEPENDENCIES |
PROCS AND TABLES USED reads table sybsystemprocs..sysobjects ![]() reads table sybsystemprocs..sysindexes ![]() reads table sybsystemprocs..sysprotects ![]() read_writes table tempdb..#oledb_results_table (1) reads table sybsystemprocs..sysusers ![]() reads table master..sysdatabases (1) ![]() |