DatabaseProcApplicationCreatedLinks
sybsystemprocssp_addremotelogin  14 déc. 14Defects Propagation Dependencies

1     
2     /* 
3     ** IMPORTANT: Please read the following instructions before
4     **   making changes to this stored procedure.
5     **
6     **	To make this stored procedure compatible with High Availability (HA),
7     **	changes to certain system tables must be propagated 
8     **	to the companion server under some conditions.
9     **	The tables include (but are not limited to):
10    **		syslogins, sysservers, sysattributes, systimeranges,
11    **		sysresourcelimits, sysalternates, sysdatabases,
12    **		syslanguages, sysremotelogins, sysloginroles,
13    **		sysalternates (master DB only), systypes (master DB only),
14    **		sysusers (master DB only), sysprotects (master DB only)
15    **	please refer to the HA documentation for detail.
16    **
17    **	Here is what you need to do: 
18    **	For each insert/update/delete statement, add three sections to
19    **	-- start HA transaction prior to the statement
20    **	-- add the statement
21    **	-- add HA synchronization code to propagate the change to the companion
22    **
23    **	For example, if you are adding 
24    **		insert master.dbo.syslogins ......
25    **	the code should look like:
26    **	1. Now, the SQL statement:
27    **		insert master.dbo.syslogins ......
28    **	2. Add a HA synchronization section right after the SQL statement:
29    **		
30    **
31    **	You may need to do similar change for each built-in function you
32    **	want to add.
33    */
34    
35    create or replace procedure sp_addremotelogin
36        @remoteserver varchar(255), /* name of remote server */
37        @loginame varchar(255) = NULL, /* user's remote name */
38        @remotename varchar(255) = NULL /* user's local user name */
39    as
40    
41        declare @msg varchar(1024)
42        declare @name varchar(255)
43        declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
44        declare @maxlen int
45        declare @retstat int
46        declare @dummy int
47        declare @dbname varchar(255) /* for logexec error message */
48        declare @master_is_rep int /* whether master db is replicated */
49        declare @gp_enabled int
50        declare @nullarg varchar(1)
51        declare @status1 int
52        declare @status2 int
53    
54    
55        select @status1 = 1
56        select @status2 = 1
57    
58    
59        select @HA_CERTIFIED = 0
60    
61    
62    
63    
64        /* check to see if we are using HA specific SP for a HA enabled server */
65        exec @retstat = sp_ha_check_certified 'sp_addremotelogin', @HA_CERTIFIED
66        if (@retstat != 0)
67            return (1)
68    
69        /*
70        ** If we are logging this system procedure for replication,
71        ** we must be in the 'master' database to avoid creating a
72        ** multi-database transaction which could make recovery of
73        ** the 'master' database impossible.
74        */
75        select @master_is_rep = getdbrepstat(1)
76    
77        if (@master_is_rep < 0)
78        begin
79            /*
80            ** 18409, "The built-in function getdbrepstat() failed."
81            */
82            raiserror 18409, "getdbrepstat"
83            return (1)
84        end
85    
86        select @dbname = db_name()
87    
88        if (@master_is_rep > 0) and (@dbname != "master")
89        begin
90            /*
91            ** 18388, "You must be in the master database in order
92            ** to run '%1!'."
93            */
94            raiserror 18388, "sp_addremotelogin"
95            return (1)
96        end
97    
98        /*
99        **  If we're in a transaction, disallow this since it might make recovery
100       **  impossible.
101       */
102       if @@trancount > 0
103       begin
104           /*
105           ** 17260, "Can't run %1! from within a transaction." 
106           */
107           raiserror 17260, "sp_addremotelogin"
108           return (1)
109       end
110       else
111       begin
112           set chained off
113       end
114       set transaction isolation level 1
115   
116   
117       /*
118       **  If granular permissions is not enabled, 
119       **  Accounts with SA or SSO role can execute it.
120       **  If granular permissions is enabled then users with 
121       ** 'manage any remote login' permission can execute it.
122       */
123       select @nullarg = NULL
124       execute @status1 = sp_aux_checkroleperm "sa_role", "manage any remote login",
125           @nullarg, @gp_enabled output
126   
127       if (@gp_enabled = 0)
128       begin
129           if (@status1 != 0)
130           begin
131               execute @status2 = sp_aux_checkroleperm "sso_role",
132                   @nullarg, @nullarg, @gp_enabled output
133   
134               if (@status2 != 0)
135               begin
136                   /* 
137                   ** proc_role() will raise permission errors
138                   ** and send audit records to the audit trail.
139                   */
140                   select @dummy = proc_role("sa_role")
141                   /* 17888, "You must possess either the System 
142                   ** Administrator (SA) or System Security Officer (SSO) 
143                   ** role to execute %1!." 
144                   */
145                   raiserror 17888, "addremotelogin"
146                   return (1)
147               end
148           end
149           if (@status1 = 0)
150               select @dummy = proc_role("sa_role")
151   
152           if (@status2 = 0)
153               select @dummy = proc_role("sso_role")
154       end
155       else
156       begin
157           select @dummy = proc_auditperm("manage any remote login",
158                   @status1)
159           if (@status1 != 0)
160               return 1
161       end
162       /*
163       **  Check that the server name is valid.
164       */
165       if not exists (select *
166               from master.dbo.sysservers
167               where srvname = @remoteserver)
168       begin
169           /*
170           ** 17270, "There is not a server named '%1!'."
171           */
172           raiserror 17270, @remoteserver
173           return (1)
174       end
175   
176       /*
177       **  If it's the local server issue a warning but continue
178       */
179       if exists (select *
180               from master.dbo.sysservers
181               where srvname = @remoteserver
182                   and srvid = 0)
183       begin
184           /*
185           ** 17166, "warning: '%1!' is the same as the local server name"
186           */
187           exec sp_getmessage 17166, @msg output
188           print @msg, @remoteserver
189       end
190   
191       /* 
192       ** This transaction also writes a log record for replicating the
193       ** invocation of this procedure. If logexec() fails, the transaction
194       ** is aborted.
195       **
196       ** IMPORTANT: The name rs_logexec is significant and is used by
197       ** Adaptive Server.
198       */
199       begin tran rs_logexec
200   
201   
202   
203       /*
204       **  There are three cases to handle.
205       **
206       **	1) if only @remoteserver is given then a entry is made in
207       **		sysremotelogins that means anyone that doesn't have
208       **		an exact of mapped match in sysremotelogins will use
209       **		their remotename as their local name and it will be looked
210       **		up in syslogins.
211       **
212       **	2) if @remotename is omitted then it means that anyone from the
213       **		remote server logging in that doesn't have a complete
214       **		match in sysremotelogins will be mapped to @loginame.
215       **
216       **	3) if @remotename and @loginame are given then it is a straight
217       **		remote login for sysremotelogins.
218       */
219   
220       /*
221       **  Case 1:  Only @remoteserver given.
222       */
223       if @loginame is null and @remotename is null
224       begin
225           /*
226           **  Check that there is not already an entry for local mapping.
227           */
228           if exists (select *
229                   from master.dbo.sysremotelogins l, master.dbo.sysservers s
230                   where l.remoteserverid = s.srvid
231                       and s.srvname = @remoteserver
232                       and l.remoteusername is null)
233           begin
234               /*
235               ** 17272, "There is already a default-name mapping of a remote login from remote server '%1!'."
236               */
237               raiserror 17272, @remoteserver
238               goto clean_all
239           end
240   
241   
242   
243           /*
244           **  Add the entry.
245           */
246           insert into master.dbo.sysremotelogins
247           (remoteserverid, remoteusername, suid, status)
248           select srvid, null, - 1, 0
249           from master.dbo.sysservers
250           where srvname = @remoteserver
251   
252   
253   
254           /*
255           ** 17273, "New remote login created."
256           */
257           exec sp_getmessage 17273, @msg output
258           print @msg
259   
260           goto ha_syn
261       end
262   
263       /*
264       **  Check that the @loginame is valid.  These is needed for both
265       **  case 2 and 3.
266       */
267       if not exists (select *
268               from master.dbo.syslogins
269               where name = @loginame and
270                   ((status & 512) != 512)) /* not LOGIN PROFILE */
271       begin
272           /*
273           ** 17274, "'%1!' isn't a local user -- remote login denied."
274           */
275           raiserror 17274, @loginame
276           goto clean_all
277       end
278   
279       /*
280       **  Check to make sure that there is not already a @remotename for 
281       **  the @remoteserver.
282       */
283       if (@remotename is not null)
284       begin
285           select @maxlen = length from master.dbo.syscolumns
286           where id = object_id("master.dbo.sysremotelogins") and
287               name = "remoteusername"
288   
289           if char_length(@remotename) > @maxlen
290           begin
291               /*
292               ** 17240, "'%1!' is not a valid name."
293               */
294               raiserror 17240, @remotename
295               goto clean_all
296           end
297       end
298   
299       if exists (select *
300               from master.dbo.sysremotelogins l, master.dbo.sysservers s
301               where l.remoteserverid = s.srvid
302                   and s.srvname = @remoteserver
303                   and l.remoteusername = @remotename)
304       begin
305           /*
306           ** 17275, "There is already a remote user named '%1!' for remote server '%2!'."
307           */
308           select @name = isnull(@remotename, "NULL")
309           raiserror 17275, @name, @remoteserver
310           goto clean_all
311       end
312   
313   
314   
315       /*
316       **  Case 2: We want to make an entry into sysremotelogins that will map
317       **	any non-exact matches to a particular local user.
318       */
319       if @remotename is null
320       begin
321           /*
322           **  Check that there is not already an entry for local mapping.
323           */
324           if exists (select *
325                   from master.dbo.sysremotelogins l, master.dbo.sysservers s
326                   where l.remoteserverid = s.srvid
327                       and s.srvname = @remoteserver
328                       and l.remoteusername is null)
329           begin
330               /*
331               ** 17272, "There is already a default-name mapping of a remote login from remote server '%1!'."
332               */
333               raiserror 17272, @remoteserver
334               goto clean_all
335           end
336   
337   
338   
339   
340           /*
341           **  Go ahead and make the entry.
342           */
343           insert into master.dbo.sysremotelogins
344           (remoteserverid, remoteusername, suid, status)
345           select srvid, null, suser_id(@loginame), 0
346           from master.dbo.sysservers
347           where srvname = @remoteserver
348   
349   
350           /*
351           ** 17273, "New remote login created."
352           */
353           exec sp_getmessage 17273, @msg output
354           print @msg
355   
356           goto ha_syn
357       end
358   
359       /*
360       **  Case 3:  All the parameters have been supplied.  All we need to check
361       **	is that the entry isn't already in sysremotelogins.
362       **	We've verified the @remoteserver and @loginame above.
363       */
364       if @loginame is not null and @remotename is not null
365       begin
366           /*
367           **  Make sure that the @loginame and @remotename are a
368           **  unique combination.
369           */
370           if exists (select *
371                   from master.dbo.sysremotelogins l, master.dbo.sysservers s
372                   where l.remoteusername = @remotename
373                       and l.remoteserverid = s.srvid
374                       and s.srvname = @remoteserver
375                       and l.suid = suser_id(@loginame))
376           begin
377               /*
378               ** 17275, "There is already a remote user named '%1!' for remote server '%2!'."
379               */
380               raiserror 17275, @remotename, @remoteserver
381               goto clean_all
382           end
383   
384   
385   
386           /*
387           **  Go ahead and do the insert.
388           */
389           insert into master.dbo.sysremotelogins
390           (remoteserverid, remoteusername, suid, status)
391           select srvid, @remotename, suser_id(@loginame), 0
392           from master.dbo.sysservers
393           where srvname = @remoteserver
394   
395   
396   
397           /*
398           ** 17273, "New remote login created."
399           */
400           exec sp_getmessage 17273, @msg output
401           print @msg
402   
403           goto ha_syn
404       end
405   
406       /*
407       **  We got here because the syntax was incorrect.
408       */
409   
410       raiserror 17276
411       goto clean_all
412   
413   ha_syn:
414   
415   
416   
417       /*
418       ** log the command for replication support
419       ** except when the server is an HADR member
420       */
421       if (@master_is_rep > 0) and not exists (select *
422               from master.dbo.sysservers
423               where (srvname = @remoteserver or srvname = @remoteserver + "DR")
424                   and srvclass = (select number
425                       from master.dbo.spt_values
426                       where type = 'X' and lower(name) = lower("HADR_MEMBER")))
427       begin
428           if (logexec(1) != 1)
429           begin
430               /*
431               ** , "17869 Stored procedure %1 failed'
432               **       because '%2' failed in database '%3'."
433               */
434               raiserror 17869, "sp_addremotelogin", "logexec()", @dbname
435               goto clean_all
436           end
437       end
438   
439       commit tran rs_logexec
440   
441       return (0)
442   
443   
444   clean_all:
445       rollback tran rs_logexec
446       return (1)
447   


