DatabaseProcApplicationCreatedLinks
sybsystemprocssp_setpsexe  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G% */
3     
4     /*
5     ** Messages for "sp_setpsexe"
6     ** 17260, "Can't run %1! from within a transaction."
7     ** 18249, "Name '%1!' is not valid."
8     ** 18262, "A non-SA user can not modify attributes of another task."
9     ** 18274, "A non-SA user can only lower its own priority value."
10    ** 18264, "No SQL Server process with the specified ID exists."
11    ** 18266, "'%1!' is not a valid execution attribute"
12    ** 18252, "'%1!' value '%2!' is not valid."
13    ** 18275, "Failed to set attribute '%1!' to '%2!' for spid '%3!'. Check
14    **	   server errorlog for any additional information."
15    ** 18367, "The specified thread pool does not exist."
16    */
17    
18    create or replace procedure sp_setpsexe
19        @spid int, /* id of SQL Server process */
20        @exeattr varchar(12), /* name of execution attribute */
21        @value varchar(30) /* value assigned to attribute */
22    as
23    
24        declare @attrib_id int,
25            @user_id int,
26            @action int,
27            @my_suid int,
28            @object_type varchar(2),
29            @dummy int,
30            @curvalue varchar(30),
31            @oldvalue varchar(255),
32            @loc int,
33            @role int,
34            @upcase_str varchar(30),
35            @kernel_mode varchar(30)
36    
37        declare @nullarg char(1)
38        declare @status int
39        declare @gp_enabled int
40    
41        /*
42        **  IF we're in a transaction, disallow this since it might make recovery
43        **  impossible.
44        */
45        IF @@trancount > 0
46        BEGIN
47            /*
48            ** 17260, "Can't run %1! from within a transaction."
49            */
50            raiserror 17260, "sp_setpsexe"
51            return (1)
52        END
53        ELSE
54        BEGIN
55            /* Use TSQL mode of unchained transactions */
56            set chained off
57        END
58    
59        /* Dont do "Dirty Reads" */
60        set transaction isolation level 1
61    
62        select @kernel_mode = @@kernelmode
63        select @action = 1 /* add operation */
64        select @user_id = NULL /* Id of user from Syslogins table */
65        select @dummy = 0
66        select @oldvalue = NULL
67    
68        /* convert system defined values to upper case */
69        select @upcase_str = upper(@value)
70        IF ((@upcase_str = "HIGH") OR (@upcase_str = "MEDIUM") OR
71                (@upcase_str = "LOW") OR (@upcase_str = "ANYENGINE") OR
72                (@upcase_str = "LASTONLINE"))
73            select @value = @upcase_str
74    
75    
76        /*
77        ** If granular permissions is not enabled then sa_role is required.
78        ** If granular permissions is enabled then the permission 
79        ** 'manage any execution class' is required.  proc_role and proc_auditperm 
80        ** will also do auditing if required. Both will also print error message 
81        ** if required.
82        */
83    
84        select @nullarg = NULL
85        execute @status = sp_aux_checkroleperm "sa_role", "manage any execution class",
86            @nullarg, @gp_enabled output
87    
88        /* check that user has sa role or modifying self */
89        IF (@status != 0)
90        BEGIN
91            select @my_suid = (select suid from master..sysprocesses
92                    where spid = @spid)
93            select @curvalue = (select priority from master..sysprocesses
94                    where spid = @spid)
95            IF (@my_suid != suser_id())
96            BEGIN
97                /*
98                ** 18262, "A non-SA user can not modify attributes of another
99                **	  process."
100               */
101               raiserror 18262
102               return (1)
103           END
104           ELSE IF @exeattr != "priority"
105           BEGIN
106               /*
107               ** 18263, "A non-SA user can only modify its priority value"
108               */
109               raiserror 18263
110               return (1)
111           END
112           ELSE IF (@curvalue != "HIGH")
113           BEGIN
114               IF (((@curvalue = "MEDIUM") AND (@value = "HIGH")) OR
115                       ((@curvalue = "LOW") AND (@value = "HIGH")) OR
116                       ((@curvalue = "LOW") AND (@value = "MEDIUM")))
117               BEGIN
118                   /*
119                   ** 18274, "A non-SA user can only lower its priority 
120                   ** value"
121                   */
122                   raiserror 18274
123                   return (1)
124               END
125           END
126       END
127       ELSE
128       BEGIN
129           /* For Auditing */
130           IF (@gp_enabled = 0)
131           BEGIN
132               /* validate that this guy is really the sa */
133               if (proc_role("sa_role") = 0)
134                   return (1)
135           END
136           ELSE
137           BEGIN
138               select @dummy = proc_auditperm("manage any execution class",
139                       @status)
140           END
141       END
142   
143       /*
144       ** Check to see that the input params are correct
145       */
146       /* Check that process exists */
147       IF @spid not in (select spid from master..sysprocesses)
148       BEGIN
149           /*
150           ** 18264,"No SQL Server process with specified id exists."
151           */
152           raiserror 18264
153           return (1)
154       END
155   
156       /* check that @exeattr value specified is valid */
157       IF ((@exeattr != 'priority')
158               AND (@exeattr != 'timeslice')
159               AND (((@kernel_mode = 'process') AND (@exeattr != 'enginegroup'))
160                   OR ((@kernel_mode = 'threaded') AND (@exeattr != 'threadpool'))))
161       BEGIN
162           /*
163           ** 18266, "'%1!' is not a valid execution attribute"
164           */
165           raiserror 18266, @exeattr
166           return (1)
167       END
168   
169       /* check the priority value is correct */
170       IF (@exeattr = "priority")
171       BEGIN
172           IF ((@value != "HIGH") AND
173                   (@value != "MEDIUM") AND
174                   (@value != "LOW"))
175           BEGIN
176               /*
177               ** 18252, "'%1!' value '%2!' is not valid."
178               */
179               raiserror 18252, @exeattr, @value
180               return (1)
181           END
182       END
183   
184       /* check that enginegroup value is ok if in process mode */
185       IF ((@kernel_mode = 'process') AND (@exeattr = "enginegroup") AND
186               (@value != "ANYENGINE") AND (@value != "LASTONLINE"))
187       BEGIN
188           IF not exists (select * from master..sysattributes where
189                       class = 6 AND
190                       attribute = 1 AND
191                       object_type = 'EG' AND
192                       object_cinfo = @value)
193   
194           BEGIN
195               /*
196               ** 18252, "'%1!' value '%2!' is not valid."
197               */
198               raiserror 18252, @exeattr, @value
199               return (1)
200           END
201       END
202       /* For THREADED kernel mode, check for valid threadpool */
203       ELSE IF ((@kernel_mode != "process") AND (@exeattr = "threadpool"))
204       BEGIN
205           IF valid_name(@value, 30) = 0
206           BEGIN
207               /*
208               ** 18249, "Name '%1!' is not valid."
209               */
210               raiserror 18249, @value
211               return (1)
212           END
213       END
214   
215   
216   
217       /*
218       ** Now hook up with Sysattributes Table...
219       */
220   
221       select @attrib_id = 3
222       select @object_type = "PS"
223       BEGIN
224           select @oldvalue = (select char_value from master..sysattributes where
225                       class = 6 AND
226                       attribute = @attrib_id AND
227                       object_type = @object_type AND
228                       object_info1 = @spid AND
229                       object_cinfo = @exeattr)
230           IF (@oldvalue is not NULL)
231           BEGIN
232               update master..sysattributes
233               set char_value = @value
234               where class = 6 AND
235                   attribute = @attrib_id AND
236                   object_type = @object_type AND
237                   object_info1 = @spid AND
238                   object_cinfo = @exeattr
239           END
240           ELSE
241           BEGIN
242               insert master..sysattributes
243               (class, attribute, object_type, object_info1,
244                   object_cinfo, char_value)
245               values (6, @attrib_id, @object_type, @spid,
246                   @exeattr, @value)
247           END
248   
249           IF attrib_notify(6, @attrib_id, @object_type, NULL, @spid,
250                   NULL, NULL, @exeattr, NULL, @value,
251                   NULL, NULL, "", @action) = 0
252           BEGIN
253               /*
254               ** 18275, "Failed to set attribute '%1!' to '%2!' for spid
255               **	   '%3!'. Check server errorlog for any additional
256               **	   information."
257               */
258               IF (@oldvalue is not NULL)
259                   update master..sysattributes
260                   set char_value = @oldvalue
261                   where class = 6 AND
262                       attribute = @attrib_id AND
263                       object_type = @object_type AND
264                       object_info1 = @spid AND
265                       object_cinfo = @exeattr
266               ELSE
267                   delete master..sysattributes
268                   where class = 6 AND
269                       attribute = @attrib_id AND
270                       object_type = @object_type AND
271                       object_info1 = @spid AND
272                       object_cinfo = @exeattr
273   
274               raiserror 18275, @exeattr, @value, @spid
275               return (1)
276           END
277       END
278   
279       return (0)
280   


