DatabaseProcApplicationCreatedLinks
sybsystemprocssp_oledb_stored_procedures  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
4     
5     /*
6     ** Messages for "sp_oledb_stored_procedures"	18041
7     **
8     ** 18041, "Stored Procedure qualifier must be name of current database."
9     **
10    */
11    create or replace procedure sp_oledb_stored_procedures
12        @sp_name varchar(771) = null, /* stored procedure name */
13        @sp_owner varchar(32) = null, /* stored procedure owner */
14        @sp_qualifier varchar(32) = null, /* stored procedure qualifier; 
15        ** For the SQL Server, the only valid
16        ** values are NULL or the current 
17        ** database name
18        */
19        @type varchar(2) = null, /* used for ado.net2 and do nothing here */
20        @is_ado int = 1
21    
22    
23    as
24    
25    
26        if @@trancount = 0
27        begin
28            set chained off
29        end
30    
31        set transaction isolation level 1
32    
33        /* If qualifier is specified */
34        if @sp_qualifier is not null
35        begin
36            /* If qualifier doesn't match current database */
37            if db_name() != @sp_qualifier
38            begin
39                /* If qualifier is not specified */
40                if @sp_qualifier = ''
41                begin
42                    /* in this case, we need to return an empty 
43                    ** result set because the user has requested a 
44                    ** database with an empty name 
45                    */
46                    select @sp_name = ''
47                    select @sp_owner = ''
48                end
49    
50                /* qualifier is specified and does not match current database */
51                else
52                begin
53                    /* 
54                    ** 18041, "Stored Procedure qualifer must be name of
55                    ** current database"
56                    */
57                    raiserror 18041
58                    return (1)
59                end
60            end
61        end
62    
63        /* If procedure name not supplied, match all */
64        if @sp_name is null
65        begin
66            select @sp_name = '%'
67        end
68        else
69        begin
70            /* If owner name is not supplied, but procedure name is */
71            if (@sp_owner is null) and (charindex('%', @sp_name) = 0)
72            begin
73                /* If procedure exists and is owned by the current user */
74                if exists (select *
75                        from sysobjects
76                        where uid = user_id()
77                            and name = @sp_name
78                            and type = 'P') /* Object type of Procedure */
79                begin
80                    /* Set owner name to current user */
81                    select @sp_owner = user_name()
82                end
83            end
84        end
85    
86        /* If procedure owner not supplied, match all */
87        if @sp_owner is null
88            select @sp_owner = '%'
89    
90        /* 
91        ** Retrieve the stored procedures and associated info on them
92        */
93        select PROCEDURE_CATALOG = db_name(),
94            PROCEDURE_SCHEMA = user_name(o.uid),
95            PROCEDURE_NAME = o.name + ';' + ltrim(str(p.number, 5)),
96            PROCEDURE_TYPE = case when o.type = 'P' then convert(smallint, 1) when o.type = 'F' then convert(smallint, 2) end,
97            PROCEDURE_DEFINITION = convert(varchar(254), null),
98            DESCRIPTION = convert(varchar(254), null), /* Remarks are NULL */
99            DATE_CREATED = case when @is_ado = 2 then convert(datetime, o.crdate) when @is_ado = 1 then convert(datetime, null) end,
100           DATE_MODIFIED = case when @is_ado = 2 then convert(datetime, o.expdate) when @is_ado = 1 then convert(datetime, null) end
101       from sysobjects o, sysprocedures p, sysusers u
102       where o.name like @sp_name
103           and p.sequence = 0
104           and user_name(o.uid) like @sp_owner
105           and o.type in ('P', 'F') /* Object type of Procedure or Function */
106           and p.id = o.id
107           and u.uid = user_id() /* constrain sysusers uid for use in 
108           ** subquery 
109           */
110   
111           and (suser_id() = 1 /* User is the System Administrator */
112               or o.uid = user_id() /* User created the object */
113               /* here's the magic..select the highest 
114               ** precedence of permissions in the 
115               ** order (user,group,public)  
116               */
117   
118               /*
119               ** The value of protecttype is
120               **
121               **		0  for grant with grant
122               **		1  for grant and,
123               **		2  for revoke
124               **
125               ** As protecttype is of type tinyint, protecttype/2 is
126               ** integer division and will yield 0 for both types of
127               ** grants and will yield 1 for revoke, i.e., when
128               ** the value of protecttype is 2.  The XOR (^) operation
129               ** will reverse the bits and thus (protecttype/2)^1 will
130               ** yield a value of 1 for grants and will yield a
131               ** value of zero for revoke.
132               **
133               ** For groups, uid = gid. We shall use this to our advantage.
134               ** 	
135               ** If there are several entries in the sysprotects table
136               ** with the same Object ID, then the following expression
137               ** will prefer an individual uid entry over a group entry
138               **
139               ** For example, let us say there are two users u1 and u2
140               ** with uids 4 and 5 respectiveley and both u1 and u2
141               ** belong to a group g12 whose uid is 16390.  procedure p1
142               ** is owned by user u0 and user u0 performs the following
143               ** actions:
144               **
145               **		grant exec on p1 to g12
146               **		revoke grant on p1 from u1
147               **
148               ** There will be two entries in sysprotects for the object
149               ** p1, one for the group g12 where protecttype = grant (1)
150               ** and one for u1 where protecttype = revoke (2).
151               **
152               ** For the group g12, the following expression will
153               ** evaluate to:
154               **
155               **		((abs(16390-16390)*2) + ((1/2)^1))
156               **		= ((0) + (0)^1) = 0 + 1 = 1
157               **
158               ** For the user entry u1, it will evaluate to:
159               **
160               **		((abs(4-16390)*2) + ((2/2)^1))
161               **		= ((abs(-16386)*2 + (1)^1)
162               **		= 16386*2 + 0 = 32772 
163               **
164               ** As the expression evaluates to a bigger number for the
165               ** user entry u1, select max() will chose 32772 which,
166               ** ANDed with 1 gives 0, i.e., sp_oledb_stored_procedures will
167               ** not display this particular procedure to the user.
168               **
169               ** When the user u2 invokes sp_oledb_stored_procedures, there is
170               ** only one entry for u2, which is the entry for the group
171               ** g12, and so this entry will be selected thus allowing
172               ** the procedure in question to be displayed.
173               **
174               ** NOTE: With the extension of the uid's into negative space, 
175               ** and uid limits going beyond 64K, the original expression 
176               ** has been modified from
177               ** ((select max(((sign(uid)*abs(uid-16383))*2)
178               **		+ ((protecttype/2)^1))
179               ** to
180               ** ((select max((abs(uid-u.gid)*2)
181               **		+ ((protecttype/2)^1))
182               ** 
183               ** Notice that multiplying by 2 makes the number an
184               ** even number (meaning the last digit is 0) so what
185               ** matters at the end is (protecttype/2)^1.
186               **
187               */
188   
189               or ((select distinct max((abs(p.uid - u2.gid) * 2) + ((p.protecttype / 2) ^ 1))
190                   from sysprotects p, sysusers u2
191                   where p.id = o.id
192                       and u2.uid = user_id()
193                       /*
194                       ** get rows for public, current users, user's groups
195                       */
196                       and (p.uid = 0 /* get rows for public */
197                           or p.uid = user_id() /* current user */
198                           or p.uid = u2.gid) /* users group */
199   
200                       /*
201                       ** check for SELECT, EXECUTE privilege.
202                       */
203                       and (p.action in (193, 224)) /* check for SELECT,EXECUTE 
204                   ** privilege 
205                   */
206                   ) & 1 /* more magic...normalize GRANT */
207               ) = 1 /* final magic...compare Grants	*/
208               /*
209               ** If one of any user defined roles or contained roles for the
210               ** user has permission, the user has the permission
211               */
212               or exists (select 1
213                   from sysprotects p1,
214                       master.dbo.syssrvroles srvro,
215                       sysroles ro
216                   where p1.id = o.id
217                       and p1.uid = ro.lrid
218                       and ro.id = srvro.srid
219                       and has_role(srvro.name, 1) > 0
220                       and p1.action = 224))
221   
222       order by PROCEDURE_CATALOG, PROCEDURE_SCHEMA, PROCEDURE_NAME
223   


