DatabaseProcApplicationCreatedLinks
sybsystemprocssp_addgroup  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     /*	4.2	28.1	05/14/90	sproc/src/addgroup */
4     
5     /*
6     ** Messages for "sp_addgroup"           17240
7     **
8     ** 17240, "'%1!' is not a valid name."
9     ** 17241, "A user with the specified group name already exists."
10    ** 17242, "A group with the specified name already exists."
11    ** 17243, "New group added."  
12    ** 17244, "All group ids have been assigned. No more groups can be added
13    **	   at this time."  
14    ** 17336, "Setting curwrite label to data_low for inserts into sysusers
15    **	   table failed."
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    ** 17265, "A role with the specified name '%1!' already exists in this Server."
20    ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%2!'"
21    */
22    
23    /* 
24    ** IMPORTANT: Please read the following instructions before
25    **   making changes to this stored procedure.
26    **
27    **	To make this stored procedure compatible with High Availability (HA),
28    **	changes to certain system tables must be propagated 
29    **	to the companion server under some conditions.
30    **	The tables include (but are not limited to):
31    **		syslogins, sysservers, sysattributes, systimeranges,
32    **		sysresourcelimits, sysalternates, sysdatabases,
33    **		syslanguages, sysremotelogins, sysloginroles,
34    **		sysalternates (master DB only), systypes (master DB only),
35    **		sysusers (master DB only), sysprotects (master DB only)
36    **	please refer to the HA documentation for detail.
37    **
38    **	Here is what you need to do: 
39    **	For each insert/update/delete statement, add three sections to
40    **	-- start HA transaction prior to the statement
41    **	-- add the statement
42    **	-- add HA synchronization code to propagate the change to the companion
43    **
44    **	For example, if you are adding 
45    **		insert master.dbo.syslogins ......
46    **	the code should look like:
47    **	1. Before that SQL statement:
48    **		
49    **	2. Now, the SQL statement:
50    **		insert master.dbo.syslogins ......
51    **	3. Add a HA synchronization section right after the SQL statement:
52    **		
53    **
54    **	You may need to do similar change for each built-in function you
55    **	want to add.
56    **
57    **	After that, you need to add a separate part at a place where it can not
58    **	be reached by the normal execution path:
59    **	clean_all:
60    **		
61    **		return (1)
62    */
63    
64    create or replace procedure sp_addgroup
65        @grpname varchar(255) /* new group name */
66    as
67    
68        declare @gid int /* group id */
69        declare @msg varchar(1024)
70        declare @dummy int
71        declare @dbname varchar(255)
72        declare @maxlen int
73        declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
74        declare @retstat int
75        declare @status int
76        declare @status1 int
77        declare @status2 int
78        declare @nullarg char(1)
79        declare @gp_enabled int
80    
81    
82    
83        select @status1 = 1
84        select @status2 = 1
85    
86    
87        select @HA_CERTIFIED = 0
88    
89    
90    
91    
92        /* check to see if we are using HA specific SP for a HA enabled server */
93        exec @retstat = sp_ha_check_certified 'sp_addgroup', @HA_CERTIFIED
94        if (@retstat != 0)
95            return (1)
96    
97        set transaction isolation level 1
98        if @@trancount = 0
99        begin
100           set chained off
101       end
102   
103       /*
104       **  If granular permissions is not enabled, only the Database Owner (DBO) or
105       **  Accounts with SA or SSO role can execute it.
106       **  If granular permissions is enabled then users with 'manage any user'
107       **  permission can execute it.
108       */
109   
110       select @dbname = db_name()
111       execute @status = sp_aux_checkroleperm "dbo",
112           "manage any user", @dbname, @gp_enabled output
113   
114       if (@status != 0)
115       begin
116           if (@gp_enabled = 0)
117           begin
118               execute @status1 = sp_aux_checkroleperm "sso_role",
119                   @nullarg, @nullarg, @gp_enabled output
120               if (@status1 != 0)
121                   execute @status2 = sp_aux_checkroleperm "sa_role",
122                       @nullarg, @nullarg, @gp_enabled output
123               if (@status1 != 0 and @status2 != 0)
124               begin
125                   select @dummy = proc_role("sa_role")
126                   select @dummy = proc_role("sso_role")
127                   return (1)
128               end
129           end
130           else
131           begin
132               /*
133               ** Call proc_auditperm here to do auditing and error
134               ** message.
135               */
136               select @dummy = proc_auditperm("manage any user",
137                       @status, @dbname)
138               return (1)
139           end
140       end
141       /*
142       ** Send apropriate audit records, already determined user has one
143       ** of the roles or the permission.
144       */
145       if (@gp_enabled = 0)
146       begin
147           if (@status1 = 0)
148               select @dummy = proc_role("sso_role")
149           if (@status2 = 0)
150               select @dummy = proc_role("sa_role")
151       end
152       else
153       begin
154           select @dummy = proc_auditperm("manage any user",
155                   @status, @dbname)
156       end
157   
158       /*
159       **  Check to see that the @grpname is valid.
160       */
161       if (@grpname is not null)
162       begin
163           select @maxlen = length from syscolumns
164           where id = object_id("sysusers") and name = "name"
165   
166           if valid_name(@grpname, @maxlen) = 0
167           begin
168               /*
169               ** 17240, "'%1!' is not a valid name."
170               */
171               raiserror 17240, @grpname
172               return 1
173           end
174       end
175   
176       /*
177       **  Check to see that @grpname is not a role name.
178       */
179       if exists (select srid from master.dbo.syssrvroles
180               where name = @grpname)
181       begin
182           /*
183           ** 17265, "A role with the specified name '%1!' already exists in this
184           **	   Server."
185           */
186           raiserror 17265, @grpname
187           return (1)
188       end
189   
190       /*
191       **  See if the new group name is already being used as a user or group name.
192       */
193       select @gid = uid
194       from sysusers
195       where name = @grpname
196   
197       /*
198       **  public group has id = 0
199       */
200       if @gid is not null
201       begin
202   
203           if (@gid != 0) and (@gid < @@mingroupid or @gid > @@maxgroupid)
204           begin
205               /*
206               ** 17241, "A user with the specified group name already exists."
207               */
208               raiserror 17241
209           end
210           else
211           begin
212               /*
213               ** 17242, "A group with the specified name already exists."
214               */
215               raiserror 17242
216           end
217           return (1)
218       end
219   
220       /*
221       **  Now get the group id for the new group.  It is the current maximum group
222       **  number + 1.  If this is the first group use the lowest possible group id
223       **  @@mingroupid.
224       */
225       select @gid = max(uid) + 1
226       from sysusers where uid = gid and gid <= @@maxgroupid
227   
228       /*
229       ** No more group ids available
230       */
231       if @gid = @@maxgroupid + 1
232       begin
233           /*
234           ** 17244, "All group ids have been assigned. No more groups can be 
235           ** 	added at this time."  
236           */
237           raiserror 17244
238           return (1)
239       end
240   
241       /*
242       **  This is the first group.
243       */
244       if @gid < @@mingroupid or @gid is NULL
245           select @gid = @@mingroupid
246   
247   
248   
249   
250       /*
251       **  Create the group.
252       */
253   
254       /* 
255       ** This transaction also writes a log record for replicating the
256       ** invocation of this procedure. If logexec() fails, the transaction
257       ** is aborted.
258       **
259       ** IMPORTANT: The name rs_logexec is significant and is used by
260       ** Replication Server.
261       */
262       begin transaction rs_logexec
263   
264   
265   
266       insert into sysusers(uid, suid, gid, name, environ)
267       values (@gid, - 2, @gid, @grpname, "")
268   
269   
270       /*
271       ** Write the log record to replicate this invocation 
272       ** of the stored procedure.
273       */
274       if (logexec() != 1)
275       begin
276           /*
277           ** 17756, "The execution of the stored procedure '%1!'
278           ** 	   in database '%2!' was aborted because there
279           ** 	   was an error in writing the replication log
280           **	   record."
281           */
282           select @dbname = db_name()
283           raiserror 17756, "sp_addgroup", @dbname
284   
285           rollback transaction rs_logexec
286           return (1)
287       end
288   
289       commit transaction rs_logexec
290   
291       /*
292       ** 17243, "New group added."  
293       */
294       exec sp_getmessage 17243, @msg out
295       print @msg
296   
297       return (0)
298   
299   clean_all:
300       rollback transaction rs_logexec
301       return (1)
302   
303   


