Database | Proc | Application | Created | Links |
sybsystemprocs | sp_encryption ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** file: encryption 4 ** Administer column level encryption 5 */ 6 create procedure sp_encryption 7 @cmd varchar(30) = null, 8 /* Should be system_encr_passwd, 9 ** help, helpkey, helpuser, 10 ** helpcol, mkey_startup_file, 11 ** or downgrade_kek_size 12 */ 13 @opt1 varchar(768) = null, /* Should be keyname for help, 14 ** newpasswd for system_encr_passwd, 15 ** username for helpuser 16 ** columname for helpcol 17 ** new master key startup 18 ** file path for 19 ** mkey_startup_file 20 ** true/false for 21 ** downgrade_kek_size 22 */ 23 @opt2 varchar(600) = null, /* 24 ** Could be "display_cols", "key_copy", 25 ** "all_dbs", "display_keys" if help or 26 ** helpkey, 27 ** "oldpasswd" if system_encr_passwd, 28 ** "key_copy", "login_passwd_check" if helpuser 29 ** "sync_with_mem" or "sync_with_qrm" 30 ** if mkey_startup_file 31 */ 32 @passwd_fmt int = 0, /* for replication or ddlgen*/ 33 @passwd_vers int = 0 /* for replicating or receiving 34 ** ddlgen system_encr_passwd 35 */ 36 as 37 declare @retval int 38 , @procval int 39 , @opt1_buf varchar(575) /* To disambiguate @opt1 */ 40 , @dbname varchar(30) 41 , @keydbname varchar(30) 42 , @username varchar(30) 43 , @callername varchar(30) 44 , @rows int 45 , @auxproc varchar(1024) 46 , @sso_role int /* indicates user has sso_role */ 47 , @keycustodian_role int /* indicates user has keycustodian_role */ 48 , @dummy int 49 , @dbid int 50 , @is_remote_tdb int 51 , @tdb_instanceid int 52 , @tdb_instancename varchar(255) 53 , @msg varchar(1024) 54 , @owner varchar(30) 55 , @objname varchar(255) 56 , @colname varchar(255) 57 , @errmsg varchar(255) 58 , @keyid int 59 , @userid int 60 , @rpid int /* replication path id */ 61 , @ra_pwd char(32) /* used for ra password format (truncated value) */ 62 , @db_offline int /* indicates a database that is offline */ 63 , @db_unrecovered int /* indicates a database that is not recovered */ 64 , @db_notrec int /* indicates a database that is still in recover process */ 65 , @db_inlddb int /* indicates a database that will not be recovered */ 66 , @db_archivedb int /* indicates a database taht is an archive database */ 67 , @system_password int 68 , @sys_encr_passwd_type int /* indicates the type of the system_encr_passwd */ 69 , @login_password int 70 , @login_access int 71 , @user_password int 72 , @key_recovery int 73 , @default_key int 74 , @keytype_master int 75 , @keytype_dualmaster int 76 , @keytype_passwd_svckey int 77 , @keytype_text_svckey int 78 , @status_master_encr int 79 , @status_dualcontrol int 80 , @status_mek_encr int 81 , @status_static_encr int 82 , @key_copy int /* indicates a key copy */ 83 , @encolstatus int /* 84 ** indicated if a column is 85 ** encrypted 86 */ 87 88 89 /* These bits are defined either in encryptkey.h or in database.h */ 90 select @login_password = 16 /* EK_LOGINPASS */ 91 , @login_access = 8 /* EK_LOGINACCESS */ 92 , @system_password = 32 /* EK_SYSENCRPASS */ 93 , @user_password = 256 /* EK_USERPWD */ 94 , @key_recovery = 64 /* EK_KEYRECOVERY */ 95 , @key_copy = 16 /* EK_KEYCOPY */ 96 , @default_key = 4 /* EK_DEFAULT */ 97 , @db_offline = 16 /* DBT2_OFFLINE */ 98 , @db_unrecovered = 256 /* DBT_SUSPECT */ 99 , @db_notrec = 64 /* DBT_NOTREC */ 100 , @db_inlddb = 32 /* DBT_INLDDB */ 101 , @db_archivedb = 4194304 /* DBT3_ARCHIVEDB */ 102 , @keytype_master = 128 /* EK_MASTER */ 103 , @keytype_dualmaster = 256 /* EK_DUALCONTROL */ 104 , @status_master_encr = 2048 /* EK_MASTER_ENCR */ 105 , @status_dualcontrol = 4096 /* EK_DUALCONTROL */ 106 , @status_mek_encr = 8192 /* EK_MEK_ENCR */ 107 , @status_static_encr = 16384 /* EK_STATIC_ENCR */ 108 , @keytype_passwd_svckey = 1024 /* EK_EXTPASSWDKEY */ 109 , @keytype_text_svckey = 2048 /* EK_SYSCOMMKEY */ 110 111 /* 112 ** If we're in a transaction, disallow this since it might make recovery 113 ** impossible. 114 */ 115 116 if @@trancount > 0 117 begin 118 /* 119 ** 17260, "Can't run %1! from within a transaction." 120 */ 121 raiserror 17260, "sp_encryption" 122 return (1) 123 end 124 else 125 begin 126 set chained off 127 end 128 set transaction isolation level 1 129 130 /* 131 ** Begin command processing. 132 */ 133 134 select @callername = user_name() 135 136 if (@cmd is null) 137 begin 138 select @cmd = "null" 139 select @retval = 1 140 end 141 else 142 if ((@cmd not in ("help", "helpkey", "helpcol", "system_encr_passwd", "helpuser", 143 "verify_downgrade", "mkey_startup_file", "downgrade_kek_size", "helpextpasswd")) 144 or ((@cmd in ("help", "helpkey", "helpcol", "helpuser", "mkey_startup_file", "downgrade_kek_size")) 145 and (@passwd_fmt != 0) and (@passwd_vers != 0))) 146 begin 147 148 /* 149 ** 19213, "Invalid argument or unsupported command: %1!." 150 */ 151 if (@cmd is null) 152 begin 153 select @cmd = "null" 154 end 155 raiserror 19213, @cmd 156 select @retval = 1 157 end 158 159 /* check for sso_role */ 160 select @sso_role = charindex("sso_role", show_role()) 161 select @keycustodian_role = charindex("keycustodian_role", show_role()) 162 163 if (@sso_role > 0) 164 begin 165 /* Send an audit record through the proc_role() built-in */ 166 select @dummy = proc_role("sso_role") 167 end 168 169 if (@keycustodian_role > 0) 170 begin 171 /* Send an audit record through the proc_role() built-in */ 172 select @dummy = proc_role("keycustodian_role") 173 end 174 175 176 if (@cmd = "system_encr_passwd") 177 begin --{ Setting the system encryption password. 178 if (@opt1 is null and @opt2 is null) 179 begin 180 /* 181 ** 19415, "The '%1!' parameter is required for the '%2!' command." 182 */ 183 raiserror 19415, "newpasswd", "system_encr_passwd" 184 return (1) 185 end 186 187 188 189 if (@passwd_fmt = 2) 190 begin 191 /* 192 ** Check for existence of system password at replicate site 193 ** when accepting system encryption password from ddlgen. 194 ** If there is, error out. 195 */ 196 if exists (select 1 from sysattributes 197 where class = 25 and attribute = 0 and object_type = 'EC') 198 begin 199 /* 200 ** 19646, "The execution of the stored procedure '%1!' 201 ** in database '%2!' was aborted because the system 202 ** encryption password already exists for this 203 ** database." 204 */ 205 select @dbname = db_name() 206 raiserror 19646, "sp_encryption", @dbname 207 return (1) 208 end 209 210 if (@opt2 is not null) 211 begin 212 /* 18291, "The parameter value '%1!' is invalid." */ 213 raiserror 18291, @opt2 214 return 1 215 end 216 end 217 218 /* 219 ** This transaction writes a log record for replicating the invocation 220 ** of this stored procedure. If logexec() fails, the transaction is 221 ** aborted. 222 ** Transaction name rs_logexec is significant and it is used by 223 ** Replication Server 224 */ 225 begin transaction rs_logexec 226 /* 227 ** ENCRCOL2 RESOLVE: when the nonpersistent system_encr_password 228 ** is implemented, the value of @sys_encr_passwd_type needs to 229 ** to be determined based on the value of @cmd. For now, by default 230 ** the type of the system_encr_passwd is 0 (SYS_ENCR_PASSWD_PERSISTENT). 231 */ 232 select @sys_encr_passwd_type = 0 233 select @retval = encr_admin(@cmd, @opt1, @opt2, @passwd_fmt, 234 @passwd_vers, @sys_encr_passwd_type) 235 if (@retval != 0) 236 begin 237 rollback transaction rs_logexec 238 239 /* Sufficient error messages delivered from server */ 240 return (1) 241 end 242 243 if (logexec() != 1) 244 begin 245 /* 246 ** 17756, "The execution of the stored procedure '%1!' 247 ** in database '%2!' was aborted because there 248 ** was an error in writing the replication log 249 ** record." 250 */ 251 select @dbname = db_name() 252 raiserror 17756, "sp_encryption", @dbname 253 254 rollback transaction rs_logexec 255 return (1) 256 end 257 258 commit transaction rs_logexec 259 260 end --} Setting the system encryption password. 261 262 if (@cmd = "mkey_startup_file") 263 begin --{ Setting the master key startup file 264 265 if (@opt2 is not null and @opt2 != "sync_with_mem" 266 and @opt2 != "sync_with_qrm") 267 begin 268 /* 18291, "The parameter value '%1!' is invalid." */ 269 raiserror 18291, @opt2 270 return 1 271 end 272 273 274 if (@sso_role = 0) 275 begin 276 /* 277 ** 19951 "Permission denied. This operation requires 278 ** System Security Officer (sso_role) role." 279 */ 280 raiserror 19951 281 return (1) 282 end 283 284 /* 285 ** If no option is specified, display the current master key 286 ** startup file pathname. The pathname is brought back by 287 ** @opt1 through calling encr_admin(). This action needs not 288 ** to be replicated and needs not to be executed in a transaction. 289 */ 290 if (@opt1 is NULL and @opt2 is NULL) 291 begin 292 select @retval = encr_admin(@cmd, @opt1, NULL, NULL, NULL, NULL) 293 294 if (@retval != 0) 295 begin 296 /* Sufficient error messages delivered from server */ 297 return (1) 298 end 299 300 301 /* 302 ** Display the current path 303 ** 304 ** 19956 " The current master key startup file is: '%1!'. 305 */ 306 raiserror 19956, @opt1 307 return (0) 308 end 309 310 /* 311 ** This transaction writes a log record for replicating the invocation 312 ** of this stored procedure. If logexec() fails, the transaction is 313 ** aborted. 314 ** Transaction name rs_logexec is significant and it is used by 315 ** Replication Server 316 */ 317 begin transaction rs_logexec 318 319 select @retval = encr_admin(@cmd, @opt1, @opt2, NULL, NULL, NULL) 320 321 if (@retval != 0) 322 begin 323 rollback transaction rs_logexec 324 325 /* Sufficient error messages delivered from server */ 326 return (1) 327 end 328 329 if (logexec() != 1) 330 begin 331 /* 332 ** 17756, "The execution of the stored procedure '%1!' 333 ** in database '%2!' was aborted because there 334 ** was an error in writing the replication log 335 ** record." 336 */ 337 select @dbname = db_name() 338 raiserror 17756, "sp_encryption", @dbname 339 340 rollback transaction rs_logexec 341 return (1) 342 end 343 344 commit transaction rs_logexec 345 346 end -- } Setting the master key startup file path 347 348 if (@cmd = "downgrade_kek_size") 349 begin --{ Setting downgrade_kek_size 350 351 if (@opt2 is not null) 352 begin 353 /* 18291, "The parameter value '%1!' is invalid." */ 354 raiserror 18291, @opt2 355 return 1 356 end 357 358 if (@opt1 is not null and lower(@opt1) != "true" 359 and lower(@opt1) != "false") 360 begin 361 /* 18291, "The parameter value '%1!' is invalid." */ 362 raiserror 18291, @opt1 363 return 1 364 end 365 366 /* 367 ** If no option is specified, display the current downgrade_kek_size 368 ** setting. 369 */ 370 if (@opt1 is NULL) 371 begin 372 select @retval = count(*) 373 from master..sysattributes 374 where class = 25 375 and attribute = 3 376 377 /* 378 ** Display the current downgrade_kek_size setting 379 ** 380 ** 19960 "The current downgrade_kek_size setting for 381 ** the server is '%1!'". 382 */ 383 if (@retval = 1) 384 begin 385 select @errmsg = "TRUE" 386 end 387 else 388 begin 389 select @errmsg = "FALSE" 390 end 391 392 raiserror 19960, @errmsg 393 return (0) 394 end 395 396 /* 397 ** This transaction writes a log record for replicating the invocation 398 ** of this stored procedure. If logexec() fails, the transaction is 399 ** aborted. 400 ** Transaction name rs_logexec is significant and it is used by 401 ** Replication Server 402 */ 403 begin transaction rs_logexec 404 405 select @retval = encr_admin(@cmd, lower(@opt1), NULL, NULL, NULL, NULL) 406 407 if (@retval != 0) 408 begin 409 rollback transaction rs_logexec 410 411 /* Sufficient error messages delivered from server */ 412 return (1) 413 end 414 415 if (logexec() != 1) 416 begin 417 /* 418 ** 17756, "The execution of the stored procedure '%1!' 419 ** in database '%2!' was aborted because there 420 ** was an error in writing the replication log 421 ** record." 422 */ 423 select @dbname = db_name() 424 raiserror 17756, "sp_encryption", @dbname 425 426 rollback transaction rs_logexec 427 return (1) 428 end 429 430 commit transaction rs_logexec 431 432 end -- } Setting downgrade_kek_size 433 434 /* 435 ** Verify downgrade. 436 */ 437 if (@cmd = "verify_downgrade") 438 begin -- { verify_downgrade. 439 declare @tmpstr char(4) 440 , @err_verify_keycopy int 441 , @err_verify_key_recovery int 442 , @err_verify_user_passwd int 443 , @err_verify_login_passwd int 444 , @err_check_key_custodian_roles int 445 , @err_check_user_key_custodian_roles int 446 , @err_check_SEP_len int 447 , @err_check_decrypt_default int 448 , @err_check_encrypted_columns int 449 , @err_check_restricted_decrypt int 450 , @err_check_column_datatype int 451 , @err_check_kek_version int 452 , @err_check_svc_key int 453 , @toversid int 454 , @cct int 455 , @c int 456 , @v char(1) 457 , @ret int 458 459 460 /* Error number defines */ 461 select @err_verify_key_recovery = 1 /* Error for key recovery */ 462 , @err_verify_keycopy = 2 /* Error for keycopy existence */ 463 , @err_verify_user_passwd = 3 /* Error for user password */ 464 , @err_verify_login_passwd = 4 /* Error for login password */ 465 , @err_check_user_key_custodian_roles = 5 /* Error for user keycustodian roles */ 466 , @err_check_key_custodian_roles = 6 /* Error for keycustodian_role roles */ 467 , @err_check_decrypt_default = 7 /* Error for decrypt default */ 468 , @err_check_encrypted_columns = 8 /* Error for encrypted column */ 469 , @err_check_column_datatype = 9 /* Error for column datatype */ 470 , @err_check_SEP_len = 10 /* Error for System Encryption Password greater than 64 bytes */ 471 , @err_check_restricted_decrypt = 11 /* Error for restricted decrypt being set */ 472 , @err_check_kek_version = 12 /* Error for kek version greater than 0 */ 473 , @err_check_svc_key = 13 /* Error for service key existence */ 474 475 /* 476 ** Validate 'toversion' and translate it to version number 477 */ 478 select @toversid = 0 479 select @cct = datalength(@opt1) 480 select @c = 1 481 while @c <= @cct 482 begin 483 select @v = substring(@opt1, @c, 1) 484 if (@v between '0' and '9') 485 begin 486 select @toversid = (@toversid * 10) + convert(int, @v) 487 end 488 else if (@v != '.') 489 begin 490 -- Not a valid version ... stop 491 print "'%1!' is not a valid downgrade version.", @opt1 492 return 1 493 end 494 select @c = @c + 1 495 end 496 if (@toversid = 0 or @toversid > 15505) 497 begin 498 print "'%1!' is not a valid downgrade version.", @opt1 499 return 1 500 end 501 while (@toversid < 10000) 502 select @toversid = @toversid * 10 503 504 print "" 505 exec sp_getmessage 19676, @errmsg output 506 print @errmsg 507 508 /* login with sso_role */ 509 if ((@sso_role > 0) or (@keycustodian_role > 0)) 510 begin -- { Roles 511 512 create table #encrypted_verify_results( 513 dbname varchar(30) null 514 , assignee varchar(30) null 515 , tabname varchar(255) null 516 , colname varchar(255) null 517 , type tinyint null 518 , length tinyint null 519 , username varchar(30) null 520 , keydbname varchar(30) null 521 , keyowner varchar(30) null 522 , keyid int null 523 , keyname varchar(255) null 524 , keyprotectedby varchar(45) null 525 , fullcolname varchar(540) null 526 , error int null 527 , errstr varchar(128) null) 528 529 create table #encrypted_table_verify( 530 dbname varchar(30) null 531 , assignee varchar(30) null 532 , username varchar(30) null 533 , tabname varchar(255) null 534 , colname varchar(255) null 535 , type tinyint null 536 , length tinyint null 537 , keydbname varchar(30) null 538 , keyowner varchar(30) null 539 , keyid int null 540 , keyname varchar(255) null 541 , keystatus int null 542 , keyprotectedby varchar(45) null 543 , action int null 544 , fullcolname varchar(540) null 545 , fullkeyname varchar(315) null) 546 547 declare database_cursor cursor for 548 select name 549 from master.dbo.sysdatabases 550 /* Skip databases that have not recovered */ 551 where status & @db_unrecovered = 0 552 /* Skip databases that are offline */ 553 and status2 & @db_offline = 0 554 /* Skip databases that are not recovered */ 555 and status & @db_notrec = 0 556 /* Skip databases that will not be recovered */ 557 and status & @db_inlddb = 0 558 /* Skip databases that are archive databases */ 559 and status3 & @db_archivedb = 0 560 561 open database_cursor 562 fetch database_cursor into @dbname 563 select @keydbname = db_name() 564 565 while (@@sqlstatus = 0) 566 begin -- { beginning of while loop. 567 568 select @auxproc = @dbname + ".dbo.sp_aux_encr_verify_downgrade" 569 570 if (@toversid < 15700) 571 begin -- { dual control was introduced in 15.7 572 truncate table #encrypted_table_verify 573 exec @retval = @auxproc 'check_kek_version', @dbname 574 575 -- check existence of service key introduced 576 -- since 15.7 577 truncate table #encrypted_table_verify 578 exec @retval = @auxproc 'check_svc_key', @dbname 579 end -- } 580 581 if (@toversid < 15020) 582 begin -- { key copy, recovery copy, user password were introduced in 15.0.2 583 truncate table #encrypted_table_verify 584 exec @retval = @auxproc 'verify_keycopy', @dbname, NULL 585 truncate table #encrypted_table_verify 586 exec @retval = @auxproc 'verify_key_recovery_copy', NULL 587 truncate table #encrypted_table_verify 588 exec @retval = @auxproc 'verify_user_passwd', NULL 589 end -- } 590 591 if (@toversid < 15010) 592 begin -- { column encryption was introduced in 15.0.1 593 truncate table #encrypted_table_verify 594 exec @retval = @auxproc 'check_key_custodian_roles', @dbname 595 596 truncate table #encrypted_table_verify 597 exec @retval = @auxproc 'check_decrypt_default', @dbname 598 599 truncate table #encrypted_table_verify 600 exec @retval = @auxproc 'check_column_datatype', @dbname 601 602 truncate table #encrypted_table_verify 603 exec @retval = @auxproc 'check_SEP_len', @dbname 604 605 if (@dbname = "master") 606 begin 607 truncate table #encrypted_table_verify 608 exec @retval = @auxproc 'check_user_key_custodian_roles', @dbname 609 610 truncate table #encrypted_table_verify 611 exec @retval = @auxproc 'check_restricted_decrypt', @dbname 612 end 613 614 truncate table #encrypted_table_verify 615 exec @retval = @auxproc 'check_encrypted_columns', @dbname 616 end --} 617 618 fetch database_cursor into @dbname 619 620 end -- } End of while loop. 621 close database_cursor 622 select @dummy = count(*) from #encrypted_verify_results 623 624 if (@dummy > 0) -- { dummy 625 begin 626 select @tmpstr = convert(char(4), @dummy) 627 print " " 628 select @errmsg = "Total Error(s) " + @tmpstr 629 print @errmsg 630 print " " 631 update #encrypted_verify_results 632 set fullcolname = 633 case 634 when (error = @err_check_kek_version) 635 then dbname + "." + keyowner + "." 636 + keyname + "; assignee: " + assignee 637 + "; protected by: " + keyprotectedby 638 639 when (error = @err_check_svc_key) 640 then dbname + "." + keyowner + "." 641 + keyname + "; assignee: " + assignee 642 when (error = @err_verify_keycopy) 643 then dbname + "." + keyowner + "." 644 + keyname + "; assignee: " + assignee 645 when (error = @err_verify_key_recovery) 646 then dbname + "." + keyowner + "." + keyname 647 when (error = @err_verify_user_passwd) 648 then dbname + "." + keyowner + "." + keyname 649 when (error = @err_check_key_custodian_roles) 650 then dbname 651 when (error = @err_check_user_key_custodian_roles) 652 then dbname 653 when (error = @err_check_SEP_len) 654 then dbname 655 when (error = @err_check_decrypt_default 656 or error = @err_check_encrypted_columns 657 or error = @err_check_column_datatype) 658 then dbname + "." 659 + username + "." + tabname 660 + "." + colname 661 when (error = @err_check_restricted_decrypt) 662 then dbname 663 end 664 where fullcolname is NULL 665 666 667 if exists (select 1 from #encrypted_verify_results) 668 begin 669 print "" 670 exec sp_getmessage 19674, @errmsg output 671 print @errmsg 672 print "" 673 exec sp_autoformat 674 @fulltabname = #encrypted_verify_results, 675 @selectlist = " 'Encrypted Columns Item' = fullcolname, 676 'Required Action' = errstr", 677 @orderby = "order by error" 678 end 679 680 select @ret = 1 681 end -- } dummy 682 else 683 begin 684 print "" 685 exec sp_getmessage 19677, @errmsg output 686 print @errmsg 687 select @ret = 0 688 end 689 deallocate cursor database_cursor 690 drop table #encrypted_verify_results 691 drop table #encrypted_table_verify 692 return @ret 693 end -- } login with sso_role 694 end -- } verify_downgrade. 695 696 /* 697 ** SP_ENCRYPTION HELP|HELPKEY [, KEY_NAME | WILDCARD] 698 ** [, ALL_DBS | KEY_COPY | DISPLAY_COLS] 699 ** SP_ENCRYPTION HELP|HELPKEY, SYSTEM_ENCR_PASSWORD [, DISPLAY_KEYS | ALL_DBS] 700 */ 701 if ((@cmd = "help") or (@cmd = "helpkey")) 702 begin --{ Display key related information 703 if not exists (select 1 from sysobjects where name = 'sysencryptkeys') 704 begin 705 raiserror 19387 706 return (1) 707 end 708 709 if ((@opt2 is not null) and (@opt2 not in ('all_dbs', 'display_cols', 710 'key_copy', 'display_keys', 711 'display_objs'))) 712 begin 713 /* 18291, "The parameter value '%1!' is invalid." */ 714 raiserror 18291, @opt2 715 return 1 716 end 717 718 /* 719 ** SP_ENCRYPTION HELP|HELPKEY, SYSTEM_ENCR_PASSWD [, DISPLAY_KEYS | ALL_DBS] 720 ** Display system_encr_passwd related information 721 */ 722 /* 723 ** SP_ENCRYPTION HELP|HELPKEY, SYSTEM_ENCR_PASSWD, DISPLAY_KEYS 724 ** Displays all keys that are encrypted with 725 ** the system encryption passwords 726 */ 727 if ((@opt1 = "system_encr_passwd") and (@opt2 = "display_keys")) 728 begin --{ "system_encr_passwd display keys" 729 /* Accumulate encryption key info in a temporary table */ 730 create table #encr_keys_info(assignee varchar(30) null, 731 keyname varchar(255) null, 732 keyowner varchar(30) null, 733 fullkeyname varchar(285) null) 734 735 if ((@sso_role > 0) or (@keycustodian_role > 0) or (user_name() = "dbo")) 736 begin 737 insert #encr_keys_info(assignee, keyname, keyowner) 738 select user_name(e1.uid), o1.name, user_name(o1.uid) 739 from sysobjects o1, sysencryptkeys e1, sysattributes a1 740 where o1.id = e1.id 741 and (e1.status & @system_password) > 0 742 and a1.class = 25 743 and a1.attribute = 0 744 and a1.object_type = "EC" 745 746 if not exists (select 1 from #encr_keys_info) 747 begin 748 /* 749 ** 19630, "There are no encryption keys encrypted with 750 ** the system encryption password." 751 */ 752 raiserror 19630 753 return (1) 754 end 755 756 end 757 else 758 begin 759 /* 760 ** Display the encryption key info for 761 ** non-privileged user 762 */ 763 insert #encr_keys_info(assignee, keyname, keyowner) 764 select user_name(e1.uid), o1.name, user_name(o1.uid) 765 from sysobjects o1, sysencryptkeys e1, sysattributes a1 766 where o1.id = e1.id 767 and (e1.status & @system_password) > 0 768 and (user_name(e1.uid) = user_name() or user_name(o1.uid) = user_name()) 769 and a1.class = 25 770 and a1.attribute = 0 771 and a1.object_type = "EC" 772 773 if not exists (select 1 from #encr_keys_info) 774 begin 775 /* 776 ** 19647, "There are no encryption keys (key copies) 777 ** encrypted with the system encryption password 778 ** for user '%1!' in the current database." 779 */ 780 raiserror 19647, @callername 781 return (1) 782 end 783 784 end 785 786 787 /* update #encr_keys_info to fill fullkeyname column */ 788 update #encr_keys_info 789 set fullkeyname = keyowner + "." + keyname 790 where fullkeyname is NULL 791 792 /* Display the encryption key info */ 793 exec sp_autoformat @fulltabname = #encr_keys_info, 794 @selectlist = "'Owner.Keyname' = fullkeyname, 'Assignee' = assignee", 795 @orderby = "order by fullkeyname" 796 797 drop table #encr_keys_info 798 end --} "system_encr_passwd display keys" 799 800 /* 801 ** SP_ENCRYPTION HELP|HELPKEY, SYSTEM_ENCR_PASSWD [, ALL_DBS] 802 ** 803 ** Displays the properties of the system encryption password 804 ** in the current database or all avaiable databases. 805 */ 806 else if ((@opt1 = "system_encr_passwd") and ((@opt2 = "all_dbs") or (@opt2 = NULL))) 807 begin --{ 808 /* Accumulate system encryption password info in a temporary table */ 809 create table #sys_encr_passwd_info(dbname varchar(30) null, 810 typeofpasswd varchar(30) null, 811 modifier varchar(30) null, 812 moddate smalldatetime null) 813 /* 814 ** SP_ENCRYPTION HELP|HELPKEY, SYSTEM_ENCR_PASSWD, ALL_DBS 815 ** Display the system encryption password information across 816 ** all available databases. 817 */ 818 if (@opt2 = "all_dbs") 819 begin 820 821 /* Only SSO can run this command */ 822 if (@sso_role = 0) 823 begin 824 select @dummy = proc_role("sso_role") 825 return (1) 826 end 827 828 declare database_cursor cursor for 829 select name, dbid 830 from master.dbo.sysdatabases 831 /* Skip databases that have not recovered */ 832 where status & @db_unrecovered = 0 833 /* Skip databases that are offline */ 834 and status2 & @db_offline = 0 835 /* Skip databases that are not recovered */ 836 and status & @db_notrec = 0 837 /* Skip databases that will not be recovered */ 838 and status & @db_inlddb = 0 839 /* Skip databases that are archive databases */ 840 and status3 & @db_archivedb = 0 841 842 open database_cursor 843 fetch database_cursor into @dbname, @dbid 844 845 while (@@sqlstatus = 0) 846 begin 847 /* 848 ** In SDC, skip local tempdbs that are only 849 ** accessible from remote instances. 850 */ 851 852 select @auxproc = 853 @dbname + ".dbo.sp_aux_sys_encr_passwd_lookup" 854 855 /* 856 ** Since sp_aux_sys_encr_passwd_lookup is a system 857 ** stored procedure, it will execute in the 858 ** context of dbname if it is called as 859 ** dbname..sp_aux_sys_encr_passwd_lookup, regardless of 860 ** which db it is called from. 861 ** 862 ** This auxilliary procedure is used to extract 863 ** the information about system encryption password. 864 */ 865 866 exec @auxproc @dbname 867 868 select @retval = @@error 869 870 if (@retval != 0) 871 begin 872 close database_cursor 873 deallocate cursor database_cursor 874 return (1) 875 end 876 fetch database_cursor into @dbname, @dbid 877 end 878 879 close database_cursor 880 deallocate cursor database_cursor 881 882 if not exists (select 1 from #sys_encr_passwd_info) 883 begin 884 /* 885 ** 19782, "The system encryption password has 886 ** not been set for all available databases." 887 */ 888 raiserror 19782 889 return (1) 890 end 891 end /* end of all_dbs */ 892 /* 893 ** SP_ENCRYPTION HELP|HELPKEY, SYSTEM_ENCR_PASSWD 894 ** Display the system encryption password information in 895 ** the current database. 896 */ 897 else 898 begin 899 /* Only SSO, KC, or DBO can run this command */ 900 if (@sso_role = 0 and @keycustodian_role = 0 and (user_name() != "dbo")) 901 begin 902 /* 903 ** 19780, "You must be the Database Owner (DBO), or 904 ** possess either the System Security Officer (SSO) or 905 ** Key Custodian (KC) role to execute this command/procedure. 906 */ 907 raiserror 19780 908 return (1) 909 end 910 911 select @dbname = db_name() 912 select @auxproc = 913 @dbname + ".dbo.sp_aux_sys_encr_passwd_lookup" 914 915 exec @auxproc @dbname 916 917 if not exists (select 1 from #sys_encr_passwd_info) 918 begin 919 /* 920 ** 19781, "The system encryption password has 921 ** not been set for the current database." 922 */ 923 raiserror 19781 924 return (1) 925 end 926 927 end 928 929 /* 930 ** Display the system encryption password info 931 */ 932 exec sp_autoformat @fulltabname = #sys_encr_passwd_info, 933 @selectlist = "'Database' = dbname, 'Type of system_encr_passwd' = typeofpasswd, 'Last modified by' = modifier, 'Date' = moddate ", 934 @orderby = "dbname " 935 936 drop table #sys_encr_passwd_info 937 938 return (0) 939 940 941 end --} 942 /* 943 ** SP_ENCRYPTION HELP|HELPKEY, MASTER | DUAL MASTER, DISPLAY_KEYS 944 ** Displays keys in the current database, which are encrypted by the 945 ** (dual) master key. 946 */ 947 else if (((@opt1 = "master") or (@opt1 = "dual master")) and (@opt2 = "display_keys")) 948 begin -- { master key or dual master key, display keys 949 declare @masterkey int 950 951 /* Accumulate encryption key info in a temporary table */ 952 create table #encr_display_keys_info(assignee varchar(30) null, 953 keyname varchar(255) null, 954 keyowner varchar(30) null, 955 fullkeyname varchar(285) null) 956 957 /* 958 ** If @opt1 equals master key, then we will check for the bit to 959 ** be set to either @status_master_encr or @status_dualcontrol 960 ** The only time it is a dual master key is when both 961 ** @dual_control and @status_master_encr are set. 962 ** @masterkey is only used to determine if we are looking for 963 ** keys encrypted with "master key" or keys encrupted with 964 ** "dual master key". 965 */ 966 967 /* Only SSO, KC, or DBO can run this command */ 968 if (@sso_role = 0 and @keycustodian_role = 0 and (user_name() != "dbo")) 969 begin 970 /* 971 ** 19780, "You must be the Database Owner (DBO), or 972 ** possess either the System Security Officer (SSO) or 973 ** Key Custodian (KC) role to execute this command/procedure. 974 */ 975 raiserror 19780 976 return (1) 977 end 978 979 if (@opt1 = "master") 980 begin 981 select @masterkey = 1 982 end 983 else 984 begin -- dual master key 985 select @masterkey = 0 986 end 987 988 insert #encr_display_keys_info(assignee, keyname, keyowner) 989 select user_name(e1.uid), o1.name, user_name(o1.uid) 990 from sysobjects o1, sysencryptkeys e1 991 where o1.id = e1.id 992 and o1.type = "EK" 993 -- Check for Master Key. 994 and (((@masterkey = 1) 995 and e1.status & (@status_master_encr | @status_dualcontrol) > 0) 996 -- Check for Dual Master key 997 or ((@masterkey = 0) and 998 ((e1.status & (@status_master_encr | @status_dualcontrol)) = (@status_master_encr | @status_dualcontrol)))) 999 1000 if not exists (select 1 from #encr_display_keys_info) 1001 begin 1002 /* 1003 ** 19955, "There are no encryption keys (key copies) 1004 ** encrypted with the '%1!' in the current database. 1005 */ 1006 raiserror 19955, @opt1 1007 return (1) 1008 end 1009 /* Display the encryption key info */ 1010 /* update #encr_keys_info to fill fullkeyname column */ 1011 update #encr_display_keys_info 1012 set fullkeyname = keyowner + "." + keyname 1013 where fullkeyname is NULL 1014 exec sp_autoformat @fulltabname = #encr_display_keys_info, 1015 @selectlist = "'Owner.Keyname' = fullkeyname, 'Assignee' = assignee", 1016 @orderby = "order by fullkeyname" 1017 drop table #encr_display_keys_info 1018 end --} master key or dual master key, display keys 1019 1020 /* 1021 ** SP_ENCRYPTION HELP|HELPKEY 1022 ** SP_ENCRYPTION HELP|HELPKEY, KEY_NAME|WILDCARD 1023 ** [, ALL_DBS | KEY_COPY | DISPLAY_COLS | display_objs] 1024 */ 1025 else 1026 begin --{ 1027 /* 1028 ** Validate the current database and disambiguate the fully qualified 1029 ** column name 1030 */ 1031 select @objname = @opt1 1032 select @opt1_buf = @opt1 1033 if (@opt1_buf like "%.%.%") 1034 begin 1035 if ((substring(@opt1_buf, 1, charindex(".", @opt1_buf) - 1) != 1036 db_name()) and (@opt2 != "all_dbs")) 1037 begin 1038 /* 17460, "Object must be in the current database." */ 1039 raiserror 17460 1040 return (1) 1041 end 1042 1043 /* Extract the database name */ 1044 select @keydbname = substring(@opt1_buf, 1, charindex(".", 1045 @opt1_buf) - 1) 1046 select @opt1_buf = substring(@opt1_buf, 1047 charindex(".", @opt1_buf) + 1, 1048 char_length(@opt1_buf)) 1049 /* Extract the owner name */ 1050 select @owner = substring(@opt1_buf, 1, charindex(".", 1051 @opt1_buf) - 1) 1052 /* Extract the object name */ 1053 select @objname = substring(@opt1_buf, charindex(".", 1054 @opt1_buf) + 1, char_length(@opt1_buf)) 1055 1056 end 1057 if (@opt1_buf like "%.%") 1058 begin 1059 /* Extract the owner name */ 1060 select @owner = substring(@opt1_buf, 1, charindex(".", @opt1_buf) - 1) 1061 /* Extract the object name */ 1062 select @objname = substring(@opt1_buf, charindex(".", @opt1_buf) + 1, 1063 char_length(@opt1_buf)) 1064 end 1065 1066 if (@opt1 is NULL) 1067 begin 1068 select @opt1 = "%" 1069 end 1070 1071 if (@keydbname is NULL) 1072 begin 1073 select @keydbname = "%" 1074 end 1075 1076 if (@owner is NULL) 1077 begin 1078 select @owner = "%" 1079 end 1080 1081 if (@objname is NULL) 1082 begin 1083 select @objname = "%" 1084 end 1085 1086 1087 if (@objname = "master") 1088 begin 1089 select @objname = "sybencrmasterkey" 1090 end 1091 1092 if (@objname = "dual master") 1093 begin 1094 select @objname = "sybencrdualmasterkey" 1095 end 1096 1097 /* 1098 ** SP_ENCRYPTION HELP|HELPKEY, KEYNAME|WILDCARD, DISPLAY_COLS 1099 ** 1100 ** Non-privileged user gets info only in current db and sso gets 1101 ** info across all dbs. 1102 */ 1103 if ((@opt2 = "display_cols")) 1104 begin -- { 1105 1106 /* 1107 ** master and dual master keys are never used to encrypt 1108 ** columns. 1109 */ 1110 if (@objname = "master" or @objname = "dual master") 1111 begin 1112 /* 1113 ** 19657, "There are no columns encrypted 1114 ** with key like '%1!' in all available 1115 ** databases." 1116 */ 1117 raiserror 19657, @opt1 1118 return (1) 1119 end 1120 if not exists (select 1 from (select o1.name 1121 from sysobjects o1 1122 where o1.type = "EK" 1123 and o1.name like @objname 1124 and user_name(o1.uid) like @owner) t) 1125 1126 begin 1127 /* 1128 ** 19603, "There are no encryption keys (key copies) like 1129 ** '%1!' in '%2!'." 1130 */ 1131 select @dbname = db_name() 1132 raiserror 19603, @opt1, @dbname 1133 return (1) 1134 end 1135 1136 /* Accumulate keys and columns in a temporary table */ 1137 create table #encrypted_column_info( 1138 keyname varchar(255) null, 1139 keyowner varchar(30) null, 1140 dbname varchar(30) null, 1141 username varchar(30) null, 1142 objname varchar(255) null, 1143 colname varchar(255) null) 1144 1145 /* login with sso_role */ 1146 if (@sso_role > 0) 1147 begin -- { 1148 1149 declare database_cursor cursor for 1150 select name, dbid 1151 1152 from master.dbo.sysdatabases 1153 /* Skip databases that have not recovered */ 1154 where status & @db_unrecovered = 0 1155 /* Skip databases that are offline */ 1156 and status2 & @db_offline = 0 1157 /* Skip databases that are not recovered */ 1158 and status & @db_notrec = 0 1159 /* Skip databases that will not be recovered */ 1160 and status & @db_inlddb = 0 1161 /* Skip databases that are archive databases */ 1162 and status3 & @db_archivedb = 0 1163 1164 open database_cursor 1165 fetch database_cursor into @dbname, @dbid 1166 1167 1168 select @keydbname = db_name() 1169 1170 while (@@sqlstatus = 0) 1171 begin 1172 1173 1174 select @auxproc = 1175 @dbname + ".dbo.sp_aux_encr_lookup" 1176 1177 /* 1178 ** Since sp_aux_encr_lookup is a system 1179 ** stored procedure, it will execute in the 1180 ** context of dbname if it is called as 1181 ** dbname..sp_aux_encr_lookup, regardless of 1182 ** which db it is called from. 1183 ** 1184 ** This auxillary procedure is used to extract 1185 ** the qualified column name that the specified 1186 ** key encrypts. 1187 */ 1188 1189 exec @auxproc @owner, @objname, @keydbname 1190 1191 select @retval = @@error, @rows = @@rowcount 1192 1193 if (@retval != 0) 1194 begin 1195 close database_cursor 1196 deallocate cursor database_cursor 1197 return (1) 1198 end 1199 1200 if (@rows != 0) 1201 begin 1202 update #encrypted_column_info 1203 set dbname = @dbname 1204 where dbname is NULL 1205 1206 end 1207 1208 fetch database_cursor into @dbname, @dbid 1209 1210 end 1211 close database_cursor 1212 deallocate cursor database_cursor 1213 1214 if not exists (select 1 from #encrypted_column_info) 1215 begin 1216 /* 1217 ** 19657, "There are no columns encrypted 1218 ** with key like '%1!' in all available 1219 ** databases." 1220 */ 1221 raiserror 19657, @opt1 1222 return (1) 1223 end 1224 1225 1226 1227 end -- }/* Login with sso_role */ 1228 1229 else /* Login without sso_role */ 1230 1231 /* 1232 ** ENCR2_RESOLVE: Currently there is no graceful way (without 1233 ** warnings) to check and see if a login is a valid user in a 1234 ** database. A new Built-in has been proposed to check this. 1235 ** So until this is done, non-privileged users get key-column 1236 ** association information only from current database. 1237 */ 1238 begin -- { 1239 1240 insert #encrypted_column_info(keyname, keyowner, 1241 username, objname, colname) 1242 select o2.name, user_name(o2.uid), user_name(o1.uid), o1.name, 1243 c.name 1244 from syscolumns c, sysobjects o1, sysobjects o2 1245 where c.id = o1.id 1246 and c.encrkeyid = o2.id 1247 and o2.type in ('EK') 1248 and o2.name like @objname 1249 and user_name(o2.uid) like @owner 1250 1251 if not exists (select 1 from #encrypted_column_info) 1252 begin 1253 /* 1254 ** 19605, "There are no columns encrypted 1255 ** with key like '%1!' in the current database." 1256 */ 1257 raiserror 19605, @opt1 1258 return (1) 1259 end 1260 1261 end -- } /* Login without sso_role */ 1262 1263 1264 1265 /* 1266 ** Fill dbname with current database name 1267 ** Note that dbname is not null for sso_role case 1268 ** because it is already filled 1269 */ 1270 update #encrypted_column_info 1271 set dbname = db_name() 1272 where dbname is NULL 1273 1274 /* 1275 ** Display the encrypted column info 1276 */ 1277 exec sp_autoformat @fulltabname = #encrypted_column_info, 1278 @selectlist = "'Key Name' = keyname, 'Key Owner' = keyowner, 'Database Name' = dbname, 'Table Owner' = username, 'Table Name' = objname, 'Column Name' = colname", 1279 @orderby = "order by keyname, keyowner, dbname, username, objname, colname " 1280 1281 drop table #encrypted_column_info 1282 1283 return (0) 1284 end -- } display cols 1285 1286 /* 1287 ** SP_ENCRYPTION HELP|HELPKEY, KEYNAME, DISPLAY_OBJS 1288 ** 1289 ** Display objects in current database protected by 1290 ** service key either syb_extpasswdkey or syb_syscommkey. 1291 */ 1292 if ((@opt2 = "display_objs")) 1293 begin -- { 1294 if ((@objname != "syb_extpasswdkey") and 1295 (@objname not like "syb_syscommkey%")) 1296 1297 begin -- { 1298 /* 1299 ** display_objs is only valid when key 1300 ** name is service key 1301 */ 1302 1303 /* 18291, "The parameter value '%1!' is invalid." */ 1304 raiserror 18291, @opt2 1305 return 1 1306 end -- } 1307 1308 if not exists (select 1 from (select o1.name 1309 from sysobjects o1 1310 where o1.type = "EK" 1311 and o1.name like @objname 1312 and user_name(o1.uid) like @owner) t) 1313 begin 1314 /* 1315 ** 19603, "There are no encryption keys (key copies) like 1316 ** '%1!' in '%2!'." 1317 */ 1318 select @dbname = db_name() 1319 raiserror 19603, @opt1, @dbname 1320 return (1) 1321 end 1322 1323 /* Only SSO, KC, or DBO can run this command */ 1324 if (@sso_role = 0 and @keycustodian_role = 0 1325 and (user_name() != "dbo")) 1326 begin 1327 /* 1328 ** 19780, "You must be the Database Owner (DBO), or 1329 ** possess either the System Security Officer (SSO) or 1330 ** Key Custodian (KC) role to execute this command/procedure. 1331 */ 1332 raiserror 19780 1333 return (1) 1334 end 1335 1336 select @dbname = db_name() 1337 1338 1339 if ((@opt1 = "syb_extpasswdkey")) 1340 begin -- { 1341 1342 /* get key owner */ 1343 select @owner = user_name(o1.uid) 1344 from sysobjects o1 1345 where o1.type = "EK" 1346 and o1.name like @objname 1347 and user_name(o1.uid) like @owner 1348 /* 1349 ** Put external passwords info into a 1350 ** temporary table 1351 */ 1352 create table #encrypted_extpasswd_info( 1353 keyname varchar(255) null, 1354 keyowner varchar(30) null, 1355 dbname varchar(30) null, 1356 passwdtype varchar(50) null, 1357 extllogin varchar(255) null) 1358 1359 /* Check ssl password */ 1360 if exists (select 1 from sysattributes 1361 where class = 15 and 1362 char_value like "5:%") 1363 begin 1364 insert #encrypted_extpasswd_info 1365 values (@objname, @owner, @dbname, 1366 "SSL_CERTIFICATE_PASSWORD", "-") 1367 1368 end 1369 1370 /* Check primary LDAP account password */ 1371 if exists (select 1 from sysattributes 1372 where class = 17 and attribute = 3 and 1373 char_value like "5:%") 1374 begin 1375 insert #encrypted_extpasswd_info 1376 values (@objname, @owner, @dbname, 1377 "PRIMARY_LDAPACCESS_PASSWORD", "-") 1378 end 1379 1380 /* Check secondary LDAP account password */ 1381 if exists (select 1 from sysattributes 1382 where class = 17 and attribute = 8 and 1383 char_value like "5:%") 1384 begin 1385 insert #encrypted_extpasswd_info 1386 values (@objname, @owner, @dbname, 1387 "SECONDARY_LDAPACCESS_PASSWORD", "-") 1388 end 1389 1390 /* Check RA password */ 1391 declare rapasswd_cursor cursor for 1392 select object_info1 from sysattributes 1393 where class = 11 and attribute = 2 and 1394 char_value like "5:%" 1395 open rapasswd_cursor 1396 fetch rapasswd_cursor into @rpid 1397 while (@@sqlstatus = 0) 1398 begin -- { 1399 insert #encrypted_extpasswd_info 1400 select @objname, @owner, @dbname, 1401 "REPAGNT_RS_PASSWORD", 1402 case 1403 when (@rpid is null or @rpid = 0) 1404 then "Default" 1405 else 1406 (select att1.char_value 1407 from sysattributes att1, sysattributes att2, sysattributes att3 1408 where att1.class = 41 and att1.attribute = 22 1409 and att2.int_value = 0 and att1.object_info1 = att2.object_info1 1410 and att3.attribute = 20 and att3.object_info1 = att2.object_info1 1411 and att3.object_info2 = (select object_info1 from sysattributes 1412 where class = 41 and attribute = 0 and object_info2 = @rpid)) 1413 end 1414 fetch rapasswd_cursor into @rpid 1415 end -- } 1416 close rapasswd_cursor 1417 deallocate cursor rapasswd_cursor 1418 1419 /* Check CIS external login passwords */ 1420 declare extlpasswd_cursor cursor for 1421 select object from sysattributes 1422 where class = 9 and attribute = 0 and 1423 image_value like "5:%" 1424 1425 open extlpasswd_cursor 1426 fetch extlpasswd_cursor into @userid 1427 while (@@sqlstatus = 0) 1428 begin -- { beginning of while loop. 1429 insert #encrypted_extpasswd_info 1430 values (@objname, @owner, @dbname, 1431 "CIS_LOGIN_PASSWORD", 1432 suser_name(@userid)) 1433 1434 fetch extlpasswd_cursor into @userid 1435 end -- } end of cursor loop 1436 close extlpasswd_cursor 1437 deallocate cursor extlpasswd_cursor 1438 1439 /* Check RTMS external login passwords */ 1440 declare rtmspasswd_cursor cursor for 1441 select object from sysattributes 1442 where class = 21 and 1443 image_value like "5:%" and 1444 attribute = 0 1445 1446 open rtmspasswd_cursor 1447 fetch rtmspasswd_cursor into @userid 1448 while (@@sqlstatus = 0) 1449 begin -- { beginning of while loop. 1450 insert #encrypted_extpasswd_info 1451 values (@objname, @owner, @dbname, 1452 "RTMS_LOGIN_PASSWORD", 1453 suser_name(@userid)) 1454 1455 fetch rtmspasswd_cursor into @userid 1456 end -- } end of cursor loop 1457 close rtmspasswd_cursor 1458 deallocate cursor rtmspasswd_cursor 1459 1460 /* Check RTMS subscription passwords */ 1461 declare rtmssubpasswd_cursor cursor for 1462 select object from sysattributes 1463 where class = 21 and 1464 image_value like "5:%" and 1465 attribute = 1 1466 1467 open rtmssubpasswd_cursor 1468 fetch rtmssubpasswd_cursor into @userid 1469 while (@@sqlstatus = 0) 1470 begin -- { beginning of while loop. 1471 insert #encrypted_extpasswd_info 1472 values (@objname, @owner, @dbname, 1473 "RTMS_SUBSCRIPTION_PASSWORD", 1474 "-") 1475 fetch rtmssubpasswd_cursor into @userid 1476 end -- } end of cursor loop 1477 close rtmssubpasswd_cursor 1478 deallocate cursor rtmssubpasswd_cursor 1479 1480 1481 1482 /* 1483 ** Display the encrypted external password info 1484 */ 1485 exec sp_autoformat @fulltabname = #encrypted_extpasswd_info, 1486 @selectlist = "'Key Name' = keyname, 'Key Owner' = keyowner, 'Database Name' = dbname, 'Password Type' = passwdtype, 'Login Name' = extllogin", 1487 @orderby = "order by keyname, keyowner, dbname, passwdtype, extllogin" 1488 1489 drop table #encrypted_extpasswd_info 1490 1491 return (0) 1492 end -- } syb_extpasswdkey 1493 else if ((@opt1 like "syb_syscommkey%")) 1494 begin -- { 1495 /* 1496 ** Put encrypted hidden text info into a 1497 ** temporary table 1498 */ 1499 create table #encrypted_text_info( 1500 keyname varchar(255) null, 1501 keyowner varchar(30) null, 1502 dbname varchar(30) null, 1503 objowner varchar(30) null, 1504 objname varchar(30) null) 1505 1506 declare commkey_cursor cursor for 1507 select id 1508 from sysobjects 1509 where type = "EK" 1510 and name like @objname 1511 and user_name(uid) like @owner 1512 1513 open commkey_cursor 1514 fetch commkey_cursor into @keyid 1515 while (@@sqlstatus = 0) 1516 begin -- { beginning of while loop. 1517 1518 insert #encrypted_text_info(keyname, keyowner, 1519 objowner, objname) 1520 select o1.name, user_name(o1.uid), user_name(o2.uid), 1521 o2.name 1522 from syscomments m, sysobjects o1, sysobjects o2 1523 where m.encrkeyid = @keyid 1524 and m.id = o2.id and m.colid = 1 1525 and o1.id = @keyid 1526 1527 fetch commkey_cursor into @keyid 1528 end -- } end of while loop 1529 1530 close commkey_cursor 1531 1532 update #encrypted_text_info 1533 set dbname = @dbname 1534 where dbname is NULL 1535 1536 /* 1537 ** Display the encrypted hidden text info 1538 */ 1539 exec sp_autoformat @fulltabname = #encrypted_text_info, 1540 @selectlist = "'Key Name' = keyname, 'Key Owner' = keyowner, 'Database Name' = dbname, 'Object Owner' = objowner, 'Object Name' = objname", 1541 @orderby = "order by keyname, keyowner, dbname, objowner, objname" 1542 1543 drop table #encrypted_text_info 1544 1545 return (0) 1546 end -- } syb_syscommkey 1547 1548 end -- } display objs 1549 1550 1551 1552 /* 1553 ** SP_ENCRYPTION HELP|HELPKEY, KEYNAME|WILDCARD [, ALL_DBS]] 1554 */ 1555 if ((@opt2 is NULL) or (@opt2 = "all_dbs")) 1556 begin -- { 1557 /* Accumulate encryption key info in a temporary table */ 1558 create table #encr_column_info(keyname varchar(255), 1559 keyowner varchar(30), 1560 keydbname varchar(30), 1561 keylength int, 1562 keyalgorithm varchar(30), 1563 keytype int, 1564 random_pad int, 1565 init_vector int, 1566 protectedby varchar(45), 1567 keyrecovery int) 1568 1569 /* Create a temporary table to store the keycopynum info */ 1570 create table #encr_column_count(keyname varchar(255), 1571 keyowner varchar(30), 1572 keydbname varchar(30), 1573 keylength int, 1574 keyalgorithm varchar(30), 1575 keytype varchar(30), 1576 random_pad int, 1577 init_vector int, 1578 protectedby varchar(45), 1579 keyrecovery int, 1580 keycopynum int, 1581 fullkeyname varchar(315) null) 1582 /* 1583 ** SP_ENCRYPTION HELP|HELPKEY, KEYNAME|WILDCARD, ALL_DBS 1584 ** 1585 ** Display key properties across all databases 1586 */ 1587 if (@opt2 = "all_dbs") 1588 begin --{ Display key properties across all databases 1589 /* check if user has sso role, proc_role will 1590 ** also do auditing if required. 1591 ** proc_role will also print error message if required. 1592 */ 1593 if (proc_role("sso_role") = 0) 1594 begin 1595 return (1) 1596 end 1597 1598 declare db_cursor cursor for 1599 select name, dbid 1600 from master.dbo.sysdatabases 1601 /* Skip databases that have not recovered */ 1602 where status & @db_unrecovered = 0 1603 /* Skip databases that are offline */ 1604 and status2 & @db_offline = 0 1605 /* Skip databases that are not recovered */ 1606 and status & @db_notrec = 0 1607 /* Skip databases that will not be recovered */ 1608 and status & @db_inlddb = 0 1609 /* Skip databases that are archive databases */ 1610 and status3 & @db_archivedb = 0 1611 1612 /* 1613 ** Loop through only matching specified 1614 ** database names. 1615 */ 1616 and name like @keydbname 1617 1618 1619 open db_cursor 1620 fetch db_cursor into @dbname, @dbid 1621 1622 while (@@sqlstatus = 0) 1623 begin 1624 1625 /* 1626 ** 1627 ** Since sp_aux_encrkey_info is a system 1628 ** stored procedure, it will execute in the 1629 ** context of dbname if it is called as 1630 ** dbname..sp_aux_encrkey_info, regardless of 1631 ** which db it is called from. 1632 ** 1633 ** This auxillary procedure is used to extract 1634 ** the key properties. 1635 */ 1636 select @auxproc = 1637 @dbname + ".dbo.sp_aux_encrkey_info" 1638 1639 exec @auxproc @owner, @objname 1640 1641 select @retval = @@error 1642 if (@retval != 0) 1643 begin 1644 close db_cursor 1645 deallocate cursor db_cursor 1646 return (1) 1647 end 1648 1649 fetch db_cursor into @dbname, @dbid 1650 end 1651 1652 close db_cursor 1653 deallocate cursor db_cursor 1654 1655 /* 1656 ** Inserting into temporary table because sp_autoformat 1657 ** currently does not support group by clause. 1658 ** We display number of keycopies (not including the keybase one), 1659 ** That's the reason of using count(*)-1 in the following sql. 1660 */ 1661 insert #encr_column_count(keyname, keyowner, keydbname, 1662 keylength, keyalgorithm, 1663 keytype, random_pad, init_vector, 1664 protectedby, keyrecovery, keycopynum) 1665 select t1.keyname, t1.keyowner, t1.keydbname, t1.keylength 1666 , t1.keyalgorithm 1667 , case 1668 when (t1.keytype & @keytype_master) > 0 1669 then "symmetric master key" 1670 when (t1.keytype & @keytype_dualmaster) > 0 1671 then "symmetric dual master key" 1672 when (t1.keytype & @keytype_passwd_svckey) > 0 1673 then "symmetric service key" 1674 when (t1.keytype & @keytype_text_svckey) > 0 and 1675 (t1.keytype & @default_key) > 0 1676 then "symmetric default service key" 1677 when (t1.keytype & @keytype_text_svckey) > 0 1678 then "symmetric service key" 1679 when (t1.keytype & @default_key) > 0 1680 then "symmetric default key" 1681 else "symmetric key" 1682 end 1683 , t1.random_pad 1684 , t1.init_vector, t1.protectedby, t1.keyrecovery 1685 , t2.keycopynum 1686 from #encr_column_info t1, (select t3.keyname as keyname, 1687 t3.keyowner as keyowner, 1688 t3.keydbname as keydbname, 1689 count(*) - 1 as keycopynum 1690 from #encr_column_info t3 1691 group by t3.keydbname, t3.keyowner, t3.keyname 1692 ) t2 1693 where (t1.keytype & @key_copy) = 0 1694 and t1.keyname = t2.keyname 1695 and t1.keydbname = t2.keydbname 1696 and t1.keyowner = t2.keyowner 1697 1698 if not exists (select 1 from #encr_column_count) 1699 begin 1700 /* 1701 ** 19603, "There are no encryption keys (key copies) like 1702 ** '%1!' in '%2!'." 1703 */ 1704 raiserror 19603, @opt1, "all databases" 1705 return (1) 1706 end 1707 1708 1709 /* update #encr_column_count to fill fullkeyname column */ 1710 begin 1711 update #encr_column_count 1712 set fullkeyname = keydbname + "." + keyowner + "." + keyname 1713 where fullkeyname is NULL 1714 end 1715 1716 /* Display the encryption key info */ 1717 exec sp_autoformat @fulltabname = #encr_column_count, 1718 @selectlist = " 'Db.Owner.Keyname' = fullkeyname, 1719 'Key Length' = keylength, 1720 'Key Algorithm' = keyalgorithm, 1721 'Key Type' = keytype, 1722 'Pad' = random_pad, 1723 'Initialization Vector' = init_vector, 1724 'Protected By' = protectedby, 1725 'Key Recovery' = keyrecovery, 1726 '# of Key Copies'= keycopynum", 1727 @orderby = "order by fullkeyname" 1728 end --} /* Display key properties across all dbs */ 1729 else 1730 1731 /* 1732 ** SP_ENCRYPTION HELP|HELPKEY [, KEYNAME | WILDCARD] 1733 ** 1734 ** Lists the properties of base keys in current database 1735 */ 1736 begin --{ Lists the properties of base keys in current database. 1737 select @dbname = db_name() 1738 /* 1739 ** Since sp_aux_encrkey_info is a system 1740 ** stored procedure, it will execute in the 1741 ** context of dbname if it is called as 1742 ** dbname..sp_aux_encrkey_info, regardless of 1743 ** which db it is called from. 1744 ** 1745 ** This auxillary procedure is used to extract 1746 ** the key properties. 1747 */ 1748 select @auxproc = 1749 @dbname + ".dbo.sp_aux_encrkey_info" 1750 1751 exec @auxproc @owner, @objname 1752 1753 select @retval = @@error 1754 if (@retval != 0) 1755 begin 1756 return (1) 1757 end 1758 1759 /* 1760 ** Inserting into temporary table because sp_autoformat 1761 ** currently does not support group by clause. 1762 */ 1763 1764 /* 1765 ** A typical #encr_column_info t3 before the following query statement 1766 ** is like: 1767 ** 1768 ** keydbname keyname keyowner keylength ... keyrecovery keycopynum 1769 ** --------- ------- -------- --------- ----------- ---------- 1770 ** tdb1 key1 dbo 128 0 0 (base key) 1771 ** tdb1 key1 dbo 128 1 0 (key copy) 1772 ** tdb1 key1 dbo 128 0 0 (key copy) 1773 ** tdb1 key2 dbo 128 0 0 (base key) 1774 ** 1775 ** sp_encryption help only shows key information about the base keys. 1776 ** We use the subquery in the following statement to get the correct value of 1777 ** keyrecovery and keycopynum. 1778 ** 1779 */ 1780 1781 insert #encr_column_count(keyname, keyowner, keydbname, 1782 keylength, keyalgorithm, 1783 keytype, random_pad, init_vector, 1784 protectedby, keyrecovery, keycopynum) 1785 select t1.keyname, t1.keyowner, t1.keydbname, t1.keylength 1786 , t1.keyalgorithm 1787 , case 1788 when (t1.keytype & @keytype_master) > 0 1789 then "symmetric master key" 1790 when (t1.keytype & @keytype_dualmaster) > 0 1791 then "symmetric dual master key" 1792 when (t1.keytype & @keytype_passwd_svckey) > 0 1793 then "symmetric service key" 1794 when (t1.keytype & @keytype_text_svckey) > 0 and 1795 (t1.keytype & @default_key) > 0 1796 then "symmetric default service key" 1797 when (t1.keytype & @keytype_text_svckey) > 0 1798 then "symmetric service key" 1799 when (t1.keytype & @default_key) > 0 1800 then "symmetric default key" 1801 else "symmetric key" 1802 1803 end 1804 , t1.random_pad 1805 , t1.init_vector 1806 , t1.protectedby 1807 , t2.keyrecovery 1808 , t2.keycopynum 1809 from #encr_column_info t1, (select t3.keyname as keyname, 1810 t3.keyowner as keyowner, 1811 t3.keydbname as keydbname, 1812 sum(t3.keyrecovery) as keyrecovery, 1813 count(t3.keyname) - 1 as keycopynum 1814 from #encr_column_info t3 1815 group by t3.keydbname, t3.keyowner, t3.keyname 1816 ) t2 1817 where (t1.keytype & @key_copy) = 0 1818 and t1.keyname = t2.keyname 1819 and t1.keyowner = t2.keyowner 1820 and t1.keydbname = t2.keydbname 1821 1822 if not exists (select 1 from #encr_column_count) 1823 begin 1824 /* 1825 ** 19603, "There are no encryption keys (key copies) like 1826 ** '%1!' in '%2!'." 1827 */ 1828 raiserror 19603, @opt1, @dbname 1829 return (1) 1830 end 1831 1832 if ((@sso_role > 0) or (@keycustodian_role > 0) 1833 or (user_name() = "dbo")) 1834 begin 1835 /* Display the encryption key info */ 1836 exec sp_autoformat @fulltabname = #encr_column_count, 1837 @selectlist = " 'Key Name' = keyname, 1838 'Key Owner' = keyowner, 1839 'Key Length' = keylength, 1840 'Key Algorithm' = keyalgorithm, 1841 'Key Type' = keytype, 1842 'Pad' = random_pad, 1843 'Initialization Vector' = init_vector, 1844 'Protected By' = protectedby, 1845 'Key Recovery' = keyrecovery, 1846 '# of Key Copies' = keycopynum", 1847 @orderby = "order by keyname, keyowner" 1848 end 1849 else 1850 begin 1851 /* 1852 ** Display the encryption key info for 1853 ** non-privileged user 1854 */ 1855 exec sp_autoformat @fulltabname = #encr_column_count, 1856 @selectlist = " 'Key Name' = keyname, 1857 'Key Owner' = keyowner, 1858 'Key Type' = keytype", 1859 @orderby = "order by keyname, keyowner" 1860 end 1861 end --} Lists the properties of base keys in current database. 1862 drop table #encr_column_info 1863 drop table #encr_column_count 1864 end -- } Null or all dbs 1865 1866 /* 1867 ** SP_ENCRYPTION HELP|HELPKEY, KEYNAME|WILDCARD, KEY_COPY 1868 ** 1869 ** Lists all the user access copies of a given key in the current 1870 ** database. 1871 */ 1872 if ((@opt2 = "key_copy")) 1873 begin -- { key_copy 1874 /* Accumulate encryption key info in a temporary table */ 1875 create table #encryption_keys_info(keyowner varchar(30) null, 1876 keyname varchar(255) null, 1877 assignee varchar(30) null, 1878 protectedby varchar(43) null, 1879 keyrecovery int null, 1880 fullkeyname varchar(285) null) 1881 1882 if ((@sso_role > 0) or (@keycustodian_role > 0) 1883 or (user_name() = "dbo")) 1884 begin -- { so_role 1885 insert #encryption_keys_info(keyowner, keyname, assignee, 1886 protectedby, keyrecovery) 1887 select user_name(o1.uid), o1.name, user_name(e1.uid) 1888 , case (e1.status & (@status_dualcontrol 1889 | @status_master_encr | @login_access 1890 | @login_password | @system_password 1891 | @user_password | @status_mek_encr 1892 | @status_static_encr)) 1893 when (@login_access | @system_password) then "login access" 1894 when (@login_access | @status_master_encr) then "login access" 1895 when @login_password then "login password" 1896 when @system_password then "system encryption password" 1897 when @user_password then "user password" 1898 when @status_master_encr then "master key" 1899 when (@status_dualcontrol | @user_password) 1900 then "dual_control(master key + user password)" 1901 when (@status_dualcontrol | @login_access | @status_master_encr) 1902 then "dual_control(login access)" 1903 when (@status_dualcontrol | @login_password) 1904 then "dual_control(master key + login password)" 1905 when (@status_dualcontrol | @status_master_encr) 1906 then "dual_control(master key + dual master key)" 1907 when (@status_mek_encr) 1908 then "automatic startup key" 1909 when (@status_static_encr) 1910 then "static key" 1911 end 1912 , case (e1.type & @key_recovery) 1913 when 0 then 0 1914 else 1 1915 end 1916 from sysobjects o1, sysencryptkeys e1 1917 where o1.id = e1.id 1918 and e1.type & @key_copy > 0 1919 and o1.name like @objname 1920 and user_name(o1.uid) like @owner 1921 1922 if not exists (select 1 from #encryption_keys_info) 1923 begin 1924 /* 1925 ** 19603, "There are no encryption keys (key copies) like 1926 ** '%1!' in '%2!'." 1927 */ 1928 select @dbname = db_name() 1929 raiserror 19603, @opt1, @dbname 1930 return (1) 1931 end 1932 1933 /* update #encryption_keys_info to fill fullkeyname column */ 1934 update #encryption_keys_info 1935 set fullkeyname = keyowner + "." + keyname 1936 where fullkeyname is NULL 1937 1938 /* Display the encryption key info */ 1939 exec sp_autoformat @fulltabname = #encryption_keys_info, 1940 @selectlist = "'Owner.Keyname' = fullkeyname, 1941 'Assignee' = assignee, 1942 'Protected By' = protectedby, 1943 'Key Recovery' = keyrecovery", 1944 @orderby = "order by fullkeyname" 1945 end -- } sso_role 1946 else 1947 begin -- { no sso_role 1948 /* 1949 ** Display the encryption key info for non-privileged user. 1950 ** A non-privileged key owner can see key copies 1951 ** of his key, and other non-privileged users can only see key copies 1952 ** that were assigned to them. 1953 */ 1954 insert #encryption_keys_info(keyowner, keyname, assignee, 1955 protectedby, keyrecovery) 1956 select user_name(o1.uid) 1957 , o1.name 1958 , user_name(e1.uid) 1959 , case (e1.status & (@status_dualcontrol 1960 | @status_master_encr | @login_access 1961 | @login_password | @system_password 1962 | @user_password | @status_mek_encr 1963 | @status_static_encr)) 1964 when (@login_access | @system_password) then "login access" 1965 when (@login_access | @status_master_encr) then "login access" 1966 when @login_password then "login password" 1967 when @system_password then "system encryption password" 1968 when @user_password then "user password" 1969 when @status_master_encr then "master key" 1970 when (@status_dualcontrol | @user_password) 1971 then "dual_control(master key + user password)" 1972 when (@status_dualcontrol | @login_access | @status_master_encr) 1973 then "dual_control(login access)" 1974 when (@status_dualcontrol | @login_password) 1975 then "dual_control(master key + login password)" 1976 when (@status_dualcontrol | @status_master_encr) 1977 then "dual_control(master key + dual master key)" 1978 when (@status_mek_encr) 1979 then "automatic startup key" 1980 when (@status_static_encr) 1981 then "static key" 1982 end 1983 , case (e1.type & @key_recovery) 1984 when 0 then 0 1985 else 1 1986 end 1987 from sysobjects o1, sysencryptkeys e1 1988 where o1.id = e1.id 1989 and e1.type & @key_copy > 0 1990 and o1.name like @objname 1991 and (user_name(e1.uid) = user_name() 1992 or user_name(o1.uid) = user_name()) 1993 1994 /* to check if we get something */ 1995 if not exists (select 1 from #encryption_keys_info) 1996 begin 1997 /* 1998 ** 19648, "There are no encryption key copies 1999 ** like '%1!' assigned to '%2!' in '%3!'." 2000 */ 2001 select @dbname = db_name() 2002 raiserror 19648, @opt1, @callername, @dbname 2003 return (1) 2004 end 2005 2006 /* update #encryption_keys_info to fill fullkeyname column */ 2007 update #encryption_keys_info 2008 set fullkeyname = keyowner + "." + keyname 2009 where fullkeyname is NULL 2010 2011 /* Display the encryption key info */ 2012 exec sp_autoformat @fulltabname = #encryption_keys_info, 2013 @selectlist = "'Owner.Keyname' = fullkeyname, 2014 'Assignee' = assignee, 2015 'Protected By' = protectedby, 2016 'Key Recovery' = keyrecovery", 2017 @orderby = "order by fullkeyname" 2018 end -- } no sso_role 2019 drop table #encryption_keys_info 2020 end --} key_copy 2021 end --} 2022 end --} Display key related information 2023 2024 /* 2025 ** SP_ENCRYPTION HELPCOL [, COLUMNNAME] 2026 ** 2027 ** Displays matched column name in current database along with the key 2028 ** used to encrypt the column. 2029 2030 ** For sso, all dbs will be searched wheras for non-privileged users, it 2031 ** applies only to the current db. If the key is present in a different 2032 ** db, then for non-privileged users, the keyid will be displayed instead 2033 ** of keyname. 2034 */ 2035 if (@cmd = "helpcol") 2036 begin 2037 if (@opt2 is not null) 2038 begin 2039 /* 19213, "Invalid argument or unsupported command: %1!." */ 2040 raiserror 19213, @opt2 2041 return (1) 2042 end 2043 2044 /* 2045 ** Resolve @opt1 based on qualified name. 2046 ** The rules are as follows, 2047 ** 1) name -> looks for encrypted columns in table 'name'. 2048 ** If none found, looks for all columns of that 'name'. 2049 ** 2) name.name -> looks for existence of owner.table 2050 ** If none found, looks for single column table.column 2051 ** 3) name.name.name -> looks for column based on table.owner.name 2052 */ 2053 select @opt1_buf = @opt1 2054 if (@opt1_buf like "%.%.%.%") 2055 begin 2056 if (substring(@opt1_buf, 1, charindex(".", @opt1_buf) - 1) != 2057 db_name()) 2058 begin 2059 /* 17460, "Object must be in the current database." */ 2060 raiserror 17460 2061 return (1) 2062 end 2063 else 2064 begin 2065 /* Strip the database name */ 2066 select @opt1_buf = substring(@opt1_buf, 2067 charindex(".", @opt1_buf) + 1, 2068 char_length(@opt1_buf)) 2069 /* Extract the owner name */ 2070 select @owner = substring(@opt1_buf, 1, charindex(".", 2071 @opt1_buf) - 1) 2072 select @opt1_buf = substring(@opt1_buf, charindex(".", 2073 @opt1_buf) + 1, char_length(@opt1_buf)) 2074 /* Extract the table name */ 2075 select @objname = substring(@opt1_buf, 1, charindex(".", 2076 @opt1_buf) - 1) 2077 /* Extract the column name */ 2078 select @colname = substring(@opt1_buf, charindex(".", 2079 @opt1_buf) + 1, 2080 char_length(@opt1_buf)) 2081 end 2082 end 2083 else if (@opt1_buf like "%.%.%") 2084 begin 2085 /* Extract the owner name */ 2086 select @owner = substring(@opt1_buf, 1, charindex(".", @opt1_buf) - 1) 2087 select @opt1_buf = substring(@opt1_buf, charindex(".", @opt1_buf) + 1, 2088 char_length(@opt1_buf)) 2089 /* Extract the table name */ 2090 select @objname = substring(@opt1_buf, 1, charindex(".", @opt1_buf) - 1) 2091 /* Extract the column name */ 2092 select @colname = substring(@opt1_buf, charindex(".", @opt1_buf) + 1, 2093 char_length(@opt1_buf)) 2094 end 2095 else if (@opt1_buf like "%.%") 2096 begin 2097 /* Extract the table name */ 2098 select @owner = substring(@opt1_buf, 1, charindex(".", @opt1_buf) - 1) 2099 /* Extract the column name */ 2100 select @objname = substring(@opt1_buf, charindex(".", @opt1_buf) + 1, 2101 char_length(@opt1_buf)) 2102 select @colname = "%" 2103 if not exists (select 1 from sysobjects where name like @objname and 2104 user_name(uid) like @owner) 2105 begin 2106 select @colname = @objname 2107 select @objname = @owner 2108 select @owner = "%" 2109 end 2110 end 2111 else if (@opt1_buf is not NULL) 2112 begin 2113 /* Check if table/view specified exists */ 2114 if exists (select 1 from sysobjects 2115 where type in ("S", "U", "V") 2116 and name like @opt1_buf) 2117 begin 2118 select @owner = "%" 2119 select @objname = @opt1_buf 2120 select @colname = "%" 2121 end 2122 else 2123 begin 2124 select @owner = "%" 2125 select @objname = "%" 2126 select @colname = @opt1_buf 2127 end 2128 end 2129 else 2130 begin 2131 select @owner = "%" 2132 select @opt1 = "%" 2133 select @objname = @opt1 2134 select @colname = "%" 2135 end 2136 2137 if (@owner is NULL) 2138 begin 2139 select @owner = "%" 2140 end 2141 2142 if (@objname is NULL) 2143 begin 2144 select @objname = "%" 2145 end 2146 2147 if (@colname is NULL) 2148 begin 2149 select @colname = "%" 2150 end 2151 2152 /* 2153 ** Accumulate encrypted columns and their tables in a 2154 ** temporary table 2155 */ 2156 create table #encrypted_table_info( 2157 username varchar(30) null, 2158 tabname varchar(255) null, 2159 colname varchar(255) null, 2160 keydbname varchar(30) null, 2161 keyowner varchar(30) null, 2162 keyid int null, 2163 keyname varchar(255) null, 2164 fullcolname varchar(540) null, 2165 fullkeyname varchar(315) null) 2166 2167 /* 2168 ** A temporary table to store the keydbnames which 2169 ** is needed by cursor db_cursor 2170 */ 2171 create table #keydbname_table(keydbname varchar(30) null) 2172 2173 2174 select @dbname = db_name() 2175 /* Indicates an encrypted column status */ 2176 select @encolstatus = 128 2177 2178 insert #encrypted_table_info(username, tabname, colname, keydbname, 2179 keyid) 2180 select user_name(o.uid), o.name, c.name 2181 , case c.encrkeydb 2182 when null then @dbname 2183 else c.encrkeydb 2184 end 2185 , c.encrkeyid 2186 from sysobjects o, syscolumns c 2187 where ((c.status2 & @encolstatus) = @encolstatus) 2188 and o.id = c.id 2189 and user_name(o.uid) like @owner 2190 and o.name like @objname 2191 and c.name like @colname 2192 2193 insert #keydbname_table 2194 select k.keydbname 2195 from #encrypted_table_info k, master.dbo.sysdatabases s 2196 where k.keydbname = s.name 2197 /* Skip databases that have not recovered */ 2198 and s.status & @db_unrecovered = 0 2199 /* Skip databases that are offline */ 2200 and s.status2 & @db_offline = 0 2201 /* Skip databases that are not recovered */ 2202 and s.status & @db_notrec = 0 2203 /* Skip databases that will not be recovered */ 2204 and s.status & @db_inlddb = 0 2205 /* Skip databases that are archive databases */ 2206 and s.status3 & @db_archivedb = 0 2207 2208 if (@sso_role > 0) 2209 begin 2210 2211 declare db_cursor cursor for 2212 select keydbname from #keydbname_table 2213 2214 open db_cursor 2215 fetch db_cursor into @dbname 2216 2217 while (@@sqlstatus = 0) 2218 begin 2219 /* 2220 ** Since sp_aux_encrkey_lookup is a system 2221 ** stored procedure, it will execute in the 2222 ** context of dbname if it is called as 2223 ** dbname..sp_aux_encrkey_lookup, regardless of 2224 ** which db it is called from. 2225 ** 2226 ** This auxillary procedure is used for 2227 ** extracting key name for specified 2228 ** keyids. 2229 */ 2230 select @auxproc = 2231 @dbname + ".dbo.sp_aux_encrkey_lookup" 2232 2233 exec @auxproc 2234 2235 select @retval = @@error 2236 if (@retval != 0) 2237 begin 2238 close db_cursor 2239 deallocate cursor db_cursor 2240 return (1) 2241 end 2242 2243 fetch db_cursor into @dbname 2244 2245 end 2246 close db_cursor 2247 deallocate cursor db_cursor 2248 end 2249 else 2250 begin 2251 /* 2252 ** ENCR2_RESOLVE: Currently there is no graceful way (without 2253 ** warnings) to check and see if a login is a valid user in a 2254 ** database. A new Built-in has been proposed to check this. 2255 ** So until this is done, non-privileged users get keyids 2256 ** for those keys that are not in current database. 2257 */ 2258 2259 update #encrypted_table_info 2260 set keyowner = user_name(o.uid) 2261 , keyname = o.name 2262 from sysobjects o 2263 where o.id = keyid 2264 and db_name() = keydbname 2265 2266 end 2267 2268 if not exists (select 1 from #encrypted_table_info) 2269 begin 2270 /* 2271 ** 19604, "There are no encrypted columns like 2272 ** '%1!'." 2273 */ 2274 raiserror 19604, @opt1 2275 return (1) 2276 end 2277 2278 /* Update fullcolname and fullkeyname */ 2279 update #encrypted_table_info 2280 set fullcolname = username + "." + tabname + "." + colname 2281 where fullcolname is NULL 2282 2283 update #encrypted_table_info 2284 set fullkeyname = 2285 case keyname 2286 when NULL then keydbname + "." + convert(varchar(10), keyid) 2287 else keydbname + "." + keyowner + "." + keyname 2288 end 2289 where fullkeyname is NULL 2290 2291 exec sp_autoformat @fulltabname = #encrypted_table_info, 2292 @selectlist = "'Owner.Table.Column' = fullcolname, 'Db.Owner.Keyname' = fullkeyname", 2293 @orderby = "order by fullcolname, fullkeyname" 2294 2295 drop table #encrypted_table_info 2296 end 2297 2298 2299 2300 /* 2301 ** SP_ENCRYPTION HELPUSER [, USERNAME | WILDCARD] [, KEY_COPY | LOGIN_PASSWD_CHECK] 2302 ** 2303 ** Displays the keys owned/assigned to a user in the current database. 2304 */ 2305 if (@cmd = "helpuser") 2306 begin 2307 2308 if ((@opt2 is not null) and (@opt2 not in ('key_copy', 'login_passwd_check'))) 2309 begin 2310 /* 18291, "The parameter value '%1!' is invalid." */ 2311 raiserror 18291, @opt2 2312 return 1 2313 end 2314 2315 select @opt1_buf = @opt1 2316 if (@opt1_buf is NULL) 2317 begin 2318 select @opt1 = "%" 2319 end 2320 2321 select @username = @opt1 2322 2323 /* 2324 ** list all base keys in current database 2325 */ 2326 if (@opt2 is null) 2327 begin 2328 2329 create table #encr_basekeys_info(keyowner varchar(30) null, 2330 keyname varchar(255) null, 2331 protectedby varchar(30) null, 2332 fullkeyname varchar(285) null) 2333 2334 /* run by SSO/key custodian/DBO */ 2335 if ((@sso_role > 0) or (@keycustodian_role > 0) 2336 or (user_name() = "dbo")) 2337 begin 2338 insert #encr_basekeys_info(keyowner, keyname, protectedby) 2339 select user_name(o1.uid) 2340 , o1.name 2341 , case (e1.status & (@status_dualcontrol 2342 | @login_password | @system_password 2343 | @user_password | @status_master_encr)) 2344 when @login_password then "login password" 2345 when @system_password then "system encryption password" 2346 when @user_password then "user password" 2347 when @status_master_encr then "master key" 2348 when (@status_dualcontrol | @user_password) 2349 then "dual_control(master key + user password)" 2350 when (@status_dualcontrol | @login_password) 2351 then "dual_control(master key + login password)" 2352 when (@status_dualcontrol | @status_master_encr) 2353 then "dual_control(master key + dual master key)" 2354 end 2355 from sysobjects o1, sysencryptkeys e1 2356 where o1.id = e1.id 2357 and e1.type & @key_copy = 0 2358 and user_name(o1.uid) like @username 2359 2360 2361 if not exists (select 1 from #encr_basekeys_info) 2362 begin 2363 /* 2364 ** 19616, "There are no encryption keys for user 2365 ** name like '%1!'." 2366 */ 2367 raiserror 19616, @opt1 2368 return (1) 2369 end 2370 2371 /* update #encr_basekeys_info to fill fullkeyname column */ 2372 update #encr_basekeys_info 2373 set fullkeyname = keyowner + "." + keyname 2374 where fullkeyname is NULL 2375 2376 /* display the base encryption keys */ 2377 exec sp_autoformat @fulltabname = #encr_basekeys_info, 2378 @selectlist = " 'Owner.Keyname' = fullkeyname, 2379 'Protected By' = protectedby", 2380 @orderby = "order by fullkeyname" 2381 end 2382 else 2383 /* run by a non-privileged user */ 2384 begin 2385 2386 /* 2387 ** a non-priviledged user is only allowed to 2388 ** check his own keys 2389 */ 2390 if (user_name() not like @username) 2391 begin 2392 /* 2393 ** 19632. "You are not authorized to check keys (key copies) 2394 ** for user name like '%1!'." 2395 */ 2396 raiserror 19632, @opt1 2397 return (1) 2398 end 2399 insert #encr_basekeys_info(keyowner, keyname, protectedby) 2400 select user_name(o1.uid) 2401 , o1.name 2402 , case (e1.status & (@status_dualcontrol 2403 | @login_password | @system_password 2404 | @user_password | @status_mek_encr)) 2405 when @login_password then "login password" 2406 when @system_password then "system encryption password" 2407 when @user_password then "user password" 2408 when @status_master_encr then "master key" 2409 when (@status_dualcontrol | @user_password) 2410 then "dual_control(master key + user password)" 2411 when (@status_dualcontrol | @login_password) 2412 then "dual_control(master key + login password)" 2413 when (@status_dualcontrol | @status_master_encr) 2414 then "dual_control(master key + dual master key)" 2415 end 2416 from sysobjects o1, sysencryptkeys e1 2417 where o1.id = e1.id 2418 and e1.type & @key_copy = 0 2419 and user_name(o1.uid) like @username 2420 and user_name(o1.uid) = user_name() 2421 2422 if not exists (select 1 from #encr_basekeys_info) 2423 begin 2424 /* 2425 ** 19616, "There are no encryption keys (key copies) 2426 ** for user name like '%1!'." 2427 */ 2428 raiserror 19616, @callername 2429 return (1) 2430 end 2431 2432 /* update #encr_basekeys_info to fill fullkeyname column */ 2433 update #encr_basekeys_info 2434 set fullkeyname = keyowner + "." + keyname 2435 where fullkeyname is NULL 2436 2437 /* display the base encryption keys */ 2438 exec sp_autoformat @fulltabname = #encr_basekeys_info, 2439 @selectlist = " 'Owner.Keyname' = fullkeyname, 2440 'Protected By' = protectedby", 2441 @orderby = "order by fullkeyname" 2442 end 2443 drop table #encr_basekeys_info 2444 end 2445 /* 2446 ** SP_ENCRYPTION HELPUSER, USERNAME|WILDCARD, KEY_COPY 2447 */ 2448 else if (@opt2 = "key_copy") 2449 begin 2450 /* Accumulate encryption key info in a temporary table */ 2451 create table #encr_keycopies_info(keyowner varchar(30) null, 2452 keyname varchar(255) null, 2453 assignee varchar(30) null, 2454 protectedby varchar(30) null, 2455 keyrecovery int null, 2456 fullkeyname varchar(285) null) 2457 2458 /* run by SSO/key custodian/DBO */ 2459 if ((@sso_role > 0) or (@keycustodian_role > 0) 2460 or (user_name() = "dbo")) 2461 begin 2462 insert #encr_keycopies_info(keyowner, keyname, assignee, 2463 protectedby, keyrecovery) 2464 select user_name(o1.uid) 2465 , o1.name 2466 , user_name(e1.uid) 2467 , case (e1.status & (@status_dualcontrol 2468 | @status_master_encr | @login_access 2469 | @login_password | @system_password 2470 | @user_password | @status_mek_encr)) 2471 when (@login_access | @system_password) then "login access" 2472 when (@login_access | @status_master_encr) then "login access" 2473 when @login_password then "login password" 2474 when @system_password then "system encryption password" 2475 when @user_password then "user password" 2476 when @status_master_encr then "master key" 2477 when (@status_dualcontrol | @user_password) 2478 then "dual_control(master key + user password)" 2479 when (@status_dualcontrol | @login_access | @status_master_encr) 2480 then "dual_control(login access)" 2481 when (@status_dualcontrol | @login_password) 2482 then "dual_control(master key + login password)" 2483 when (@status_dualcontrol | @status_master_encr) 2484 then "dual_control(master key + dual master key)" 2485 when (@status_mek_encr) then "automatic startup key" 2486 end 2487 , case (e1.type & @key_recovery) 2488 when 0 then 0 2489 else 1 2490 end 2491 from sysobjects o1, sysencryptkeys e1 2492 where o1.id = e1.id 2493 and e1.type & @key_copy > 0 2494 and user_name(e1.uid) like @username 2495 2496 if not exists (select 1 from #encr_keycopies_info) 2497 begin 2498 /* 2499 ** 19616, "There are no encryption keys (key copies) 2500 ** for user name like '%1!'." 2501 */ 2502 raiserror 19616, @opt1 2503 return (1) 2504 end 2505 2506 /* Update #encr_keycopies_info to fill fullkeyname column */ 2507 update #encr_keycopies_info 2508 set fullkeyname = keyowner + "." + keyname 2509 where fullkeyname is NULL 2510 2511 /* Display the encryption key info */ 2512 exec sp_autoformat @fulltabname = #encr_keycopies_info, 2513 @selectlist = "'Owner.Keyname' = fullkeyname, 2514 'Assignee' = assignee, 2515 'Protected By' = protectedby, 2516 'Key Recovery' = keyrecovery", 2517 @orderby = "order by assignee" 2518 end 2519 /* Run by non-privileged users */ 2520 else 2521 begin 2522 2523 /* 2524 ** a non-priviledged user is only allowed to 2525 ** check his own keys 2526 */ 2527 if (user_name() not like @username) 2528 begin 2529 /* 2530 ** 19632. "You are not authorized to check keys (key copies) 2531 ** for user name like '%1!'." 2532 */ 2533 raiserror 19632, @opt1 2534 return (1) 2535 end 2536 2537 insert #encr_keycopies_info(keyowner, keyname, assignee, 2538 protectedby, keyrecovery) 2539 select user_name(o1.uid) 2540 , o1.name 2541 , user_name(e1.uid) 2542 , case (e1.status & (@status_dualcontrol 2543 | @status_master_encr | @login_access 2544 | @login_password | @system_password 2545 | @user_password | @status_mek_encr)) 2546 when (@login_access | @system_password) then "login access" 2547 when (@login_access | @status_master_encr) then "login access" 2548 when @login_password then "login password" 2549 when @system_password then "system encryption password" 2550 when @user_password then "user password" 2551 when @status_master_encr then "master key" 2552 when (@status_dualcontrol | @user_password) 2553 then "dual_control(master key + user password)" 2554 when (@status_dualcontrol | @login_access | @status_master_encr) 2555 then "dual_control(login access)" 2556 when (@status_dualcontrol | @login_password) 2557 then "dual_control(master key + login password)" 2558 when (@status_dualcontrol | @status_master_encr) 2559 then "dual_control(master key + dual master key)" 2560 when (@status_mek_encr) then "automatic startup key" 2561 end 2562 , case (e1.type & @key_recovery) 2563 when 0 then 0 2564 else 1 2565 end 2566 from sysobjects o1, sysencryptkeys e1 2567 where o1.id = e1.id 2568 and e1.type & @key_copy > 0 2569 and user_name(e1.uid) like @username 2570 and user_name(e1.uid) like user_name() 2571 2572 if not exists (select 1 from #encr_keycopies_info) 2573 begin 2574 /* 2575 ** 19616, "There are no encryption keys (key copies) 2576 ** for user name like '%1!'." 2577 */ 2578 raiserror 19616, @callername 2579 return (1) 2580 end 2581 2582 /* update #encr_keycopies_info to fill fullkeyname column */ 2583 update #encr_keycopies_info 2584 set fullkeyname = keyowner + "." + keyname 2585 where fullkeyname is NULL 2586 2587 /* Display the encryption key info */ 2588 exec sp_autoformat @fulltabname = #encr_keycopies_info, 2589 @selectlist = "'Owner.Keyname' = fullkeyname, 2590 'Assignee' = assignee, 2591 'Protected By' = protectedby, 2592 'Key Recovery' = keyrecovery", 2593 @orderby = "order by assignee" 2594 2595 end 2596 drop table #encr_keycopies_info 2597 end 2598 2599 /* 2600 ** SP_ENCRYPTION HELPUSER, USERNAME|WILDCARD, LOGIN_PASSWD_CHECK 2601 */ 2602 else if (@opt2 = "login_passwd_check") 2603 begin 2604 /* Accumulate login passwd and login access keycopy info in a temporary table */ 2605 create table #encr_loginpwdcheck_info(keyowner varchar(30) null, 2606 keyname varchar(255) null, 2607 assignee varchar(30) null, 2608 protectedby varchar(30) null, 2609 fullkeyname varchar(285) null, 2610 dateofupdate datetime null, 2611 consistent int null) 2612 2613 /* run by SSO/key custodian/DBO */ 2614 if ((@sso_role > 0) or (@keycustodian_role > 0) 2615 or (user_name() = "dbo")) 2616 begin 2617 insert #encr_loginpwdcheck_info(keyowner, keyname, assignee, 2618 protectedby, dateofupdate) 2619 select user_name(o1.uid), o1.name, user_name(e1.uid) 2620 , case e1.status & (@status_dualcontrol | @login_access | @login_password) 2621 when (@login_access) then "login access" 2622 when (@login_password) then "login password" 2623 when (@status_dualcontrol | @login_access) 2624 then "dual_control(login access)" 2625 when (@status_dualcontrol | @login_password) 2626 then "dual_control(master key + login password)" 2627 end 2628 , e1.pwdate 2629 from sysobjects o1, sysencryptkeys e1 2630 where o1.id = e1.id 2631 and e1.type & @key_copy > 0 2632 and (((e1.status & @login_access) > 0) 2633 or ((e1.status & @login_password) > 0)) 2634 and user_name(e1.uid) like @username 2635 2636 if not exists (select 1 from #encr_loginpwdcheck_info) 2637 begin 2638 /* 2639 ** 19616, "There are no encryption keys (key copies) 2640 ** for user name like '%1!'." 2641 */ 2642 raiserror 19616, @opt1 2643 return (1) 2644 end 2645 2646 /* Update #encr_loginpwdcheck_info to fill fullkeyname column */ 2647 update #encr_loginpwdcheck_info 2648 set fullkeyname = keyowner + "." + keyname 2649 where fullkeyname is NULL 2650 2651 /* Update #encr_loginpwdcheck_info to fill consistent column */ 2652 update #encr_loginpwdcheck_info 2653 set consistent = 2654 case 2655 when (e.pwdate >= l.pwdate) then 1 2656 else 0 2657 end 2658 from master..syslogins l, sysencryptkeys e, sysusers u, sysobjects o 2659 where e.uid = u.uid 2660 and u.suid = l.suid 2661 and o.id = e.id 2662 and assignee = user_name(e.uid) 2663 and keyname = o.name 2664 2665 /* Display the encryption key info */ 2666 exec sp_autoformat @fulltabname = #encr_loginpwdcheck_info, 2667 @selectlist = " 'Owner.Keyname' = fullkeyname, 2668 'Assignee' = assignee, 2669 'Protected By' = protectedby, 2670 'Last Updated' = dateofupdate, 2671 'Synchronized with Password' = consistent", 2672 @orderby = "order by assignee" 2673 end 2674 /* Run by non-privileged users */ 2675 else 2676 begin 2677 2678 /* 2679 ** a non-priviledged user is only allowed to 2680 ** check his own key copies 2681 */ 2682 if (user_name() not like @username) 2683 begin 2684 /* 2685 ** 19632. "You are not authorized to check keys (key copies) 2686 ** for user name like '%1!'." 2687 */ 2688 raiserror 19632, @opt1 2689 return (1) 2690 end 2691 2692 insert #encr_loginpwdcheck_info(keyowner, keyname, assignee, 2693 protectedby, dateofupdate) 2694 select user_name(o1.uid), o1.name, user_name(e1.uid) 2695 , case e1.status & (@status_dualcontrol | @login_access | @login_password) 2696 when (@login_access) then "login access" 2697 when (@login_password) then "login password" 2698 when (@status_dualcontrol | @login_access) 2699 then "dual_control(login access)" 2700 when (@status_dualcontrol | @login_password) 2701 then "dual_control(master key + login password)" 2702 end 2703 , e1.pwdate 2704 from sysobjects o1, sysencryptkeys e1 2705 where o1.id = e1.id 2706 and e1.type & @key_copy > 0 2707 and (((e1.status & @login_access) > 0) 2708 or ((e1.status & @login_password) > 0)) 2709 and user_name(e1.uid) like @username 2710 and user_name(e1.uid) like user_name() 2711 2712 if not exists (select 1 from #encr_loginpwdcheck_info) 2713 begin 2714 /* 2715 ** 19616, "There are no encryption keys (key copies) 2716 ** for user name like '%1!'." 2717 */ 2718 raiserror 19616, @callername 2719 return (1) 2720 end 2721 2722 /* Update #encr_loginpwdcheck_info to fill fullkeyname column */ 2723 update #encr_loginpwdcheck_info 2724 set fullkeyname = keyowner + "." + keyname 2725 where fullkeyname is NULL 2726 2727 /* Update #encr_loginpwdcheck_info to fill consistent column */ 2728 update #encr_loginpwdcheck_info 2729 set consistent = 2730 case 2731 when (e.pwdate >= l.pwdate) then 1 2732 else 0 2733 end 2734 from master..syslogins l, sysencryptkeys e, sysusers u, sysobjects o 2735 where e.uid = u.uid 2736 and u.suid = l.suid 2737 and o.id = e.id 2738 and assignee = user_name(e.uid) 2739 and keyname = o.name 2740 2741 /* Display the encryption key info */ 2742 exec sp_autoformat @fulltabname = #encr_loginpwdcheck_info, 2743 @selectlist = " 'Owner.Keyname' = fullkeyname, 2744 'Assignee' = assignee, 2745 'Protected By' = protectedby, 2746 'Last Updated' = dateofupdate, 2747 'Synchronized with Password' = consistent", 2748 @orderby = "order by assignee" 2749 end 2750 drop table #encr_loginpwdcheck_info 2751 end 2752 end 2753 2754 /* 2755 ** Check external password status. 2756 */ 2757 if (@cmd = "helpextpasswd") 2758 begin -- { helpextpasswd 2759 2760 if (@sso_role = 0) 2761 begin 2762 /* 2763 ** 19951 "Permission denied. This operation requires 2764 ** System Security Officer (sso_role) role." 2765 */ 2766 raiserror 19951 2767 return (1) 2768 end 2769 2770 /* 2771 ** Put external passwords info into a 2772 ** temporary table 2773 */ 2774 create table #extpasswd_status_info( 2775 dbname varchar(50) null, 2776 passwdtype varchar(50) null, 2777 extluser varchar(255) null, 2778 status varchar(30) null) 2779 2780 select @dbname = db_name() 2781 2782 /* Add ssl password infomation */ 2783 insert #extpasswd_status_info(passwdtype, extluser, status) 2784 select "SSL_CERTIFICATE_PASSWORD", "-", 2785 case 2786 when (char_value like "5:%") 2787 then "FIPS Encryption" 2788 when (char_value like "0:0000000000000000") 2789 then "Needs Reset" 2790 else 2791 "Legacy Encryption" 2792 end 2793 from sysattributes 2794 where class = 15 2795 2796 /* Add LDAP account password information */ 2797 insert #extpasswd_status_info(passwdtype, extluser, status) 2798 select 2799 case 2800 when (attribute = 3) 2801 then "PRIMARY_LDAPACCESS_PASSWORD" 2802 when (attribute = 8) 2803 then "SECONDARY_LDAPACCESS_PASSWORD" 2804 end 2805 , "-", 2806 case 2807 when (char_value like "5:%") 2808 then "FIPS Encryption" 2809 when (char_value like "0:0000000000000000") 2810 then "Needs Reset" 2811 else 2812 "Legacy Encryption" 2813 end 2814 from sysattributes 2815 where class = 17 and (attribute = 3 or attribute = 8) 2816 2817 /* Add RA password information */ 2818 declare rapasswd_cursor cursor for 2819 select char_value, object_info1 from sysattributes 2820 where class = 11 and attribute = 2 2821 open rapasswd_cursor 2822 fetch rapasswd_cursor into @ra_pwd, @rpid 2823 while (@@sqlstatus = 0) 2824 begin -- { 2825 insert #extpasswd_status_info(passwdtype, extluser, status) 2826 select "REPAGNT_RS_PASSWORD", 2827 case 2828 when (@rpid is null or @rpid = 0) 2829 then "Default" 2830 else 2831 (select att1.char_value 2832 from sysattributes att1, sysattributes att2, sysattributes att3 2833 where att1.class = 41 and att1.attribute = 22 2834 and att2.int_value = 0 and att1.object_info1 = att2.object_info1 2835 and att3.attribute = 20 and att3.object_info1 = att2.object_info1 2836 and att3.object_info2 = (select object_info1 from sysattributes 2837 where class = 41 and attribute = 0 and object_info2 = @rpid)) 2838 end, 2839 case 2840 when (@ra_pwd like "5:%") 2841 then "FIPS Encryption" 2842 when (@ra_pwd like "0:0000000000000000") 2843 then "Needs Reset" 2844 else 2845 "Legacy Encryption" 2846 end 2847 fetch rapasswd_cursor into @ra_pwd, @rpid 2848 end -- } 2849 close rapasswd_cursor 2850 deallocate cursor rapasswd_cursor 2851 2852 /* Add CIS external login passwords information */ 2853 insert #extpasswd_status_info(passwdtype, extluser, status) 2854 select "CIS_LOGIN_PASSWORD", 2855 suser_name(object), 2856 case 2857 when (image_value like "5:%") 2858 then "FIPS Encryption" 2859 when (image_value like "0:0000000000000000") 2860 then "Needs Reset" 2861 else 2862 "Legacy Encryption" 2863 end 2864 from sysattributes 2865 where class = 9 and attribute = 0 2866 2867 /* Add RTMS external login passwords information */ 2868 insert #extpasswd_status_info(passwdtype, extluser, status) 2869 select "RTMS_LOGIN_PASSWORD", 2870 suser_name(object), 2871 case 2872 when (image_value like "5:%") 2873 then "FIPS Encryption" 2874 when (image_value like "0:0000000000000000") 2875 then "Needs Reset" 2876 else 2877 "Legacy Encryption" 2878 end 2879 from sysattributes 2880 where class = 21 and attribute = 0 2881 2882 /* Add RTMS subscription passwords information */ 2883 insert #extpasswd_status_info(passwdtype, extluser, status) 2884 select "RTMS_SUBSCRIPTION_PASSWORD", 2885 "-", 2886 case 2887 when (image_value like "5:%") 2888 then "FIPS Encryption" 2889 when (image_value like "0:0000000000000000") 2890 then "Needs Reset" 2891 else 2892 "Legacy Encryption" 2893 end 2894 from sysattributes 2895 where class = 21 and attribute = 1 2896 2897 update #extpasswd_status_info 2898 set dbname = @dbname 2899 where dbname is NULL 2900 2901 /* 2902 ** Display the encrypted external password info 2903 */ 2904 exec sp_autoformat @fulltabname = #extpasswd_status_info, 2905 @selectlist = "'Database Name' = dbname, 'Password Type' = passwdtype, 'Login Name' = extluser, 'Status' = status", 2906 @orderby = "order by dbname, passwdtype, extluser, status" 2907 2908 drop table #extpasswd_status_info 2909 2910 return (0) 2911 end -- } helpextpasswd 2912 2913 if (@retval != 1) 2914 begin 2915 return (0) 2916 end 2917 else 2918 begin 2919 print "sp_encryption Usage: sp_encryption command [, option1 [, option2]]" 2920 print "sp_encryption commands:" 2921 print "sp_encryption 'system_encr_passwd', 'newpasswd'[, 'oldpasswd']" 2922 print "sp_encryption 'mkey_startup_file'[, {'newpath' | 'default_location' | 'null'} [, {sync_with_mem | sync_with_qrm}]]" 2923 print "sp_encryption 'help'[, {'keyname'|'master'|'dual master'}[, 'display_cols']]" 2924 print "sp_encryption 'help'[, 'servicekeyname'[, 'display_objs']]" 2925 print "sp_encryption 'helpkey'[, {'keyname'|'master'|'dual master'}[, 'display_cols'|'all_dbs'|'key_copy']]" 2926 print "sp_encryption 'helpkey', 'system_encr_passwd' [, 'display_keys' | 'all_dbs']" 2927 print "sp_encryption 'helpkey', {'master'|'dual master'} [, 'display_keys' | 'all_dbs']" 2928 print "sp_encryption 'helpcol'[, 'colname'][, 'display_keys']]" 2929 print "sp_encryption 'helpuser'[, 'username'][, 'key_copy', 'login_passwd_check']" 2930 print "sp_encryption 'downgrade_kek_size' [, 'true'|'false']" 2931 print "sp_encryption 'helpextpasswd'" 2932 end 2933 return (1) 2934
exec sp_procxmode 'sp_encryption', 'AnyMode' go Grant Execute on sp_encryption to public go
DEFECTS | |
![]() | 739 |
![]() | 765 |
![]() | 225 |
![]() | 258 |
![]() | 317 |
![]() | 344 |
![]() | 403 |
![]() | 430 |
![]() | 504 |
![]() | 669 |
![]() | 672 |
![]() | 684 |
![]() | master..sysattributes |
![]() | sybsystemprocs..sysattributes |
![]() | sybsystemprocs..sysencryptkeys |
![]() | 1031 |
![]() | 1032 |
![]() | 1521 |
![]() | 2053 |
![]() | 2119 |
![]() | 2126 |
![]() | 2133 |
![]() | 2315 |
![]() | 2321 |
![]() | 674 |
![]() | 793 |
![]() | 932 |
![]() | 1014 |
![]() | 1277 |
![]() | 1485 |
![]() | 1539 |
![]() | 1717 |
![]() | 1836 |
![]() | 1855 |
![]() | 1939 |
![]() | 2012 |
![]() | 2291 |
![]() | 2377 |
![]() | 2438 |
![]() | 2512 |
![]() | 2588 |
![]() | 2666 |
![]() | 2742 |
![]() | 2904 |
![]() | 1520 |
![]() (id, type, uid) Intersection: {uid} | 767 |
![]() (name, uid) Intersection: {uid} | 768 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_info1} Uncovered: [class, attribute, object_type, object, object_info2, object_info3, object_cinfo] | 1409 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_info1} Uncovered: [object_type, object, object_info2, object_info3, object_cinfo] | 1409 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_info1} Uncovered: [class, attribute, object_type, object, object_info2, object_info3, object_cinfo] | 1410 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_info1} Uncovered: [class, object_type, object, object_info3, object_cinfo] | 1410 |
![]() (id, number, colid2, colid, texttype) Intersection: {colid} | 1523 |
![]() (id, type, uid) Intersection: {type} | 1918 |
![]() (id, type, uid) Intersection: {uid, type} | 1989 |
![]() (id, type, uid) Intersection: {type} | 2357 |
![]() (name, uid) Intersection: {uid} | 2358 |
![]() (id, type, uid) Intersection: {type} | 2418 |
![]() (name, uid) Intersection: {uid} | 2419 |
![]() (id, type, uid) Intersection: {uid, type} | 2493 |
![]() (id, type, uid) Intersection: {uid, type} | 2568 |
![]() (id, type, uid) Intersection: {uid, type} | 2631 |
![]() (id, type, uid) Intersection: {uid, type} | 2706 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_info1} Uncovered: [class, attribute, object_type, object, object_info2, object_info3, object_cinfo] | 2834 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_info1} Uncovered: [object_type, object, object_info2, object_info3, object_cinfo] | 2834 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_info1} Uncovered: [class, attribute, object_type, object, object_info2, object_info3, object_cinfo] | 2835 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_info1} Uncovered: [class, object_type, object, object_info3, object_cinfo] | 2835 |
![]() | 197 |
![]() | 374 |
![]() | 375 |
![]() | 742 |
![]() | 743 |
![]() | 769 |
![]() | 770 |
![]() | 1361 |
![]() | 1372 |
![]() | 1382 |
![]() | 1393 |
![]() | 1408 |
![]() | 1410 |
![]() | 1412 |
![]() | 1422 |
![]() | 1442 |
![]() | 1444 |
![]() | 1463 |
![]() | 1465 |
![]() | 1524 |
![]() | 2655 |
![]() | 2731 |
![]() | 2794 |
![]() | 2800 |
![]() | 2802 |
![]() | 2815 |
![]() | 2820 |
![]() | 2833 |
![]() | 2835 |
![]() | 2837 |
![]() | 2865 |
![]() | 2880 |
![]() | 2895 |
![]() | 548 |
![]() | 829 |
![]() | 1150 |
![]() | 1392 |
![]() | 1421 |
![]() | 1441 |
![]() | 1462 |
![]() | 1507 |
![]() | 1599 |
![]() | 2212 |
![]() | 2819 |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | 61 |
![]() | 806 |
![]() | 505 |
![]() | 572 |
![]() | 577 |
![]() | 583 |
![]() | 585 |
![]() | 587 |
![]() | 593 |
![]() | 596 |
![]() | 599 |
![]() | 602 |
![]() | 607 |
![]() | 610 |
![]() | 614 |
![]() | 631 |
![]() | 670 |
![]() | 673 |
![]() | 685 |
![]() | 737 |
![]() | 763 |
![]() | 788 |
![]() | 793 |
![]() | 932 |
![]() | 988 |
![]() | 1011 |
![]() | 1014 |
![]() | 1202 |
![]() | 1240 |
![]() | 1270 |
![]() | 1277 |
![]() | 1364 |
![]() | 1375 |
![]() | 1385 |
![]() | 1399 |
![]() | 1429 |
![]() | 1450 |
![]() | 1471 |
![]() | 1485 |
![]() | 1518 |
![]() | 1532 |
![]() | 1539 |
![]() | 1661 |
![]() | 1711 |
![]() | 1717 |
![]() | 1781 |
![]() | 1836 |
![]() | 1855 |
![]() | 1885 |
![]() | 1934 |
![]() | 1939 |
![]() | 1954 |
![]() | 2007 |
![]() | 2012 |
![]() | 2178 |
![]() | 2259 |
![]() | 2279 |
![]() | 2283 |
![]() | 2291 |
![]() | 2338 |
![]() | 2372 |
![]() | 2377 |
![]() | 2399 |
![]() | 2433 |
![]() | 2438 |
![]() | 2462 |
![]() | 2507 |
![]() | 2512 |
![]() | 2537 |
![]() | 2583 |
![]() | 2588 |
![]() | 2617 |
![]() | 2647 |
![]() | 2652 |
![]() | 2666 |
![]() | 2692 |
![]() | 2723 |
![]() | 2728 |
![]() | 2742 |
![]() | 2783 |
![]() | 2797 |
![]() | 2825 |
![]() | 2853 |
![]() | 2868 |
![]() | 2883 |
![]() | 2897 |
![]() | 2904 |
![]() | 122 |
![]() | 136 |
![]() | 142 |
![]() | 151 |
![]() | 163 |
![]() | 169 |
![]() | 176 |
![]() | 178 |
![]() | 184 |
![]() | 189 |
![]() | 207 |
![]() | 210 |
![]() | 235 |
![]() | 240 |
![]() | 243 |
![]() | 255 |
![]() | 262 |
![]() | 265 |
![]() | 274 |
![]() | 281 |
![]() | 290 |
![]() | 294 |
![]() | 297 |
![]() | 307 |
![]() | 321 |
![]() | 326 |
![]() | 329 |
![]() | 341 |
![]() | 348 |
![]() | 351 |
![]() | 358 |
![]() | 370 |
![]() | 383 |
![]() | 393 |
![]() | 407 |
![]() | 412 |
![]() | 415 |
![]() | 427 |
![]() | 437 |
![]() | 484 |
![]() | 486 |
![]() | 488 |
![]() | 496 |
![]() | 501 |
![]() | 509 |
![]() | 565 |
![]() | 570 |
![]() | 581 |
![]() | 591 |
![]() | 605 |
![]() | 624 |
![]() | 634 |
![]() | 639 |
![]() | 642 |
![]() | 645 |
![]() | 647 |
![]() | 649 |
![]() | 651 |
![]() | 653 |
![]() | 655 |
![]() | 661 |
![]() | 701 |
![]() | 706 |
![]() | 709 |
![]() | 727 |
![]() | 735 |
![]() | 753 |
![]() | 781 |
![]() | 806 |
![]() | 818 |
![]() | 822 |
![]() | 825 |
![]() | 845 |
![]() | 870 |
![]() | 874 |
![]() | 889 |
![]() | 900 |
![]() | 908 |
![]() | 924 |
![]() | 938 |
![]() | 947 |
![]() | 968 |
![]() | 976 |
![]() | 979 |
![]() | 1007 |
![]() | 1033 |
![]() | 1035 |
![]() | 1040 |
![]() | 1057 |
![]() | 1066 |
![]() | 1071 |
![]() | 1076 |
![]() | 1081 |
![]() | 1087 |
![]() | 1092 |
![]() | 1103 |
![]() | 1110 |
![]() | 1118 |
![]() | 1133 |
![]() | 1146 |
![]() | 1170 |
![]() | 1193 |
![]() | 1197 |
![]() | 1200 |
![]() | 1222 |
![]() | 1258 |
![]() | 1283 |
![]() | 1292 |
![]() | 1294 |
![]() | 1320 |
![]() | 1324 |
![]() | 1333 |
![]() | 1339 |
![]() | 1397 |
![]() | 1403 |
![]() | 1427 |
![]() | 1448 |
![]() | 1469 |
![]() | 1491 |
![]() | 1493 |
![]() | 1515 |
![]() | 1545 |
![]() | 1555 |
![]() | 1587 |
![]() | 1593 |
![]() | 1595 |
![]() | 1622 |
![]() | 1642 |
![]() | 1646 |
![]() | 1705 |
![]() | 1754 |
![]() | 1756 |
![]() | 1829 |
![]() | 1832 |
![]() | 1872 |
![]() | 1882 |
![]() | 1907 |
![]() | 1909 |
![]() | 1930 |
![]() | 1978 |
![]() | 1980 |
![]() | 2003 |
![]() | 2035 |
![]() | 2037 |
![]() | 2041 |
![]() | 2054 |
![]() | 2056 |
![]() | 2061 |
![]() | 2083 |
![]() | 2095 |
![]() | 2111 |
![]() | 2137 |
![]() | 2142 |
![]() | 2147 |
![]() | 2208 |
![]() | 2217 |
![]() | 2236 |
![]() | 2240 |
![]() | 2275 |
![]() | 2305 |
![]() | 2308 |
![]() | 2316 |
![]() | 2326 |
![]() | 2335 |
![]() | 2368 |
![]() | 2390 |
![]() | 2397 |
![]() | 2429 |
![]() | 2448 |
![]() | 2459 |
![]() | 2485 |
![]() | 2503 |
![]() | 2527 |
![]() | 2534 |
![]() | 2560 |
![]() | 2579 |
![]() | 2602 |
![]() | 2614 |
![]() | 2621 |
![]() | 2622 |
![]() | 2643 |
![]() | 2655 |
![]() | 2682 |
![]() | 2689 |
![]() | 2696 |
![]() | 2697 |
![]() | 2719 |
![]() | 2731 |
![]() | 2757 |
![]() | 2760 |
![]() | 2767 |
![]() | 2786 |
![]() | 2788 |
![]() | 2800 |
![]() | 2802 |
![]() | 2807 |
![]() | 2809 |
![]() | 2823 |
![]() | 2828 |
![]() | 2840 |
![]() | 2842 |
![]() | 2857 |
![]() | 2859 |
![]() | 2872 |
![]() | 2874 |
![]() | 2887 |
![]() | 2889 |
![]() | 2910 |
![]() | 2913 |
![]() | 2915 |
![]() | 2933 |
![]() | 1558 |
![]() | 1570 |
![]() | 512 |
![]() | 529 |
![]() | 730 |
![]() | 809 |
![]() | 952 |
![]() | 1137 |
![]() | 1352 |
![]() | 1499 |
![]() | 1558 |
![]() | 1570 |
![]() | 1875 |
![]() | 2156 |
![]() | 2171 |
![]() | 2329 |
![]() | 2451 |
![]() | 2605 |
![]() | 2774 |
![]() | 128 |
![]() | 737 |
![]() | 763 |
![]() | 988 |
![]() | 1240 |
![]() | 1518 |
![]() | 1661 |
![]() | 1781 |
![]() | 1885 |
![]() | 1954 |
![]() | 2178 |
![]() | 2338 |
![]() | 2399 |
![]() | 2462 |
![]() | 2537 |
![]() | 2617 |
![]() | 2692 |
![]() | 2783 |
![]() | 2797 |
![]() | 2825 |
![]() | 2853 |
![]() | 2868 |
![]() | 2883 |
![]() | 1694 |
![]() | 1818 |
![]() | 2196 |
![]() | 2263 |
![]() | 2662 |
![]() | 2663 |
![]() | 2738 |
![]() | 2739 |
![]() | 739 |
![]() | 765 |
![]() | 990 |
![]() | 1244 |
![]() | 1407 |
![]() | 1522 |
![]() | 1686 |
![]() | 1809 |
![]() | 1916 |
![]() | 1987 |
![]() | 2186 |
![]() | 2195 |
![]() | 2355 |
![]() | 2416 |
![]() | 2491 |
![]() | 2566 |
![]() | 2629 |
![]() | 2658 |
![]() | 2704 |
![]() | 2734 |
![]() | 2832 |
![]() | 1411 |
![]() | 2263 |
![]() | 2264 |
![]() | 2662 |
![]() | 2663 |
![]() | 2738 |
![]() | 2739 |
![]() | 2836 |
![]() | 994 |
![]() | 997 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, object_type, attribute} | 197 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, attribute} | 374 |
![]() (name, uid) Intersection: {name} | 703 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, object_type, attribute} | 742 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, object_type, attribute} | 769 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class} | 1361 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, attribute} | 1372 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, attribute} | 1382 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, attribute} | 1393 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_info2, class, attribute} | 1412 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, attribute} | 1422 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, attribute} | 1442 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, attribute} | 1463 |
![]() (name, uid) Intersection: {name} | 2115 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class} | 2794 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, attribute} | 2815 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, attribute} | 2820 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_info2, class, attribute} | 2837 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, attribute} | 2865 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, attribute} | 2880 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, attribute} | 2895 |
![]() | 464 |
![]() | 824 |
![]() | 38 |
![]() | 50 |
![]() | 51 |
![]() | 52 |
![]() | 53 |
![]() | 548 |
![]() | 829 |
![]() | 1150 |
![]() | 1392 |
![]() | 1421 |
![]() | 1441 |
![]() | 1462 |
![]() | 1507 |
![]() | 1599 |
![]() | 2212 |
![]() | 2819 |
![]() | 1120 |
![]() | 1308 |
![]() | 1686 |
![]() | 1809 |
![]() | 573 |
![]() | 578 |
![]() | 584 |
![]() | 586 |
![]() | 588 |
![]() | 594 |
![]() | 597 |
![]() | 600 |
![]() | 603 |
![]() | 608 |
![]() | 611 |
![]() | 615 |
![]() | 866 |
![]() | 915 |
![]() | 1189 |
![]() | 1639 |
![]() | 1751 |
![]() | 2233 |
![]() | 196 |
![]() | 703 |
![]() | 1360 |
![]() | 1371 |
![]() | 1381 |
![]() | 1406 |
![]() | 1411 |
![]() | 2103 |
![]() | 2114 |
![]() | 2831 |
![]() | 2836 |
![]() | 6 |
![]() | 6 |
![]() | 6 |
![]() | 738 |
![]() | 764 |
![]() | 989 |
![]() | 1242 |
![]() | 1406 |
![]() | 1520 |
![]() | 1887 |
![]() | 1956 |
![]() | 2180 |
![]() | 2339 |
![]() | 2400 |
![]() | 2464 |
![]() | 2539 |
![]() | 2619 |
![]() | 2694 |
![]() | 2831 |