exec sp_procxmode 'sp_setpsexe', 'AnyMode'
go

Grant Execute on sp_setpsexe to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 251
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MTYP 4 Assignment type mismatch @kernel_mode: varchar(30) = int 62
 MTYP 4 Assignment type mismatch attribute: smallint = int 245
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 92
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 94
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 189
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 190
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 225
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 226
 QTYP 4 Comparison type mismatch smallint = int 226
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 234
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 235
 QTYP 4 Comparison type mismatch smallint = int 235
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 261
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 262
 QTYP 4 Comparison type mismatch smallint = int 262
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 268
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 269
 QTYP 4 Comparison type mismatch smallint = int 269
 TNOI 4 Table with no index master..sysprocesses master..sysprocesses
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysprocesses  
 MGTP 3 Grant to public sybsystemprocs..sp_setpsexe  
 MNER 3 No Error Check should check @@error after update 232
 MNER 3 No Error Check should check @@error after insert 242
 MNER 3 No Error Check should check @@error after update 259
 MNER 3 No Error Check should check @@error after delete 267
 MUCO 3 Useless Code Useless Brackets 51
 MUCO 3 Useless Code Useless Brackets 70
 MUCO 3 Useless Code Useless Brackets 89
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 110
 MUCO 3 Useless Code Useless Brackets 112
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 134
 MUCO 3 Useless Code Useless Brackets 153
 MUCO 3 Useless Code Useless Brackets 157
 MUCO 3 Useless Code Useless Brackets 166
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 172
 MUCO 3 Useless Code Useless Brackets 180
 MUCO 3 Useless Code Useless Brackets 185
 MUCO 3 Useless Code Useless Brackets 199
 MUCO 3 Useless Code Useless Brackets 203
 MUCO 3 Useless Code Useless Brackets 211
 MUCO 3 Useless Code Useless Begin-End Pair 223
 MUCO 3 Useless Code Useless Brackets 230
 MUCO 3 Useless Code Useless Brackets 258
 MUCO 3 Useless Code Useless Brackets 275
 MUCO 3 Useless Code Useless Brackets 279
 MUOT 3 Updates outside transaction 267
 QISO 3 Set isolation level 60
 QIWC 3 Insert with not all columns specified missing 9 columns out of 15 243
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object_cinfo, attribute, class}
189
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_info1, object_type, object_cinfo, attribute, class}
225
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_info1, object_type, object_cinfo, attribute, class}
234
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_info1, object_type, object_cinfo, attribute, class}
261
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_info1, object_type, object_cinfo, attribute, class}
268
 VNRD 3 Variable is not read @user_id 64
 VNRD 3 Variable is not read @dummy 138
 VUNU 3 Variable is not used @loc 32
 VUNU 3 Variable is not used @role 33
 MSUB 2 Subquery Marker 91
 MSUB 2 Subquery Marker 93
 MSUB 2 Subquery Marker 188
 MSUB 2 Subquery Marker 224
 MTR1 2 Metrics: Comments Ratio Comments: 34% 18
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 38 = 48dec - 12exi + 2 18
 MTR3 2 Metrics: Query Complexity Complexity: 143 18

