DatabaseProcApplicationCreatedLinks
sybsystemprocssp_addalias  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/addalias */
4     
5     /*
6     ** Messages for "sp_addalias"           17230
7     **
8     ** 17231, "No login with the specified name exists."
9     ** 17232, "No user with the specified name exists in the current database."
10    ** 17233, "'%1!' is already a user in the current database."
11    ** 17234, "The specified user name is already aliased."
12    ** 17235, "Alias user added."
13    ** 17236, "Setting curwrite label to data_low for inserts into sysalternates
14    **	   table failed."
15    ** 17240, "'%1!' is not a valid name."
16    ** 17289, "Set your curwrite to the hurdle of current database."
17    ** 17756, "The execution of the stored procedure '%1!' in database
18    **         '%2!' was aborted because there was an error in writing the
19    **         replication log record."
20    ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%2!'"
21    ** 18778, "A login with Login name '%1!' AND login id '%2!' could not be found in syslogins."
22    ** 18795, "Unable to find a user with name '%1!' and login id '%2!' in sysusers."
23    */
24    
25    /* 
26    ** IMPORTANT: Please read the following instructions before
27    **   making changes to this stored procedure.
28    **
29    **	To make this stored procedure compatible with High Availability (HA),
30    **	changes to certain system tables must be propagated 
31    **	to the companion server under some conditions.
32    **	The tables include (but are not limited to):
33    **		syslogins, sysservers, sysattributes, systimeranges,
34    **		sysresourcelimits, sysalternates, sysdatabases,
35    **		syslanguages, sysremotelogins, sysloginroles,
36    **		sysalternates (master DB only), systypes (master DB only),
37    **		sysusers (master DB only), sysprotects (master DB only)
38    **	please refer to the HA documentation for detail.
39    **
40    **	Here is what you need to do: 
41    **	For each insert/update/delete statement, add three sections to
42    **	-- start HA transaction prior to the statement
43    **	-- add the statement
44    **	-- add HA synchronization code to propagate the change to the companion
45    **
46    **	For example, if you are adding 
47    **		insert master.dbo.syslogins ......
48    **	the code should look like:
49    **	1. Before that SQL statement:
50    **		
51    **	2. Now, the SQL statement:
52    **		insert master.dbo.syslogins ......
53    **	3. Add a HA synchronization section right after the SQL statement:
54    **		
55    **
56    **	You may need to do similar change for each built-in function you
57    **	want to add.
58    **
59    **	After that, you need to add a separate part at a place where it can not
60    **	be reached by the normal execution path:
61    **	clean_all:
62    **		
63    **		return (1)
64    */
65    
66    create or replace procedure sp_addalias
67        @loginame varchar(255), /* the name of the pretender */
68        @name_in_db varchar(255) /* who the pretender wants to pretend to be */
69    as
70    
71        declare @suid int /* the suid of the pretender */
72        declare @asuid int /* the suid of the person to impersonate */
73        declare @msg varchar(1024)
74        declare @name varchar(255)
75        declare @dbname varchar(255)
76        declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
77        declare @retstat int
78        declare @dummy int
79        declare @status1 int
80        declare @status2 int
81        declare @nullarg varchar(1)
82        declare @gp_enabled int
83    
84    
85        select @HA_CERTIFIED = 0
86        select @status1 = 1
87        select @status2 = 1
88        select @nullarg = NULL
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_addalias', @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       **  Only the Database Owner (DBO) or
105       **  Accounts with SA or SSO role can execute it.
106       **  If the user has SA role, it's uid will
107       **  be DBO uid (1). If granular permissions is enabled then
108       **  users with 'manage any user' permission can execute it.
109       */
110       select @dbname = db_name()
111       execute @retstat = sp_aux_checkroleperm "dbo", "manage any user",
112           @dbname, @gp_enabled output
113   
114       if (@retstat != 0)
115       begin
116           if (@gp_enabled = 0)
117           begin
118               execute @status1 = sp_aux_checkroleperm "sso_role",
119                   @nullarg, @dbname, @gp_enabled output
120   
121               if (@status1 != 0)
122                   execute @status2 = sp_aux_checkroleperm "sa_role",
123                       @nullarg, @dbname, @gp_enabled output
124               if (@status1 != 0 and @status2 != 0)
125               begin
126                   /* 
127                   ** proc_role() will raise permission errors
128                   ** and send audit records to the audit trail.
129                   */
130                   select @dummy = proc_role("sa_role")
131                   select @dummy = proc_role("sso_role")
132                   return (1)
133               end
134           end
135           else
136           begin
137               /* 
138               ** Call proc_auditperm here to do auditing and error 
139               ** message. 
140               */
141               select @dummy = proc_auditperm("manage any user",
142                       @retstat, @dbname)
143               return (1)
144           end
145       end
146   
147       /* 
148       ** Send apropriate audit records, already determined user has one
149       ** of the roles or the permission. 
150       */
151       if (@gp_enabled = 0)
152       begin
153           if (@status1 = 0)
154               select @dummy = proc_role("sso_role")
155           if (@status2 = 0)
156               select @dummy = proc_role("sa_role")
157       end
158       else
159       begin
160           select @dummy = proc_auditperm("manage any user",
161                   @retstat, @dbname)
162       end
163   
164       /*
165       **  Make sure that the pretender has an account.
166       */
167       select @suid = suid
168       from master.dbo.syslogins
169       where name = @loginame
170           and ((status & 512) != 512) /* not LOGIN PROFILE */
171   
172       if @suid is NULL
173       begin
174           /* 17231, "No login with the specified name exists." */
175   
176           raiserror 17231
177       end
178   
179       /*
180       **  Make sure that the pretender has an account.
181       */
182       select @suid = suid
183       from master.dbo.syslogins
184       where name = @loginame
185           and ((status & 512) != 512) /* not LOGIN PROFILE */
186   
187       if @suid is NULL
188       begin
189           /* 17231, "No login with the specified name exists." */
190   
191           raiserror 17231
192           return (1)
193       end
194   
195       /*
196       **  Get the suid of the person we want to pretend to be.
197       */
198       select @asuid = suid
199       from sysusers
200       where name = @name_in_db
201           and ((uid >= @@minuserid and uid < @@mingroupid and uid != 0)
202               or uid > @@maxgroupid)
203   
204       /*
205       **  Does the user to be impersonated exist in the current database?
206       */
207       if @asuid is NULL
208       begin
209           /* 
210           ** 17232, "No user with the specified name exists in the
211           ** current database." 
212           */
213   
214           raiserror 17232
215           return (1)
216       end
217   
218       /*
219       **  Does the login to do the impersonating already a user in the current db?
220       */
221       if exists (select *
222               from sysusers
223               where suid = @suid)
224       begin
225           /* 17233, "'%1!' is already a user in the current database." */
226           select @name = name
227           from sysusers
228           where suid = @suid
229   
230           raiserror 17233, @name
231           return (1)
232       end
233   
234       /*
235       ** Is the person already aliased to a user?
236       */
237       if exists (select *
238               from sysalternates
239               where suid = @suid)
240       begin
241           /* 17234, "The specified user name is already aliased." */
242   
243           raiserror 17234
244           return (1)
245       end
246   
247   
248   
249       /*
250       **  Add the alias.
251       */
252   
253       /* 
254       ** This transaction also writes a log record for replicating the
255       ** invocation of this procedure. If logexec() fails, the transaction
256       ** is aborted.
257       **
258       ** IMPORTANT: The name rs_logexec is significant and is used by
259       ** Replication Server.
260       */
261       begin transaction rs_logexec
262   
263   
264   
265       insert into sysalternates(suid, altsuid)
266       values (@suid, @asuid)
267   
268       /*
269       ** Write the log record to replicate this invocation 
270       ** of the stored procedure.
271       */
272       if (logexec() != 1)
273       begin
274           /*
275           ** 17756, "The execution of the stored procedure '%1!'
276           ** 	   in database '%2!' was aborted because there
277           ** 	   was an error in writing the replication log
278           **	   record."
279           */
280           select @dbname = db_name()
281           raiserror 17756, "sp_addalias", @dbname
282   
283           rollback transaction rs_logexec
284           return (1)
285       end
286   
287   
288   
289       commit transaction rs_logexec
290   
291       /* Update protection timestamp in Resource */
292       grant all to null
293   
294       /* 17235, "Alias user added." */
295       exec sp_getmessage 17235, @msg out
296       print @msg
297   
298       return (0)
299   
300   clean_all:
301       rollback transaction rs_logexec
302       return (1)
303   
304   


