DatabaseProcApplicationCreatedLinks
sybsystemprocssp_adduser  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/adduser */
4     
5     /*
6     ** Messages for "sp_adduser"            17330
7     **
8     ** 17240, "'" + @name_in_db + "' is not a valid name." 
9     ** 17231, "No login with the specified name exists." 
10    ** 17330, "A user with the same name already exists in the database."
11    ** 17331, "User already has a login under a different name."
12    ** 17332, "User already has alias access to the database."
13    ** 17333, "No group with the specified name exists." 
14    ** 17334, "All user ids have been assigned."
15    ** 17335, "New user added."
16    ** 17336, "Setting curwrite label to data_low for inserts into sysusers
17    **	   table failed."
18    ** 17289, "Set your curwrite to the hurdle of current database."
19    ** 17756, "The execution of the stored procedure '%1!' in database
20    **         '%2!' was aborted because there was an error in writing the
21    **         replication log record."
22    ** 17265, "A role with the specified name '%1!' already exists in this Server."
23    ** 18773, "HA_LOG: HA consistency check failure in stored procedure '% 1!' on the companion server '%2!'"
24    ** 18778, "A login with Login name '%1!' AND login id '%2!' could not be found in syslogins."
25    ** 18796, "A user with name '%1!' or login id '%2!' already exists in sysusers."
26    */
27    
28    /* 
29    ** IMPORTANT: Please read the following instructions before
30    **   making changes to this stored procedure.
31    **
32    **	To make this stored procedure compatible with High Availability (HA),
33    **	changes to certain system tables must be propagated 
34    **	to the companion server under some conditions.
35    **	The tables include (but are not limited to):
36    **		syslogins, sysservers, sysattributes, systimeranges,
37    **		sysresourcelimits, sysalternates, sysdatabases,
38    **		syslanguages, sysremotelogins, sysloginroles,
39    **		sysalternates (master DB only), systypes (master DB only),
40    **		sysusers (master DB only), sysprotects (master DB only)
41    **	please refer to the HA documentation for detail.
42    **
43    **	Here is what you need to do: 
44    **	For each insert/update/delete statement, add three sections to
45    **	-- start HA transaction prior to the statement
46    **	-- add the statement
47    **	-- add HA synchronization code to propagate the change to the companion
48    **
49    **	For example, if you are adding 
50    **		insert master.dbo.syslogins ......
51    **	the code should look like:
52    **	1. Before that SQL statement:
53    **		
54    **	2. Now, the SQL statement:
55    **		insert master.dbo.syslogins ......
56    **	3. Add a HA synchronization section right after the SQL statement:
57    **		
58    **
59    **	You may need to do similar change for each built-in function you
60    **	want to add.
61    **
62    **	After that, you need to add a separate part at a place where it can not
63    **	be reached by the normal execution path:
64    **	clean_all:
65    **		
66    **		return (1)
67    */
68    
69    create or replace procedure sp_adduser
70        @loginame varchar(255), /* user's login name in syslogins */
71        @name_in_db varchar(255) = NULL, /* user's name to add to current db */
72        @grpname varchar(255) = NULL /* group to put new user in */
73    as
74    
75        declare @suid int /* user's system id */
76        declare @grpid int /* group id of group to put user in */
77        declare @uid int /* new user's id */
78        declare @msg varchar(1024)
79        declare @dummy int
80        declare @dbname varchar(255)
81        declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
82        declare @retstat int
83        declare @maxlen int
84        declare @status1 int
85        declare @status2 int
86        declare @nullarg char(1)
87        declare @gp_enabled int
88    
89        select @status1 = 1
90        select @status2 = 1
91    
92    
93        select @uid = NULL
94        select @HA_CERTIFIED = 0
95    
96    
97    
98        /* check to see if we are using HA specific SP for a HA enabled server */
99        exec @retstat = sp_ha_check_certified 'sp_adduser', @HA_CERTIFIED
100       if (@retstat != 0)
101           return (1)
102   
103   
104       /*
105       ** Do not allow this system procedure to be run from within a transaction
106       ** to avoid creating a multi-database transaction where the 'master'
107       ** database is not the co-ordinating database.
108       */
109       if @@trancount > 0
110       begin
111           /*
112           ** 17260, "Can't run %1! from within a transaction."
113           */
114           raiserror 17260, "sp_adduser"
115           return (1)
116       end
117       else
118       begin
119           set chained off
120       end
121       set transaction isolation level 1
122   
123       /*
124       **  Only the Database Owner (DBO) or
125       **  Accounts with SA or SSO role can execute it.
126       **  If the user has SA role, it's uid will
127       **  be DBO uid (1). If granular permissions is enabled then
128       **  users with 'manage any user' permission can execute it.
129       */
130       select @dbname = db_name()
131       select @nullarg = NULL
132       execute @status1 = sp_aux_checkroleperm "sso_role",
133           "manage any user", @dbname, @gp_enabled output
134   
135       if (@status1 != 0)
136       begin
137           if (@gp_enabled = 0)
138           begin
139               if (user_id() != 1)
140               begin
141                   execute @status2 = sp_aux_checkroleperm "sa_role",
142                       @nullarg, @nullarg, @gp_enabled output
143                   if (@status2 > 0)
144                   begin
145                       /* 
146                       ** proc_role() will raise permission errors
147                       ** and send audit records to the audit trail.
148                       */
149                       select @dummy = proc_role("sa_role")
150                       select @dummy = proc_role("sso_role")
151                       return (1)
152                   end
153               end
154           end
155           else
156           begin
157               /* 
158               ** Call proc_auditperm here to do auditing and error 
159               ** message. 
160               */
161               select @dummy = proc_auditperm("manage any user",
162                       @status1, @dbname)
163               return (1)
164           end
165       end
166   
167       /* 
168       ** Send apropriate audit records, already determined user has one
169       ** of the roles or the permission. 
170       */
171       if (@gp_enabled = 0)
172       begin
173           if (@status1 = 0)
174               select @dummy = proc_role("sso_role")
175   
176           if (@status2 = 0)
177               select @dummy = proc_role("sa_role")
178       end
179       else
180       begin
181           select @dummy = proc_auditperm("manage any user",
182                   @status1, @dbname)
183       end
184   
185       /*
186       **  If no new user name is given, use the login name.
187       */
188       if @name_in_db is NULL
189           select @name_in_db = @loginame
190   
191       /*
192       **  Check to see that the @name_in_db is valid.
193       */
194       if (@name_in_db is not null)
195       begin
196           select @maxlen = length from syscolumns
197           where id = object_id("sysusers") and name = "name"
198   
199           if valid_name(@name_in_db, @maxlen) = 0
200           begin
201               /*
202               ** 17240, "'" + @name_in_db + "' is not a valid name." 
203               */
204               raiserror 17240, @name_in_db
205               return (1)
206           end
207       end
208   
209       /*
210       **  The name guest is a special case.  If it doesn't have a login it
211       **  can still be a valid user.  We'll catch it here and special case it.
212       */
213       if @loginame = "guest" and not exists
214               (select name
215               from master.dbo.syslogins(index ncsyslogins)
216               where name = @loginame)
217       begin
218           if exists (select *
219                   from sysusers(index ncsysusers1)
220                   where name = @loginame)
221           begin
222               /*
223               ** 17330, "A user with the same name already exists in the database."
224               */
225               raiserror 17330
226               return (1)
227           end
228   
229   
230   
231           /*
232           **  Add the guest user and return.
233           */
234   
235           /*
236           ** This transaction also writes a log record for replicating the
237           ** invocation of this procedure. If logexec() fails, the transaction
238           ** is aborted.
239           **
240           ** IMPORTANT: The name rs_logexec is significant and is used by
241           ** Replication Server.
242           */
243   
244           begin transaction rs_logexec
245   
246   
247   
248           insert into sysusers(uid, suid, gid, name)
249           values (@@guestuserid, - 1, 0, "guest")
250   
251   
252   
253           /*
254           ** Write the log record to replicate this invocation 
255           ** of the stored procedure.
256           */
257           if (logexec() != 1)
258           begin
259               /*
260               ** 17756, "The execution of the stored procedure
261               **         '%1!' in database '%2!' was aborted
262               **	    because there was an error in writing
263               **	    the replication log record."
264               */
265               select @dbname = db_name()
266               raiserror 17756, "sp_adduser", @dbname
267   
268               rollback transaction rs_logexec
269               return (1)
270           end
271   
272           commit transaction rs_logexec
273   
274           /* Update protection timestamp in Resource */
275           grant all to null
276   
277           return (0)
278       end
279   
280       /*
281       **  Check to see that the user has a login name.
282       **  We'll also initialize @grpid to 0 while we're here.
283       */
284       select @suid = suid, @grpid = 0
285       from master.dbo.syslogins(index ncsyslogins)
286       where name = @loginame and
287           ((status & 512) != 512) /* not LOGIN PROFILE */
288   
289       if @suid is NULL
290       begin
291           /*
292           ** 17231, "No login with the specified name exists." 
293           */
294           raiserror 17231
295           return (1)
296       end
297   
298       /*
299       **  Now check to see if the user already exists in the database.
300       **  This will also check if there is a role or group with @name_in_db
301       **  already in this database.
302       */
303       if exists (select *
304               from sysusers(index ncsysusers1)
305               where name = @name_in_db)
306       begin
307           /*
308           ** 17330, "A user with the same name already exists in the database."
309           */
310           raiserror 17330
311           return (1)
312       end
313   
314       /*
315       **  See if the user already has an account under a different name.
316       **  That is, is the user's suid already in the sysusers table.
317       */
318       if exists (select *
319               from sysusers(index sysusers)
320               where suid = @suid)
321       begin
322           /*
323           ** 17331, "User already has a login under a different name."
324           */
325           raiserror 17331
326           return (1)
327       end
328   
329       /*
330       **  See if the user is known in the database already with an alias.
331       **  That is, does the user's suid appear in the sysalternates table.
332       */
333       if exists (select *
334               from sysalternates
335               where suid = @suid)
336       begin
337           /*
338           ** 17332, "User already has alias access to the database."
339           */
340           raiserror 17332
341           return (1)
342       end
343   
344       /*
345       ** Make sure a role does not already exist with name.
346       */
347       if exists (select *
348               from master.dbo.syssrvroles
349               where name = @name_in_db)
350       begin
351           /*
352           ** 17265, "A role with the specified name '%1!' already exists in this
353           **	   Server."
354           */
355           raiserror 17265, @name_in_db
356           return (1)
357       end
358   
359       /*
360       **  If a group name is given, check to see that it is valid.
361       **  public group has id = 0
362       */
363       if @grpname is not NULL
364       begin
365           select @grpid = - 1
366           select @grpid = gid from sysusers
367           where name = @grpname
368               and ((uid = 0) or
369                   (uid between @@mingroupid and @@maxgroupid))
370               and not exists (select name from master.dbo.syssrvroles
371                   where name = @grpname)
372   
373   
374           if @grpid = - 1
375           begin
376               /*
377               ** 17333, "No group with the specified name exists." 
378               */
379               raiserror 17333
380               return (1)
381           end
382       end
383   
384       /*
385       **  Add the user to the sysusers table.
386       **  Check to see if the special user 'guest' (uid = 2) has already
387       **  been added.  If not, then the uid to use is 3, otherwise max(uid) + 1.
388       **  uid can go upto (@@mingroupid - 1) and then there will be a discontinuity 
389       **  in the range @@mingroupid to @@maxgroupid which are reserved for groups.  
390       **  We can continue assigning uids from (@@maxgroupid + 1) upto @@maxuserid.  
391       **  After reaching @@maxuserid, we can wrap around to the -ve space and 
392       **  assign uid in the range (@@invaliduserid - 1) to @@minuserid.   
393       */
394       /* 
395       **  HA NOTE: Whenever the logic of uid generation is changed, please also
396       **	  change it same way to the @uid_hacmp
397       */
398       select @uid = max(uid) from sysusers
399       where (uid > @@guestuserid and uid < @@mingroupid)
400           or (uid > @@maxgroupid and uid <= @@maxuserid)
401   
402       if @uid is NULL
403           /* The first regular user has uid = 3 */
404           select @uid = @@guestuserid + 1
405       else
406       begin
407           if @uid = @@mingroupid - 1
408               /* If we step into group range, skip group values */
409               select @uid = @@maxgroupid + 1
410           else
411           begin
412               if @uid <> @@maxuserid
413                   select @uid = @uid + 1
414               else
415               begin
416                   /* @@maxuserid has been used up, check the -ve space */
417                   select @uid = min(uid) from sysusers
418                   where uid < @@invaliduserid AND uid >= @@minuserid
419   
420                   if @uid is NULL
421                       /* the first negative uid is -2 */
422                       select @uid = @@invaliduserid - 1
423                   else
424                   begin
425                       if @uid <> @@minuserid
426                           select @uid = @uid - 1
427                       else
428                       begin
429                           /* 17334, All uids have been assigned */
430                           raiserror 17334
431                           return (1)
432                       end
433                   end
434               end
435           end
436       end
437   
438   
439   
440       /* 
441       ** This transaction also writes a log record for replicating the
442       ** invocation of this procedure. If logexec() fails, the transaction
443       ** is aborted.
444       **
445       ** IMPORTANT: The name rs_logexec is significant and is used by
446       ** Replication Server.
447       */
448       begin transaction rs_logexec
449   
450   
451   
452       insert into sysusers(uid, suid, gid, name)
453       values (@uid, @suid, @grpid, @name_in_db)
454   
455   
456       /*
457       ** Write the log record to replicate this invocation 
458       ** of the stored procedure.
459       */
460       if (logexec() != 1)
461       begin
462           /*
463           ** 17756, "The execution of the stored procedure '%1!'
464           ** 	   in database '%2!' was aborted because there
465           ** 	   was an error in writing the replication log
466           **	   record."
467           */
468           select @dbname = db_name()
469           raiserror 17756, "sp_adduser", @dbname
470   
471           rollback transaction rs_logexec
472           return (1)
473       end
474   
475       commit transaction rs_logexec
476   
477       /* Update protection timestamp in Resource */
478       grant all to null
479   
480       /*
481       ** 17335, "New user added."
482       */
483       exec sp_getmessage 17335, @msg output
484       print @msg
485   
486       return (0)
487   
488   clean_all:
489       rollback transaction rs_logexec
490       return (1)
491   


