DatabaseProcApplicationCreatedLinks
sybsystemprocssp_ssladmin  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /* 
4     ** file: ssladmin 
5     ** Administer SSL Certificates
6     */
7     
8     /*
9     ** Messages for "sp_ssladmin"
10    **
11    ** 17260, "Can't run %1! from within a transaction."
12    ** 
13    */
14    create or replace procedure sp_ssladmin
15        @cmd varchar(30) = null,
16        @opt1 varchar(16384) = null,
17        @opt2 varchar(64) = null
18    as
19    
20        declare @retval int /* return value from ssl_admin() built-in function */
21        declare @msg varchar(255) /* message variable */
22        declare @nullarg char(1)
23        declare @dummy int
24        declare @status int
25        declare @gp_enabled int
26    
27        /*
28        ** Establish a version to pass to built-in function.
29        */
30        declare @vers int
31        select @vers = 0
32    
33        /*
34        **  If we're in a transaction, disallow this since it might make recovery
35        **  impossible.
36        */
37        if @@trancount > 0
38        begin
39            /*
40            ** 17260, "Can't run %1! from within a transaction."
41            */
42            raiserror 17260, "sp_ssladmin"
43            return (1)
44        end
45        else
46        begin
47            set chained off
48        end
49        set transaction isolation level 1
50    
51        /* 
52        ** If granular permissions is not enabled then sso_role is required.
53        ** If granular permissions is enabled then the permission 'manage security configuration' is
54        ** required.  proc_role and proc_auditperm will also do auditing
55        ** if required. Both will also print error message if required.
56        */
57    
58        select @nullarg = NULL
59        execute @status = sp_aux_checkroleperm "sso_role",
60            "manage security configuration", @nullarg, @gp_enabled output
61    
62        /* For Auditing */
63        if (@gp_enabled = 0)
64        begin
65            if (proc_role("sso_role") = 0)
66                return (1)
67        end
68        else
69        begin
70            select @dummy = proc_auditperm("manage security configuration", @status)
71        end
72    
73        if (@status != 0)
74            return (1)
75    
76        /*
77        **  Begin command processing.
78        */
79    
80        if (@cmd = "addcert") or (@cmd = "dropcert") or (@cmd = "lscert")
81            or (@cmd = "lscipher") or (@cmd = "setcipher")
82        begin
83    
84            /* Process lscert in sproc instead of builtin */
85            if (@cmd = "lscert")
86            begin
87                select certificate_path = comments from master.dbo.sysattributes
88                where class = 15
89    
90                select @retval = @@error
91            end
92            else
93            begin
94                /* Process parts of cipher commands in sproc */
95                if (@cmd = "lscipher") or (@cmd = "setcipher")
96                begin
97    
98                    select @retval = ssl_admin(@cmd, @opt1, @opt2, @vers)
99                    if (@retval = 0)
100                   begin
101                       /* 
102                       ** attrib is the cipher id
103                       ** object is the default order
104                       ** int_value is the preferred order
105                       ** char_value is the cipher name
106                       */
107                       if (@cmd = "lscipher")
108                       begin
109                           select
110                               'Cipher Suite Name' = convert(varchar(64), char_value),
111                               'Preference' = int_value
112                           from sysattributes
113                           where class = 24 and object > 0
114                           order by object
115                       end
116                       else
117                       begin /* "setcipher" */
118                           /* RESOLVE: add new sproc messages */
119                           print "The following cipher suites and order of preference are set for SSL connections:"
120                           select
121                               'Cipher Suite Name' = convert(varchar(64), char_value),
122                               'Preference' = int_value
123                           from sysattributes
124                           where class = 24 and int_value > 0
125                           order by int_value
126                       end
127                   end
128   
129               end
130               else
131               begin
132                   select @retval = ssl_admin(@cmd, @opt1, @opt2, @vers)
133               end
134           end
135   
136           if (@retval != 0)
137           begin
138               return (1)
139           end
140       end
141       else
142       begin
143           print "sp_ssladmin Usage: sp_ssladmin command [, option1 [, option2]]"
144           print "sp_ssladmin commands:"
145           print "sp_ssladmin 'addcert', 'certificatepath', 'password'"
146           print "sp_ssladmin 'dropcert', 'certificatepath'"
147           print "sp_ssladmin 'lscert'"
148           print "sp_ssladmin 'setcipher', { 'FIPS' | 'Strong' | 'Weak' | 'All' | quoted_list_of_ciphersuites "
149           print "sp_ssladmin 'lscipher'"
150           print "sp_ssladmin 'help'"
151       end
152   
153   
154   
155       return (0)
156   


exec sp_procxmode 'sp_ssladmin', 'AnyMode'
go

Grant Execute on sp_ssladmin to public
go
RESULT SETS
sp_ssladmin_rset_003
sp_ssladmin_rset_002
sp_ssladmin_rset_001

DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes sybsystemprocs..sysattributes
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 88
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 113
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 124
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sp_ssladmin  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MUCO 3 Useless Code Useless Brackets 43
 MUCO 3 Useless Code Useless Brackets 63
 MUCO 3 Useless Code Useless Brackets 65
 MUCO 3 Useless Code Useless Brackets 66
 MUCO 3 Useless Code Useless Brackets 73
 MUCO 3 Useless Code Useless Brackets 74
 MUCO 3 Useless Code Useless Brackets 85
 MUCO 3 Useless Code Useless Brackets 99
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 136
 MUCO 3 Useless Code Useless Brackets 138
 MUCO 3 Useless Code Useless Brackets 155
 QCRS 3 Conditional Result Set 87
 QCRS 3 Conditional Result Set 109
 QCRS 3 Conditional Result Set 120
 QISO 3 Set isolation level 49
 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: {class}
88
 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: {class, object}
113
 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: {class}
124
 VNRD 3 Variable is not read @dummy 70
 VUNU 3 Variable is not used @msg 21
 MRST 2 Result Set Marker 87
 MRST 2 Result Set Marker 109
 MRST 2 Result Set Marker 120
 MTR1 2 Metrics: Comments Ratio Comments: 30% 14
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 14 = 16dec - 4exi + 2 14
 MTR3 2 Metrics: Query Complexity Complexity: 68 14

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sp_ssladmin_rset_002 
reads table master..sysattributes (1)  
reads table sybsystemprocs..sysattributes  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
writes table sybsystemprocs..sp_ssladmin_rset_003 
writes table sybsystemprocs..sp_ssladmin_rset_001