exec sp_procxmode 'sp_oledb_stored_procedures', 'AnyMode'
go

Grant Execute on sp_oledb_stored_procedures to public
go
RESULT SETS
sp_oledb_stored_procedures_rset_001

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 40
 MEST 4 Empty String will be replaced by Single Space 46
 MEST 4 Empty String will be replaced by Single Space 47
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysprotects sybsystemprocs..sysprotects
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysobjects o and [sybsystemprocs..sysusers u], 5 tables with rc=1 93
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {sequence}
103
 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]
198
 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}
220
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 220
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 106
 MAW1 3 Warning message on %name% sybsystemprocs..sysprocedures.id: Warning message on sysprocedures 106
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 191
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 191
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 216
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 216
 MAW1 3 Warning message on %name% sybsystemprocs..sysroles.id: Warning message on sysroles 218
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_oledb_stored_procedures  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysprocedures  
 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 58
 MUCO 3 Useless Code Useless Brackets 189
 QDIS 3 Check correct use of 'select distinct' 189
 QGWO 3 Group by/Distinct/Union without order by 189
 QISO 3 Set isolation level 31
 QNAJ 3 Not using ANSI Inner Join 101
 QNAJ 3 Not using ANSI Inner Join 190
 QNAJ 3 Not using ANSI Inner Join 213
 QPNC 3 No column in condition 111
 QPNC 3 No column in condition 189
 MRST 2 Result Set Marker 93
 MSUB 2 Subquery Marker 74
 MSUC 2 Correlated Subquery Marker 189
 MSUC 2 Correlated Subquery Marker 212
 MTR1 2 Metrics: Comments Ratio Comments: 63% 11
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 12 = 12dec - 2exi + 2 11
 MTR3 2 Metrics: Query Complexity Complexity: 67 11
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 93
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, u=sybsystemprocs..sysusers} 0 189
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, r=sybsystemprocs..sysroles, s=master..syssrvroles} 0 212

DEPENDENCIES
PROCS AND TABLES USED
reads table master..syssrvroles (1)  
writes table sybsystemprocs..sp_oledb_stored_procedures_rset_001 
reads table sybsystemprocs..sysprocedures  
reads table sybsystemprocs..sysprotects  
reads table sybsystemprocs..sysroles  
reads table sybsystemprocs..sysusers  
reads table sybsystemprocs..sysobjects