exec sp_procxmode 'sp_addalias', 'AnyMode'
go

Grant Execute on sp_addalias to public
go
DEFECTS
 MURC 6 Unreachable Code 300
 MURC 6 Unreachable Code 301
 MURC 6 Unreachable Code 302
 MGTP 3 Grant to public master..syslogins  
 MGTP 3 Grant to public sybsystemprocs..sp_addalias  
 MGTP 3 Grant to public sybsystemprocs..sysalternates  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check @@error after insert 265
 MNER 3 No Error Check should check return value of exec 295
 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 121
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 151
 MUCO 3 Useless Code Useless Brackets 153
 MUCO 3 Useless Code Useless Brackets 155
 MUCO 3 Useless Code Useless Brackets 192
 MUCO 3 Useless Code Useless Brackets 215
 MUCO 3 Useless Code Useless Brackets 231
 MUCO 3 Useless Code Useless Brackets 244
 MUCO 3 Useless Code Useless Brackets 272
 MUCO 3 Useless Code Useless Brackets 284
 MUCO 3 Useless Code Useless Brackets 298
 MUCO 3 Useless Code Useless Brackets 302
 QISO 3 Set isolation level 97
 QSWV 3 Sarg with variable @suid, Candidate Index: sysusers.csysusers clustered(suid) F 223
 QSWV 3 Sarg with variable @suid, Candidate Index: sysusers.csysusers clustered(suid) F 228
 VNRD 3 Variable is not read @dummy 160
 MSUB 2 Subquery Marker 221
 MSUB 2 Subquery Marker 237
 MTR1 2 Metrics: Comments Ratio Comments: 60% 66
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 17 = 23dec - 8exi + 2 66
 MTR3 2 Metrics: Query Complexity Complexity: 110 66

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