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