DatabaseProcApplicationCreatedLinks
sybsystemprocssp_dropremotelogin  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/defaultlanguage */
4     
5     /*
6     ** Messages for "sp_dropremotelogin"    17512
7     **
8     ** 17260, "Can't run %1! from within a transaction." 
9     ** 17512, "Remote login dropped."
10    ** 17513, "There is no remote user '%1!' mapped to local user '%2!' from the remote server '%3!'."
11    ** 17869, "Stored procedure %1 failed because %2 failed in database %3."
12    ** 18388, "You must be in the master database in order to run '%1'!."
13    ** 18409, "The built-in function getdbrepstat() failed."
14    ** 18773, "HA_LOG: HA consistency check failure in stored procedure '%1!' on companion server '%2!'"
15    ** 18780, "Synchronization will not occur because server '%1!' is the companion server."
16    ** 18782, "Unable to find a server with name '%1!' and id '%2!'."
17    ** 18778, "Unable to find login '%1!' with id '%2!' in syslogins."
18    */
19    
20    /* 
21    ** IMPORTANT: Please read the following instructions before
22    **   making changes to this stored procedure.
23    **
24    **	To make this stored procedure compatible with High Availability (HA),
25    **	changes to certain system tables must be propagated 
26    **	to the companion server under some conditions.
27    **	The tables include (but are not limited to):
28    **		syslogins, sysservers, sysattributes, systimeranges,
29    **		sysresourcelimits, sysalternates, sysdatabases,
30    **		syslanguages, sysremotelogins, sysloginroles,
31    **		sysalternates (master DB only), systypes (master DB only),
32    **		sysusers (master DB only), sysprotects (master DB only)
33    **	please refer to the HA documentation for detail.
34    **
35    **	Here is what you need to do: 
36    **	For each insert/update/delete statement, add three sections to
37    **	-- start HA transaction prior to the statement
38    **	-- add the statement
39    **	-- add HA synchronization code to propagate the change to the companion
40    **
41    **	For example, if you are adding 
42    **		insert master.dbo.syslogins ......
43    **	the code should look like:
44    **	1. Now, the SQL statement:
45    **		insert master.dbo.syslogins ......
46    **	2. Add a HA synchronization section right after the SQL statement:
47    **		
48    **
49    **	You may need to do similar change for each built-in function you
50    **	want to add.
51    */
52    
53    create or replace procedure sp_dropremotelogin
54        @remoteserver varchar(255), /* name of remote server */
55        @loginame varchar(255) = NULL, /* user's remote name */
56        @remotename varchar(255) = NULL /* user's local user name */
57    as
58    
59        declare @msg varchar(1024)
60        declare @suid int
61        declare @rname varchar(255)
62        declare @lname varchar(255)
63        declare @rtn_code int
64        declare @rowcount_save int
65        declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
66        declare @retstat int
67        declare @dummy int
68        declare @dbname varchar(255) /* for logexec error message */
69        declare @master_is_rep int /* whether master db is replicated */
70        declare @nullarg char(1)
71        declare @gp_enabled int
72        declare @status1 int
73        declare @status2 int
74    
75        select @status1 = 1
76        select @status2 = 1
77        select @HA_CERTIFIED = 0
78    
79    
80    
81    
82        /*
83        ** If we are logging this system procedure for replication,
84        ** we must be in the 'master' database to avoid creating a
85        ** multi-database transaction which could make recovery of
86        ** the 'master' database impossible.
87        */
88        select @master_is_rep = getdbrepstat(1)
89    
90        if (@master_is_rep < 0)
91        begin
92            /*
93            ** 18409, "The built-in function getdbrepstat() failed."
94            */
95            raiserror 18409, "getdbrepstat"
96            return (1)
97        end
98    
99        select @dbname = db_name()
100   
101       if (@master_is_rep > 0) and (@dbname != "master")
102       begin
103           /*
104           ** 18388, "You must be in the master database in order
105           ** to run '%1!'."
106           */
107           raiserror 18388, "sp_dropremotelogin"
108           return (1)
109       end
110   
111       /*
112       **  If we're in a transaction, disallow this since it might make recovery
113       **  impossible.
114       */
115       if @@trancount > 0
116       begin
117           /*
118           ** 17260, "Can't run %1! from within a transaction." 
119           */
120           raiserror 17260, "sp_dropremotelogin"
121           return (1)
122       end
123       else
124       begin
125           set chained off
126       end
127   
128       set transaction isolation level 1
129   
130       /*
131       **  If granular permissions is not enabled, 
132       **  Accounts with SA or SSO role can execute it.
133       **  If granular permissions is enabled then users with 
134       ** 'manage any remote login' permission can execute it.
135       */
136       select @nullarg = NULL
137       execute @status1 = sp_aux_checkroleperm "sa_role", "manage any remote login",
138           @nullarg, @gp_enabled output
139   
140       if (@gp_enabled = 0)
141       begin
142           if (@status1 != 0)
143           begin
144               execute @status2 = sp_aux_checkroleperm "sso_role",
145                   @nullarg, @nullarg, @gp_enabled output
146   
147               if (@status2 != 0)
148               begin
149                   /* 
150                   ** proc_role() will raise permission errors
151                   ** and send audit records to the audit trail.
152                   */
153                   select @dummy = proc_role("sa_role")
154                   select @dummy = proc_role("sso_role")
155                   return (1)
156               end
157           end
158           /* For Auditing. */
159           if (@status1 = 0)
160               select @dummy = proc_role("sa_role")
161   
162           if (@status2 = 0)
163               select @dummy = proc_role("sso_role")
164       end
165       else
166       begin
167           select @dummy = proc_auditperm("manage any remote login",
168                   @status1)
169           if (@status1 != 0)
170               return 1
171       end
172   
173   
174       /*
175       **  If @loginame is NULL then we want to set @suid = -1. Otherwise get
176       **  it real value.
177       */
178       if @loginame is null
179           select @suid = - 1
180       else select @suid = suser_id(@loginame)
181   
182       /* 
183       ** This transaction also writes a log record for replicating the
184       ** invocation of this procedure. If logexec() fails, the transaction
185       ** is aborted.
186       **
187       ** IMPORTANT: The name rs_logexec is significant and is used by
188       ** Adaptive Server.
189       */
190       begin tran rs_logexec
191   
192   
193   
194       /*
195       **  Delete the remote login.
196       */
197   
198       delete master.dbo.sysremotelogins
199       from master.dbo.sysremotelogins l, master.dbo.sysservers s
200       where l.remoteserverid = s.srvid
201           and s.srvname = @remoteserver
202           and l.remoteusername = @remotename
203           and l.suid = @suid
204   
205       /*
206       ** Check @@rowcount when it works
207       */
208       select @rowcount_save = @@rowcount
209   
210       if (@rowcount_save > 0)
211           select @rtn_code = 0
212       else
213           select @rtn_code = 1
214   
215   
216   
217       if (@rtn_code = 0)
218       begin
219           /*
220           ** 17512, "Remote login dropped."
221           */
222           exec sp_getmessage 17512, @msg output
223           print @msg
224       end
225       else
226       begin
227           /*
228           ** 17513, "There is no remote user '%1!' mapped to local user '%2!' from the remote server '%3!'."
229           */
230           select @rname = isnull(@remotename, "NULL")
231           select @lname = isnull(@loginame, "NULL")
232           raiserror 17513, @rname, @lname, @remoteserver
233   
234           goto clean_all
235       end
236   
237       /*
238       ** log the command for replication support
239       ** except when the server is an HADR member
240       */
241       if (@master_is_rep > 0) and not exists (select *
242               from master.dbo.sysservers
243               where (srvname = @remoteserver or srvname = @remoteserver + "DR")
244                   and srvclass = (select number
245                       from master.dbo.spt_values
246                       where type = 'X' and lower(name) = lower("HADR_MEMBER")))
247       begin
248           if (logexec(1) != 1)
249           begin
250               /*
251               ** , "17869 Stored procedure %1 failed'
252               **       because '%2' failed in database '%3'."
253               */
254               raiserror 17869, "sp_dropremotelogin", "logexec()", @dbname
255               goto clean_all
256           end
257       end
258   
259       commit tran rs_logexec
260   
261       return (0)
262   
263   clean_all:
264       rollback tran rs_logexec
265       return (1)
266   
267   


