| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_locklogin | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** Generated by spgenmsgs.pl on Thu Feb 2 00:39:17 2006 4 */ 5 /* 6 ** raiserror Messages for locklogin [Total 3] 7 ** 8 ** 17756, "The execution of the stored procedure '%1!' in database '%2!' was aborted because there was an error in writing the replication log record." 9 ** 18388, "You must be in the master database in order to run '%1!'." 10 ** 18409, "The built-in function '%1!' failed. Please see the other messages printed along with this message." 11 ** 19606, "To lock inactive accounts, you must run 12 ** 'sp_passwordpolicy 'set','enable last login updates','1''." 13 ** 19607, "You cannot specify both 'inactive_days' and 'unlock' at the same time." 14 */ 15 /* 16 ** sp_getmessage Messages for locklogin [Total 16] 17 ** 18 ** 17260, "Can't run %1! from within a transaction." 19 ** 17880, "No such account -- nothing changed." 20 ** 17912, "Error in locking the account." 21 ** 17913, "Locked account(s):" 22 ** 17914, "Account unlocked." 23 ** 17917, "Error: locktype must either be 'lock' or 'unlock'." 24 ** 17918, "Nothing changed." 25 ** 17919, "Account locked." 26 ** 18419, "Account 'sybmail' cannot be unlocked." 27 ** 18773, "HA_LOG: HA consistency check failure in stored procedure '%1!' on the companion server '%2!'." 28 ** 19390, "One or more accounts do not exist on the companion server." 29 ** 19391, "Warning: One or more accounts specified are active." 30 ** 19392, "Permission Denied. The current account is already locked." 31 ** 19393, "The sets of sa/sso logins on the primary and companion servers are not same." 32 ** 19394, "The set of logins specified is empty." 33 ** 19395, "The login or role to be excluded from locking/unlocking is invalid." 34 ** 19444, "The range for %1! is %2! to %3!." 35 ** 19643, "No account(s) locked." 36 */ 37 /* 38 ** End spgenmsgs.pl output. 39 */ 40 41 /* 42 ** IMPORTANT: Please read the following instructions before 43 ** making changes to this stored procedure. 44 ** 45 ** To make this stored procedure compatible with High Availability (HA), 46 ** changes to certain system tables must be propagated 47 ** to the companion server under some conditions. 48 ** The tables include (but are not limited to): 49 ** syslogins, sysservers, sysattributes, systimeranges, 50 ** sysresourcelimits, sysalternates, sysdatabases, 51 ** syslanguages, sysremotelogins, sysloginroles, 52 ** sysalternates (master DB only), systypes (master DB only), 53 ** sysusers (master DB only), sysprotects (master DB only) 54 ** please refer to the HA documentation for detail. 55 ** 56 ** Here is what you need to do: 57 ** For each insert/update/delete statement, add three sections to 58 ** -- start HA transaction prior to the statement 59 ** -- add the statement 60 ** -- add HA synchronization code to propagate the change to the companion 61 ** 62 ** For example, if you are adding 63 ** insert master.dbo.syslogins ...... 64 ** the code should look like: 65 ** 1. Before that SQL statement: 66 ** 67 ** if (@nHARSTClass = 1) 68 ** begin tran ha_dynsyn 69 ** 70 ** 2. Now, the SQL statement: 71 ** insert master.dbo.syslogins ...... 72 ** 3. Add a HA synchronization section right after the SQL statement: 73 ** 74 ** if (@@error != 0) 75 ** goto clean_all 76 ** if (@nHARSTClass = 1) 77 ** begin 78 ** insert master.dbo.rmt_ha_syslogins ...... 79 ** if (@@error != 0) 80 ** goto clean_all 81 ** 82 ** commit tran ha_dynsyn 83 ** end 84 ** 85 ** 86 ** You may need to do similar change for each built-in function you 87 ** want to add. 88 ** 89 ** Finally, add a separate part at a place where it can not 90 ** be reached by the normal execution path: 91 ** clean_all: 92 ** 93 ** if (@nHARSTClass = 1) 94 ** rollback tran ha_dynsyn 95 ** 96 ** return (1) 97 */ 98 99 create procedure sp_locklogin 100 @loginame varchar(30) = NULL, /* name of the login to be locked */ 101 @locktype varchar(30) = NULL, /* "locked" or "unlock" */ 102 @except varchar(30) = NULL, /* login/role to be exempted */ 103 @inactive_days int = NULL /* no of inactive days of a login */ 104 as 105 begin 106 declare @suid int /* suid of login to be modified */ 107 declare @msg varchar(1024) /* message text */ 108 declare @dummy int 109 declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */ 110 declare @retstat int 111 declare @current_id int /* suid of the current login */ 112 declare @except_is_login tinyint /* flag to indicate if exception is login/role */ 113 declare @except_is_role tinyint 114 declare @encompasses_all_logins tinyint 115 declare @warning_active tinyint 116 declare @row_count_temp int 117 declare @encompasses_all_rmt_ha_logins tinyint 118 declare @temp_current_id int 119 declare @current_date datetime 120 declare @scope varchar(32) /* SDC command execution scope */ 121 122 select @HA_CERTIFIED = 0 123 select @except_is_login = 0 124 select @except_is_role = 0 125 select @encompasses_all_logins = 1 126 select @encompasses_all_rmt_ha_logins = 1 127 select @warning_active = 0 128 select @row_count_temp = 0 129 select @current_date = getdate() 130 131 132 133 declare @log_for_rep int 134 declare @db_rep_level_all int 135 declare @db_rep_level_none int 136 declare @db_rep_level_l1 int 137 declare @lt_rep_get_failed int 138 139 /* 140 ** Initialize some constants 141 */ 142 select @db_rep_level_all = - 1, 143 @db_rep_level_none = 0, 144 @db_rep_level_l1 = 1, 145 @lt_rep_get_failed = - 2 146 147 148 149 /* check to see if we are using HA specific SP for a HA enabled server */ 150 exec @retstat = sp_ha_check_certified 'sp_locklogin', @HA_CERTIFIED 151 if (@retstat != 0) 152 return (1) 153 154 /* create a dummy table to store temporary rows */ 155 create table #dummy_table(suid int) 156 157 /* 158 ** Do not allow this system procedure to be run from within a transaction 159 ** to avoid creating a multi-database transaction where the 'master' 160 ** database is not the co-ordinating database. 161 */ 162 if @@trancount > 0 163 begin 164 /* 17260, "Can't run %1! from within a transaction." */ 165 exec sp_getmessage 17260, @msg output 166 print @msg, "sp_locklogin" 167 return (1) 168 end 169 else 170 begin 171 set chained off 172 end 173 174 set transaction isolation level 1 175 176 /* 177 ** Get the replication status of the 'master' database 178 */ 179 select @log_for_rep = getdbrepstat(1) 180 if (@log_for_rep = @lt_rep_get_failed) 181 begin 182 raiserror 18409, "getdbrepstat" 183 return (1) 184 end 185 186 /* 187 ** Convert the replication status to a boolean 188 */ 189 if (@log_for_rep != @db_rep_level_none) 190 select @log_for_rep = 1 191 else 192 select @log_for_rep = 0 193 194 /* 195 ** If we are logging this system procedure for replication, we must be in 196 ** the 'master' database to avoid creating a multi-database transaction 197 ** which could make recovery of the 'master' database impossible. 198 */ 199 if (@log_for_rep = 1) and (db_name() != "master") 200 begin 201 raiserror 18388, "sp_locklogin" 202 return (1) 203 end 204 205 206 207 /* 208 ** Check if user has sso role, proc_role will also do auditing 209 ** if required. proc_role will also print error message if required. 210 */ 211 212 if (proc_role("sso_role") = 0) 213 return (1) 214 215 216 if @loginame is NULL and @locktype is NULL 217 begin 218 /* 219 ** 17913, "Locked account(s):" 220 */ 221 exec sp_getmessage 17913, @msg output 222 print @msg 223 select name 224 from master.dbo.syslogins 225 where ((status & 2) = 2) /* LOGIN_LOCKED */ 226 and ((status & 512) != 512) /* not LOGIN PROFILE */ 227 return (0) 228 end 229 230 if (@loginame is not NULL) 231 begin 232 /* Check if any such account exists */ 233 234 if not exists (select suid 235 from master.dbo.syslogins 236 where name like @loginame 237 and ((status & 512) != 512)) /* not LOGIN PROFILE */ 238 begin 239 /* 240 ** 17880, "No such account -- nothing changed." 241 */ 242 exec sp_getmessage 17880, @msg output 243 print @msg 244 return (1) 245 end 246 247 end 248 249 250 if ((@locktype != "unlock") and (@locktype != "lock")) 251 begin 252 /* 253 ** 17917, "Error: locktype must either be 'lock' or 'unlock'." 254 */ 255 exec sp_getmessage 17917, @msg output 256 print @msg 257 /* 258 ** 17918, "Nothing changed." 259 */ 260 exec sp_getmessage 17918, @msg output 261 print @msg 262 return (1) 263 end 264 265 /* 266 ** Check if the exception specified is a login or a role 267 ** and set the variable @except_is_login accordingly 268 */ 269 270 if (@except is not NULL) 271 begin 272 if exists (select 1 from master.dbo.syslogins 273 where name = @except and 274 ((status & 512) != 512)) /* not LOGIN PROFILE */ 275 begin 276 /* @except is a login */ 277 278 select @except_is_login = 1 279 end 280 281 /* Check if @except is a valid role */ 282 if exists (select 1 from master.dbo.sysloginroles as srole, 283 master.dbo.syssrvroles as rolename 284 where srole.srid = rolename.srid 285 and rolename.name = @except) 286 begin 287 select @except_is_role = 1 288 end 289 290 if (@except_is_login = 0) and (@except_is_role = 0) 291 begin 292 /* 19395, The login or role to be excluded from locking/unlocking is invalid */ 293 exec sp_getmessage 19395, @msg output 294 print @msg 295 return (1) 296 end 297 end 298 299 /* 300 ** Check if the user specified "effectively NULL" set of logins 301 ** to be locked/unlocked. If so, return doing nothing 302 ** For instance the user might have specified same set of logins 303 ** to be locked as the set to be exempted. 304 */ 305 306 if (@loginame is not NULL) and 307 (((@except_is_login = 1) and 308 (not exists (select suid from master.dbo.syslogins 309 where name like @loginame 310 and name != @except))) or 311 ((@except_is_role = 1) and 312 (not exists (select suid from master.dbo.syslogins 313 where name like @loginame and 314 suid not in 315 (select suid from master.dbo.sysloginroles 316 where srid = role_id(@except)))))) 317 begin 318 319 /* 19394, "The set of logins specified is empty." */ 320 exec sp_getmessage 19394, @msg output 321 print @msg 322 323 return (1) 324 end 325 326 327 328 329 330 if @locktype = "unlock" 331 begin 332 333 /* 'Sybmail' account cannot be unlocked */ 334 if (@loginame = "sybmail") 335 begin 336 /* 337 ** 18419, "Account 'sybmail' cannot be unlocked." 338 */ 339 exec sp_getmessage 18419, @msg output 340 print @msg 341 return (1) 342 end 343 344 /* '@inactive_days' and 'unlock' cannot be specified at the same time */ 345 if (@inactive_days != NULL) 346 begin 347 /* 348 ** 19607, "You cannot specify both 349 ** 'inactive_days' and 'unlock' at the same time." 350 */ 351 exec sp_getmessage 19607, @msg output 352 print @msg 353 return (1) 354 end 355 356 /* Unlock any existing account */ 357 358 if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1)) 359 begin tran rs_logexec 360 361 362 363 if (@loginame is NULL) 364 begin 365 if (@except_is_login = 1) 366 begin 367 /* 368 ** Unlock logins except ones which match @except 369 ** 370 ** The columns lockdate, lockreason, locksuid are 371 ** reset with values NULL, NULL, NULL whenever 372 ** a login account is unlocked. 373 ** 374 ** The columns lockdate, lockreason and locksuid 375 ** are set to context specific values when an account 376 ** is locked. The values reflect the datetime, 377 ** reason code, and the suid of the login 378 ** that is locking the account. 379 ** 380 ** Changes to these columns should be recorded 381 ** in the audit trail, and will do so in a future 382 ** ASE release when locking is done outside of 383 ** stored procedure. 384 */ 385 update master.dbo.syslogins 386 set status = status & (~ 2), 387 logincount = 0, 388 lockdate = NULL, 389 lockreason = NULL, 390 locksuid = NULL 391 where name != @except 392 and ((status & 512) != 512) /* 393 ** don't update 394 ** LOGIN PROFILE, if any 395 */ 396 end 397 else 398 if (@except_is_role = 1) 399 begin 400 /* 401 ** @except is a role 402 ** Unlock all logins except ones whose role matches 403 ** @except 404 */ 405 406 update master.dbo.syslogins 407 set status = status & (~ 2), 408 logincount = 0, 409 lockdate = NULL, 410 lockreason = NULL, 411 locksuid = NULL 412 where suid not in 413 (select suid from master.dbo.sysloginroles 414 where srid = role_id(@except)) 415 and ((status & 512) != 512) /* 416 ** don't update 417 ** LOGIN PROFILE, if any 418 */ 419 end 420 else 421 begin 422 /* @except is NULL */ 423 update master.dbo.syslogins 424 set status = status & (~ 2), 425 logincount = 0, 426 lockdate = NULL, 427 lockreason = NULL, 428 locksuid = NULL 429 where ((status & 512) != 512) /* 430 ** don't update 431 ** LOGIN PROFILE, if any 432 */ 433 end 434 end 435 else 436 begin 437 /* 438 ** @loginame is not NULL 439 ** Same cases as above but considering @loginame as well 440 ** Unlock logins which match the specified @loginame 441 ** excluding the ones mentioned in @except 442 */ 443 444 if (@except_is_login = 1) 445 begin 446 update master.dbo.syslogins 447 set status = status & (~ 2), /* LOGIN_UNLOCKED */ 448 logincount = 0, 449 lockdate = NULL, 450 lockreason = NULL, 451 locksuid = NULL 452 where name like @loginame 453 and name != @except 454 and ((status & 512) != 512) /* 455 ** don't update 456 ** LOGIN PROFILE, if any 457 */ 458 end 459 else 460 if (@except_is_role = 1) 461 begin 462 update master.dbo.syslogins 463 set status = status & (~ 2), 464 logincount = 0, 465 lockdate = NULL, 466 lockreason = NULL, 467 locksuid = NULL 468 where name like @loginame and 469 suid not in 470 (select suid from master.dbo.sysloginroles 471 where srid = role_id(@except)) 472 and ((status & 512) != 512) /* 473 ** don't update 474 ** LOGIN PROFILE, if any 475 */ 476 end 477 else 478 begin 479 update master.dbo.syslogins 480 set status = status & (~ 2), 481 logincount = 0, 482 lockdate = NULL, 483 lockreason = NULL, 484 locksuid = NULL 485 where name like @loginame 486 and ((status & 512) != 512) /* 487 ** don't update 488 ** LOGIN PROFILE, if any 489 */ 490 end 491 end 492 493 494 495 if (@log_for_rep = 1) 496 begin 497 /* 498 ** If the 'master' database is marked for replication, the 499 ** T-SQL built-in 'logexec()' will log for replication the 500 ** execution instance of this system procedure. Otherwise, 501 ** the T-SQL built-in 'logexec()' is a no-op. 502 */ 503 if (logexec(1) != 1) 504 begin 505 raiserror 17756, "sp_locklogin", "master" 506 goto clean_all 507 end 508 end 509 510 if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1)) 511 commit tran rs_logexec 512 513 /* 514 ** 17914, "Account unlocked." 515 */ 516 exec sp_getmessage 17914, @msg output 517 print @msg 518 return (0) 519 end 520 521 /* 522 ** When it comes down to here, it must be the complicated "lock" case. 523 */ 524 525 select @current_id = suser_id() 526 /* 527 ** Check if the set of logins to be locked (after taking exceptions into 528 ** consideration) encompasses all the sso/sa logins in the server. 529 ** If it does, we will be locking all the sa/sso logins; so in this case 530 ** leave out the current login and lock the rest of the logins. 531 ** If it doesn't, go ahead and lock the set of logins specified. 532 ** By encompassing all sso/sa logins, we mean there must be atleast 533 ** one login with sso privilege and at least one login with sa privilege 534 ** outside our locking set. First we check for sso logins (srid =1) and 535 ** then for sa logins (srid = 0) and set the flag @encompasses_all_logins 536 ** to false even if one of them is satisfied. 537 */ 538 539 if (@loginame is not NULL) 540 begin 541 if (@except_is_login = 1) 542 begin 543 /* 544 ** SSO logins srid = 1 and SA logins srid = 0 545 ** If there exists an sso and sa login outside the 546 ** set of logins to be locked then it means 547 ** the set doesn't encompass all of sso and sa logins 548 ** Similar logic applied in the subsequent code 549 ** with changes according as login or role specified 550 */ 551 552 if exists (select 1 from master.dbo.syslogins as ms, 553 master.dbo.sysloginroles as msr 554 where (ms.status) & 2 = 0 and 555 ms.suid = msr.suid and 556 msr.srid = 1 and 557 (ms.name not like @loginame or ms.name = @except)) 558 and 559 exists (select 1 from master.dbo.syslogins as ms, 560 master.dbo.sysloginroles as msr 561 where (ms.status) & 2 = 0 and 562 ms.suid = msr.suid and 563 msr.srid = 0 and 564 (ms.name not like @loginame or ms.name = @except)) 565 begin 566 select @encompasses_all_logins = 0 567 end 568 569 end 570 else if (@except_is_role = 1) 571 begin 572 /* 573 ** Taking into consideration the exception specified is a role, 574 ** find out if the set of logins to be locked doesn't encompass 575 ** all SA and all SSO logins 576 */ 577 if exists (select 1 from master.dbo.syslogins as ms, 578 master.dbo.sysloginroles as msr 579 where (ms.status) & 2 = 0 and 580 ms.suid = msr.suid and 581 msr.srid = 1 and 582 ((ms.name not like @loginame) or 583 ms.suid in 584 (select suid from master.dbo.sysloginroles 585 where srid = role_id(@except)))) 586 and 587 exists (select 1 from master.dbo.syslogins as ms, 588 master.dbo.sysloginroles as msr 589 where (ms.status) & 2 = 0 and 590 ms.suid = msr.suid and 591 msr.srid = 0 and 592 ((ms.name not like @loginame) or 593 ms.suid in 594 (select suid from master.dbo.sysloginroles 595 where srid = role_id(@except)))) 596 597 begin 598 select @encompasses_all_logins = 0 599 end 600 601 end 602 else 603 begin 604 /* 605 ** @except is NULL 606 ** See if the set of logins to be locked encompasses 607 ** all SA logins or all SSO logins 608 ** If it doesn't encompass all SA logins and all SSO 609 ** logins set @encompasses_all_logins to 0. 610 */ 611 if exists (select 1 from master.dbo.syslogins as ms, 612 master.dbo.sysloginroles as msr 613 where (ms.status) & 2 = 0 and 614 ms.suid = msr.suid and 615 msr.srid = 1 and 616 ms.name not like @loginame) 617 and 618 exists (select 1 from master.dbo.syslogins as ms, 619 master.dbo.sysloginroles as msr 620 where (ms.status) & 2 = 0 and 621 ms.suid = msr.suid and 622 msr.srid = 0 and 623 ms.name not like @loginame) 624 begin 625 select @encompasses_all_logins = 0 626 end 627 628 end 629 end 630 else 631 begin 632 /* @loginame = NULL */ 633 634 if (@except_is_login = 1) 635 begin 636 /* 637 ** Set @encompasses_all_logins to 0 if there exists 638 ** one SA and one SSO login outside the set of logins 639 ** to be locked. 640 */ 641 if exists (select 1 from master.dbo.syslogins as ms, 642 master.dbo.sysloginroles as msr 643 where (ms.status) & 2 = 0 and 644 ms.suid = msr.suid and 645 msr.srid = 1 and 646 ms.name = @except) 647 and 648 exists (select 1 from master.dbo.syslogins as ms, 649 master.dbo.sysloginroles as msr 650 where (ms.status) & 2 = 0 and 651 ms.suid = msr.suid and 652 msr.srid = 0 and 653 ms.name = @except) 654 begin 655 select @encompasses_all_logins = 0 656 end 657 658 end 659 else if (@except_is_role = 1) 660 begin 661 /* 662 ** The exception specified is a role. Set @encompasses_all_logins 663 ** as above considering this @except as a role 664 */ 665 if exists (select 1 from master.dbo.syslogins as ms, 666 master.dbo.sysloginroles as msr 667 where (ms.status) & 2 = 0 and 668 ms.suid = msr.suid and 669 msr.srid = 1 and 670 ms.suid in 671 (select suid from master.dbo.sysloginroles 672 where srid = role_id(@except))) 673 and 674 exists (select 1 from master.dbo.syslogins as ms, 675 master.dbo.sysloginroles as msr 676 where (ms.status) & 2 = 0 and 677 ms.suid = msr.suid and 678 msr.srid = 0 and 679 ms.suid in 680 (select suid from master.dbo.sysloginroles 681 where srid = role_id(@except))) 682 begin 683 select @encompasses_all_logins = 0 684 end 685 686 end 687 end 688 689 /* 690 ** check if @inactive_days is in valid range 0-32767 and 691 ** 'enable last login' is 1 in sysattributes. 692 */ 693 if (@inactive_days != NULL) 694 begin 695 if ((@inactive_days < 0) or (@inactive_days > 32767)) 696 begin 697 /* 698 ** 19444, "The range for %1! is %2! to %3!." 699 */ 700 raiserror 19444, 'inactive days', 0, 32767 701 return (1) 702 end 703 /* 704 ** no of inactive days = today's date - last login date. 705 ** For calculation of inactive days, last login updates must be enabled, 706 ** so attribute row for 'enable last login updates' 707 ** in sysattribute should not be set to 0. 708 ** Either the row should not exists OR it should be set to 1. 709 */ 710 if ((select int_value from master.dbo.sysattributes 711 where class = 32 and attribute = 0) = 0) 712 begin 713 /* 714 ** 19606, To lock inactive accounts, you must run 715 ** sp_passwordpolicy 'set','enable last login updates',1 716 */ 717 raiserror 19606 718 return (1) 719 end 720 end 721 /* 722 ** If any of the accounts to be locked are active, go ahead and try 723 ** to lock them, but issue a warning message. 724 ** If @encompasses_all_logins = 1 then the @current_id is not locked 725 ** but a warning message could be displayed erroneously as the account 726 ** is active. So set @temp_current_id and use it to exclude the 727 ** current account. If @encompasses_all_logins = 0 @current_id shouldn't 728 ** come into picture - hence invalidate it. 729 */ 730 731 if (@encompasses_all_logins = 1) 732 select @temp_current_id = @current_id 733 else 734 select @temp_current_id = - 2 /* @temp_current_id is invalidated */ 735 736 /* 737 ** SDC only, check clusterwide sysprocesses for active login 738 */ 739 if (@@clustermode = "shared disk cluster") 740 begin 741 select @scope = @@system_view 742 set system_view cluster 743 end 744 745 if (@loginame is not NULL) 746 begin 747 if (@except_is_login = 1) 748 begin 749 /* 750 ** set @warning_active to 1 if the set of logins to 751 ** be locked has any active login 752 */ 753 if exists (select 1 from master.dbo.sysprocesses as mp, 754 master.dbo.syslogins as ms 755 where mp.suid = ms.suid and 756 ms.name like @loginame and 757 ms.name != @except and 758 ms.suid != @temp_current_id) 759 begin 760 select @warning_active = 1 761 end 762 763 end 764 else if (@except_is_role = 1) 765 begin 766 /* 767 ** Similar to the above condition except that 768 ** the exception specified is a role 769 */ 770 if exists (select 1 from master.dbo.sysprocesses as mp, 771 master.dbo.syslogins as ms 772 where mp.suid = ms.suid and 773 ms.name like @loginame and 774 ms.suid != @temp_current_id and 775 ms.suid not in 776 (select suid from master.dbo.sysloginroles 777 where srid = role_id(@except))) 778 begin 779 select @warning_active = 1 780 end 781 782 end 783 else 784 /* @except is NULL */ 785 begin 786 /* 787 ** Find out if any login to be locked is active 788 ** There is no exception specified. 789 */ 790 if exists (select 1 from master.dbo.sysprocesses as mp, 791 master.dbo.syslogins as ms 792 where ms.suid = mp.suid and 793 ms.name like @loginame and 794 ms.suid != @temp_current_id) 795 begin 796 select @warning_active = 1 797 end 798 end 799 end 800 else 801 begin 802 /* @loginame = NULL */ 803 if (@except_is_login = 1) 804 begin 805 /* 806 ** Find out if the set of logins to be locked has 807 ** any active login 808 */ 809 if exists (select 1 from master.dbo.sysprocesses as mp, 810 master.dbo.syslogins as ms 811 where mp.suid = ms.suid and 812 ms.name != @except and 813 ms.suid != @temp_current_id) 814 begin 815 select @warning_active = 1 816 end 817 818 end 819 else if (@except_is_role = 1) 820 begin 821 /* 822 ** Similar to the above case 823 ** Here the @except specified is a role 824 */ 825 if exists (select 1 from master.dbo.sysprocesses as mp, 826 master.dbo.syslogins as ms 827 where mp.suid = ms.suid and 828 ms.suid != @temp_current_id and 829 ms.suid not in 830 (select suid from master.dbo.sysloginroles 831 where srid = role_id(@except))) 832 begin 833 select @warning_active = 1 834 end 835 836 end 837 else 838 /* @except is NULL */ 839 begin 840 if exists (select 1 from master.dbo.sysprocesses as mp, 841 master.dbo.syslogins as ms 842 where ms.suid = mp.suid and 843 ms.suid != @temp_current_id) 844 begin 845 select @warning_active = 1 846 end 847 848 end 849 end 850 851 /* 852 ** SDC only: restore previous system_view scope 853 */ 854 if (@@clustermode = "shared disk cluster") 855 begin 856 set system_view @scope 857 end 858 859 if (@warning_active = 1) 860 begin 861 /* 862 ** 19391, "Warning: One or more accounts specified are active." 863 */ 864 865 exec sp_getmessage 19391, @msg output 866 print @msg 867 868 /* FALL THROUGH */ 869 end 870 871 872 873 874 if (@encompasses_all_logins = 1) 875 begin 876 /* 877 ** Check if the current account is already locked. If yes, 878 ** flag an error as locking all accounts excluding the 879 ** current is not possible. 880 ** In other words, an account which is already locked at 881 ** this point of time cannot be allowed to issue any lock 882 ** command. 883 ** However, under certain race conditions where more than 884 ** one such accounts issue lock commands simultaneously, 885 ** this check doesn't guarantee consistency. If the current 886 ** account is locked AFTER this check passes through by some 887 ** other login, we have a situation where a locked account 888 ** will be allowed to lock other accounts. This drawback 889 ** cannot be overcome without removing the check and without 890 ** doing the check as well as locking operation in a transaction. 891 ** Considering it is rare that such a situation occurs, this 892 ** approach is adopted. 893 */ 894 895 if exists (select 1 from master.dbo.syslogins 896 where (status & 2) = 2 and 897 suid = @current_id) 898 begin 899 /* 19392, Permission Denied. The current account is already locked. */ 900 exec sp_getmessage 19392, @msg output 901 print @msg 902 903 return (1) 904 end 905 906 end 907 908 /* Feel free to lock the account */ 909 910 if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1)) 911 begin tran rs_logexec 912 913 914 915 /* 916 ** There is a race condition possible. Even though we unlock the current account 917 ** before quitting (see the later part of the code), if we lock the current account 918 ** here AND the set of accts encompasses all SA/SSO logins, we have a situation 919 ** where for a brief period, all SA/SSO logins may be locked. So, if we find that 920 ** all SA/SSO logins have been encompassed we exclude the current account(which has 921 ** SA/SSO role) from locking in which case we set the variable @current_id to 922 ** the suid of the current account. however if all SA/SSO logins have not been 923 ** encompassed we need not exclude the current account from the set of logins 924 ** being locked, so invalidate the variable @current_id 925 */ 926 927 if (@encompasses_all_logins = 0) 928 select @current_id = - 2 /* Invalidate @current_id value */ 929 930 if (@loginame is not NULL) 931 begin 932 if (@except_is_login = 1) 933 begin 934 /* 935 ** Lock all the accounts matching @loginame except 936 ** "@except" login and current account (current acct 937 ** may be valid or invalid) 938 */ 939 if (@inactive_days != NULL) 940 begin 941 /* 942 ** As 'last login update' feature(securing passwords) 943 ** will not be HA-ized in 15.0.2 release, we need 944 ** to handle it specially on primary and companion server. 945 ** To keep locked accounts in sync, we compare 'lastlogindate' 946 ** column on both servers, and use the most recent date 947 ** to calculate inactive days. 948 ** 949 ** Check if companion server is configured. and 950 ** then accordingly decide the set of accounts to lock. 951 */ 952 if (@HA_CERTIFIED = 0) 953 begin 954 /* 955 ** Companion server is not configured, so you 956 ** can use master.dbo.syslogins for lastlogindate. 957 ** No need to look at master.dbo.rmt_ha_syslogins 958 */ 959 update master.dbo.syslogins 960 set status = status | 2, /* LOGIN_LOCKED */ 961 lockreason = 1, /* locked because inactive */ 962 lockdate = @current_date, 963 locksuid = suser_id() 964 where name like @loginame 965 /* 966 ** login is unlocked and is not exempted 967 ** from locking due to inactivity. 968 */ 969 and (status & 10) = 0 970 and suid != @current_id 971 and name != @except 972 and (sybsystemprocs.dbo.udf_inactivity_check( 973 lpid, lastlogindate, pwdate, 974 @current_date, @inactive_days) 975 = 1) 976 and ((status & 512) != 512) /* 977 ** don't update 978 ** LOGIN PROFILE, if any 979 */ 980 end 981 else 982 begin 983 /* 984 ** A 'begin - end' block , that includes all statement in '##if - ##endif' only, 985 ** gives syntax error. The 'begin - end' block is considered as empty block. 986 ** following 'select' statement is included to avoid that error. 987 */ 988 select @dummy = 1 989 990 /* 991 ** Companion server is configured. 992 ** If a login, 993 ** is inactive on primary server, 994 ** we check if it is inactive on companion server also. 995 ** If is inactive on both servers, then only it is locked. 996 */ 997 998 end 999 end 1000 else /* @inactive_days = NULL */ 1001 begin 1002 update master.dbo.syslogins 1003 set status = status | 2, /* LOGIN_LOCKED */ 1004 lockreason = 0, /* locked manually using sp_locklogin */ 1005 lockdate = @current_date, 1006 locksuid = suser_id() 1007 where name like @loginame 1008 and (status & 2) = 0 1009 and suid != @current_id 1010 and name != @except 1011 and ((status & 512) != 512) /* 1012 ** don't update 1013 ** LOGIN PROFILE, if any 1014 */ 1015 end 1016 end 1017 else if (@except_is_role = 1) 1018 begin 1019 /* 1020 ** Lock all the accounts matching @loginame except 1021 ** those which have the role @except and excluding 1022 ** current account (which may be valid or invalid) 1023 */ 1024 if (@inactive_days != NULL) 1025 begin 1026 if (@HA_CERTIFIED = 0) 1027 begin 1028 update master.dbo.syslogins 1029 set status = status | 2, /* LOGIN_LOCKED */ 1030 lockreason = 1, /* locked because inactive */ 1031 lockdate = @current_date, 1032 locksuid = suser_id() 1033 where name like @loginame 1034 /* 1035 ** login is unlocked and is not exempted 1036 ** from locking due to inactivity. 1037 */ 1038 and (status & 10) = 0 1039 and suid != @current_id 1040 and suid not in 1041 (select suid from master.dbo.sysloginroles 1042 where srid = role_id(@except)) 1043 and (sybsystemprocs.dbo.udf_inactivity_check( 1044 lpid, lastlogindate, pwdate, 1045 @current_date, @inactive_days) 1046 = 1) 1047 and ((status & 512) != 512) /* 1048 ** don't update 1049 ** LOGIN PROFILE, if any 1050 */ 1051 end 1052 else /* HA server is configured */ 1053 begin 1054 select @dummy = 1 1055 1056 end 1057 end 1058 else /* @inactive_days = NULL */ 1059 begin 1060 update master.dbo.syslogins 1061 set status = status | 2, 1062 lockreason = 0, /* locked manually using sp_locklogin */ 1063 lockdate = @current_date, 1064 locksuid = suser_id() 1065 where name like @loginame 1066 and (status & 2) = 0 1067 and suid != @current_id 1068 and suid not in 1069 (select suid from master.dbo.sysloginroles 1070 where srid = role_id(@except)) 1071 and ((status & 512) != 512) /* 1072 ** don't update 1073 ** LOGIN PROFILE, if any 1074 */ 1075 end 1076 end 1077 else 1078 /* @except is NULL */ 1079 begin 1080 /* 1081 ** Lock all the accounts matching @loginame 1082 ** excluding current account (which may be valid or invalid) 1083 */ 1084 if (@inactive_days != NULL) 1085 begin 1086 if (@HA_CERTIFIED = 0) 1087 begin 1088 update master.dbo.syslogins 1089 set status = status | 2, /* LOGIN_LOCKED */ 1090 lockreason = 1, /* locked because inactive */ 1091 lockdate = @current_date, 1092 locksuid = suser_id() 1093 where name like @loginame 1094 /* 1095 ** login is unlocked and is not exempted 1096 ** from locking due to inactivity. 1097 */ 1098 and (status & 10) = 0 1099 and suid != @current_id 1100 and (sybsystemprocs.dbo.udf_inactivity_check( 1101 lpid, lastlogindate, pwdate, 1102 @current_date, @inactive_days) 1103 = 1) 1104 and ((status & 512) != 512) /* 1105 ** don't update 1106 ** LOGIN PROFILE, if any 1107 */ 1108 end 1109 else /* HA server is configured */ 1110 begin 1111 select @dummy = 1 1112 1113 end 1114 end 1115 else /* @inactive_days = NULL */ 1116 begin 1117 update master.dbo.syslogins 1118 set status = status | 2, 1119 lockreason = 0, /* locked manually using sp_locklogin */ 1120 lockdate = @current_date, 1121 locksuid = suser_id() 1122 where name like @loginame 1123 and (status & 2) = 0 1124 and suid != @current_id 1125 and ((status & 512) != 512) /* 1126 ** don't update 1127 ** LOGIN PROFILE, if any 1128 */ 1129 end 1130 end 1131 end 1132 else 1133 /* @loginame is NULL */ 1134 begin 1135 if (@except_is_login = 1) 1136 begin 1137 /* 1138 ** Lock all logins except that match @except 1139 ** leaving out current login 1140 */ 1141 if (@inactive_days != NULL) 1142 begin 1143 if (@HA_CERTIFIED = 0) 1144 begin 1145 update master.dbo.syslogins 1146 set status = status | 2, /* LOGIN_LOCKED */ 1147 lockreason = 1, /* locked because inactive */ 1148 lockdate = @current_date, 1149 locksuid = suser_id() 1150 where suid != @current_id 1151 /* 1152 ** login is unlocked and is not exempted 1153 ** from locking due to inactivity. 1154 */ 1155 and (status & 10) = 0 1156 and name != @except 1157 and (sybsystemprocs.dbo.udf_inactivity_check( 1158 lpid, lastlogindate, pwdate, 1159 @current_date, @inactive_days) 1160 = 1) 1161 and ((status & 512) != 512) /* 1162 ** don't update 1163 ** LOGIN PROFILE, if any 1164 */ 1165 end 1166 else /* HA server is configured */ 1167 begin 1168 select @dummy = 1 1169 1170 end 1171 end 1172 else /* @inactive_days = NULL */ 1173 begin 1174 update master.dbo.syslogins 1175 set status = status | 2, /* LOGIN_LOCKED */ 1176 lockreason = 0, /* locked manually using sp_locklogin */ 1177 lockdate = @current_date, 1178 locksuid = suser_id() 1179 where suid != @current_id 1180 and (status & 2) = 0 1181 and name != @except 1182 and ((status & 512) != 512) /* 1183 ** don't update 1184 ** LOGIN PROFILE, if any 1185 */ 1186 end 1187 end 1188 else if (@except_is_role = 1) 1189 begin 1190 /* 1191 ** Lock all logins except whose role matches the 1192 ** role specified as exception 1193 */ 1194 if (@inactive_days != NULL) 1195 begin 1196 if (@HA_CERTIFIED = 0) 1197 begin 1198 update master.dbo.syslogins 1199 set status = status | 2, 1200 lockreason = 1, /* locked because inactive */ 1201 lockdate = @current_date, 1202 locksuid = suser_id() 1203 where suid != @current_id 1204 /* 1205 ** login is unlocked and is not exempted 1206 ** from locking due to inactivity. 1207 */ 1208 and (status & 10) = 0 1209 and suid not in 1210 (select suid from master.dbo.sysloginroles 1211 where srid = role_id(@except)) 1212 and (sybsystemprocs.dbo.udf_inactivity_check( 1213 lpid, lastlogindate, pwdate, 1214 @current_date, @inactive_days) 1215 = 1) 1216 and ((status & 512) != 512) /* 1217 ** don't update 1218 ** LOGIN PROFILE, if any 1219 */ 1220 end 1221 else /* HA server is configured */ 1222 begin 1223 select @dummy = 1 1224 1225 end 1226 end 1227 else /* @inactive_days = NULL */ 1228 begin 1229 update master.dbo.syslogins 1230 set status = status | 2, 1231 lockreason = 0, /* locked manually using sp_locklogin */ 1232 lockdate = @current_date, 1233 locksuid = suser_id() 1234 where suid != @current_id 1235 and (status & 2) = 0 1236 and suid not in 1237 (select suid from master.dbo.sysloginroles 1238 where srid = role_id(@except)) 1239 and ((status & 512) != 512) /* 1240 ** don't update 1241 ** LOGIN PROFILE, if any 1242 */ 1243 end 1244 end 1245 else 1246 begin 1247 /* No exception is specified */ 1248 if (@inactive_days != NULL) 1249 begin 1250 if (@HA_CERTIFIED = 0) 1251 begin 1252 update master.dbo.syslogins 1253 set status = status | 2, 1254 lockreason = 1, /* locked because inactive */ 1255 lockdate = @current_date, 1256 locksuid = suser_id() 1257 where suid != @current_id 1258 /* 1259 ** login is unlocked and is not exempted 1260 ** from locking due to inactivity. 1261 */ 1262 and (status & 10) = 0 1263 and (sybsystemprocs.dbo.udf_inactivity_check( 1264 lpid, lastlogindate, pwdate, 1265 @current_date, @inactive_days) 1266 = 1) 1267 and ((status & 512) != 512) /* 1268 ** don't update 1269 ** LOGIN PROFILE, if any 1270 */ 1271 end 1272 else 1273 begin 1274 select @dummy = 1 1275 1276 end 1277 end 1278 else 1279 begin 1280 update master.dbo.syslogins 1281 set status = status | 2, 1282 lockreason = 0, /* locked manually using sp_locklogin */ 1283 lockdate = @current_date, 1284 locksuid = suser_id() 1285 where suid != @current_id 1286 and (status & 2) = 0 1287 and ((status & 512) != 512) /* 1288 ** don't update 1289 ** LOGIN PROFILE, if any 1290 */ 1291 end 1292 end 1293 end 1294 1295 /* 1296 ** Because of some possible race-conditions unlock the account 1297 ** just before quitting to ensure the current account remains 1298 ** unlocked. Race between different logins trying to lock a set of accounts 1299 ** The last login which executes this statement unlocks that 1300 ** account, at the end we are sure that at least one login remains unlocked 1301 */ 1302 1303 select @row_count_temp = @@rowcount 1304 1305 if (@encompasses_all_logins = 1) 1306 begin 1307 update master.dbo.syslogins 1308 set status = status & ~ 2 /* Unlock current account before quitting */ 1309 where suid = @current_id 1310 end 1311 1312 /* 1313 ** Check @@rowcount when it works 1314 */ 1315 1316 if (@row_count_temp > 0) 1317 begin 1318 1319 1320 1321 if (@log_for_rep = 1) 1322 begin 1323 /* 1324 ** If the 'master' database is marked for replication, the 1325 ** T-SQL built-in 'logexec()' will log for replication the 1326 ** execution instance of this system procedure. Otherwise, 1327 ** the T-SQL built-in 'logexec()' is a no-op. 1328 */ 1329 if (logexec(1) != 1) 1330 begin 1331 raiserror 17756, "sp_locklogin", "master" 1332 goto clean_all 1333 end 1334 end 1335 1336 if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1)) 1337 commit tran rs_logexec 1338 1339 /* 1340 ** 17919, "Account locked." 1341 */ 1342 exec sp_getmessage 17919, @msg output 1343 print @msg 1344 return (0) 1345 end 1346 else 1347 begin 1348 /* 1349 ** 19643, "No account(s) locked." 1350 */ 1351 exec sp_getmessage 19643, @msg output 1352 print @msg 1353 1354 /* 1355 ** No accounts were locked, as there were no unlocked accounts to be locked. 1356 ** so rollback the transaction , but return 0 as return value. 1357 */ 1358 if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1)) 1359 rollback tran rs_logexec 1360 return (0) 1361 end 1362 1363 clean_all: 1364 if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1)) 1365 rollback tran rs_logexec 1366 return (1) 1367 end 1368
exec sp_procxmode 'sp_locklogin', 'AnyMode' go Grant Execute on sp_locklogin to public go
| RESULT SETS | |
| sp_locklogin_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED read_writes table master..syslogins (1)  reads table master..syssrvroles (1) calls proc sybsystemprocs..sp_ha_check_certified  reads table tempdb..sysobjects (1)  writes table tempdb..#dummy_table (1) reads table master..sysloginroles (1) calls proc sybsystemprocs..sp_getmessage  calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages reads table master..sysmessages (1) reads table master..sysprocesses (1) reads table master..sysattributes (1) CALLERS called by proc sybsystemprocs..sp_addlogin  called by proc sybsystemprocs..sp_droplogin |
