DatabaseProcApplicationCreatedLinks
sybsystemprocssp_remoteoption  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/remoteoption */
4     
5     /*
6     ** Messages for "sp_remoteoption"       17770
7     **
8     ** 17513, "There is no remote user '%1!' mapped to local user '%2!' from
9     **	the remote server '%3!'."
10    ** 17260, "Can't run %1! from within a transaction."
11    ** 17770, "Settable remote login options."
12    ** 17771, "There is no remote user '%1!' mapped to local user '%2!' on
13    **	remote server '%3!'."
14    ** 17772, "Usage: sp_remoteoption [remoteserver, loginame, remotename,
15    **	optname, {true | false}]"
16    ** 17773, "Remote login option doesn't exist or can't be set by user."
17    ** 17774, "Run sp_remoteoption with no parameters to see options."
18    ** 17775, "Remote login option is not unique."
19    ** 17777, "Option '%1!' turned on."
20    ** 17778, "Option '%1!' turned off."
21    ** 17431, "true"
22    ** 17432, "false"
23    ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%2!'"
24    ** 18778, "Unable to find login '%1!' with id '%2!' in syslogins."
25    ** 18780, "Synchronization will not occur because server '%1!' is the companion server."
26    ** 18782 "Unable to find a server with name '%1!' and id '%2!'."
27    */
28    
29    /* 
30    ** IMPORTANT: Please read the following instructions before
31    **   making changes to this stored procedure.
32    **
33    **	To make this stored procedure compatible with High Availability (HA),
34    **	changes to certain system tables must be propagated 
35    **	to the companion server under some conditions.
36    **	The tables include (but are not limited to):
37    **		syslogins, sysservers, sysattributes, systimeranges,
38    **		sysresourcelimits, sysalternates, sysdatabases,
39    **		syslanguages, sysremotelogins, sysloginroles,
40    **		sysalternates (master DB only), systypes (master DB only),
41    **		sysusers (master DB only), sysprotects (master DB only)
42    **	please refer to the HA documentation for detail.
43    **
44    **	Here is what you need to do: 
45    **	For each insert/update/delete statement, add three sections to
46    **	-- start HA transaction prior to the statement
47    **	-- add the statement
48    **	-- add HA synchronization code to propagate the change to the companion
49    **
50    **	For example, if you are adding 
51    **		insert master.dbo.syslogins ......
52    **	the code should look like:
53    **	1. Before that SQL statement:
54    **		
55    **	2. Now, the SQL statement:
56    **		insert master.dbo.syslogins ......
57    **	3. Add a HA synchronization section right after the SQL statement:
58    **		
59    **
60    **	You may need to do similar change for each built-in function you
61    **	want to add.
62    **
63    **	Finally, add a separate part at a place where it can not
64    **	be reached by the normal execution path:
65    **	clean_all:
66    **		
67    **		return (1)
68    */
69    
70    create or replace procedure sp_remoteoption
71        @remoteserver varchar(255) = NULL, /* server name to change */
72        @loginame varchar(255) = NULL, /* user's remote name */
73        @remotename varchar(255) = NULL, /* user's local user name */
74        @optname varchar(20) = NULL, /* option name to turn on/off */
75        @optvalue varchar(10) = NULL /* true or false */
76    as
77    
78        declare @statvalue smallint /* number of option */
79        declare @optcount int /* number of options like @optname */
80        declare @msg varchar(1024)
81        declare @suid int
82        declare @rname varchar(255)
83        declare @lname varchar(255)
84        declare @true varchar(10)
85        declare @false varchar(10)
86        declare @sptlang int
87        declare @whichone int /* Which language */
88        declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
89        declare @retstat int
90        declare @nullarg char(1)
91        declare @dummy int
92        declare @status int
93        declare @gp_enabled int
94    
95    
96        select @HA_CERTIFIED = 0
97    
98    
99    
100   
101       /* check to see if we are using HA specific SP for a HA enabled server */
102       exec @retstat = sp_ha_check_certified 'sp_remoteoption', @HA_CERTIFIED
103       if (@retstat != 0)
104           return (1)
105   
106       if @@trancount = 0
107       begin
108           set chained off
109       end
110   
111       set transaction isolation level 1
112   
113       /* 
114       ** If granular permissions is not enabled then sso_role is required.
115       ** If granular permissions is enabled then the permission 
116       ** 'manage any remote login' is required.  proc_role and proc_auditperm will 
117       ** also do auditing if required. Both will also print error message if required.
118       */
119   
120       select @nullarg = NULL
121       execute @status = sp_aux_checkroleperm "sso_role", "manage any remote login",
122           @nullarg, @gp_enabled output
123   
124       /* For Auditing */
125       if (@gp_enabled = 0)
126       begin
127           if (proc_role("sso_role") = 0)
128               return (1)
129       end
130       else
131       begin
132           select @dummy = proc_auditperm("manage any remote login", @status)
133       end
134   
135       if (@status != 0)
136           return (1)
137   
138       select @sptlang = 0, @whichone = 0
139   
140       if @@langid != 0
141       begin
142           if not exists (
143                   select * from master.dbo.sysmessages where error
144                       between 17070 and 17079
145                       and langid = @@langid)
146               select @sptlang = 0
147       end
148   
149       /*
150       **  If no @server given, just list the possible remote login options.
151       **  Only certain status bits may be set or cleared.  
152       **	   settable                	    not settable
153       **      ------------------------------  --------------------------
154       **	trusted (1)
155       */
156       if @remoteserver is null
157       begin
158           /*
159           ** 17770, "Settable remote login options."
160           */
161           exec sp_getmessage 17770, @msg output
162           print @msg
163           if @sptlang = 0
164               exec sp_autoformat @fulltabname = "master.dbo.spt_values",
165                   @selectlist = "remotelogin_option = name",
166                   @whereclause = "where type = 'F' and number in (1, 1) and number > 0",
167                   @orderby = "order by name"
168           else
169           begin
170               select remotelogin_option = name, description
171               into #remoption1rs
172               from master.dbo.spt_values, master.dbo.sysmessages
173               where type = "F"
174                   and number in (1, 1)
175                   and number > 0
176                   and msgnum = error
177                   and error between 17070 and 17079
178                   and langid = @sptlang
179               exec sp_autoformat @fulltabname = #remoption1rs,
180                   @orderby = "order by remotelogin_option"
181               drop table #remoption1rs
182           end
183           return (0)
184       end
185   
186       /*
187       **  If @loginame is NULL then we want to set @suid = -1. Otherwise get
188       **  it real value.
189       */
190       if @loginame is null
191           select @suid = - 1
192       else select @suid = suser_id(@loginame)
193   
194       /*
195       **  Verify the server name, local, and remote names. 
196       */
197       if not exists (select *
198               from master.dbo.sysremotelogins r, master.dbo.sysservers s
199               where r.remoteserverid = s.srvid
200                   and s.srvname = @remoteserver
201                   and r.remoteusername = @remotename
202                   and r.suid = @suid)
203       begin
204           /*
205           ** 17771, "There is no remote user '%1!' mapped to local user '%2!' on
206           **	remote server '%3!'."
207           */
208           select @rname = isnull(@remotename, "NULL")
209           select @lname = isnull(@loginame, "NULL")
210           raiserror 17771, @rname, @lname, @remoteserver
211           return (1)
212       end
213   
214       /*
215       **  Check remaining parameters.
216       */
217       /* 17431, "true" */
218       exec sp_getmessage 17431, @true out
219       /* 17432, "false" */
220       exec sp_getmessage 17432, @false out
221       if @optname is NULL or lower(@optvalue) not in
222           ("true", "false", @true, @false) or @optvalue is null
223       begin
224           /*
225           ** 17772, "Usage: sp_remoteoption [remoteserver, loginame, remotename,
226           **	optname, {true | false}]"
227           */
228           raiserror 17772
229           return (1)
230       end
231   
232       /*
233       **  Use @optname and try to find the right option.
234       **  If there isn't just one, print appropriate diagnostics and return.
235       */
236       select @optcount = count(*)
237       from master.dbo.spt_values
238       where name like "%" + @optname + "%" and type = "F"
239           and number in (1, 1)
240           and number > 0
241   
242       /*
243       ** If option not found, and language not english, then check some more
244       */
245       if @optcount = 0 and @sptlang != 0
246       begin
247           select @optcount = count(*)
248           from master.dbo.spt_values, master.dbo.sysmessages
249           where description like "%" + @optname + "%" and type = "F"
250               and number in (1, 1)
251               and number > 0
252               and error between 17070 and 17079
253               and msgnum = error
254               and langid = @sptlang
255           select @whichone = 1
256       end
257   
258       /*
259       **  If no option, show the user what the options are.
260       */
261       if @optcount = 0
262       begin
263           /*
264           ** 17773, "Remote login option doesn't exist or can't be set by user."
265           */
266           raiserror 17773
267           /*
268           ** 17774, "Run sp_remoteoption with no parameters to see options."
269           */
270           exec sp_getmessage 17774, @msg output
271           print @msg
272           return (1)
273       end
274   
275       /*
276       **  If more than one option like @optname, show the duplicates and return.
277       */
278       if @optcount > 1
279       begin
280           /*
281           ** 17775, "Remote login option is not unique."
282           */
283           raiserror 17775
284   
285           if @sptlang = 0
286           begin
287               select duplicate_option = name
288               into #remoption2rs
289               from master.dbo.spt_values
290               where name like "%" + @optname + "%"
291                   and type = "F"
292                   and number in (1, 1)
293                   and number > 0
294               exec sp_autoformat @fulltabname = #remoption2rs
295               drop table #remoption2rs
296           end
297           else
298           begin
299               select duplicate_option = name, description
300               into #remoption3rs
301               from master.dbo.spt_values, master.dbo.sysmessages
302               where
303                   (name like "%" + @optname + "%"
304                       or description like "%" + @optname + "%")
305                   and type = "F"
306                   and number in (1, 1)
307                   and number > 0
308                   and error between 17070 and 17079
309                   and msgnum = error
310                   and langid = @sptlang
311               exec sp_autoformat @fulltabname = #remoption3rs
312               drop table #remoption3rs
313           end
314           return (1)
315       end
316   
317       /*
318       **  If we're in a transaction, disallow this since it might make recovery
319       **  impossible.
320       */
321       if @@trancount > 0
322       begin
323           /*
324           ** 17260, "Can't run %1! from within a transaction."
325           */
326           raiserror 17260, "sp_remoteoption"
327           return (1)
328       end
329   
330       /*
331       **  Get the number which is the bit value to set
332       */
333       if @whichone = 0
334           select @statvalue = number
335           from master.dbo.spt_values
336           where name like "%" + @optname + "%" and type = "F"
337               and number in (1, 1)
338               and number > 0
339       else
340           select @statvalue = number
341           from master.dbo.spt_values, master.dbo.sysmessages
342           where description like "%" + @optname + "%"
343               and type = "F"
344               and number in (1, 1)
345               and number > 0
346               and error between 17070 and 17079
347               and msgnum = error
348               and langid = @sptlang
349   
350   
351   
352       /*
353       **  Now update sysremotelogins.
354       */
355       if lower(@optvalue) in ("true", @true)
356       begin
357           update master.dbo.sysremotelogins
358           set status = status | @statvalue
359           from master.dbo.sysremotelogins r, master.dbo.sysservers s
360           where r.remoteserverid = s.srvid
361               and s.srvname = @remoteserver
362               and r.remoteusername = @remotename
363               and r.suid = @suid
364   
365   
366   
367           /*
368           ** 17777, "Option '%1!' turned on."
369           */
370           exec sp_getmessage 17777, @msg output
371           print @msg, @optname
372   
373   
374           return (0)
375       end
376   
377       /*
378       **  We want to turn it off.
379       */
380       else
381       begin
382           update master.dbo.sysremotelogins
383           set status = status & ~ @statvalue
384           from master.dbo.sysremotelogins r, master.dbo.sysservers s
385           where r.remoteserverid = s.srvid
386               and s.srvname = @remoteserver
387               and r.remoteusername = @remotename
388               and r.suid = @suid
389   
390   
391   
392           /*
393           ** 17778, "Option '%1!' turned off."
394           */
395           exec sp_getmessage 17778, @msg output
396           print @msg, @optname
397   
398   
399   
400           return (0)
401       end
402   
403       return (0)
404   
405   
406   