exec sp_procxmode 'sp_dropremotelogin', 'AnyMode'
go

Grant Execute on sp_dropremotelogin to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysremotelogins master..sysremotelogins
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysremotelogins.csysremotelogins unique clustered
(remoteserverid, remoteusername)
Intersection: {remoteusername}
202
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
246
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 244
 QTYP 4 Comparison type mismatch smallint = int 244
 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..sysremotelogins  
 MGTP 3 Grant to public master..sysservers  
 MGTP 3 Grant to public sybsystemprocs..sp_dropremotelogin  
 MNER 3 No Error Check should check @@error after delete 198
 MNER 3 No Error Check should check return value of exec 222
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 108
 MUCO 3 Useless Code Useless Brackets 121
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 142
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 155
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 162
 MUCO 3 Useless Code Useless Brackets 169
 MUCO 3 Useless Code Useless Brackets 210
 MUCO 3 Useless Code Useless Brackets 217
 MUCO 3 Useless Code Useless Brackets 248
 MUCO 3 Useless Code Useless Brackets 261
 MUCO 3 Useless Code Useless Brackets 265
 QISO 3 Set isolation level 128
 QNAJ 3 Not using ANSI Inner Join 199
 VNRD 3 Variable is not read @HA_CERTIFIED 77
 VNRD 3 Variable is not read @gp_enabled 145
 VNRD 3 Variable is not read @dummy 167
 VUNU 3 Variable is not used @retstat 66
 MSUB 2 Subquery Marker 241
 MSUB 2 Subquery Marker 244
 MTR1 2 Metrics: Comments Ratio Comments: 56% 53
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 16 = 20dec - 6exi + 2 53
 MTR3 2 Metrics: Query Complexity Complexity: 103 53
 PRED_QUERY_COLLECTION 2 {r=master..sysremotelogins, s=master..sysservers} 0 198

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