DatabaseProcApplicationCreatedLinks
sybsystemprocssp_odbc_stored_procedures  14 déc. 14Defects Propagation Dependencies

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


exec sp_procxmode 'sp_odbc_stored_procedures', 'AnyMode'
go

Grant Execute on sp_odbc_stored_procedures to public
go
RESULT SETS
sp_odbc_stored_procedures_rset_001

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 37
 MEST 4 Empty String will be replaced by Single Space 43
 MEST 4 Empty String will be replaced by Single Space 44
 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 90
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {sequence}
100
 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]
195
 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}
217
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 217
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 103
 MAW1 3 Warning message on %name% sybsystemprocs..sysprocedures.id: Warning message on sysprocedures 103
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 188
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 188
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 213
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 213
 MAW1 3 Warning message on %name% sybsystemprocs..sysroles.id: Warning message on sysroles 215
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_odbc_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 55
 MUCO 3 Useless Code Useless Brackets 186
 QDIS 3 Check correct use of 'select distinct' 186
 QGWO 3 Group by/Distinct/Union without order by 186
 QISO 3 Set isolation level 28
 QNAJ 3 Not using ANSI Inner Join 98
 QNAJ 3 Not using ANSI Inner Join 187
 QNAJ 3 Not using ANSI Inner Join 210
 QPNC 3 No column in condition 108
 QPNC 3 No column in condition 186
 MRST 2 Result Set Marker 90
 MSUB 2 Subquery Marker 71
 MSUC 2 Correlated Subquery Marker 186
 MSUC 2 Correlated Subquery Marker 209
 MTR1 2 Metrics: Comments Ratio Comments: 66% 12
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 12 = 12dec - 2exi + 2 12
 MTR3 2 Metrics: Query Complexity Complexity: 63 12
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 90
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, u=sybsystemprocs..sysusers} 0 186
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects, r=sybsystemprocs..sysroles, s=master..syssrvroles} 0 209

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