DATA PROPAGATION detailed
ColumnWritten To
@exeattrsysattributes.object_cinfo   sp_optgoal_rset_002.name sp_passwordpolicy_rset_001.value sp_rjs_retrieve_rset_001.js_server
@spidsysattributes.object_info1   °.char_value   sysattributes.object_info1   °.int_value   °.char_value   sp_dropdevice_rset_001.device sp_displayroles_rset_001.Role Name sp_displayroles_rset_002.Role Name sp_displayroles_rset_003.Role Name sp_displayroles_rset_004.Role Name
sp_dropdevice_rset_001.device sp_forceonline_object_rset_001.Object °.status sp_forceonline_object_rset_002.Object °.status sp_forceonline_page_rset_001.status sp_forceonline_page_rset_002.status sp_listsuspect_object_rset_001.Object °.Access sp_listsuspect_page_rset_001.Object
°.Access sp_makesuspect_obj_rset_001.Obj °.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_002.Obj °.Indid
°.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_003.Obj °.Indid °.LogType °.PageType °.ErrType
°.Delay °.TotalNum sp_memlog_rset_001.dumps_per_file sp_passwordpolicy_rset_001.message sp_passwordpolicy_rset_002.value sp_passwordpolicy_rset_003.value sp_passwordpolicy_rset_004.Policy_option sp_rjs_retrieve_rset_001.host_name sp_setsuspect_granularity_rset_001.Online mode sp_ssladmin_rset_001.certificate_path
sp_ssladmin_rset_002.Cipher Suite Name °.Preference
@valuesysattributes.char_value   sysattributes.int_value   °.char_value   sp_dropdevice_rset_001.device sp_dropdevice_rset_001.device sp_forceonline_object_rset_001.status sp_forceonline_object_rset_002.status sp_forceonline_page_rset_001.status sp_forceonline_page_rset_002.status sp_listsuspect_object_rset_001.Access
sp_listsuspect_page_rset_001.Access sp_makesuspect_obj_rset_001.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_002.Indid °.LogType °.PageType
°.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_003.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_passwordpolicy_rset_001.message
sp_passwordpolicy_rset_002.value sp_passwordpolicy_rset_003.value sp_passwordpolicy_rset_004.Policy_option sp_rjs_retrieve_rset_001.host_name sp_setsuspect_granularity_rset_001.Online mode sp_ssladmin_rset_001.certificate_path sp_ssladmin_rset_002.Cipher Suite Name °.Preference

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
read_writes table master..sysattributes (1)  
reads table master..sysprocesses (1)