exec sp_procxmode 'sp_addgroup', 'AnyMode'
go

Grant Execute on sp_addgroup to public
go
DEFECTS
 MURC 6 Unreachable Code 299
 MURC 6 Unreachable Code 300
 MURC 6 Unreachable Code 301
 MEST 4 Empty String will be replaced by Single Space 267
 MTYP 4 Assignment type mismatch name: sysname = varchar(255) 267
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 164
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_addgroup  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check @@error after insert 266
 MNER 3 No Error Check should check return value of exec 294
 MUCO 3 Useless Code Useless Brackets 94
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 138
 MUCO 3 Useless Code Useless Brackets 145
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 149
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 187
 MUCO 3 Useless Code Useless Brackets 217
 MUCO 3 Useless Code Useless Brackets 238
 MUCO 3 Useless Code Useless Brackets 274
 MUCO 3 Useless Code Useless Brackets 286
 MUCO 3 Useless Code Useless Brackets 297
 MUCO 3 Useless Code Useless Brackets 301
 QAFM 3 Var Assignment from potentially many rows 163
 QISO 3 Set isolation level 97
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
164
 VNRD 3 Variable is not read @dummy 154
 MSUB 2 Subquery Marker 179
 MTR1 2 Metrics: Comments Ratio Comments: 59% 64
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 18 = 24dec - 8exi + 2 64
 MTR3 2 Metrics: Query Complexity Complexity: 107 64

DATA PROPAGATION detailed
ColumnWritten To
@grpnamesysusers.name   sp_checknames_rset_003.suid sp_checknames_rset_014.name sp_checkreswords_rset_010.Reserved Word Segment Names sp_dropgroup_rset_001.name sp_droptype_rset_001.owner sp_dropuser_rset_002.user_type sp_indsuspect_rset_001.Own.Tab.Ind (Obj_ID, Ind_ID) sp_indsuspect_rset_002.Own.Tab.Ind (Obj_ID, Ind_ID) sp_jdbc_getschemas_rset_001.TABLE_SCHEM
sp_jdbc_getsupertypes_rset_001.TYPE_SCHEM sp_tab_suspectptn_rset_001.Partition type, Own.Tab.Ind (Obj_ID, Ind_ID) sp_tab_suspectptn_rset_002.Partition Type, Own.Tab.Ind.Ptn (Obj_ID, Ind_ID, Ptn_ID)

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)  
read_writes table sybsystemprocs..sysusers  
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  
reads table sybsystemprocs..syscolumns  
reads table master..syssrvroles (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)