DatabaseProcApplicationCreatedLinks
sybsystemprocssp_oledb_tables  14 déc. 14Defects Propagation 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 or replace 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 = '' or @table_schema is null) and
72            (@table_name = '' or @table_name is null)
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 = '' or @table_name is null)
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

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 71
 MEST 4 Empty String will be replaced by Single Space 72
 MEST 4 Empty String will be replaced by Single Space 104
 MEST 4 Empty String will be replaced by Single Space 106
 MEST 4 Empty String will be replaced by Single Space 141
 MEST 4 Empty String will be replaced by Single Space 148
 MEST 4 Empty String will be replaced by Single Space 149
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysprotects sybsystemprocs..sysprotects
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysusers u and [sybsystemprocs..sysobjects o], 6 tables with rc=1 237
 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]
354
 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}
379
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 379
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 218
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 258
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 345
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 345
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 375
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 375
 MAW1 3 Warning message on %name% sybsystemprocs..sysroles.id: Warning message on sysroles 377
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_oledb_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  
 MNER 3 No Error Check should check @@error after insert 78
 MNER 3 No Error Check should check @@error after insert 112
 MNER 3 No Error Check should check @@error after insert 236
 MUCO 3 Useless Code Useless Brackets 24
 MUCO 3 Useless Code Useless Brackets 37
 MUCO 3 Useless Code Useless Brackets 42
 MUCO 3 Useless Code Useless Brackets 49
 MUCO 3 Useless Code Useless Brackets 183
 MUCO 3 Useless Code Useless Brackets 189
 MUCO 3 Useless Code Useless Brackets 195
 MUCO 3 Useless Code Useless Brackets 343
 MUCO 3 Useless Code Useless Brackets 383
 MUCO 3 Useless Code Useless Brackets 389
 MUCO 3 Useless Code Useless Brackets 392
 QCRS 3 Conditional Result Set 384
 QCRS 3 Conditional Result Set 387
 QISO 3 Set isolation level 35
 QNAJ 3 Not using ANSI Inner Join 255
 QNAJ 3 Not using ANSI Inner Join 344
 QNAJ 3 Not using ANSI Inner Join 372
 QNAM 3 Select expression has no name null 384
 QPNC 3 No column in condition 271
 QPNC 3 No column in condition 343
 VUNU 3 Variable is not used @tableindex 22
 MRST 2 Result Set Marker 384
 MRST 2 Result Set Marker 387
 MSUB 2 Subquery Marker 216
 MSUC 2 Correlated Subquery Marker 343
 MSUC 2 Correlated Subquery Marker 371
 MTR1 2 Metrics: Comments Ratio Comments: 48% 14
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 32 = 33dec - 3exi + 2 14
 MTR3 2 Metrics: Query Complexity Complexity: 120 14
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, u=sybsystemprocs..sysusers} 0 343
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, r=sybsystemprocs..sysroles, s=master..syssrvroles} 0 371

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sp_oledb_tables_rset_002 
writes table sybsystemprocs..sp_oledb_tables_rset_001 
read_writes table tempdb..#oledb_results_table (1) 
reads table sybsystemprocs..sysusers  
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..sysprotects  
reads table sybsystemprocs..sysroles  
reads table master..syssrvroles (1)  
reads table master..sysdatabases (1)