DatabaseProcApplicationCreatedLinks
sybsystemprocssp_stored_procedures  14 déc. 14Defects Propagation Dependencies

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


exec sp_procxmode 'sp_stored_procedures', 'AnyMode'
go

Grant Execute on sp_stored_procedures to public
go
RESULT SETS
sp_stored_procedures_rset_001

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 42
 MEST 4 Empty String will be replaced by Single Space 48
 MEST 4 Empty String will be replaced by Single Space 49
 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 110
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {sequence}
119
 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]
214
 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}
236
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 236
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 122
 MAW1 3 Warning message on %name% sybsystemprocs..sysprocedures.id: Warning message on sysprocedures 122
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 207
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 207
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 232
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 232
 MAW1 3 Warning message on %name% sybsystemprocs..sysroles.id: Warning message on sysroles 234
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_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 60
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 205
 QISO 3 Set isolation level 33
 QNAJ 3 Not using ANSI Inner Join 117
 QNAJ 3 Not using ANSI Inner Join 206
 QNAJ 3 Not using ANSI Inner Join 229
 QPNC 3 No column in condition 127
 QPNC 3 No column in condition 205
 VNRD 3 Variable is not read @gp_enabled 101
 VUNU 3 Variable is not used @dbname 24
 MRST 2 Result Set Marker 110
 MSUB 2 Subquery Marker 76
 MSUC 2 Correlated Subquery Marker 205
 MSUC 2 Correlated Subquery Marker 228
 MTR1 2 Metrics: Comments Ratio Comments: 67% 10
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 13 = 13dec - 2exi + 2 10
 MTR3 2 Metrics: Query Complexity Complexity: 72 10
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 110
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, u=sybsystemprocs..sysusers} 0 205
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, r=sybsystemprocs..sysroles, s=master..syssrvroles} 0 228

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
reads table sybsystemprocs..sysroles  
reads table sybsystemprocs..sysprocedures  
writes table sybsystemprocs..sp_stored_procedures_rset_001 
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..sysusers  
reads table sybsystemprocs..sysprotects  
reads table master..syssrvroles (1)