DatabaseProcApplicationCreatedLinks
sybsystemprocssp_odbc_tables  14 déc. 14Defects Propagation Dependencies

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


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

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 50
 MEST 4 Empty String will be replaced by Single Space 51
 MEST 4 Empty String will be replaced by Single Space 81
 MEST 4 Empty String will be replaced by Single Space 83
 MEST 4 Empty String will be replaced by Single Space 112
 MEST 4 Empty String will be replaced by Single Space 119
 MEST 4 Empty String will be replaced by Single Space 120
 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]
315
 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}
340
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 340
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 186
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 224
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 306
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 306
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 336
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 336
 MAW1 3 Warning message on %name% sybsystemprocs..sysroles.id: Warning message on sysroles 338
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_odbc_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 35
 MUCO 3 Useless Code Useless Brackets 42
 MUCO 3 Useless Code Useless Brackets 152
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 164
 MUCO 3 Useless Code Useless Brackets 304
 QCRS 3 Conditional Result Set 57
 QCRS 3 Conditional Result Set 89
 QCRS 3 Conditional Result Set 205
 QISO 3 Set isolation level 32
 QNAJ 3 Not using ANSI Inner Join 305
 QNAJ 3 Not using ANSI Inner Join 333
 QPNC 3 No column in condition 232
 QPNC 3 No column in condition 304
 VUNU 3 Variable is not used @tableindex 24
 MRST 2 Result Set Marker 57
 MRST 2 Result Set Marker 89
 MRST 2 Result Set Marker 205
 MSUB 2 Subquery Marker 184
 MSUC 2 Correlated Subquery Marker 304
 MSUC 2 Correlated Subquery Marker 332
 MTR1 2 Metrics: Comments Ratio Comments: 59% 16
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 28 = 29dec - 3exi + 2 16
 MTR3 2 Metrics: Query Complexity Complexity: 95 16
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, u=sybsystemprocs..sysusers} 0 304
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, r=sybsystemprocs..sysroles, s=master..syssrvroles} 0 332

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysroles  
reads table sybsystemprocs..sysprotects  
reads table sybsystemprocs..sysusers  
reads table sybsystemprocs..sysobjects  
writes table sybsystemprocs..sp_odbc_tables_rset_001 
writes table sybsystemprocs..sp_odbc_tables_rset_002 
writes table sybsystemprocs..sp_odbc_tables_rset_003 
reads table master..sysdatabases (1)  
reads table master..syssrvroles (1)