Database | Proc | Application | Created | Links |
sybsystemprocs | sp_aux_encr_verify_downgrade ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 create procedure sp_aux_encr_verify_downgrade 3 @cmd varchar(30), 4 @opt1 varchar(600) 5 as 6 begin -- { Beginning of sp_aux_encr_verify_downgrade. 7 declare @dbname varchar(255) /* Database name */ 8 , @key_recovery int 9 , @login_access int 10 , @login_password int 11 , @system_password int 12 , @user_password int 13 , @status_master_encr int 14 , @status_dualcontrol int 15 , @status_mek_encr int 16 , @status_static_encr int 17 , @key_copy int 18 , @encolstatus int 19 , @user_name varchar(255) 20 , @maxszsep int 21 , @decrypt_default int 22 , @seplen int 23 , @confignum int 24 , @sysstatus int 25 , @configname varchar(255) 26 , @fullconfigname varchar(255) 27 , @oldcfgvalue int 28 , @oldcfgvalue_char varchar(255) 29 , @defvalue varchar(255) 30 , @value char(10) 31 , @extpasswd_key int 32 , @syscomm_key int 33 , @syscomm_key_def int 34 , @err_verify_keycopy int 35 , @err_verify_key_recovery int 36 , @err_verify_user_passwd int 37 , @err_verify_login_passwd int 38 , @err_check_key_custodian_roles int 39 , @err_check_user_key_custodian_roles int 40 , @err_check_SEP_len int 41 , @err_check_decrypt_default int 42 , @err_check_encrypted_columns int 43 , @err_check_restricted_decrypt int 44 , @err_check_column_datatype int 45 , @err_check_kek_version int 46 , @err_check_svc_key int 47 , @msgstr1 varchar(255) 48 , @msgstr2 varchar(255) 49 50 51 /* These bits are defined either in encryptkey.h or in database.h */ 52 select @key_recovery = 64 /* EK_KEYRECOVERY */ 53 , @login_access = 8 /* EK_LOGINACCESS */ 54 , @login_password = 16 /* EK_LOGINPASS */ 55 , @system_password = 32 /* EK_SYSENCRPASS */ 56 , @user_password = 256 /* EK_USERPWD */ 57 , @status_master_encr = 2048 /* EK_MASTER_ENCR */ 58 , @status_dualcontrol = 4096 /* EK_DUALCONTROL */ 59 , @status_mek_encr = 8192 /* EK_MEK_ENCR */ 60 , @status_static_encr = 16384 /* EK_STATIC_ENCR */ 61 , @key_copy = 16 /* EK_KEYCOPY */ 62 , @extpasswd_key = 1025 /* service key syb_extpasswdkey */ 63 , @syscomm_key_def = 2053 /* default service key syb_syscommkey */ 64 , @syscomm_key = 2048 /* service key syb_syscommkey */ 65 , @encolstatus = 128 /* Indicates encrypted column status */ 66 , @maxszsep = 193 /* Max size System Encrypted Passwd */ 67 , @decrypt_default = 4096 /* decrypt_default bit */ 68 , @dbname = db_name() 69 , @err_verify_key_recovery = 1 /* Error for key recovery */ 70 , @err_verify_keycopy = 2 /* Error for keycopy existence */ 71 , @err_verify_user_passwd = 3 /* Error for user password */ 72 , @err_verify_login_passwd = 4 /* Error for login password */ 73 , @err_check_user_key_custodian_roles = 5 /* Error for user keycustodian roles */ 74 , @err_check_key_custodian_roles = 6 /* Error for keycustodian_role roles */ 75 , @err_check_decrypt_default = 7 /* Error for decrypt default */ 76 , @err_check_encrypted_columns = 8 /* Error for encrypted column */ 77 , @err_check_column_datatype = 9 /* Error for column datatype */ 78 , @err_check_SEP_len = 10 /* Error for System Encryption Password greater than 64 bytes */ 79 , @err_check_restricted_decrypt = 11 /* Error for restricted decrypt being set */ 80 , @err_check_kek_version = 12 /* Error for kek version greater than 0 */ 81 , @err_check_svc_key = 13 /* Error for service key existence */ 82 83 /* 84 ** Checking for the existence of key copies. 85 */ 86 if (@cmd = "verify_keycopy") 87 begin -- { Beginning of verify_keycopy 88 89 insert #encrypted_table_verify(assignee, keyname, keyid, keyowner) 90 select user_name(e1.uid) 91 , o1.name, e1.id 92 , user_name(o1.uid) 93 from sysobjects o1, sysencryptkeys e1 94 where o1.id = e1.id 95 and e1.type & @key_copy > 0 96 97 if exists (select 1 from #encrypted_table_verify) 98 begin 99 exec sp_getmessage 19664, @msgstr1 output 100 insert #encrypted_verify_results(dbname, assignee, keyname, keyid, keyowner, error, errstr) 101 select @dbname 102 , assignee 103 , keyname 104 , keyid 105 , keyowner 106 , @err_verify_keycopy 107 , @msgstr1 108 from #encrypted_table_verify 109 110 update #encrypted_verify_results 111 set assignee = "NULL" 112 where assignee is NULL 113 114 return (1) 115 end 116 else 117 begin 118 return (0) 119 end 120 end -- } End of verify_keycopy 121 if (@cmd = "verify_key_recovery_copy") 122 begin -- { Beginning of verify_key_recovery_copy 123 124 insert #encrypted_table_verify(assignee, keyname, keyid, keyowner) 125 select user_name(e1.uid) 126 , o1.name, e1.id 127 , user_name(o1.uid) 128 from sysobjects o1, sysencryptkeys e1 129 where o1.id = e1.id 130 and e1.type & @key_recovery > 0 131 132 if exists (select 1 from #encrypted_table_verify) 133 begin 134 exec sp_getmessage 19687, @msgstr1 output 135 insert #encrypted_verify_results(dbname, assignee, keyname, keyid, keyowner, error, errstr) 136 select @dbname 137 , assignee 138 , keyname 139 , keyid 140 , keyowner 141 , @err_verify_key_recovery 142 , @msgstr1 143 from #encrypted_table_verify 144 145 /* 146 update #encrypted_verify_results 147 set assignee = "NULL" 148 where assignee is NULL 149 */ 150 151 return (1) 152 end 153 else 154 begin 155 return (0) 156 end 157 end -- } End of verify_key_recovery_copy 158 159 /* 160 ** Checking sysencryptkeys.status for keys encrypted by user password. 161 */ 162 if (@cmd = "verify_user_passwd") 163 begin -- { Beginning of verify_user_passwd 164 165 insert #encrypted_table_verify(assignee, keyname, keyid, keyowner, keystatus) 166 select user_name(e1.uid) 167 , o1.name 168 , e1.id 169 , user_name(o1.uid) 170 , e1.status 171 from sysobjects o1, sysencryptkeys e1 172 where o1.id = e1.id 173 and e1.type & @key_copy = 0 174 175 if exists (select 1 from #encrypted_table_verify) 176 begin 177 exec sp_getmessage 19665, @msgstr1 output 178 insert #encrypted_verify_results(dbname, username, keyname, 179 keyid, keyowner, error, errstr) 180 select @dbname 181 , assignee 182 , keyname 183 , keyid 184 , keyowner 185 , @err_verify_user_passwd 186 , @msgstr1 187 from #encrypted_table_verify 188 where keystatus & @user_password > 0 189 190 update #encrypted_verify_results 191 set username = "NULL" 192 where username is NULL 193 194 return (1) 195 end 196 else 197 begin 198 return (0) 199 end 200 end -- } end of verify_user_passwd 201 202 /* 203 ** Checking existence of permissions granted to keycustodian_role. 204 */ 205 if (@cmd = "check_key_custodian_roles") 206 begin -- { Beginning of check_key_custodian_roles 207 208 209 insert #encrypted_table_verify(action) 210 select p1.action 211 from sysprotects p1, sysusers u1 212 where u1.uid = p1.uid 213 and u1.name = "keycustodian_role" 214 215 if not exists (select 1 from #encrypted_table_verify) 216 begin 217 return (0) 218 end 219 else 220 begin 221 exec sp_getmessage 19667, @msgstr1 output 222 insert #encrypted_verify_results(dbname, error, errstr) 223 select @dbname 224 , @err_check_key_custodian_roles 225 , @msgstr1 226 return (1) 227 end 228 end -- } end of check_key_custodian_roles 229 230 /* 231 ** Checking if keycustodian_role is granted to any user. 232 */ 233 234 if (@cmd = "check_user_key_custodian_roles") 235 begin -- { Beginning of check_user_key_custodian_roles 236 237 insert #encrypted_table_verify(assignee) 238 select s.name 239 from master.dbo.syslogins s, master.dbo.sysloginroles r 240 where s.suid = r.suid 241 and r.srid = 16 242 243 if not exists (select 1 from #encrypted_table_verify) 244 begin 245 return (0) 246 end 247 else 248 begin 249 exec sp_getmessage 19668, @msgstr1 output 250 insert #encrypted_verify_results(dbname, username, error, errstr) 251 select @dbname 252 , assignee 253 , @err_check_user_key_custodian_roles 254 , @msgstr1 255 from #encrypted_table_verify 256 return (1) 257 end 258 end -- } end of check_user_key_custodian_roles 259 260 /* 261 ** Check the length of the System Encryption PWD. 262 */ 263 if (@cmd = "check_SEP_len") 264 begin -- { Beginning of check_SEP_len 265 select @seplen = char_length(char_value) from sysattributes 266 where class = 25 and attribute = 0 267 if (@seplen > @maxszsep) 268 begin 269 exec sp_getmessage 19669, @msgstr1 output 270 insert #encrypted_verify_results(dbname, error, errstr) 271 select @dbname 272 , @err_check_SEP_len 273 , @msgstr1 274 return (1) 275 end 276 else 277 begin 278 return (0) 279 end 280 end -- } end of check_SEP_len 281 282 /* 283 ** Check for presence of decrypt_default. 284 */ 285 if (@cmd = "check_decrypt_default") 286 begin -- { Beginning of check_decrypt_default 287 288 289 insert #encrypted_table_verify(dbname 290 , username 291 , tabname 292 , colname 293 ) 294 select @dbname 295 , user_name(o1.uid) 296 , object_name(o1.id, d1.dbid) 297 , col_name(o1.id, c1.colid, d1.dbid) 298 from master.dbo.sysdatabases d1 299 , syscolumns c1 300 , sysusers u1 301 , sysobjects o1 302 where d1.name = @dbname 303 and c1.status2 & @decrypt_default > 0 304 and d1.suid = u1.suid 305 and c1.id = o1.id 306 307 if exists (select 1 from #encrypted_table_verify) 308 begin 309 exec sp_getmessage 19670, @msgstr1 output 310 insert #encrypted_verify_results(dbname, username, tabname, colname, error, errstr) 311 select @dbname 312 , username 313 , tabname 314 , colname 315 , @err_check_decrypt_default 316 , @msgstr1 317 from #encrypted_table_verify 318 319 return (1) 320 end 321 else 322 begin 323 return (0) 324 end 325 326 end -- } end of check_decrypt_default 327 328 /* 329 ** Check is restricted decrypt set. 330 */ 331 if (@cmd = "check_restricted_decrypt") 332 begin -- { Beginning of check_restricted_decrypt 333 334 335 select @configname = "restricted decrypt permission" 336 337 /* set @confignum */ 338 select @confignum = config, 339 @sysstatus = status, 340 @fullconfigname = name 341 from master.dbo.sysconfigures 342 where name like "%" + @configname + "%" 343 and parent != 19 344 and config != 19 345 346 select @oldcfgvalue = b.value, 347 @oldcfgvalue_char = b.value2, 348 @defvalue = c.defvalue 349 from master.dbo.sysconfigures b, 350 master.dbo.syscurconfigs c 351 where b.config = @confignum 352 and b.config *= c.config 353 354 if (@oldcfgvalue > 0) 355 begin 356 exec sp_getmessage 19671, @msgstr1 output 357 select @value = convert(char(10), @oldcfgvalue) 358 insert #encrypted_verify_results(dbname, error, errstr) 359 select @dbname 360 , @err_check_restricted_decrypt 361 , @msgstr1 362 return (1) 363 end 364 else 365 begin 366 return (0) 367 end 368 369 end -- } End of check_restricted_decrypt 370 371 /* 372 ** Check existence of encrypted columns. 373 */ 374 if (@cmd = "check_encrypted_columns") 375 begin -- { Beginning of check_encrypted_columns 376 377 insert #encrypted_table_verify(username, tabname, colname) 378 select user_name(o1.uid) 379 , object_name(o1.id, d1.dbid) 380 , c.name 381 from syscolumns c 382 , sysobjects o1 383 , master.dbo.sysdatabases d1 384 where d1.name = @dbname 385 and c.id = o1.id 386 and ((c.status2 & @encolstatus) = @encolstatus) 387 388 if exists (select 1 from #encrypted_table_verify) 389 begin 390 exec sp_getmessage 19672, @msgstr1 output 391 insert #encrypted_verify_results(dbname, username, tabname, colname, error, errstr) 392 select @dbname 393 , username 394 , tabname 395 , colname 396 , @err_check_encrypted_columns 397 , @msgstr1 398 from #encrypted_table_verify 399 400 return (1) 401 end 402 else 403 begin 404 return (0) 405 end 406 end -- } End of check_encrypted_columns 407 408 /* 409 ** Checking for encrypted columns whose datatypes can't be encrpted 410 ** in 15.0.1. 411 */ 412 if (@cmd = "check_column_datatype") 413 begin -- { Beginning of check_column_datatype 414 insert #encrypted_table_verify(username, tabname, colname, type, length) 415 select user_name(o1.uid) 416 , object_name(o1.id, d1.dbid) 417 , c.name 418 , c.type 419 , c.length 420 from syscolumns c 421 , sysobjects o1 422 , master.dbo.sysdatabases d1 423 , master.dbo.systypes t1 424 where d1.name = @dbname 425 and c.id = o1.id 426 and ((c.status2 & @encolstatus) = @encolstatus) 427 and c.type = t1.type 428 and t1.name in ("bigint", "ubigint", "bit" 429 , "time", "date" 430 , "datetime", "smalldatetime" 431 , "money", "smallmoney" 432 , "unichar", "univarchar") 433 if exists (select 1 from #encrypted_table_verify) 434 begin 435 exec sp_getmessage 19672, @msgstr1 output 436 insert #encrypted_verify_results(dbname, username, tabname, colname, type, length, error, errstr) 437 select @dbname 438 , username 439 , tabname 440 , colname 441 , type 442 , length 443 , @err_check_encrypted_columns 444 , @msgstr1 445 from #encrypted_table_verify 446 return (1) 447 end 448 else 449 begin 450 return (0) 451 end 452 end -- } End of check_column_datatype 453 454 /* 455 ** Checking for encryption keys that are in KEK version 1 456 */ 457 if (@cmd = "check_kek_version") 458 begin -- {Beginning of check_kek_version 459 insert #encrypted_table_verify(assignee, keyname, keyid, keyowner, keyprotectedby) 460 select user_name(e1.uid) 461 , o1.name 462 , e1.id 463 , user_name(o1.uid) 464 , case (e1.status & (@status_dualcontrol 465 | @status_master_encr | @login_access 466 | @login_password 467 | @user_password | @status_mek_encr 468 | @status_static_encr)) 469 when (@login_access | @system_password) then "login access" 470 when (@login_access | @status_master_encr) then "login access" 471 when @login_password then "login password" 472 when @user_password then "user password" 473 when @status_master_encr then "master key" 474 when (@status_dualcontrol | @user_password) 475 then "dual_control(master key + user password)" 476 when (@status_dualcontrol | @login_access | @status_master_encr) 477 then "dual_control(login access)" 478 when (@status_dualcontrol | @login_password) 479 then "dual_control(master key + login password)" 480 when (@status_dualcontrol | @status_master_encr) 481 then "dual_control(master key + dual master key)" 482 when (@status_mek_encr) 483 then "automatic startup key" 484 when (@status_static_encr) 485 then "static key" 486 end 487 from sysobjects o1, sysencryptkeys e1 488 where o1.id = e1.id 489 and convert(int, substring(e1.eksalt, 1, 2)) > 0 490 and e1.type != @extpasswd_key 491 and (e1.type & @syscomm_key) = 0 492 493 if exists (select 1 from #encrypted_table_verify) 494 begin 495 exec sp_getmessage 17022, @msgstr1 output 496 insert #encrypted_verify_results(dbname, assignee, keyname, 497 keyid, keyowner, keyprotectedby, error, errstr) 498 select @dbname 499 , assignee 500 , keyname 501 , keyid 502 , keyowner 503 , keyprotectedby 504 , @err_check_kek_version 505 , @msgstr1 506 from #encrypted_table_verify 507 508 update #encrypted_verify_results 509 set assignee = "NULL" 510 where assignee is NULL 511 512 return (1) 513 end 514 else 515 begin 516 return (0) 517 end 518 end -- } End of check_kek_version 519 520 /* 521 ** Checking for existence of service keys 522 */ 523 if (@cmd = "check_svc_key") 524 begin -- {Beginning of check_svc_key 525 526 insert #encrypted_table_verify(assignee, keyname, keyowner) 527 select user_name(e.uid) 528 , o.name 529 , user_name(o.uid) 530 from sysobjects o, sysencryptkeys e 531 where o.id = e.id 532 and e.type = @extpasswd_key 533 534 -- Don't expose specific syb_syscommkey name 535 insert #encrypted_table_verify(assignee, keyname, keyowner) 536 select user_name(e.uid) 537 , "syb_syscommkey" 538 , user_name(o.uid) 539 from sysobjects o, sysencryptkeys e 540 where o.id = e.id 541 and e.type = @syscomm_key_def 542 543 if exists (select 1 from #encrypted_table_verify) 544 begin 545 exec sp_getmessage 17023, @msgstr1 output 546 insert #encrypted_verify_results(dbname, assignee, keyname, 547 keyowner, error, errstr) 548 select @dbname 549 , assignee 550 , keyname 551 , keyowner 552 , @err_check_svc_key 553 , @msgstr1 554 from #encrypted_table_verify 555 556 update #encrypted_verify_results 557 set assignee = "NULL" 558 where assignee is NULL 559 560 return (1) 561 end 562 else 563 begin 564 return (0) 565 end 566 end -- } End of check_svc_key 567 568 end -- } End of sp_aux_encr_verify_downgrade. 569
exec sp_procxmode 'sp_aux_encr_verify_downgrade', 'AnyMode' go Grant Execute on sp_aux_encr_verify_downgrade to public go
DEFECTS | |
![]() | 352 |
![]() | 427 |
![]() | master..sysconfigures |
![]() | sybsystemprocs..sysattributes |
![]() | sybsystemprocs..sysencryptkeys |
![]() | sybsystemprocs..sysprotects |
![]() | 294 |
![]() | 378 |
![]() | 415 |
![]() (id, type, uid) Intersection: {type} | 95 |
![]() (id, type, uid) Intersection: {type} | 130 |
![]() (id, type, uid) Intersection: {type} | 173 |
![]() (id, type, uid) Intersection: {type} | 489 |
![]() (id, type, uid) Intersection: {type} | 532 |
![]() (id, type, uid) Intersection: {type} | 541 |
![]() | 266 |
![]() | 343 |
![]() | 344 |
![]() | 351 |
![]() | 351 |
![]() | 490 |
![]() | 490 |
![]() | 532 |
![]() | 532 |
![]() | 541 |
![]() | 541 |
![]() | master..syscurconfigs |
![]() | master..sysloginroles |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | 89 |
![]() | 99 |
![]() | 100 |
![]() | 110 |
![]() | 124 |
![]() | 134 |
![]() | 135 |
![]() | 165 |
![]() | 177 |
![]() | 178 |
![]() | 190 |
![]() | 209 |
![]() | 221 |
![]() | 222 |
![]() | 237 |
![]() | 249 |
![]() | 250 |
![]() | 269 |
![]() | 270 |
![]() | 289 |
![]() | 309 |
![]() | 310 |
![]() | 356 |
![]() | 358 |
![]() | 377 |
![]() | 390 |
![]() | 391 |
![]() | 414 |
![]() | 435 |
![]() | 436 |
![]() | 459 |
![]() | 495 |
![]() | 496 |
![]() | 508 |
![]() | 526 |
![]() | 535 |
![]() | 545 |
![]() | 546 |
![]() | 556 |
![]() | 6 |
![]() | 86 |
![]() | 114 |
![]() | 118 |
![]() | 121 |
![]() | 151 |
![]() | 155 |
![]() | 162 |
![]() | 194 |
![]() | 198 |
![]() | 205 |
![]() | 217 |
![]() | 226 |
![]() | 234 |
![]() | 245 |
![]() | 256 |
![]() | 263 |
![]() | 267 |
![]() | 274 |
![]() | 278 |
![]() | 285 |
![]() | 319 |
![]() | 323 |
![]() | 331 |
![]() | 354 |
![]() | 362 |
![]() | 366 |
![]() | 374 |
![]() | 400 |
![]() | 404 |
![]() | 412 |
![]() | 446 |
![]() | 450 |
![]() | 457 |
![]() | 482 |
![]() | 484 |
![]() | 512 |
![]() | 516 |
![]() | 523 |
![]() | 560 |
![]() | 564 |
![]() | 265 |
![]() | 338 |
![]() | 346 |
![]() | 89 |
![]() | 100 |
![]() | 124 |
![]() | 135 |
![]() | 165 |
![]() | 178 |
![]() | 209 |
![]() | 222 |
![]() | 237 |
![]() | 250 |
![]() | 270 |
![]() | 289 |
![]() | 310 |
![]() | 358 |
![]() | 377 |
![]() | 391 |
![]() | 414 |
![]() | 436 |
![]() | 459 |
![]() | 496 |
![]() | 526 |
![]() | 535 |
![]() | 546 |
![]() | 93 |
![]() | 128 |
![]() | 171 |
![]() | 211 |
![]() | 239 |
![]() | 298 |
![]() | 381 |
![]() | 420 |
![]() | 487 |
![]() | 530 |
![]() | 539 |
![]() | 349 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, attribute} | 266 |
![]() | 351 |
![]() | 300 |
![]() | 72 |
![]() | 77 |
![]() | 339 |
![]() | 340 |
![]() | 347 |
![]() | 348 |
![]() | 357 |
![]() | 4 |
![]() | 19 |
![]() | 48 |
![]() | 2 |
![]() | 2 |
![]() | 2 |
![]() | 90 |
![]() | 125 |
![]() | 166 |
![]() | 210 |
![]() | 238 |
![]() | 294 |
![]() | 378 |
![]() | 415 |
![]() | 460 |
![]() | 527 |
![]() | 536 |
DEPENDENCIES |
PROCS AND TABLES USED reads table master..sysdatabases (1) ![]() reads table master..sysloginroles (1) ![]() reads table master..systypes (1) ![]() read_writes table tempdb..#encrypted_table_verify (1) reads table master..sysconfigures (1) ![]() calls proc sybsystemprocs..sp_getmessage ![]() calls proc sybsystemprocs..sp_validlang ![]() reads table master..syslanguages (1) ![]() reads table master..syslanguages (1) ![]() reads table master..sysmessages (1) ![]() reads table sybsystemprocs..sysusermessages ![]() reads table sybsystemprocs..sysencryptkeys ![]() reads table sybsystemprocs..sysprotects ![]() reads table master..syscurconfigs (1) ![]() reads table sybsystemprocs..sysobjects ![]() reads table sybsystemprocs..syscolumns ![]() reads table master..syslogins (1) ![]() writes table tempdb..#encrypted_verify_results (1) reads table sybsystemprocs..sysusers ![]() reads table sybsystemprocs..sysattributes ![]() |