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