DatabaseProcApplicationCreatedLinks
sybsystemprocssp_dropmessage  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "@(#) generic/sproc/src/%M% %I% %G%" */
3     /* generic/sproc/dropmessage		14.2	11/12/91 */
4     
5     /* 
6     ** Messages from sysmessages
7     **
8     ** 17201, "'%1!' is not an official language name from Syslanguages."
9     ** 17202, "Message number %1! does not exist in the %2! language."
10    ** 17210, "Message number must be at least 20000."
11    ** 17220, "Message number %1! does not exist."
12    ** 17221, "Message deleted."
13    ** 17222, "User '%1!' does not have permission to drop message number %2!."
14    ** 17223, "User '%1!' does not have permission to drop message number %2! in the %3! language."
15    ** 17224, "Drop failed.  Your curwrite label must match the security label of the message.  Check sysusermessages."
16    ** 17756, "The execution of the stored procedure '%1!' in database
17    **         '%2!' was aborted because there was an error in writing the
18    **         replication log record."
19    ** 18293, "Auditing for '%1!' event has failed due to internal error. Contact a user with System Security Officer (SSO) role."
20    */
21    
22    create or replace procedure sp_dropmessage
23        @message_num int,
24        @language varchar(30) = NULL
25    as
26    
27        declare @msg varchar(1024)
28        declare @msg2 varchar(255)
29        declare @lang_id smallint
30        declare @privileged smallint
31        declare @returncode smallint
32        declare @procval int
33    
34        declare @eventnum int /* event number for add message auditing */
35        declare @mod_ok_fail int /* successful add message auditing  */
36        declare @uid int /* successful add message auditing  */
37        /* Invalid id to send to sp_auxcheckroleperm */
38    
39    
40    
41        declare @nullarg char(1)
42        declare @status1 int
43        declare @dbname varchar(255)
44        declare @gp_enabled int
45    
46        select @status1 = 1
47        select @eventnum = 32 /* event number for add message */
48        select @dbname = db_name()
49    
50        if @@trancount = 0
51        begin
52            set chained off
53        end
54    
55        set transaction isolation level 1
56    
57        /*
58        ** Checking "OWN"ership of the message by the user is on by default by
59        ** resetting the "priveleged" flag. This is bypassed if needed by setting it
60        ** to 1
61        */
62        select @privileged = 0
63    
64        /*
65        **  Only the Database Owner (DBO) or
66        **  Accounts with SA role can execute it.
67        **  Call proc_role() with the required SA role.
68        **  Check sa_role first as it cleans up the code.
69        */
70        select @nullarg = NULL
71        execute @returncode = sp_aux_checkroleperm "dbo",
72            "own database", @dbname, @gp_enabled output
73    
74        if (@gp_enabled = 0 and @returncode != 0)
75        begin
76            execute @returncode = sp_aux_checkroleperm "sa_role",
77                @nullarg, @nullarg, @gp_enabled output
78            select @status1 = @returncode
79        end
80        if (@returncode = 0)
81        begin
82            if (@gp_enabled = 0)
83            begin
84                /*
85                ** If user has sa_role, wether by explicit granting or by being
86                ** SA or DBO we assume that "sa_role" is being used to drop the
87                ** message & thus we want to send an audit record by calling
88                ** proc_role("sa_role") & we set the privileged flag to say that
89                ** this does not have to be the owner of the message being 
90                ** dropped.
91                */
92                if (@status1 = 0)
93                begin
94                    select @procval = proc_role("sa_role")
95                end
96            end
97            else
98            begin
99                select @procval = proc_auditperm("own database",
100                       @returncode, @dbname)
101           end
102           select @privileged = 1
103       end
104   
105       /*
106       ** Check that language is valid.
107       */
108       if @language is null
109           select @lang_id = - 1 /* (all) */
110       else
111       BEGIN
112           execute @returncode = sp_validlang @language
113           if @returncode != 0
114           begin
115               /* Us_english is always valid */
116               if @language != "us_english"
117               BEGIN
118                   /* 
119                   ** 17201, "'%1!' is not an official language
120                   **		name from Syslanguages."
121                   */
122                   raiserror 17201, @language
123                   return @returncode
124               END
125   
126               /* set to us_english */
127               select @lang_id = NULL
128           end
129   
130           else
131               select @lang_id = langid from master.dbo.syslanguages
132               where @language = name
133       END
134   
135       if @message_num < 20000
136       begin
137           /* 17210, "Message number must be at least 20000." */
138           raiserror 17210
139           return (1)
140       end
141   
142       /*
143       ** Check that message exists 
144       */
145       if not exists (select * from sysusermessages
146               where error = @message_num)
147       begin
148           /* 17220, "Message number %1! does not exist." */
149           raiserror 17220, @message_num
150           return (1)
151       end
152   
153   
154   
155       /*
156       ** ... In the Proper language
157       */
158       if @lang_id != - 1 and not exists (select * from sysusermessages
159               where error = @message_num and langid = @lang_id)
160       begin
161           /* 17202, "Message number %1! does not exist in the %2! language." */
162           raiserror 17202, @message_num, @language
163           return (1)
164       end
165   
166       if (@privileged = 1)
167       BEGIN
168           /* get the owner id of the object */
169           select @uid = uid from sysusermessages where error = @message_num
170   
171           select @mod_ok_fail = 1
172           /*
173           ** Generate successful audit record.
174           */
175           select @returncode = ad_hoc_audit(@eventnum, @mod_ok_fail,
176                   str(@message_num), db_name(), NULL,
177                   suser_name(@uid), 0, NULL
178               )
179           if (@returncode != 0)
180           begin
181               /* 
182               ** 18293, "Auditing for '%1!' event has failed due to 
183               ** internal error. Contact a user with System Security 
184               ** Officer (SSO) role."
185               */
186               raiserror 18293, @eventnum
187           end
188   
189           /* Start the transaction to log the execution of this procedure.
190           **
191           ** IMPORTANT: The name "rs_logexec is significant and is used by 
192           **            Replication Server
193           */
194           begin transaction rs_logexec
195   
196           /*
197           ** Do they want to delete all the messages?
198           */
199           if @lang_id = - 1
200           begin
201               delete sysusermessages
202               where error = @message_num
203           end
204           /*
205           ** If not, delete only the message with the
206           ** specific language
207           */
208           else
209           begin
210               delete sysusermessages
211               where error = @message_num and
212                   langid = @lang_id
213           end
214   
215           /*
216           ** Write the log record to replicate this invocation 
217           ** of the stored procedure.
218           */
219           if (logexec() != 1)
220           begin
221               /*
222               ** 17756, "The execution of the stored procedure '%1!'
223               ** 	   in database '%2!' was aborted because there
224               ** 	   was an error in writing the replication log
225               **	   record."
226               */
227               raiserror 17756, "sp_dropmessage", @dbname
228   
229               rollback transaction rs_logexec
230               return (1)
231           end
232   
233           commit transaction
234   
235           /* 17221, "Message deleted." */
236           exec sp_getmessage 17221, @msg output
237           print @msg
238   
239       END
240       else
241       /* Only delete the user's specific messages */
242       BEGIN
243           /*
244           ** Do they want to delete all the messages?
245           */
246           if @lang_id = - 1
247           begin
248               /* get the owner id of the object */
249               select @uid = uid from sysusermessages
250               where error = @message_num
251               if exists (select * from sysusermessages
252                       where error = @message_num
253                           and uid = suser_id())
254               begin /* the user owns the message */
255   
256                   select @mod_ok_fail = 1
257               end
258               else /* the use does not own the message */
259               begin
260                   select @mod_ok_fail = 2
261               end
262               /*
263               ** Generate successful/fail audit record.
264               */
265               select @returncode = ad_hoc_audit(@eventnum,
266                       @mod_ok_fail, str(@message_num),
267                       db_name(), NULL, suser_name(@uid),
268                       0, NULL
269                   )
270               if (@returncode != 0)
271               begin
272                   /* 
273                   ** 18293, "Auditing for '%1!' event has failed 
274                   ** due to internal error. Contact a user with 
275                   ** System Security Officer (SSO) role."
276                   */
277                   raiserror 18293, @eventnum
278               end
279               if exists (select * from sysusermessages
280                       where error = @message_num
281                           and uid = suser_id())
282               begin
283   
284                   /* Start the transaction to log the execution
285                   ** of this procedure.
286                   **
287                   ** IMPORTANT: The name "rs_logexec is
288                   ** significant and is used by Replication Server
289                   */
290                   begin transaction rs_logexec
291   
292                   delete sysusermessages
293                   where error = @message_num
294                       and uid = suser_id()
295                   /*
296                   ** Write the log record to replicate this invocation 
297                   ** of the stored procedure.
298                   */
299                   if (logexec() != 1)
300                   begin
301                       /*
302                       ** 17756, "The execution of the stored
303                       **         procedure '%1!' in database
304                       **         '%2!' was aborted because
305                       **         there was an error in writing
306                       **         the replication log record."
307                       */
308                       select @dbname = db_name()
309                       raiserror 17756, "sp_dropmessage", @dbname
310   
311                       rollback transaction rs_logexec
312                       return (1)
313                   end
314   
315                   commit transaction
316   
317                   /* 17221, "Message deleted." */
318                   exec sp_getmessage 17221, @msg output
319                   print @msg
320   
321               end
322               else
323               begin
324                   /* 
325                   ** 17222 "User '%1!' does not have permission to 
326                   ** 		drop message number %2!"
327                   */
328                   select @msg2 = user_name()
329                   raiserror 17222, @msg2, @message_num
330               end
331           end
332           /*
333           ** If not, delete only the message with the
334           ** specific language
335           */
336           else
337           begin
338               /* get the owner id of the object */
339               select @uid = uid from sysusermessages
340               where error = @message_num
341                   and langid = @lang_id
342               if exists (select * from sysusermessages
343                       where error = @message_num
344                           and langid = @lang_id
345                           and uid = suser_id())
346               begin
347                   select @mod_ok_fail = 1
348               end
349               else
350               begin
351                   select @mod_ok_fail = 2
352               end
353               /*
354               ** Generate successful/fail audit record.
355               */
356               select @returncode = ad_hoc_audit(@eventnum,
357                       @mod_ok_fail, str(@message_num),
358                       db_name(), NULL, suser_name(@uid),
359                       0, NULL
360                   )
361               if (@returncode != 0)
362               begin
363                   /* 
364                   ** 18293, "Auditing for '%1!' event has failed 
365                   ** due to internal error. Contact a user with 
366                   ** System Security Officer (SSO) role."
367                   */
368                   raiserror 18293, @eventnum
369               end
370               if exists (select * from sysusermessages
371                       where error = @message_num
372                           and langid = @lang_id
373                           and uid = suser_id())
374               begin
375                   /* Start the transaction to log the execution
376                   ** of this procedure.
377                   **
378                   ** IMPORTANT: The name "rs_logexec is
379                   **            significant and is used by
380                   **            Replication Server
381                   */
382                   begin transaction rs_logexec
383   
384                   delete sysusermessages
385                   where error = @message_num
386                       and langid = @lang_id
387                       and uid = suser_id()
388                   /*
389                   ** Write the log record to replicate this invocation 
390                   ** of the stored procedure.
391                   */
392                   if (logexec() != 1)
393                   begin
394                       /*
395                       ** 17756, "The execution of the stored
396                       **         procedure '%1!' in database
397                       **         '%2!' was aborted because
398                       **         there was an error in writing
399                       **         the replication log record."
400                       */
401                       select @dbname = db_name()
402                       raiserror 17756, "sp_dropmessage", @dbname
403   
404                       rollback transaction rs_logexec
405                       return (1)
406                   end
407   
408                   commit transaction
409   
410                   /* 17221, "Message deleted." */
411                   exec sp_getmessage 17221, @msg output
412                   print @msg
413   
414               end
415               else
416               begin
417                   /* 
418                   ** 17223 "User '%1!' does not have permission to 
419                   ** 		drop message number %2! in the %3! language."
420                   */
421                   select @msg2 = user_name()
422                   raiserror 17223, @msg2, @message_num, @language
423                   return (1)
424               end
425           end
426       END
427       return (0)
428   
429   


