DatabaseProcApplicationCreatedLinks
sybsystemprocssp_ssladmin  31 Aug 14Defects 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 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    
23        /*
24        ** Establish a version to pass to built-in function.
25        */
26        declare @vers int
27        select @vers = 0
28    
29        /*
30        **  If we're in a transaction, disallow this since it might make recovery
31        **  impossible.
32        */
33        if @@trancount > 0
34        begin
35            /*
36            ** 17260, "Can't run %1! from within a transaction."
37            */
38            raiserror 17260, "sp_ssladmin"
39            return (1)
40        end
41        else
42        begin
43            set chained off
44        end
45        set transaction isolation level 1
46    
47        /* 
48        **  Check if user has sso role.  proc_role() will do auditing
49        **  if required and will also print error message if required.
50        */
51        if (proc_role("sso_role") = 0)
52            return (1)
53    
54    
55        /*
56        **  Begin command processing.
57        */
58    
59        if (@cmd = "addcert") or (@cmd = "dropcert") or (@cmd = "lscert")
60            or (@cmd = "lscipher") or (@cmd = "setcipher")
61        begin
62    
63            /* Process lscert in sproc instead of builtin */
64            if (@cmd = "lscert")
65            begin
66                select certificate_path = comments from master.dbo.sysattributes
67                where class = 15
68    
69                select @retval = @@error
70            end
71            else
72            begin
73                /* Process parts of cipher commands in sproc */
74                if (@cmd = "lscipher") or (@cmd = "setcipher")
75                begin
76    
77                    select @retval = ssl_admin(@cmd, @opt1, @opt2, @vers)
78                    if (@retval = 0)
79                    begin
80                        /* 
81                        ** attrib is the cipher id
82                        ** object is the default order
83                        ** int_value is the preferred order
84                        ** char_value is the cipher name
85                        */
86                        if (@cmd = "lscipher")
87                        begin
88                            select
89                                'Cipher Suite Name' = convert(varchar(64), char_value),
90                                'Preference' = int_value
91                            from sysattributes
92                            where class = 24 and object > 0
93                            order by object
94                        end
95                        else
96                        begin /* "setcipher" */
97                            /* RESOLVE: add new sproc messages */
98                            print "The following cipher suites and order of preference are set for SSL connections:"
99                            select
100                               'Cipher Suite Name' = convert(varchar(64), char_value),
101                               'Preference' = int_value
102                           from sysattributes
103                           where class = 24 and int_value > 0
104                           order by int_value
105                       end
106                   end
107   
108               end
109               else
110               begin
111                   select @retval = ssl_admin(@cmd, @opt1, @opt2, @vers)
112               end
113           end
114   
115           if (@retval != 0)
116           begin
117               return (1)
118           end
119       end
120       else
121       begin
122           print "sp_ssladmin Usage: sp_ssladmin command [, option1 [, option2]]"
123           print "sp_ssladmin commands:"
124           print "sp_ssladmin 'addcert', 'certificatepath', 'password'"
125           print "sp_ssladmin 'dropcert', 'certificatepath'"
126           print "sp_ssladmin 'lscert'"
127           print "sp_ssladmin 'setcipher', { 'FIPS' | 'Strong' | 'Weak' | 'All' | quoted_list_of_ciphersuites "
128           print "sp_ssladmin 'lscipher'"
129           print "sp_ssladmin 'help'"
130       end
131   
132   
133   
134       return (0)
135   


exec sp_procxmode 'sp_ssladmin', 'AnyMode'
go

Grant Execute on sp_ssladmin to sso_role
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 67
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 92
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 103
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MUCO 3 Useless Code Useless Brackets 39
 MUCO 3 Useless Code Useless Brackets 51
 MUCO 3 Useless Code Useless Brackets 52
 MUCO 3 Useless Code Useless Brackets 64
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 86
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 117
 MUCO 3 Useless Code Useless Brackets 134
 QCRS 3 Conditional Result Set 66
 QCRS 3 Conditional Result Set 88
 QCRS 3 Conditional Result Set 99
 QISO 3 Set isolation level 45
 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}		 67
 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}		 92
 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}		 103
 VUNU 3 Variable is not used @msg 21
 MRST 2 Result Set Marker 66
 MRST 2 Result Set Marker 88
 MRST 2 Result Set Marker 99
 MTR1 2 Metrics: Comments Ratio Comments: 29% 14
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 12 = 13dec - 3exi + 2 14
 MTR3 2 Metrics: Query Complexity Complexity: 56 14

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysattributes (1)  
reads table sybsystemprocs..sysattributes