DatabaseProcApplicationCreatedLinks
sybsystemprocssp_ijdbc_stored_procedures  14 déc. 14Defects Propagation Dependencies

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


exec sp_procxmode 'sp_ijdbc_stored_procedures', 'AnyMode'
go

Grant Execute on sp_ijdbc_stored_procedures to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 50
 MEST 4 Empty String will be replaced by Single Space 56
 MEST 4 Empty String will be replaced by Single Space 57
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysprotects sybsystemprocs..sysprotects
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {sequence}
99
 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]
190
 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}
212
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 212
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 102
 MAW1 3 Warning message on %name% sybsystemprocs..sysprocedures.id: Warning message on sysprocedures 102
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 182
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 182
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 208
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 208
 MAW1 3 Warning message on %name% sybsystemprocs..sysroles.id: Warning message on sysroles 210
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_ijdbc_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  
 MNER 3 No Error Check should check @@error after delete 41
 MNER 3 No Error Check should check return value of exec 67
 MNER 3 No Error Check should check @@error after insert 88
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 179
 MUCO 3 Useless Code Useless Brackets 180
 QISO 3 Set isolation level 39
 QNAJ 3 Not using ANSI Inner Join 97
 QNAJ 3 Not using ANSI Inner Join 205
 QPNC 3 No column in condition 107
 QPNC 3 No column in condition 179
 MSUC 2 Correlated Subquery Marker 179
 MSUC 2 Correlated Subquery Marker 204
 MTR1 2 Metrics: Comments Ratio Comments: 68% 22
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 7 = 7dec - 2exi + 2 22
 MTR3 2 Metrics: Query Complexity Complexity: 54 22
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 89
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, u=sybsystemprocs..sysusers} 0 179
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, r=sybsystemprocs..sysroles, s=master..syssrvroles} 0 204

DEPENDENCIES
PROCS AND TABLES USED
reads table master..syssrvroles (1)  
reads table sybsystemprocs..sysprotects  
writes table tempdb..#tmp_stored_procedures (1) 
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..sysprocedures  
reads table sybsystemprocs..sysusers  
reads table sybsystemprocs..sysroles