exec sp_procxmode 'sp_dropmessage', 'AnyMode'
go

Grant Execute on sp_dropmessage to public
go
DEFECTS
 MCTR 4 Conditional Begin Tran or Commit Tran 194
 MCTR 4 Conditional Begin Tran or Commit Tran 233
 MCTR 4 Conditional Begin Tran or Commit Tran 290
 MCTR 4 Conditional Begin Tran or Commit Tran 315
 MCTR 4 Conditional Begin Tran or Commit Tran 382
 MCTR 4 Conditional Begin Tran or Commit Tran 408
 MINU 4 Unique Index with nullable columns master..syslanguages master..syslanguages
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysusermessages sybsystemprocs..sysusermessages
 MGTP 3 Grant to public master..syslanguages  
 MGTP 3 Grant to public sybsystemprocs..sp_dropmessage  
 MGTP 3 Grant to public sybsystemprocs..sysusermessages  
 MNER 3 No Error Check should check return value of exec 76
 MNER 3 No Error Check should check @@error after delete 201
 MNER 3 No Error Check should check @@error after delete 210
 MNER 3 No Error Check should check return value of exec 236
 MNER 3 No Error Check should check @@error after delete 292
 MNER 3 No Error Check should check return value of exec 318
 MNER 3 No Error Check should check @@error after delete 384
 MNER 3 No Error Check should check return value of exec 411
 MUCO 3 Useless Code Useless Brackets 74
 MUCO 3 Useless Code Useless Brackets 80
 MUCO 3 Useless Code Useless Brackets 82
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 139
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 166
 MUCO 3 Useless Code Useless Brackets 179
 MUCO 3 Useless Code Useless Brackets 219
 MUCO 3 Useless Code Useless Brackets 230
 MUCO 3 Useless Code Useless Brackets 270
 MUCO 3 Useless Code Useless Brackets 299
 MUCO 3 Useless Code Useless Brackets 312
 MUCO 3 Useless Code Useless Brackets 361
 MUCO 3 Useless Code Useless Brackets 392
 MUCO 3 Useless Code Useless Brackets 405
 MUCO 3 Useless Code Useless Brackets 423
 MUCO 3 Useless Code Useless Brackets 427
 QAFM 3 Var Assignment from potentially many rows 169
 QAFM 3 Var Assignment from potentially many rows 249
 QISO 3 Set isolation level 55
 VNRD 3 Variable is not read @procval 99
 MSUB 2 Subquery Marker 145
 MSUB 2 Subquery Marker 158
 MSUB 2 Subquery Marker 251
 MSUB 2 Subquery Marker 279
 MSUB 2 Subquery Marker 342
 MSUB 2 Subquery Marker 370
 MTR1 2 Metrics: Comments Ratio Comments: 48% 22
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 28 = 35dec - 9exi + 2 22
 MTR3 2 Metrics: Query Complexity Complexity: 176 22

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
read_writes table sybsystemprocs..sysusermessages  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
reads table master..syslanguages (1)  
calls proc sybsystemprocs..sp_validlang