DatabaseProcApplicationCreatedLinks
sybsystemprocssp_update_authmech_value  14 déc. 14Defects Propagation 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 or replace 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        /* variables for granular permission permission checking */
37        declare @nullarg char(1),
38            @dummy int,
39            @status int,
40            @gp_enabled int
41    
42    
43    
44        select @HA_CERTIFIED = 0
45    
46    
47    
48        /* check to see if we are using HA specific SP for a HA enabled server */
49        exec @retstat = sp_ha_check_certified 'sp_update_authmech_value',
50            @HA_CERTIFIED
51        if (@retstat != 0)
52            return (1)
53    
54        /*
55        ** Do not allow this system procedure to be run from within a transaction
56        ** to avoid creating a multi-database transaction where the 'master'
57        ** database is not the co-ordinating database.
58        */
59        if @@trancount > 0
60        begin
61            /* 17260, "Can't run %1! from within a transaction." */
62            raiserror 17260, "sp_update_authmech_value"
63            return (1)
64        end
65        else
66        begin
67            set chained off
68        end
69    
70        set transaction isolation level 1
71    
72        /*
73        ** Check for the version of ASE on which this procedure is getting executed.
74        ** Procedure should be executed only on 12.5.4 or 15.0.2 servers where an
75        ** update to status is required.
76        */
77        if (((@@version_as_integer = 12500) AND (@@version_number < 12540)) OR
78                ((@@version_as_integer = 15000) AND (@@version_number < 15020)) OR
79                (@@version_as_integer < 12500))
80        begin
81            /*
82            ** 19882, "sp_update_authmech_value should not be executed on this
83            ** release of Adaptive Server, no changes made."
84            */
85            raiserror 19882
86            return (1)
87        end
88    
89        /* 
90        ** If granular permissions is not enabled then sso_role is required.
91        ** If granular permissions is enabled then the permission 'manage any login' is
92        ** required.  proc_role and proc_auditperm will also do auditing
93        ** if required. Both will also print error message if required.
94        */
95    
96        select @nullarg = NULL
97        execute @status = sp_aux_checkroleperm "sso_role", "manage any login",
98            @nullarg, @gp_enabled output
99    
100       /* For Auditing */
101       if (@gp_enabled = 0)
102       begin
103           if (proc_role("sso_role") = 0)
104               return (1)
105       end
106       else
107       begin
108           select @dummy = proc_auditperm("manage any login", @status)
109       end
110   
111       if (@status != 0)
112           return (1)
113   
114   
115   
116       /* Obtain the authentication mask value from master.dbo.spt_values */
117       select @auth_mask = low from master.dbo.spt_values
118       where type = 'ua' and name = 'AUTH_MASK'
119   
120       /* Obtain bitmask for ASE, LDAP, PAM bits */
121       select @auth_1253_all = (select low from master.dbo.spt_values
122               where name = 'ASE') |
123               (select low from master.dbo.spt_values
124               where name = 'LDAP') |
125               (select low from master.dbo.spt_values
126               where name = 'PAM')
127   
128       /*
129       ** Update all the master.dbo.syslogins row to unset the authentication
130       ** mechanism bits where the status has value of 224 i.e ASE, LDAP and PAM
131       ** bits set which indicated 'ANY' authentication mechanism in pre 12.5.4
132       ** versions.
133       */
134       update master.dbo.syslogins set status = status & ~ @auth_mask
135       where ((status & @auth_1253_all) = @auth_1253_all)
136   
137       select @prisavedupdatecount = @@rowcount
138   
139   
140       return (0)
141   
142   clean_all:
143   
144       return (1)
145   


exec sp_procxmode 'sp_update_authmech_value', 'AnyMode'
go

Grant Execute on sp_update_authmech_value to public
go
DEFECTS
 MURC 6 Unreachable Code 142
 MURC 6 Unreachable Code 144
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
118
 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 134
 MUCO 3 Useless Code Useless Brackets 51
 MUCO 3 Useless Code Useless Brackets 52
 MUCO 3 Useless Code Useless Brackets 63
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 86
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 104
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 112
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 144
 QAFM 3 Var Assignment from potentially many rows 117
 QISO 3 Set isolation level 70
 VNRD 3 Variable is not read @dummy 108
 VNRD 3 Variable is not read @prisavedupdatecount 137
 VUNU 3 Variable is not used @ase_version 26
 VUNU 3 Variable is not used @msg 31
 MSUB 2 Subquery Marker 121
 MSUB 2 Subquery Marker 123
 MSUB 2 Subquery Marker 125
 MTR1 2 Metrics: Comments Ratio Comments: 56% 24
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 10 = 12dec - 4exi + 2 24
 MTR3 2 Metrics: Query Complexity Complexity: 51 24

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