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


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

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 55
 MEST 4 Empty String will be replaced by Single Space 56
 MEST 4 Empty String will be replaced by Single Space 85
 MEST 4 Empty String will be replaced by Single Space 87
 MEST 4 Empty String will be replaced by Single Space 116
 MEST 4 Empty String will be replaced by Single Space 123
 MEST 4 Empty String will be replaced by Single Space 124
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysprotects sybsystemprocs..sysprotects
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype, predid)
Intersection: {uid}
Uncovered: [id, grantor, protecttype, predid]
336
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype, predid)
Intersection: {action}
361
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 361
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 190
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 245
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 327
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 327
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 357
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 357
 MAW1 3 Warning message on %name% sybsystemprocs..sysroles.id: Warning message on sysroles 359
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_tables  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysprotects  
 MGTP 3 Grant to public sybsystemprocs..sysroles  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MUCO 3 Useless Code Useless Brackets 40
 MUCO 3 Useless Code Useless Brackets 47
 MUCO 3 Useless Code Useless Brackets 156
 MUCO 3 Useless Code Useless Brackets 162
 MUCO 3 Useless Code Useless Brackets 168
 MUCO 3 Useless Code Useless Brackets 222
 MUCO 3 Useless Code Useless Brackets 325
 QCRS 3 Conditional Result Set 62
 QCRS 3 Conditional Result Set 93
 QCRS 3 Conditional Result Set 227
 QISO 3 Set isolation level 37
 QNAJ 3 Not using ANSI Inner Join 326
 QNAJ 3 Not using ANSI Inner Join 354
 QPNC 3 No column in condition 253
 QPNC 3 No column in condition 325
 VNRD 3 Variable is not read @dbname 217
 VNRD 3 Variable is not read @gp_enabled 220
 VUNU 3 Variable is not used @tableindex 22
 VUNU 3 Variable is not used @dummy 26
 MRST 2 Result Set Marker 62
 MRST 2 Result Set Marker 93
 MRST 2 Result Set Marker 227
 MSUB 2 Subquery Marker 188
 MSUC 2 Correlated Subquery Marker 325
 MSUC 2 Correlated Subquery Marker 353
 MTR1 2 Metrics: Comments Ratio Comments: 59% 14
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 26 = 27dec - 3exi + 2 14
 MTR3 2 Metrics: Query Complexity Complexity: 105 14
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, u=sybsystemprocs..sysusers} 0 325
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, r=sybsystemprocs..sysroles, s=master..syssrvroles} 0 353

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..sysusers  
reads table master..syssrvroles (1)  
writes table sybsystemprocs..sp_tables_rset_003 
writes table sybsystemprocs..sp_tables_rset_001 
reads table sybsystemprocs..sysroles  
reads table sybsystemprocs..sysprotects  
writes table sybsystemprocs..sp_tables_rset_002 
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
reads table master..sysdatabases (1)