DatabaseProcApplicationCreatedLinks
sybsystemprocssp_dropgroup  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     /*	4.8	1.1	06/14/90	sproc/src/dropgroup */
4     
5     /*
6     ** Messages for "sp_dropgroup"          17486
7     **
8     ** 17289, "Set your curwrite to the hurdle of current database."
9     ** 17333, "No group with the specified name exists." 
10    ** 17486, "Can't drop the group 'public'."
11    ** 17487, "You cannot drop group because it owns objects in database."
12    ** 17488, "Group has members.  It must be empty before it can be dropped."
13    ** 17489, "Group has been dropped."
14    ** 17756, "The execution of the stored procedure '%1!' in database
15    **         '%2!' was aborted because there was an error in writing the
16    **         replication log record."
17    ** 18075, "Set your maxwrite label correctly."
18    ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%2!'"
19    **
20    */
21    
22    /* 
23    ** IMPORTANT: Please read the following instructions before
24    **   making changes to this stored procedure.
25    **
26    **	To make this stored procedure compatible with High Availability (HA),
27    **	changes to certain system tables must be propagated 
28    **	to the companion server under some conditions.
29    **	The tables include (but are not limited to):
30    **		syslogins, sysservers, sysattributes, systimeranges,
31    **		sysresourcelimits, sysalternates, sysdatabases,
32    **		syslanguages, sysremotelogins, sysloginroles,
33    **		sysalternates (master DB only), systypes (master DB only),
34    **		sysusers (master DB only), sysprotects (master DB only)
35    **	please refer to the HA documentation for detail.
36    **
37    **	Here is what you need to do: 
38    **	For each insert/update/delete statement, add three sections to
39    **	-- start HA transaction prior to the statement
40    **	-- add the statement
41    **	-- add HA synchronization code to propagate the change to the companion
42    **
43    **	For example, if you are adding 
44    **		insert master.dbo.syslogins ......
45    **	the code should look like:
46    **	1. Before that SQL statement:
47    **		
48    **	2. Now, the SQL statement:
49    **		insert master.dbo.syslogins ......
50    **	3. Add a HA synchronization section right after the SQL statement:
51    **		
52    **
53    **	You may need to do similar change for each built-in function you
54    **	want to add.
55    **
56    **	After that, you need to add a separate part at a place where it can not
57    **	be reached by the normal execution path:
58    **	clean_all:
59    **		
60    **		return (1)
61    **
62    */
63    
64    create or replace procedure sp_dropgroup
65        @grpname varchar(255) /* group to be dropped */
66    as
67    
68        declare @gid int /* group id of the group to be dropped */
69        declare @msg varchar(1024)
70        declare @dummy int
71        declare @dbname varchar(255)
72        declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
73        declare @retstat int
74        declare @status int
75        declare @status1 int
76        declare @status2 int
77        declare @nullarg char(1)
78        declare @gp_enabled int
79    
80    
81    
82        select @status = 1
83        select @status1 = 1
84        select @status2 = 1
85    
86    
87    
88    
89        select @HA_CERTIFIED = 0
90    
91    
92    
93    
94        /* check to see if we are using HA specific SP for a HA enabled server */
95        exec @retstat = sp_ha_check_certified 'sp_dropgroup', @HA_CERTIFIED
96        if (@retstat != 0)
97            return (1)
98    
99        if @@trancount = 0
100       begin
101           set chained off
102       end
103   
104       set transaction isolation level 1
105   
106       /*
107       **  Only the Database Owner (DBO) or
108       **  Accounts with SA or SSO role can execute it.
109       **  Call proc_role() with the required SA role.
110       */
111       select @dbname = db_name()
112       execute @status = sp_aux_checkroleperm "dbo",
113           "manage any user", @dbname, @gp_enabled output
114   
115       if (@status != 0)
116   
117       begin
118           if (@gp_enabled = 0)
119           begin
120               execute @status1 = sp_aux_checkroleperm "sso_role",
121                   @nullarg, @nullarg, @gp_enabled output
122               if (@status1 > 0)
123                   execute @status2 = sp_aux_checkroleperm "sa_role",
124                       @nullarg, @nullarg, @gp_enabled output
125               if (@status1 > 0 and @status2 > 0)
126               begin
127                   select @dummy = proc_role("sa_role")
128                   select @dummy = proc_role("sso_role")
129                   return (1)
130               end
131           end
132           else
133           begin
134               /*
135               ** Call proc_auditperm here to do auditing and error
136               ** message.
137               */
138               select @dummy = proc_auditperm("manage any user",
139                       @status, @dbname)
140               return (1)
141           end
142       end
143       /*
144       ** Send appropriate audit records, already determined user has one
145       ** of the roles or the permission.
146       */
147       if (@gp_enabled = 0)
148       begin
149           if (@status1 = 0)
150               select @dummy = proc_role("sso_role")
151           if (@status2 = 0)
152               select @dummy = proc_role("sa_role")
153       end
154       else
155       begin
156           select @dummy = proc_auditperm("manage any user",
157                   @status, @dbname)
158       end
159   
160   
161       /*
162       **  See if the group exists.
163       */
164       select @gid = uid from sysusers
165       where name = @grpname
166           and ((uid between @@mingroupid and @@maxgroupid) or uid = 0)
167           and not exists (select name from master.dbo.syssrvroles where name = @grpname)
168   
169       if @gid is NULL
170       begin
171           /*
172           ** 17333, "No group with the specified name exists." 
173           */
174           raiserror 17333
175           return (1)
176       end
177   
178       /*
179       **  Can't drop the group public.
180       */
181       if @gid = 0
182       begin
183           /*
184           ** 17486, "Can't drop the group 'public'."
185           */
186           raiserror 17486
187           return (1)
188       end
189   
190       /*
191       **  Check to see if the group owns anything.  If so, return.
192       */
193       if exists (select * from sysobjects where uid = @gid)
194       begin
195           /*
196           ** 17487, "You cannot drop group because it owns objects in database."
197           */
198           raiserror 17487
199   
200           /*
201           **  Show what is owned by the group.
202           */
203           select s.name, s.type, owner = u.name
204           from sysobjects s, sysusers u
205           where s.id = @gid
206               and u.uid = @gid
207           return (1)
208       end
209   
210   
211       /*
212       **  Check to see that nobody is in the group.  If so, return.
213       */
214       if (select count(*) from sysusers
215               where gid = @gid and (uid < @@mingroupid or uid > @@maxgroupid)) != 0
216       begin
217           /*
218           ** 17488, "Group has members.  It must be empty before it can be dropped."
219           */
220           raiserror 17488
221   
222           /*
223           **  Show who is in the group.
224           */
225           select name from sysusers
226           where gid = @gid and (uid < @@mingroupid or uid > @@maxgroupid)
227   
228           return (1)
229       end
230   
231   
232   
233   out_of_HA_checking:
234   
235       /*
236       **  Drop the group.
237       **  Also drop any references to the group in the sysprotects table.
238       */
239       begin transaction rs_logexec
240   
241   
242   
243       delete from sysusers
244       where uid = @gid
245   
246   
247       delete from sysprotects
248       where uid = @gid
249   
250   
251   
252       /*
253       ** Write the log record to replicate this invocation 
254       ** of the stored procedure.
255       */
256       if (logexec() != 1)
257       begin
258           /*
259           ** 17756, "The execution of the stored procedure '%1!' in
260           **         database '%2!' was aborted because there was an
261           **         error in writing the replication log record."
262           */
263           select @dbname = db_name()
264           raiserror 17756, "sp_dropgroup", @dbname
265   
266           rollback transaction rs_logexec
267           return (1)
268       end
269   
270   
271   
272       commit transaction rs_logexec
273   
274       /*
275       ** 17489, "Group has been dropped."
276       */
277       exec sp_getmessage 17489, @msg output
278       print @msg
279   
280       return (0)
281   
282   clean_all:
283       rollback transaction rs_logexec
284       return (1)
285   