exec sp_procxmode 'sp_adduser', 'AnyMode'
go

Grant Execute on sp_adduser to public
go
DEFECTS
 MURC 6 Unreachable Code 488
 MURC 6 Unreachable Code 489
 MURC 6 Unreachable Code 490
 MCTR 4 Conditional Begin Tran or Commit Tran 244
 MCTR 4 Conditional Begin Tran or Commit Tran 272
 MTYP 4 Assignment type mismatch name: sysname = varchar(255) 453
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 197
 MGTP 3 Grant to public master..syslogins  
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_adduser  
 MGTP 3 Grant to public sybsystemprocs..sysalternates  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check @@error after insert 248
 MNER 3 No Error Check should check @@error after insert 452
 MNER 3 No Error Check should check return value of exec 483
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 139
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 151
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 171
 MUCO 3 Useless Code Useless Brackets 173
 MUCO 3 Useless Code Useless Brackets 176
 MUCO 3 Useless Code Useless Brackets 194
 MUCO 3 Useless Code Useless Brackets 205
 MUCO 3 Useless Code Useless Brackets 226
 MUCO 3 Useless Code Useless Brackets 257
 MUCO 3 Useless Code Useless Brackets 269
 MUCO 3 Useless Code Useless Brackets 277
 MUCO 3 Useless Code Useless Brackets 295
 MUCO 3 Useless Code Useless Brackets 311
 MUCO 3 Useless Code Useless Brackets 326
 MUCO 3 Useless Code Useless Brackets 341
 MUCO 3 Useless Code Useless Brackets 356
 MUCO 3 Useless Code Useless Brackets 380
 MUCO 3 Useless Code Useless Brackets 431
 MUCO 3 Useless Code Useless Brackets 460
 MUCO 3 Useless Code Useless Brackets 472
 MUCO 3 Useless Code Useless Brackets 486
 MUCO 3 Useless Code Useless Brackets 490
 QAFM 3 Var Assignment from potentially many rows 196
 QFID 3 Force index master..syslogins 215
 QFID 3 Force index sybsystemprocs..sysusers 219
 QFID 3 Force index master..syslogins 285
 QFID 3 Force index sybsystemprocs..sysusers 304
 QFID 3 Force index sybsystemprocs..sysusers 319
 QISO 3 Set isolation level 121
 QIWC 3 Insert with not all columns specified missing 1 columns out of 5 248
 QIWC 3 Insert with not all columns specified missing 1 columns out of 5 452
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
197
 QSWV 3 Sarg with variable @suid, Candidate Index: sysusers.csysusers clustered(suid) F 320
 QSWV 3 Sarg with variable @@guestuserid, Candidate Index: sysusers.ncsysusers2 unique(uid) S 399
 QSWV 3 Sarg with variable @@mingroupid, Candidate Index: sysusers.ncsysusers2 unique(uid) S 399
 QSWV 3 Sarg with variable @@maxgroupid, Candidate Index: sysusers.ncsysusers2 unique(uid) S 400
 QSWV 3 Sarg with variable @@maxuserid, Candidate Index: sysusers.ncsysusers2 unique(uid) S 400
 QSWV 3 Sarg with variable @@invaliduserid, Candidate Index: sysusers.ncsysusers2 unique(uid) S 418
 QSWV 3 Sarg with variable @@minuserid, Candidate Index: sysusers.ncsysusers2 unique(uid) S 418
 QTLO 3 Top-Level OR 399
 VNRD 3 Variable is not read @dummy 181
 MSUB 2 Subquery Marker 214
 MSUB 2 Subquery Marker 218
 MSUB 2 Subquery Marker 303
 MSUB 2 Subquery Marker 318
 MSUB 2 Subquery Marker 333
 MSUB 2 Subquery Marker 347
 MSUB 2 Subquery Marker 370
 MTR1 2 Metrics: Comments Ratio Comments: 59% 69
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 26 = 40dec - 16exi + 2 69
 MTR3 2 Metrics: Query Complexity Complexity: 181 69

DATA PROPAGATION detailed
ColumnWritten To
@loginamesysusers.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)
@name_in_dbsysusers.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
reads table sybsystemprocs..syscolumns  
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)  
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)  
reads table sybsystemprocs..sysalternates  
read_writes table sybsystemprocs..sysusers  
reads table master..syslogins (1)  
reads table master..syssrvroles (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)