DatabaseProcApplicationCreatedLinks
sybsystemprocssp_clearpsexe  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G% */
3     
4     /*
5     ** Messages for "sp_clearpsexe"
6     ** 17260, "Can't run %1! from within a transaction."
7     ** 18262, "A non-SA user can not modify attributes of another task."
8     ** 18263, "A non-SA user can only modify its priority value."
9     ** 18265, "No specification for the specified process exists."
10    ** 18266, "'%1!' is not a valid execution attribute"
11    ** 18267, "Failed to clear value of attribute '%1!' for spid '%2!'. Check
12    **	   server errorlog for any additional information."
13    */
14    
15    create procedure sp_clearpsexe
16        @spid int, /* name of class */
17        @exeattr varchar(12) /* name of execution attribute */
18    as
19    
20        declare @attrib_id int,
21            @user_id int,
22            @action int,
23            @my_suid int,
24            @object_type varchar(2),
25            @dummy int,
26            @loc int,
27            @role int,
28            @kernel_mode varchar(15),
29            @attr_val varchar(255)
30    
31        select @kernel_mode = @@kernelmode
32        select @action = 3
33        select @user_id = NULL /* Id of user from Syslogins table */
34    
35        /*
36        **  IF we're in a transaction, disallow this since it might make recovery
37        **  impossible.
38        */
39        IF @@trancount > 0
40        BEGIN
41            /*
42            ** 17260, "Can't run %1! from within a transaction."
43            */
44            raiserror 17260, "sp_clearpsexe"
45            return (1)
46        END
47        ELSE
48        BEGIN
49            /* Use TSQL mode of unchained transactions */
50            set chained off
51        END
52    
53        /* Dont do "Dirty Reads" */
54        set transaction isolation level 1
55    
56    
57        /* Find out if the caller has sa_role. Note that 'sa_role'
58        ** string may be embedded in a user defined role name and
59        ** users with these roles should be checked for permissions.
60        */
61        select @role = 1
62        select @loc = charindex("sa_role", show_role())
63        IF (@loc > 0)
64        BEGIN
65            IF (@loc != 1)
66            BEGIN
67                /* check for space just before 'sa_role' string */
68                IF (ascii(substring(show_role(), (@loc - 1), 8)) != 32)
69                    select @role = 0
70            END
71    
72            /* check if role string ends in space */
73            IF ((@role = 1) AND
74                    (char_length(substring(show_role(), @loc,
75                                (char_length(show_role()) - (@loc - 1)))) != 7) AND
76                    (ascii(substring(show_role(), (@loc + 7), 1)) != 32))
77    
78                select @role = 0
79        END
80        ELSE
81            select @role = 0
82    
83    
84        /* check that user has sa role or modifying self */
85        IF (@role = 0)
86        BEGIN
87            select @my_suid = (select suid from master..sysprocesses
88                    where spid = @spid)
89            IF (@my_suid != suser_id())
90            BEGIN
91                /*
92                ** 18262, "A non-SA user can not modify attributes of another
93                ** process."
94                */
95                raiserror 18262
96                return (1)
97            END
98            ELSE IF @exeattr != "priority"
99            BEGIN
100               /*
101               ** 18263, "A non-SA user can only modify its priority value"
102               */
103               raiserror 18263
104               return (1)
105           END
106       END
107       ELSE
108       /* validate that this guy is really the sa */
109       IF (proc_role("sa_role") = 0)
110           return 1
111   
112       /* check that @exeattr value specified is valid */
113       IF ((@exeattr != 'priority')
114               and (@exeattr != 'timeslice')
115               and (((@kernel_mode = 'process') AND (@exeattr != 'enginegroup'))
116                   OR ((@kernel_mode = 'threaded') AND (@exeattr != 'threadpool'))))
117       BEGIN
118           /*
119           ** 18266, "'%1!' is not a valid execution attribute"
120           */
121           raiserror 18266, @exeattr
122           return (1)
123       END
124   
125   
126   
127       /* Check that an entry exists in the sysattributes table */
128       select @attr_val = (select char_value from master..sysattributes where
129                   (class = 6 AND
130                       attribute = 3 AND
131                       object_type = "PS" AND
132                       object_info1 = @spid AND
133                       object_cinfo = @exeattr))
134       IF (@attr_val is NULL)
135       BEGIN
136           /*
137           ** 18265, "No specification for the specified process exists."
138           */
139           raiserror 18265
140           return (1)
141       END
142   
143       /* Now hook up with Sysattributes Table */
144   
145       select @attrib_id = 3
146       select @object_type = "PS"
147       BEGIN
148           delete master..sysattributes
149           where class = 6 AND
150               attribute = 3 AND
151               object_type = "PS" AND
152               object_info1 = @spid AND
153               object_cinfo = @exeattr
154   
155           IF attrib_notify(6, @attrib_id, @object_type, NULL, @spid,
156                   NULL, NULL, @exeattr, NULL, NULL,
157                   NULL, NULL, "", @action) = 0
158           BEGIN
159               insert master..sysattributes
160               (class, attribute, object_type, object_info1, object_cinfo,
161                   char_value)
162               values (6, 3, 'PS', @spid, @exeattr, @attr_val)
163   
164               /*
165               ** 18267, "Failed to clear value of attribute '%1!' for
166               **	   spid '%2!'. Check server errorlog for any
167               **	   additional information."
168               */
169               raiserror 18267, @exeattr, @spid
170               return (1)
171           END
172       END
173       return (0)
174   


exec sp_procxmode 'sp_clearpsexe', 'AnyMode'
go

Grant Execute on sp_clearpsexe to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 157
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MTYP 4 Assignment type mismatch @kernel_mode: varchar(15) = int 31
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 88
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 129
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 130
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 149
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 150
 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_clearpsexe  
 MNER 3 No Error Check should check @@error after delete 148
 MNER 3 No Error Check should check @@error after insert 159
 MUCO 3 Useless Code Useless Brackets 45
 MUCO 3 Useless Code Useless Brackets 63
 MUCO 3 Useless Code Useless Brackets 65
 MUCO 3 Useless Code Useless Brackets 68
 MUCO 3 Useless Code Useless Brackets 73
 MUCO 3 Useless Code Useless Brackets 85
 MUCO 3 Useless Code Useless Brackets 89
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 104
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 134
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Begin-End Pair 147
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 173
 MUOT 3 Updates outside transaction 159
 QISO 3 Set isolation level 54
 QIWC 3 Insert with not all columns specified missing 9 columns out of 15 160
 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}		 129
 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}		 149
 VNRD 3 Variable is not read @user_id 33
 VUNU 3 Variable is not used @dummy 25
 MSUB 2 Subquery Marker 87
 MSUB 2 Subquery Marker 128
 MTR1 2 Metrics: Comments Ratio Comments: 40% 15
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 17 = 23dec - 8exi + 2 15
 MTR3 2 Metrics: Query Complexity Complexity: 78 15

DEPENDENCIES
PROCS AND TABLES USED
read_writes table master..sysattributes (1)  
reads table master..sysprocesses (1)