DatabaseProcApplicationCreatedLinks
sybsystemprocssp_password  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/password */
4     
5     /*
6     ** Generated by spgenmsgs.pl on Thu Feb  2 00:39:18 2006 
7     */
8     /*
9     ** raiserror Messages for password [Total 3]
10    **
11    ** 17260, "Can't run %1! from within a transaction."
12    ** 17720, "Error:  Unable to set the Password."
13    ** 17756, "The execution of the stored procedure '%1!' in database '%2!' was aborted because there was an error in writing the replication log record."
14    */
15    /*
16    ** sp_getmessage Messages for password [Total 1]
17    **
18    ** 17721, "Password correctly set."
19    */
20    /*
21    ** End spgenmsgs.pl output.
22    */
23    
24    /* 
25    ** IMPORTANT: Please read the following instructions before
26    **   making changes to this stored procedure.
27    **
28    **	To make this stored procedure compatible with High Availability (HA),
29    **	changes to certain system tables must be propagated 
30    **	to the companion server under some conditions.
31    **	The tables include (but are not limited to):
32    **		syslogins, sysservers, sysattributes, systimeranges,
33    **		sysresourcelimits, sysalternates, sysdatabases,
34    **		syslanguages, sysremotelogins, sysloginroles,
35    **		sysalternates (master DB only), systypes (master DB only),
36    **		sysusers (master DB only), sysprotects (master DB only)
37    **	please refer to the HA documentation for detail.
38    **
39    **	Here is what you need to do: 
40    **	For each insert/update/delete statement, add three sections to
41    **	-- start HA transaction prior to the statement
42    **	-- add the statement
43    **	-- add HA synchronization code to propagate the change to the companion
44    **
45    **	For example, if you are adding 
46    **		insert master.dbo.syslogins ......
47    **	the code should look like:
48    **	1. Before that SQL statement:
49    **		
50    **	2. Now, the SQL statement:
51    **		insert master.dbo.syslogins ......
52    **	3. Add a HA synchronization section right after the SQL statement:
53    **		
54    **
55    **	You may need to do similar change for each built-in function you
56    **	want to add.
57    **
58    **	Finally, add a separate part at a place where it can not
59    **	be reached by the normal execution path:
60    **	clean_all:
61    **		
62    **		return (1)
63    */
64    
65    create or replace procedure sp_password
66        @caller_password varchar(255) = NULL, /* the current password of caller */
67        @new_password varchar(256) = NULL, /* the new password of the target acct*/
68        /* a length of 256 is required to test if
69        ** user entered a passwd > 255 chars.
70        */
71        @loginame varchar(255) = NULL, /* user to change password on */
72        @immediate int = 0 /* if not 0, change the password in
73    					** all running processes for loginame.
74    					*/
75    as
76    
77        declare @returncode int
78        declare @msg varchar(1024)
79        declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
80        declare @retstat int
81        declare @nullarg char(1)
82        declare @dummy int
83        declare @status int
84        declare @gp_enabled int
85    
86    
87    
88        select @HA_CERTIFIED = 0
89    
90    
91    
92    
93        /* check to see if we are using HA specific SP for a HA enabled server */
94        exec @retstat = sp_ha_check_certified 'sp_password', @HA_CERTIFIED
95        if (@retstat != 0)
96            return (1)
97    
98        /*
99        ** Do not allow this system procedure to be run from within a transaction
100       ** to avoid creating a multi-database transaction where the 'master'
101       ** database is not the co-ordinating database.
102       */
103       if @@trancount > 0
104       begin
105           /*
106           ** 17260, "Can't run %1! from within a transaction."
107           */
108           raiserror 17260, "sp_password"
109           return (1)
110       end
111       else
112       begin
113           set chained off
114       end
115   
116       set transaction isolation level 1
117   
118       if ((@loginame is not NULL) and (suser_id(@loginame) != suser_id()))
119       begin
120           /* 
121           ** If granular permissions is not enabled then sso_role is required.
122           ** If granular permissions is enabled then the permission 'change  
123           ** password' is required.  proc_role and proc_auditperm will also do 
124           ** auditing if required. Both will also print error message if required.
125           */
126   
127           select @nullarg = NULL
128           execute @status = sp_aux_checkroleperm "sso_role",
129               @nullarg, @nullarg, @gp_enabled output
130   
131           /* For Auditing */
132           if (@gp_enabled = 0)
133           begin
134               if (proc_role("sso_role") = 0)
135                   return (1)
136           end
137           if (@status != 0)
138           begin
139               return (1)
140           end
141       end
142   
143       /*
144       **  Encrypt and store the input @new_password.
145       **  @caller_password will be checked against the password of the caller.
146       **  set_password() builtin will print out nice messages.
147       */
148       select @returncode = set_password(@caller_password, @new_password, @loginame, @immediate)
149   
150   
151   
152   
153       if (@returncode = 0)
154       begin
155           /*
156           ** 17720, "Error:  Unable to set the Password."
157           */
158           raiserror 17720
159           return (1)
160       end
161       else
162       begin
163           /*
164           ** Before we log our system procedure execution instance,
165           ** re-initialize the '@caller_password' parameter to NULL and the
166           ** '@new_password' parameter to the encrypted form of the password.
167           ** This prevents the passwords from being stored in clear text in
168           ** the transaction log as well as in the Replication Server stable
169           ** queues.
170           **
171           ** When the ASE RepAgent Thread sends the system procedure
172           ** execution instance to the Replication Server, the ASE RepAgent
173           ** will re-name the system procedure from 'sp_password()' to
174           ** 'sp_password_rep()'.  This will cause the Replication Server to
175           ** execute, at the target ASE, the system procedure
176           ** 'sp_password_rep()' which knows how to properly process the
177           ** encrypted password.
178           */
179           select @caller_password = NULL
180   
181           if (@loginame is not NULL)
182           begin
183               select @new_password = password
184               from master.dbo.syslogins
185               where name = @loginame
186           end
187           else
188           begin
189               select @new_password = password
190               from master.dbo.syslogins
191               where suid = suser_id()
192           end
193   
194           /*
195           ** If the 'master' database is marked for replication, the T-SQL
196           ** built-in 'logexec()' will log for replication the execution
197           ** instance of this system procedure.  Otherwise, the T-SQL
198           ** built-in 'logexec()' is a no-op.
199           */
200           if (logexec(1) != 1)
201           begin
202               raiserror 17756, "sp_password", "master"
203               return (1)
204           end
205   
206           /*
207           ** 17721, "Password correctly set."
208           */
209           exec sp_getmessage 17721, @msg output
210           print @msg
211           return (0)
212       end
213   
214   


exec sp_procxmode 'sp_password', 'AnyMode'
go

Grant Execute on sp_password to public
go
DEFECTS
 MGTP 3 Grant to public master..syslogins  
 MGTP 3 Grant to public sybsystemprocs..sp_password  
 MNER 3 No Error Check should check return value of exec 128
 MNER 3 No Error Check should check return value of exec 209
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 134
 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 153
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 181
 MUCO 3 Useless Code Useless Brackets 200
 MUCO 3 Useless Code Useless Brackets 203
 MUCO 3 Useless Code Useless Brackets 211
 QISO 3 Set isolation level 116
 VNRD 3 Variable is not read @caller_password 179
 VNRD 3 Variable is not read @new_password 189
 VUNU 3 Variable is not used @dummy 82
 MTR1 2 Metrics: Comments Ratio Comments: 71% 65
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 6 = 11dec - 7exi + 2 65
 MTR3 2 Metrics: Query Complexity Complexity: 54 65

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

CALLERS
called by proc sybsystemprocs..sp_addlogin