DatabaseProcApplicationCreatedLinks
sybsystemprocssp_oledb_statistics  31 Aug 14Defects 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_statistics"         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 procedure sp_oledb_statistics
15        @table_catalog varchar(32) = null,
16        @table_schema varchar(32) = null,
17        @table_name varchar(255) = null
18    
19    as
20    
21        declare @type1 varchar(3)
22        declare @tname varchar(255)
23        declare @startedInTransaction bit
24    
25        if (@@trancount > 0)
26            select @startedInTransaction = 1
27        else
28            select @startedInTransaction = 0
29    
30        if @@trancount = 0
31        begin
32            set chained off
33        end
34    
35        set transaction isolation level 1
36    
37        if (@startedInTransaction = 1)
38            save transaction oledb_keep_temptable_tx
39    
40        /* temp table */
41        if (@table_name like "#%" and
42                db_name() != db_name(tempdb_id()))
43        begin
44            /*
45            ** Can return data about temp. tables only in tempdb
46            */
47            raiserror 17676
48            return (1)
49        end
50    
51        create table #oledb_results_table
52        (
53    
54            TABLE_CATALOG varchar(32) null,
55            TABLE_SCHEMA varchar(32) null,
56            TABLE_NAME varchar(255) null,
57            CARDINALITY int null
58        )
59    
60    
61    
62        /*
63        ** Special feature #1:	enumerate databases when owner and name
64        ** are blank but catalog is explicitly '%'.  
65        */
66        if @table_catalog = '%' and
67            @table_schema = '' and
68            @table_name = ''
69        begin
70    
71            /*
72            ** If enumerating databases 
73            */
74            insert #oledb_results_table
75            select
76                TABLE_CATALOG = name,
77                TABLE_SCHEMA = null,
78                TABLE_NAME = null,
79                CARDINALITY = null
80            from master..sysdatabases
81    
82            /*
83            ** eliminate MODEL database 
84            */
85            where name != 'model'
86            order by TABLE_CATALOG
87        end
88    
89        /*
90        ** Special feature #2:	enumerate owners when qualifier and name
91        ** are blank but owner is explicitly '%'.
92        */
93        else if @table_catalog = '' and
94            @table_schema = '%' and
95            @table_name = ''
96        begin
97    
98            /*
99            ** If enumerating owners 
100           */
101           insert #oledb_results_table
102           select distinct
103               TABLE_CATALOG = null,
104               TABLE_SCHEMA = user_name(uid),
105               TABLE_NAME = null,
106               CARDINALITY = null
107           from sysobjects
108           order by TABLE_CATALOG
109       end
110       else
111       begin
112   
113           /*
114           ** end of special features -- do normal processing 
115           */
116           if @table_catalog is not null
117   
118           begin
119               if db_name() != @table_catalog
120               begin
121                   if @table_catalog = ''
122                   begin
123   
124                       /*
125                       ** If empty qualifier supplied
126                       ** Force an empty result set by 
127                       ** going directly to select
128                       */
129                       goto SelectClause
130                   end
131               end
132           end
133   
134           select @type1 = 'SUV'
135   
136   
137           if @table_name is null
138   
139           begin
140   
141               /*
142               ** If table name not supplied, match all 
143               */
144               select @table_name = '%'
145           end
146           else
147           begin
148               if (@table_schema is null) and
149                   (charindex('%', @table_name) = 0)
150               begin
151   
152                   /*
153                   ** If owner not specified and table is specified 
154                   */
155                   if exists (select * from sysobjects
156                           where uid = user_id()
157                               and id = object_id(@table_name)
158                               and (type = 'U' or type = 'V'
159                                   or type = 'S'))
160                   begin
161   
162                       /*
163                       ** Override supplied owner w/owner of table 
164                       */
165                       select @table_schema = user_name()
166                   end
167               end
168           end
169   
170           /*
171           ** If no owner supplied, force wildcard 
172           */
173           if @table_schema is null
174               select @table_schema = '%'
175           /*
176           ** If no catalog supplied, force wildcard 
177           */
178           if @table_catalog is null
179               select @table_catalog = '%'
180   
181   
182           insert #oledb_results_table
183           select
184               TABLE_CATALOG = db_name(),
185               TABLE_SCHEMA = user_name(o.uid),
186               TABLE_NAME = o.name,
187               --		CARDINALITY = rowcnt(x.doampg)
188               CARDINALITY = row_count(db_id(), x.id)
189   
190           from sysusers u, sysobjects o, sysindexes x
191           where
192               /* Special case for temp. tables.  Match ids */
193               (o.name like @table_name or o.id = object_id(@table_name))
194               and user_name(o.uid) like @table_schema
195   
196               /*
197               ** Only desired types
198               */
199               and charindex(substring(o.type, 1, 1), @type1) != 0
200               and o.name = x.name
201   
202               /*
203               ** constrain sysusers uid for use in subquery 
204               */
205               and u.uid = user_id()
206               and (
207                   suser_id() = 1 /* User is the System Administrator */
208                   or o.uid = user_id() /* User created the object */
209                   /* here's the magic..select the highest
210                   ** precedence of permissions in the
211                   ** order (user,group,public)
212                   */
213   
214                   /*
215                   ** The value of protecttype is
216                   **
217                   **      0  for grant with grant
218                   **      1  for grant and,
219                   **      2  for revoke
220                   **
221                   ** As protecttype is of type tinyint, protecttype/2 is
222                   ** integer division and will yield 0 for both types of
223                   ** grants and will yield 1 for revoke, i.e., when
224                   ** the value of protecttype is 2.  The XOR (^) operation
225                   ** will reverse the bits and thus (protecttype/2)^1 will
226                   ** yield a value of 1 for grants and will yield a
227                   ** value of zero for revoke.
228                   **
229                   ** For groups, uid = gid. We shall use this to our advantage.
230                   **
231                   ** If there are several entries in the sysprotects table
232                   ** with the same Object ID, then the following expression
233                   ** will prefer an individual uid entry over a group entry
234                   **
235                   ** For example, let us say there are two users u1 and u2
236                   ** with uids 4 and 5 respectiveley and both u1 and u2
237                   ** belong to a group g12 whose uid is 16390.  table t1
238                   ** is owned by user u0 and user u0 performs the following
239                   ** actions:
240                   **
241                   **      grant select on t1 to g12
242                   **      revoke select on t1 from u1
243                   **
244                   ** There will be two entries in sysprotects for the object t1,
245                   ** one for the group g12 where protecttype = grant (1) and
246                   ** one for u1 where protecttype = revoke (2).
247                   **
248                   ** For the group g12, the following expression will
249                   ** evaluate to:
250                   **
251                   **      ((abs(16390-16390)*2) + ((1/2)^1)
252                   **      = ((0) + (0)^1) = 0 + 1 = 1
253                   **
254                   ** For the user entry u1, it will evaluate to:
255                   **
256                   **      (((+)*abs(4-16390)*2) + ((2/2)^1))
257                   **      = (abs(-16386)*2 + (1)^1)
258                   **      = 16386*2 + 0 = 32772
259                   **
260                   ** As the expression evaluates to a bigger number for the
261                   ** user entry u1, select max() will chose 32772 which,
262                   ** ANDed with 1 gives 0, i.e., sp_oledb_statistics will not display
263                   ** this particular table to the user.
264                   **
265                   ** When the user u2 invokes sp_oledb_statistics, there is only one
266                   ** entry for u2, which is the entry for the group g12, and
267                   ** so the group entry will be selected thus allowing the
268                   ** table t1 to be displayed.
269                   **
270                   ** ((select max((abs(uid-u.gid)*2)
271                   ** 		+ ((protecttype/2)^1))
272                   **
273                   ** Notice that multiplying by 2 makes the number an
274                   ** even number (meaning the last digit is 0) so what
275                   ** matters at the end is (protecttype/2)^1.
276                   **
277                   **/
278   
279                   or ((select max((abs(p.uid - u2.gid) * 2) + ((p.protecttype / 2) ^ 1))
280                       from sysprotects p, sysusers u2
281                       where p.id = o.id /* outer join to correlate
282                           ** with all rows in sysobjects
283                           */
284                           and u2.uid = user_id()
285                           /*
286                           ** get rows for public, current users, user's groups
287                           */
288                           and (p.uid = 0 or /* public */
289                               p.uid = user_id() or /* current user */
290                               p.uid = u2.gid) /* users group */
291   
292                           /*
293                           ** check for SELECT, EXECUTE privilege.
294                           */
295                           and (p.action in (193, 224))) & 1
296   
297                   /*
298                   ** more magic...normalise GRANT
299                   ** and final magic...compare
300                   ** Grants.
301                   */
302                   ) = 1)
303           order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
304   
305       end
306   
307   SelectClause: /* Adaptive Server has expanded all '*' elements in the following statement */
308       select #oledb_results_table.TABLE_CATALOG, #oledb_results_table.TABLE_SCHEMA, #oledb_results_table.TABLE_NAME, #oledb_results_table.CARDINALITY from #oledb_results_table where TABLE_CATALOG like @table_catalog
309           AND TABLE_SCHEMA like @table_schema
310           AND TABLE_NAME like @table_name order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
311   
312   
313       if (@startedInTransaction = 1)
314           rollback transaction oledb_keep_temptable_tx
315   
316       return (0)
317   


