DatabaseProcApplicationCreatedLinks
sybsystemprocssp_aux_encrkey_info  31 Aug 14Defects Dependencies

1     
2     
3     create procedure sp_aux_encrkey_info
4         @owner varchar(30),
5         @keyname varchar(255)
6     as
7         declare @random_pad int
8             , @init_vector int
9             , @system_password int
10            , @login_access int
11            , @login_password int
12            , @user_password int
13            , @aes int
14            , @default_key int
15            , @key_recovery int
16            , @status_master_encr int
17            , @status_dualcontrol int
18            , @status_mek_encr int
19            , @status_static_encr int
20            , @dbname varchar(30)
21            , @keyname_buf varchar(575)
22        begin
23            /* These bits are defined in encryptkey.h */
24            select @random_pad = 2 /* EK_RANDOMPAD */
25                , @init_vector = 1 /* EK_INITVECTOR */
26                , @login_password = 16 /* EK_LOGINPASS */
27                , @login_access = 8 /* EK_LOGINACCESS */
28                , @system_password = 32 /* EK_SYSENCRPASS */
29                , @user_password = 256 /* EK_USERPWD */
30                , @aes = 1 /* AES algorithm */
31                , @default_key = 4 /* EK_DEFAULT */
32                , @key_recovery = 64 /* EK_KEYRECOVERY */
33                , @status_master_encr = 2048 /* EK_MASTER_ENCR */
34                , @status_dualcontrol = 4096 /* EK_DUALCONTROL */
35                , @status_mek_encr = 8192 /* EK_MEK_ENCR */
36                , @status_static_encr = 16384 /* EK_STATIC_ENCR */
37    
38    
39            begin --{
40                select @dbname = db_name()
41                select @keyname_buf = @keyname
42                select @keyname = @keyname_buf
43    
44                insert #encr_column_info(keyname, keyowner, keydbname, keylength,
45                    keyalgorithm, keytype, random_pad, init_vector,
46                    protectedby, keyrecovery)
47                select
48                    o1.name
49                    , user_name(o1.uid), @dbname, e1.eklen
50                    , case (e1.ekalgorithm & @aes)
51                        when 0 then "RSA"
52                        else "AES"
53                    end
54                    , e1.type
55                    , case (e1.status & @random_pad)
56                        when 0 then 0
57                        else 1
58                    end
59                    , case (e1.status & @init_vector)
60                        when 0 then 0
61                        else 1
62                    end
63                    , case (e1.status & (@status_dualcontrol
64                    | @status_master_encr | @login_access
65                    | @login_password | @system_password
66                    | @user_password | @status_mek_encr
67                    | @status_static_encr))
68                        when (@login_access | @system_password) then "login access"
69                        when (@login_access | @status_master_encr) then "login access"
70                        when @login_password then "login password"
71                        when @system_password then "system encryption password"
72                        when @user_password then "user password"
73                        when @status_master_encr then "master key"
74                        when (@status_dualcontrol | @user_password) then
75                        "dual_control(master key + user password)"
76                        when (@status_dualcontrol | @login_access | @status_master_encr) then
77                        "dual_control(login access)"
78                        when (@status_dualcontrol | @login_password) then
79                        "dual_control(master key + login password)"
80                        when (@status_dualcontrol | @status_master_encr) then
81                        "dual_control(master key + dual master key)"
82                        when @status_mek_encr then "automatic startup key"
83                        when @status_static_encr then "static key"
84                    end
85                    , case (e1.type & @key_recovery)
86                        when 0 then 0
87                        else 1
88                    end
89                from sysobjects o1, sysencryptkeys e1
90                where o1.id = e1.id
91                    and o1.type = "EK"
92                    and o1.name like @keyname
93                    and user_name(o1.uid) like @owner
94            end --}
95    
96        end
97    


exec sp_procxmode 'sp_aux_encrkey_info', 'AnyMode'
go

Grant Execute on sp_aux_encrkey_info to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysencryptkeys sybsystemprocs..sysencryptkeys
 MTYP 4 Assignment type mismatch @keyname: varchar(255) = varchar(575) 42
 MGTP 3 Grant to public sybsystemprocs..sp_aux_encrkey_info  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check @@error after insert 44
 MUCO 3 Useless Code Useless Begin-End Pair 22
 MUCO 3 Useless Code Useless Begin-End Pair 39
 QNAJ 3 Not using ANSI Inner Join 89
 VNRD 3 Variable is not read @default_key 31
 MTR1 2 Metrics: Comments Ratio Comments: 7% 3
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 1 = 0dec - 1exi + 2 3
 MTR3 2 Metrics: Query Complexity Complexity: 14 3
 PRED_QUERY_COLLECTION 2 {e=sybsystemprocs..sysencryptkeys, o=sybsystemprocs..sysobjects} 0 47

DEPENDENCIES
PROCS AND TABLES USED
writes table tempdb..#encr_column_info (1) 
reads table sybsystemprocs..sysencryptkeys  
reads table sybsystemprocs..sysobjects