exec sp_procxmode 'sp_addremotelogin', 'AnyMode'
go

Grant Execute on sp_addremotelogin to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysremotelogins master..sysremotelogins
 MTYP 4 Assignment type mismatch remoteusername: varchar(30) = varchar(255) 391
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysremotelogins.csysremotelogins unique clustered
(remoteserverid, remoteusername)
Intersection: {remoteusername}
232
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysremotelogins.csysremotelogins unique clustered
(remoteserverid, remoteusername)
Intersection: {remoteusername}
303
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysremotelogins.csysremotelogins unique clustered
(remoteserverid, remoteusername)
Intersection: {remoteusername}
328
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysremotelogins.csysremotelogins unique clustered
(remoteserverid, remoteusername)
Intersection: {remoteusername}
372
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
426
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 182
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 424
 QTYP 4 Comparison type mismatch smallint = int 424
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MAW1 3 Warning message on %name% master..syscolumns.id: Warning message on syscolumns 286
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..syscolumns  
 MGTP 3 Grant to public master..syslogins  
 MGTP 3 Grant to public master..sysremotelogins  
 MGTP 3 Grant to public master..sysservers  
 MGTP 3 Grant to public sybsystemprocs..sp_addremotelogin  
 MNER 3 No Error Check should check return value of exec 187
 MNER 3 No Error Check should check @@error after insert 246
 MNER 3 No Error Check should check return value of exec 257
 MNER 3 No Error Check should check @@error after insert 343
 MNER 3 No Error Check should check return value of exec 353
 MNER 3 No Error Check should check @@error after insert 389
 MNER 3 No Error Check should check return value of exec 400
 MUCO 3 Useless Code Useless Brackets 66
 MUCO 3 Useless Code Useless Brackets 67
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 83
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 108
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 134
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 149
 MUCO 3 Useless Code Useless Brackets 152
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 173
 MUCO 3 Useless Code Useless Brackets 283
 MUCO 3 Useless Code Useless Brackets 428
 MUCO 3 Useless Code Useless Brackets 441
 MUCO 3 Useless Code Useless Brackets 446
 QAFM 3 Var Assignment from potentially many rows 285
 QISO 3 Set isolation level 114
 QNAJ 3 Not using ANSI Inner Join 229
 QNAJ 3 Not using ANSI Inner Join 300
 QNAJ 3 Not using ANSI Inner Join 325
 QNAJ 3 Not using ANSI Inner Join 371
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
286
 VNRD 3 Variable is not read @gp_enabled 132
 VNRD 3 Variable is not read @dummy 157
 MSUB 2 Subquery Marker 165
 MSUB 2 Subquery Marker 179
 MSUB 2 Subquery Marker 228
 MSUB 2 Subquery Marker 267
 MSUB 2 Subquery Marker 299
 MSUB 2 Subquery Marker 324
 MSUB 2 Subquery Marker 370
 MSUB 2 Subquery Marker 421
 MSUB 2 Subquery Marker 424
 MTR1 2 Metrics: Comments Ratio Comments: 49% 35
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 39 = 44dec - 7exi + 2 35
 MTR3 2 Metrics: Query Complexity Complexity: 179 35
 PRED_QUERY_COLLECTION 2 {r=master..sysremotelogins, s=master..sysservers} 0 228
 PRED_QUERY_COLLECTION 2 {r=master..sysremotelogins, s=master..sysservers} 0 299
 PRED_QUERY_COLLECTION 2 {r=master..sysremotelogins, s=master..sysservers} 0 324
 PRED_QUERY_COLLECTION 2 {r=master..sysremotelogins, s=master..sysservers} 0 370

DATA PROPAGATION detailed
ColumnWritten To
@loginamesysremotelogins.suid  
@remotenamesysremotelogins.remoteusername   sp_checknames_rset_008.srvname sp_checkreswords_rset_003.Table

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