DatabaseProcApplicationCreatedLinks
sybsystemprocssp_update_authmech_value  31 Aug 14Defects Dependencies

1     
2     
3     /*
4     ** This stored procedure is used to update the syslogins.status from 224 or 
5     ** 480 to 0 for logins upgraded from pre 12.5.4 to 12.5.4 or 15.0.2.
6     ** Logins created on pre 12.5.4 code had the status equal to 224 which
7     ** indicated 'ANY' authentication. 224 is formed by ASE, LDAP and PAM bits,
8     ** viz. 32, 64, 128 respectively.
9     ** From 12.5.4 onwards, KERBEROS was also included in this status. So,
10    ** effectively, the value that would indicate 'ANY' became 32+64+128+256 = 480
11    ** If now the ASE is upgraded to 12.5.4 or 15.0.2, the new ASE will compare
12    ** it with 480 instead of 224 to check if the authentication is 'ANY'.
13    ** In case of KERBEROS authentication, new ASE server was looking for status
14    ** as 256 for KERBEROS logins, which lead to their failure. In order to avoid
15    ** this, this procedure will update the status bits appropriately.
16    */
17    /*
18    ** Messages for update_authmech_value [Total 3]
19    ** 17260, "Can't run %1! from within a transaction."
20    ** 19882, "sp_update_authmech_value should not be executed on this release of Adaptive Server, no changes made."
21    ** 19883, "Updated authentication mechanism for %1! row(s) on %2! server."
22    */
23    
24    create procedure sp_update_authmech_value
25    as
26        declare @ase_version char(10), /* Indicate current ASE version */
27            @HA_CERTIFIED tinyint, /* Is the SP HA certified ? */
28            @retstat int,
29            @auth_mask int, /* Authentication Mask */
30            @auth_1253_all int, /* ASE, LDAP and PAM bit mask */
31            @msg varchar(1024),
32            @prisavedupdatecount int /* Number of rows updated on
33        ** primary server
34        */
35    
36        select @HA_CERTIFIED = 0
37    
38    
39    
40        /* check to see if we are using HA specific SP for a HA enabled server */
41        exec @retstat = sp_ha_check_certified 'sp_update_authmech_value',
42            @HA_CERTIFIED
43        if (@retstat != 0)
44            return (1)
45    
46        /*
47        ** Do not allow this system procedure to be run from within a transaction
48        ** to avoid creating a multi-database transaction where the 'master'
49        ** database is not the co-ordinating database.
50        */
51        if @@trancount > 0
52        begin
53            /* 17260, "Can't run %1! from within a transaction." */
54            raiserror 17260, "sp_update_authmech_value"
55            return (1)
56        end
57        else
58        begin
59            set chained off
60        end
61    
62        set transaction isolation level 1
63    
64        /*
65        ** Check for the version of ASE on which this procedure is getting executed.
66        ** Procedure should be executed only on 12.5.4 or 15.0.2 servers where an
67        ** update to status is required.
68        */
69        if (((@@version_as_integer = 12500) AND (@@version_number < 12540)) OR
70                ((@@version_as_integer = 15000) AND (@@version_number < 15020)) OR
71                (@@version_as_integer < 12500))
72        begin
73            /*
74            ** 19882, "sp_update_authmech_value should not be executed on this
75            ** release of Adaptive Server, no changes made."
76            */
77            raiserror 19882
78            return (1)
79        end
80    
81        /*
82        ** Check if user has sso role, proc_role will also do auditing
83        ** if required. proc_role will also print error message if required.
84        */
85        if (proc_role("sso_role") = 0)
86            return (1)
87    
88    
89    
90        /* Obtain the authentication mask value from master.dbo.spt_values */
91        select @auth_mask = low from master.dbo.spt_values
92        where type = 'ua' and name = 'AUTH_MASK'
93    
94        /* Obtain bitmask for ASE, LDAP, PAM bits */
95        select @auth_1253_all = (select low from master.dbo.spt_values
96                where name = 'ASE') |
97                (select low from master.dbo.spt_values
98                where name = 'LDAP') |
99                (select low from master.dbo.spt_values
100               where name = 'PAM')
101   
102       /*
103       ** Update all the master.dbo.syslogins row to unset the authentication
104       ** mechanism bits where the status has value of 224 i.e ASE, LDAP and PAM
105       ** bits set which indicated 'ANY' authentication mechanism in pre 12.5.4
106       ** versions.
107       */
108       update master.dbo.syslogins set status = status & ~ @auth_mask
109       where ((status & @auth_1253_all) = @auth_1253_all)
110   
111       select @prisavedupdatecount = @@rowcount
112   
113   
114       return (0)
115   
116   clean_all:
117   
118       return (1)
119   


exec sp_procxmode 'sp_update_authmech_value', 'AnyMode'
go

Grant Execute on sp_update_authmech_value to public
go
DEFECTS
 MURC 6 Unreachable Code 116
 MURC 6 Unreachable Code 118
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}		 92
 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..syslogins  
 MGTP 3 Grant to public sybsystemprocs..sp_update_authmech_value  
 MNER 3 No Error Check should check @@error after update 108
 MUCO 3 Useless Code Useless Brackets 43
 MUCO 3 Useless Code Useless Brackets 44
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 85
 MUCO 3 Useless Code Useless Brackets 86
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 118
 QAFM 3 Var Assignment from potentially many rows 91
 QISO 3 Set isolation level 62
 VNRD 3 Variable is not read @prisavedupdatecount 111
 VUNU 3 Variable is not used @ase_version 26
 VUNU 3 Variable is not used @msg 31
 MSUB 2 Subquery Marker 95
 MSUB 2 Subquery Marker 97
 MSUB 2 Subquery Marker 99
 MTR1 2 Metrics: Comments Ratio Comments: 59% 24
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 8 = 9dec - 3exi + 2 24
 MTR3 2 Metrics: Query Complexity Complexity: 42 24

DEPENDENCIES
PROCS AND TABLES USED
writes table master..syslogins (1)  
reads table master..spt_values (1)  
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)