exec sp_procxmode 'sp_oledb_statistics', 'AnyMode'
go

Grant Execute on sp_oledb_statistics to public
go
RESULT SETS
sp_oledb_statistics_rset_001

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 67
 MEST 4 Empty String will be replaced by Single Space 68
 MEST 4 Empty String will be replaced by Single Space 93
 MEST 4 Empty String will be replaced by Single Space 95
 MEST 4 Empty String will be replaced by Single Space 121
 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..sysindexes x, sybsystemprocs..sysobje... 183
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype)
Intersection: {uid}
Uncovered: [id, grantor, protecttype]
290
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_oledb_statistics  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysprotects  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check @@error after insert 74
 MNER 3 No Error Check should check @@error after insert 101
 MNER 3 No Error Check should check @@error after insert 182
 MUCO 3 Useless Code Useless Brackets 25
 MUCO 3 Useless Code Useless Brackets 37
 MUCO 3 Useless Code Useless Brackets 41
 MUCO 3 Useless Code Useless Brackets 48
 MUCO 3 Useless Code Useless Brackets 279
 MUCO 3 Useless Code Useless Brackets 313
 MUCO 3 Useless Code Useless Brackets 316
 QISO 3 Set isolation level 35
 QNAJ 3 Not using ANSI Inner Join 190
 QNAJ 3 Not using ANSI Inner Join 280
 QPNC 3 No column in condition 207
 QPNC 3 No column in condition 279
 VUNU 3 Variable is not used @tname 22
 MRST 2 Result Set Marker 308
 MSUB 2 Subquery Marker 155
 MSUC 2 Correlated Subquery Marker 279
 MTR1 2 Metrics: Comments Ratio Comments: 58% 14
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 25 = 25dec - 2exi + 2 14
 MTR3 2 Metrics: Query Complexity Complexity: 98 14
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 183
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, u=sybsystemprocs..sysusers} 0 279

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..sysindexes  
reads table sybsystemprocs..sysprotects  
read_writes table tempdb..#oledb_results_table (1) 
reads table sybsystemprocs..sysusers  
reads table master..sysdatabases (1)