| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_oledb_tables | | 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_tables" 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_tables 15 @table_catalog varchar(32) = null, 16 @table_schema varchar(32) = null, 17 @table_name varchar(771) = null, 18 @table_type varchar(100) = null 19 as 20 21 declare @type1 varchar(3) 22 declare @tableindex int 23 declare @startedInTransaction bit 24 if (@@trancount > 0) 25 select @startedInTransaction = 1 26 else 27 select @startedInTransaction = 0 28 29 if @@trancount = 0 30 begin 31 set chained off 32 end 33 34 35 set transaction isolation level 1 36 37 if (@startedInTransaction = 1) 38 save transaction oledb_keep_temptable_tx 39 40 41 /* temp table */ 42 if (@table_name like "#%" and 43 db_name() != db_name(tempdb_id())) 44 begin 45 /* 46 ** Can return data about temp. tables only in tempdb 47 */ 48 raiserror 17676 49 return (1) 50 end 51 52 create table #oledb_results_table 53 ( 54 55 TABLE_CATALOG varchar(32) null, 56 TABLE_SCHEMA varchar(32) null, 57 TABLE_NAME varchar(255) null, 58 TABLE_TYPE varchar(32) null, 59 TABLE_GUID varchar(32) null, 60 DESCRIPTION varchar(255) null, 61 TABLE_PROPID int null, 62 DATE_CREATED datetime null, 63 DATE_MODIFIED datetime null 64 ) 65 66 /* 67 ** Special feature #1: enumerate databases when owner and name 68 ** are blank but qualifier is explicitly '%'. 69 */ 70 if @table_catalog = '%' and 71 @table_schema = '' and 72 @table_name = '' 73 begin 74 75 /* 76 ** If enumerating databases 77 */ 78 insert #oledb_results_table 79 select 80 TABLE_CATALOG = name, 81 TABLE_SCHEMA = null, 82 TABLE_NAME = null, 83 TABLE_TYPE = 'Database', 84 TABLE_GUID = convert(varchar(32), null), 85 DESCRIPTION = convert(varchar(255), null), 86 TABLE_PROPID = convert(int, null), 87 DATE_CREATED = convert(datetime, null), 88 DATE_MODIFIED = convert(datetime, null) 89 90 91 from master..sysdatabases 92 93 /* 94 ** eliminate MODEL database 95 */ 96 where name != 'model' 97 order by TABLE_CATALOG 98 end 99 100 /* 101 ** Special feature #2: enumerate owners when qualifier and name 102 ** are blank but owner is explicitly '%'. 103 */ 104 else if @table_catalog = '' and 105 @table_schema = '%' and 106 @table_name = '' 107 begin 108 109 /* 110 ** If enumerating owners 111 */ 112 insert #oledb_results_table 113 select distinct 114 TABLE_CATALOG = null, 115 TABLE_SCHEMA = user_name(uid), 116 TABLE_NAME = null, 117 TABLE_TYPE = 'Owner', 118 TABLE_GUID = convert(varchar(32), null), 119 DESCRIPTION = convert(varchar(255), null), 120 TABLE_PROPID = convert(int, null), 121 DATE_CREATED = convert(datetime, null), 122 DATE_MODIFIED = convert(datetime, null) 123 124 125 126 127 from sysobjects 128 order by TABLE_SCHEMA 129 end 130 else 131 begin 132 133 /* 134 ** end of special features -- do normal processing 135 */ 136 if @table_catalog is not null 137 138 begin 139 if LOWER(db_name()) != LOWER(@table_catalog) 140 begin 141 if @table_catalog = '' 142 begin 143 144 /* 145 ** If empty qualifier supplied 146 ** Force an empty result set 147 */ 148 select @table_name = '' 149 select @table_schema = '' 150 end 151 else 152 begin 153 154 /* 155 ** If qualifier doesn't match current 156 ** database. 157 */ 158 raiserror 18039 159 return 1 160 end 161 end 162 end 163 if @table_type is null 164 165 begin 166 167 /* 168 ** Select all oledb supported table types 169 */ 170 select @type1 = 'SUV' 171 end 172 else 173 begin 174 /* 175 ** TableType are case sensitive if CS server 176 */ 177 select @type1 = null 178 179 180 /* 181 ** Add System Tables 182 */ 183 if (patindex("%SYSTEM TABLE%", @table_type) != 0) 184 select @type1 = 'S' 185 186 /* 187 ** Add User Tables 188 */ 189 if (patindex("%TABLE%", @table_type) != 0) 190 select @type1 = @type1 + 'U' 191 192 /* 193 ** Add Views 194 */ 195 if (patindex("%VIEW%", @table_type) != 0) 196 select @type1 = @type1 + 'V' 197 end 198 if @table_name is null 199 200 begin 201 202 /* 203 ** If table name not supplied, match all 204 */ 205 select @table_name = '%' 206 end 207 else 208 begin 209 if (@table_schema is null) and 210 (charindex('%', @table_name) = 0) 211 begin 212 213 /* 214 ** If owner not specified and table is specified 215 */ 216 if exists (select * from sysobjects 217 where uid = user_id() 218 and id = object_id(@table_name) 219 and (type = 'U' or type = 'V' 220 or type = 'S')) 221 begin 222 223 /* 224 ** Override supplied owner w/owner of table 225 */ 226 select @table_schema = user_name() 227 end 228 end 229 end 230 231 /* 232 ** If no owner supplied, force wildcard 233 */ 234 if @table_schema is null 235 select @table_schema = '%' 236 insert #oledb_results_table 237 select 238 TABLE_CATALOG = db_name(), 239 TABLE_SCHEMA = user_name(o.uid), 240 TABLE_NAME = o.name, 241 TABLE_TYPE = rtrim( 242 substring('SYSTEM TABLE TABLE VIEW ', 243 /* 244 ** 'S'=0,'U'=2,'V'=3 245 */ 246 (ascii(o.type) - 83) * 12 + 1, 12)), 247 248 249 TABLE_GUID = convert(varchar(32), null), 250 DESCRIPTION = convert(varchar(255), null), 251 TABLE_PROPID = convert(int, null), 252 DATE_CREATED = convert(datetime, null), 253 DATE_MODIFIED = convert(datetime, null) 254 255 from sysusers u, sysobjects o 256 where 257 /* Special case for temp. tables. Match ids */ 258 (o.name like @table_name or o.id = object_id(@table_name)) 259 and user_name(o.uid) like @table_schema 260 261 /* 262 ** Only desired types 263 */ 264 and charindex(substring(o.type, 1, 1), @type1) != 0 265 266 /* 267 ** constrain sysusers uid for use in subquery 268 */ 269 and u.uid = user_id() 270 and ( 271 suser_id() = 1 /* User is the System Administrator */ 272 or o.uid = user_id() /* User created the object */ 273 /* here's the magic..select the highest 274 ** precedence of permissions in the 275 ** order (user,group,public) 276 */ 277 278 /* 279 ** The value of protecttype is 280 ** 281 ** 0 for grant with grant 282 ** 1 for grant and, 283 ** 2 for revoke 284 ** 285 ** As protecttype is of type tinyint, protecttype/2 is 286 ** integer division and will yield 0 for both types of 287 ** grants and will yield 1 for revoke, i.e., when 288 ** the value of protecttype is 2. The XOR (^) operation 289 ** will reverse the bits and thus (protecttype/2)^1 will 290 ** yield a value of 1 for grants and will yield a 291 ** value of zero for revoke. 292 ** 293 ** For groups, uid = gid. We shall use this to our advantage. 294 ** 295 ** If there are several entries in the sysprotects table 296 ** with the same Object ID, then the following expression 297 ** will prefer an individual uid entry over a group entry 298 ** 299 ** For example, let us say there are two users u1 and u2 300 ** with uids 4 and 5 respectiveley and both u1 and u2 301 ** belong to a group g12 whose uid is 16390. table t1 302 ** is owned by user u0 and user u0 performs the following 303 ** actions: 304 ** 305 ** grant select on t1 to g12 306 ** revoke select on t1 from u1 307 ** 308 ** There will be two entries in sysprotects for the object t1, 309 ** one for the group g12 where protecttype = grant (1) and 310 ** one for u1 where protecttype = revoke (2). 311 ** 312 ** For the group g12, the following expression will 313 ** evaluate to: 314 ** 315 ** ((abs(16390-16390)*2) + ((1/2)^1) 316 ** = ((0) + (0)^1) = 0 + 1 = 1 317 ** 318 ** For the user entry u1, it will evaluate to: 319 ** 320 ** (((+)*abs(4-16390)*2) + ((2/2)^1)) 321 ** = (abs(-16386)*2 + (1)^1) 322 ** = 16386*2 + 0 = 32772 323 ** 324 ** As the expression evaluates to a bigger number for the 325 ** user entry u1, select max() will chose 32772 which, 326 ** ANDed with 1 gives 0, i.e., sp_oledb_tables will not display 327 ** this particular table to the user. 328 ** 329 ** When the user u2 invokes sp_oledb_tables, there is only one 330 ** entry for u2, which is the entry for the group g12, and 331 ** so the group entry will be selected thus allowing the 332 ** table t1 to be displayed. 333 ** 334 ** ((select max((abs(uid-u.gid)*2) 335 ** + ((protecttype/2)^1)) 336 ** 337 ** Notice that multiplying by 2 makes the number an 338 ** even number (meaning the last digit is 0) so what 339 ** matters at the end is (protecttype/2)^1. 340 ** 341 **/ 342 343 or ((select max((abs(p.uid - u2.gid) * 2) + ((p.protecttype / 2) ^ 1)) 344 from sysprotects p, sysusers u2 345 where p.id = o.id /* outer join to correlate 346 ** with all rows in sysobjects 347 */ 348 and u2.uid = user_id() 349 /* 350 ** get rows for public, current users, user's groups 351 */ 352 and (p.uid = 0 or /* public */ 353 p.uid = user_id() or /* current user */ 354 p.uid = u2.gid) /* users group */ 355 356 /* 357 ** check for SELECT, EXECUTE privilege. 358 */ 359 and (p.action in (193, 224))) & 1 360 361 /* 362 ** more magic...normalise GRANT 363 ** and final magic...compare 364 ** Grants. 365 */ 366 ) = 1 367 /* 368 ** If one of any user defined roles or contained roles for the 369 ** user has permission, the user has the permission 370 */ 371 or exists (select 1 372 from sysprotects p1, 373 master.dbo.syssrvroles srvro, 374 sysroles ro 375 where p1.id = o.id 376 and p1.uid = ro.lrid 377 and ro.id = srvro.srid 378 and has_role(srvro.name, 1) > 0 379 and p1.action = 193)) 380 381 order by TABLE_TYPE, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME 382 end 383 if (patindex("%VIEW%", @table_type) != 0) 384 select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, null, null, null, 385 DESCRIPTION, DATE_CREATED, DATE_MODIFIED from #oledb_results_table 386 else /* Adaptive Server has expanded all '*' elements in the following statement */ 387 select #oledb_results_table.TABLE_CATALOG, #oledb_results_table.TABLE_SCHEMA, #oledb_results_table.TABLE_NAME, #oledb_results_table.TABLE_TYPE, #oledb_results_table.TABLE_GUID, #oledb_results_table.DESCRIPTION, #oledb_results_table.TABLE_PROPID, #oledb_results_table.DATE_CREATED, #oledb_results_table.DATE_MODIFIED from #oledb_results_table 388 389 if (@startedInTransaction = 1) 390 rollback transaction oledb_keep_temptable_tx 391 392 return (0) 393
exec sp_procxmode 'sp_oledb_tables', 'AnyMode' go Grant Execute on sp_oledb_tables to public go
| RESULT SETS | |
| sp_oledb_tables_rset_002 | |
| sp_oledb_tables_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..sysroles reads table sybsystemprocs..sysobjects reads table sybsystemprocs..sysprotects reads table sybsystemprocs..sysusers reads table master..syssrvroles (1)  read_writes table tempdb..#oledb_results_table (1) reads table master..sysdatabases (1) |
