DatabaseProcApplicationCreatedLinks
sybsystemprocssp_oledb_views  31 Aug 14Defects Dependencies

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


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 55
 MEST 4 Empty String will be replaced by Single Space 56
 MEST 4 Empty String will be replaced by Single Space 87
 MEST 4 Empty String will be replaced by Single Space 89
 MEST 4 Empty String will be replaced by Single Space 121
 MEST 4 Empty String will be replaced by Single Space 128
 MEST 4 Empty String will be replaced by Single Space 129
 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 184
 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]
293
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype)
Intersection: {action}
318
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 318
 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 62
 MNER 3 No Error Check should check @@error after insert 95
 MNER 3 No Error Check should check @@error after insert 183
 MUCO 3 Useless Code Useless Brackets 11
 MUCO 3 Useless Code Useless Brackets 23
 MUCO 3 Useless Code Useless Brackets 27
 MUCO 3 Useless Code Useless Brackets 34
 MUCO 3 Useless Code Useless Brackets 282
 MUCO 3 Useless Code Useless Brackets 325
 MUCO 3 Useless Code Useless Brackets 328
 QISO 3 Set isolation level 22
 QNAJ 3 Not using ANSI Inner Join 194
 QNAJ 3 Not using ANSI Inner Join 283
 QNAJ 3 Not using ANSI Inner Join 311
 QPNC 3 No column in condition 210
 QPNC 3 No column in condition 282
 VUNU 3 Variable is not used @tableindex 9
 MRST 2 Result Set Marker 322
 MSUB 2 Subquery Marker 164
 MSUC 2 Correlated Subquery Marker 282
 MSUC 2 Correlated Subquery Marker 310
 MTR1 2 Metrics: Comments Ratio Comments: 51% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 21 = 22dec - 3exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 99 2
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, u=sybsystemprocs..sysusers} 0 282
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, r=sybsystemprocs..sysroles, s=master..syssrvroles} 0 310

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