DatabaseProcApplicationCreatedLinks
sybsystemprocssp_oledb_views  14 déc. 14Defects Propagation Dependencies

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


exec sp_procxmode 'sp_oledb_views', 'AnyMode'
go

Grant Execute on sp_oledb_views to public
go
RESULT SETS
sp_oledb_views_rset_001

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 58
 MEST 4 Empty String will be replaced by Single Space 59
 MEST 4 Empty String will be replaced by Single Space 90
 MEST 4 Empty String will be replaced by Single Space 92
 MEST 4 Empty String will be replaced by Single Space 124
 MEST 4 Empty String will be replaced by Single Space 131
 MEST 4 Empty String will be replaced by Single Space 132
 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 187
 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]
296
 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}
321
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 321
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 169
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 200
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 287
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 287
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 317
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 317
 MAW1 3 Warning message on %name% sybsystemprocs..sysroles.id: Warning message on sysroles 319
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_oledb_views  
 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 65
 MNER 3 No Error Check should check @@error after insert 98
 MNER 3 No Error Check should check @@error after insert 186
 MUCO 3 Useless Code Useless Brackets 14
 MUCO 3 Useless Code Useless Brackets 26
 MUCO 3 Useless Code Useless Brackets 30
 MUCO 3 Useless Code Useless Brackets 37
 MUCO 3 Useless Code Useless Brackets 285
 MUCO 3 Useless Code Useless Brackets 328
 MUCO 3 Useless Code Useless Brackets 331
 QISO 3 Set isolation level 25
 QNAJ 3 Not using ANSI Inner Join 197
 QNAJ 3 Not using ANSI Inner Join 286
 QNAJ 3 Not using ANSI Inner Join 314
 QPNC 3 No column in condition 213
 QPNC 3 No column in condition 285
 VUNU 3 Variable is not used @tableindex 12
 MRST 2 Result Set Marker 325
 MSUB 2 Subquery Marker 167
 MSUC 2 Correlated Subquery Marker 285
 MSUC 2 Correlated Subquery Marker 313
 MTR1 2 Metrics: Comments Ratio Comments: 50% 5
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 22 = 23dec - 3exi + 2 5
 MTR3 2 Metrics: Query Complexity Complexity: 99 5
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, u=sybsystemprocs..sysusers} 0 285
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, r=sybsystemprocs..sysroles, s=master..syssrvroles} 0 313

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