DatabaseProcApplicationCreatedLinks
sybsystemprocssp_fix_dtm_ha_roles  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Messages for "sp_fix_dtm_ha_roles"          18040
4     **
5     ** 18040, "Catalog procedure '%1!' can not be run in a transaction.
6     **
7     ** dtm_tm_role and ha_role metadata is not added to the
8     ** system catalog as part of an upgrade from 11.5.x or 11.9.x to 12.0.
9     ** This stored procedure can be used to fix these catalogs if a
10    ** customer has already upgraded from 11.5.x or 11.9.x to 12.0.
11    **
12    ** NOTE that an upgrade from 11.0.x to 12.0 does not require this
13    ** stored procedure to be executed nor for an upgrade from
14    ** 11.5.x or 11.9.x to a version that is greater than or equal
15    ** to 12.0.0.1 ESD#1.
16    */
17    create or replace procedure sp_fix_dtm_ha_roles
18    as
19        declare @sqlstr varchar(255)
20        declare @dbname varchar(30)
21        declare @rid int
22        declare @failure int
23        declare @msg varchar(255)
24        declare @nullarg char(1)
25        declare @dummy int
26        declare @status int
27        declare @gp_enabled int
28    
29    
30    
31        if (@@trancount > 0)
32        begin
33            /*
34            ** 18040, "Catalog procedure '%1!' can not be run in a transaction.
35            */
36            raiserror 18040, "sp_fix_dtm_ha_roles"
37            return (1)
38        end
39    
40        /* 
41        ** If granular permissions is not enabled then sa_role is required.
42        ** If granular permissions is enabled then the permission 'manage roles' is
43        ** required.  proc_role and proc_auditperm will also do auditing
44        ** if required. Both will also print error message if required.
45        */
46    
47        select @nullarg = NULL
48        execute @status = sp_aux_checkroleperm "sa_role", "manage roles",
49            @nullarg, @gp_enabled output
50    
51        /* For Auditing */
52        if (@gp_enabled = 0)
53        begin
54            if (proc_role("sa_role") = 0)
55                return (1)
56        end
57        else
58        begin
59            select @dummy = proc_auditperm("manage roles", @status)
60        end
61    
62        if (@status != 0)
63            return (1)
64    
65        set transaction isolation level 1
66        set chained off
67    
68        /* In case these roles are not in syssrvroles */
69    
70        if not exists (select 1 from master.dbo.syssrvroles where srid = 7)
71        begin
72            insert into master.dbo.syssrvroles(srid, name, password)
73            values (7, 'dtm_tm_role', NULL)
74        end
75    
76        if not exists (select 1 from master.dbo.syssrvroles where srid = 8)
77        begin
78            insert into master.dbo.syssrvroles(srid, name, password)
79            values (8, 'ha_role', NULL)
80        end
81    
82        /*
83        ** Inserts an attribute that holds class id and the password expiration
84        ** for dtm_tm_role and ha_role.
85        */
86        if not exists (select * from master.dbo.sysattributes
87                where class = 14 and object_type = 'PS' and
88                    object_cinfo = 'role' and object = 7)
89        begin
90            insert master.dbo.sysattributes
91            (class, attribute, object_type, object_cinfo, object, int_value)
92            values (14, 0, 'PS', 'role', 7, 0)
93        end
94    
95        if not exists (select * from master.dbo.sysattributes
96                where class = 14 and object_type = 'PS' and
97                    object_cinfo = 'role' and object = 8)
98        begin
99            insert master.dbo.sysattributes
100           (class, attribute, object_type, object_cinfo, object, int_value)
101           values (14, 0, 'PS', 'role', 8, 0)
102       end
103   
104       return (0)
105   
106   


exec sp_procxmode 'sp_fix_dtm_ha_roles', 'AnyMode'
go

Grant Execute on sp_fix_dtm_ha_roles to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 87
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 96
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_fix_dtm_ha_roles  
 MNER 3 No Error Check should check @@error after insert 72
 MNER 3 No Error Check should check @@error after insert 78
 MNER 3 No Error Check should check @@error after insert 90
 MNER 3 No Error Check should check @@error after insert 99
 MUCO 3 Useless Code Useless Brackets 31
 MUCO 3 Useless Code Useless Brackets 37
 MUCO 3 Useless Code Useless Brackets 52
 MUCO 3 Useless Code Useless Brackets 54
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 62
 MUCO 3 Useless Code Useless Brackets 63
 MUCO 3 Useless Code Useless Brackets 104
 MUOT 3 Updates outside transaction 99
 QISO 3 Set isolation level 65
 QIWC 3 Insert with not all columns specified missing 6 columns out of 9 72
 QIWC 3 Insert with not all columns specified missing 6 columns out of 9 78
 QIWC 3 Insert with not all columns specified missing 9 columns out of 15 91
 QIWC 3 Insert with not all columns specified missing 9 columns out of 15 100
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, object_cinfo, class}
87
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, object_cinfo, class}
96
 VNRD 3 Variable is not read @dummy 59
 VUNU 3 Variable is not used @sqlstr 19
 VUNU 3 Variable is not used @dbname 20
 VUNU 3 Variable is not used @rid 21
 VUNU 3 Variable is not used @failure 22
 VUNU 3 Variable is not used @msg 23
 MSUB 2 Subquery Marker 70
 MSUB 2 Subquery Marker 76
 MSUB 2 Subquery Marker 86
 MSUB 2 Subquery Marker 95
 MTR1 2 Metrics: Comments Ratio Comments: 36% 17
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 14 = 15dec - 3exi + 2 17
 MTR3 2 Metrics: Query Complexity Complexity: 58 17

DEPENDENCIES
PROCS AND TABLES USED
read_writes table master..syssrvroles (1)  
read_writes table master..sysattributes (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)