exec sp_procxmode 'sp_dropgroup', 'AnyMode'
go

Grant Execute on sp_dropgroup to public
go
RESULT SETS
sp_dropgroup_rset_002
sp_dropgroup_rset_001

DEFECTS
 MURC 6 Unreachable Code 282
 MURC 6 Unreachable Code 283
 MURC 6 Unreachable Code 284
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysprotects sybsystemprocs..sysprotects
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysobjects s and [sybsystemprocs..sysusers u], 2 tables with rc=1 203
 QCSC 4 Costly 'select count()', use 'exists()' 214
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {uid}
193
 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: {uid}
248
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 205
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_dropgroup  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysprotects  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check @@error after delete 243
 MNER 3 No Error Check should check @@error after delete 247
 MNER 3 No Error Check should check return value of exec 277
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 97
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 125
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 149
 MUCO 3 Useless Code Useless Brackets 151
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 187
 MUCO 3 Useless Code Useless Brackets 207
 MUCO 3 Useless Code Useless Brackets 228
 MUCO 3 Useless Code Useless Brackets 256
 MUCO 3 Useless Code Useless Brackets 267
 MUCO 3 Useless Code Useless Brackets 280
 MUCO 3 Useless Code Useless Brackets 284
 QCRS 3 Conditional Result Set 203
 QCRS 3 Conditional Result Set 225
 QISO 3 Set isolation level 104
 QNAJ 3 Not using ANSI Inner Join 204
 VNRD 3 Variable is not read @dummy 156
 MRST 2 Result Set Marker 203
 MRST 2 Result Set Marker 225
 MSUB 2 Subquery Marker 167
 MSUB 2 Subquery Marker 193
 MSUB 2 Subquery Marker 214
 MTR1 2 Metrics: Comments Ratio Comments: 55% 64
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 16 = 22dec - 8exi + 2 64
 MTR3 2 Metrics: Query Complexity Complexity: 112 64

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sysprotects  
reads table master..syssrvroles (1)  
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
writes table sybsystemprocs..sp_dropgroup_rset_001 
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)  
read_writes table sybsystemprocs..sysusers  
writes table sybsystemprocs..sp_dropgroup_rset_002 
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)