exec sp_procxmode 'sp_remoteoption', 'AnyMode'
go

Grant Execute on sp_remoteoption to public
go
DEFECTS
 MURC 6 Unreachable Code 403
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MINU 4 Unique Index with nullable columns master..sysremotelogins master..sysremotelogins
 MTYP 4 Assignment type mismatch @statvalue: smallint = int 334
 MTYP 4 Assignment type mismatch @statvalue: smallint = int 340
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 179
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 294
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 311
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysremotelogins.csysremotelogins unique clustered
(remoteserverid, remoteusername)
Intersection: {remoteusername}
201
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysremotelogins.csysremotelogins unique clustered
(remoteserverid, remoteusername)
Intersection: {remoteusername}
362
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysremotelogins.csysremotelogins unique clustered
(remoteserverid, remoteusername)
Intersection: {remoteusername}
387
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 145
 QTYP 4 Comparison type mismatch smallint = int 145
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 178
 QTYP 4 Comparison type mismatch smallint = int 178
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 254
 QTYP 4 Comparison type mismatch smallint = int 254
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 310
 QTYP 4 Comparison type mismatch smallint = int 310
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 348
 QTYP 4 Comparison type mismatch smallint = int 348
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public master..sysremotelogins  
 MGTP 3 Grant to public master..sysservers  
 MGTP 3 Grant to public sybsystemprocs..sp_remoteoption  
 MNER 3 No Error Check should check return value of exec 161
 MNER 3 No Error Check should check return value of exec 164
 MNER 3 No Error Check should check @@error after select into 170
 MNER 3 No Error Check should check return value of exec 179
 MNER 3 No Error Check should check return value of exec 218
 MNER 3 No Error Check should check return value of exec 220
 MNER 3 No Error Check should check return value of exec 270
 MNER 3 No Error Check should check @@error after select into 287
 MNER 3 No Error Check should check return value of exec 294
 MNER 3 No Error Check should check @@error after select into 299
 MNER 3 No Error Check should check return value of exec 311
 MNER 3 No Error Check should check @@error after update 357
 MNER 3 No Error Check should check return value of exec 370
 MNER 3 No Error Check should check @@error after update 382
 MNER 3 No Error Check should check return value of exec 395
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 104
 MUCO 3 Useless Code Useless Brackets 125
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 128
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 136
 MUCO 3 Useless Code Useless Brackets 183
 MUCO 3 Useless Code Useless Brackets 211
 MUCO 3 Useless Code Useless Brackets 229
 MUCO 3 Useless Code Useless Brackets 272
 MUCO 3 Useless Code Useless Brackets 314
 MUCO 3 Useless Code Useless Brackets 327
 MUCO 3 Useless Code Useless Brackets 374
 MUCO 3 Useless Code Useless Brackets 400
 MUCO 3 Useless Code Useless Brackets 403
 MUOT 3 Updates outside transaction 382
 QAFM 3 Var Assignment from potentially many rows 334
 QAFM 3 Var Assignment from potentially many rows 340
 QCTC 3 Conditional Table Creation 170
 QCTC 3 Conditional Table Creation 287
 QCTC 3 Conditional Table Creation 299
 QISO 3 Set isolation level 111
 QNAJ 3 Not using ANSI Inner Join 172
 QNAJ 3 Not using ANSI Inner Join 198
 QNAJ 3 Not using ANSI Inner Join 248
 QNAJ 3 Not using ANSI Inner Join 301
 QNAJ 3 Not using ANSI Inner Join 341
 QNAJ 3 Not using ANSI Inner Join 359
 QNAJ 3 Not using ANSI Inner Join 384
 QNUA 3 Should use Alias: Column description should use alias sysmessages 170
 QNUA 3 Should use Alias: Column name should use alias spt_values 170
 QNUA 3 Should use Alias: Table master..spt_values 172
 QNUA 3 Should use Alias: Table master..sysmessages 172
 QNUA 3 Should use Alias: Column type should use alias spt_values 173
 QNUA 3 Should use Alias: Column number should use alias spt_values 174
 QNUA 3 Should use Alias: Column number should use alias spt_values 175
 QNUA 3 Should use Alias: Column error should use alias sysmessages 176
 QNUA 3 Should use Alias: Column msgnum should use alias spt_values 176
 QNUA 3 Should use Alias: Column error should use alias sysmessages 177
 QNUA 3 Should use Alias: Column langid should use alias sysmessages 178
 QNUA 3 Should use Alias: Table master..spt_values 248
 QNUA 3 Should use Alias: Table master..sysmessages 248
 QNUA 3 Should use Alias: Column description should use alias sysmessages 249
 QNUA 3 Should use Alias: Column type should use alias spt_values 249
 QNUA 3 Should use Alias: Column number should use alias spt_values 250
 QNUA 3 Should use Alias: Column number should use alias spt_values 251
 QNUA 3 Should use Alias: Column error should use alias sysmessages 252
 QNUA 3 Should use Alias: Column error should use alias sysmessages 253
 QNUA 3 Should use Alias: Column msgnum should use alias spt_values 253
 QNUA 3 Should use Alias: Column langid should use alias sysmessages 254
 QNUA 3 Should use Alias: Column description should use alias sysmessages 299
 QNUA 3 Should use Alias: Column name should use alias spt_values 299
 QNUA 3 Should use Alias: Table master..spt_values 301
 QNUA 3 Should use Alias: Table master..sysmessages 301
 QNUA 3 Should use Alias: Column name should use alias spt_values 303
 QNUA 3 Should use Alias: Column description should use alias sysmessages 304
 QNUA 3 Should use Alias: Column type should use alias spt_values 305
 QNUA 3 Should use Alias: Column number should use alias spt_values 306
 QNUA 3 Should use Alias: Column number should use alias spt_values 307
 QNUA 3 Should use Alias: Column error should use alias sysmessages 308
 QNUA 3 Should use Alias: Column error should use alias sysmessages 309
 QNUA 3 Should use Alias: Column msgnum should use alias spt_values 309
 QNUA 3 Should use Alias: Column langid should use alias sysmessages 310
 QNUA 3 Should use Alias: Column number should use alias spt_values 340
 QNUA 3 Should use Alias: Table master..spt_values 341
 QNUA 3 Should use Alias: Table master..sysmessages 341
 QNUA 3 Should use Alias: Column description should use alias sysmessages 342
 QNUA 3 Should use Alias: Column type should use alias spt_values 343
 QNUA 3 Should use Alias: Column number should use alias spt_values 344
 QNUA 3 Should use Alias: Column number should use alias spt_values 345
 QNUA 3 Should use Alias: Column error should use alias sysmessages 346
 QNUA 3 Should use Alias: Column error should use alias sysmessages 347
 QNUA 3 Should use Alias: Column msgnum should use alias spt_values 347
 QNUA 3 Should use Alias: Column langid should use alias sysmessages 348
 QNUA 3 Should use Alias: Column status should use alias r 358
 QNUA 3 Should use Alias: Column status should use alias r 383
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
143
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
176
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
253
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
309
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
347
 VNRD 3 Variable is not read @dummy 132
 MSUB 2 Subquery Marker 142
 MSUB 2 Subquery Marker 197
 MTR1 2 Metrics: Comments Ratio Comments: 44% 70
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 57 = 65dec - 10exi + 2 70
 MTR3 2 Metrics: Query Complexity Complexity: 192 70
 PRED_QUERY_COLLECTION 2 {m=master..sysmessages, sv=master..spt_values} 0 170
 PRED_QUERY_COLLECTION 2 {r=master..sysremotelogins, s=master..sysservers} 0 197
 PRED_QUERY_COLLECTION 2 {m=master..sysmessages, sv=master..spt_values} 0 247
 PRED_QUERY_COLLECTION 2 {m=master..sysmessages, sv=master..spt_values} 0 299
 PRED_QUERY_COLLECTION 2 {m=master..sysmessages, sv=master..spt_values} 0 340
 PRED_QUERY_COLLECTION 2 {r=master..sysremotelogins, s=master..sysservers} 0 357
 PRED_QUERY_COLLECTION 2 {r=master..sysremotelogins, s=master..sysservers} 0 382

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysmessages (1)  
writes table tempdb..#remoption1rs (1) 
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
writes table tempdb..#remoption2rs (1) 
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)  
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   reads table master..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table tempdb..syscolumns (1)  
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_005 
reads table master..spt_values (1)  
writes table tempdb..#remoption3rs (1) 
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
read_writes table master..sysremotelogins (1)  
reads table master..sysservers (1)