github.com/ldc1995/fabric-ca@v2.0.0-alpha.0.20200422214819-8d49c278c386+incompatible/lib/dbaccessor.go (about) 1 /* 2 Copyright IBM Corp. All Rights Reserved. 3 4 SPDX-License-Identifier: Apache-2.0 5 */ 6 7 package lib 8 9 import ( 10 "encoding/json" 11 "strings" 12 13 "github.com/cloudflare/cfssl/log" 14 "github.com/hyperledger/fabric-ca/api" 15 "github.com/hyperledger/fabric-ca/lib/attr" 16 "github.com/hyperledger/fabric-ca/lib/caerrors" 17 "github.com/hyperledger/fabric-ca/lib/server/db" 18 cadbutil "github.com/hyperledger/fabric-ca/lib/server/db/util" 19 "github.com/hyperledger/fabric-ca/lib/server/user" 20 cadbuser "github.com/hyperledger/fabric-ca/lib/server/user" 21 "github.com/hyperledger/fabric-ca/lib/spi" 22 "github.com/hyperledger/fabric-ca/util" 23 "github.com/jmoiron/sqlx" 24 "github.com/kisielk/sqlstruct" 25 "github.com/pkg/errors" 26 "golang.org/x/crypto/bcrypt" 27 "golang.org/x/crypto/ocsp" 28 ) 29 30 // Match to sqlx 31 func init() { 32 sqlstruct.TagName = "db" 33 } 34 35 const ( 36 insertUser = ` 37 INSERT INTO users (id, token, type, affiliation, attributes, state, max_enrollments, level, incorrect_password_attempts) 38 VALUES (:id, :token, :type, :affiliation, :attributes, :state, :max_enrollments, :level, :incorrect_password_attempts);` 39 40 deleteUser = ` 41 DELETE FROM users 42 WHERE (id = ?);` 43 44 updateUser = ` 45 UPDATE users 46 SET token = :token, type = :type, affiliation = :affiliation, attributes = :attributes, state = :state, max_enrollments = :max_enrollments, level = :level, incorrect_password_attempts = :incorrect_password_attempts 47 WHERE (id = :id);` 48 49 getUser = ` 50 SELECT * FROM users 51 WHERE (id = ?)` 52 53 insertAffiliation = ` 54 INSERT INTO affiliations (name, prekey, level) 55 VALUES (?, ?, ?)` 56 57 deleteAffiliation = ` 58 DELETE FROM affiliations 59 WHERE (name = ?)` 60 61 deleteAffAndSubAff = ` 62 DELETE FROM affiliations 63 WHERE (name = ? OR name LIKE ?)` 64 65 getAffiliationQuery = ` 66 SELECT * FROM affiliations 67 WHERE (name = ?)` 68 69 getAllAffiliationsQuery = ` 70 SELECT * FROM affiliations 71 WHERE (name = ? OR name LIKE ?)` 72 73 getIDsWithAffiliation = ` 74 SELECT * FROM users 75 WHERE (affiliation = ?)` 76 77 updateAffiliation = ` 78 UPDATE affiliations 79 SET name = ?, prekey = ? 80 WHERE (name = ?)` 81 ) 82 83 // Accessor implements db.Accessor interface. 84 type Accessor struct { 85 db db.FabricCADB 86 } 87 88 // NewDBAccessor is a constructor for the database API 89 func NewDBAccessor(cadb db.FabricCADB) *Accessor { 90 return &Accessor{ 91 db: cadb, 92 } 93 } 94 95 func (d *Accessor) checkDB() error { 96 if d.db == nil { 97 return errors.New("Failed to correctly setup database connection") 98 } 99 return nil 100 } 101 102 // SetDB changes the underlying sql.DB object Accessor is manipulating. 103 func (d *Accessor) SetDB(db db.FabricCADB) { 104 d.db = db 105 } 106 107 // InsertUser inserts user into database 108 func (d *Accessor) InsertUser(user *cadbuser.Info) error { 109 if user == nil { 110 return errors.New("User is not defined") 111 } 112 log.Debugf("DB: Add identity %s", user.Name) 113 114 err := d.checkDB() 115 if err != nil { 116 return err 117 } 118 119 attrBytes, err := json.Marshal(user.Attributes) 120 if err != nil { 121 return err 122 } 123 124 // Hash the password before storing it 125 pwd := []byte(user.Pass) 126 pwd, err = bcrypt.GenerateFromPassword(pwd, bcrypt.DefaultCost) 127 if err != nil { 128 return errors.Wrap(err, "Failed to hash password") 129 } 130 131 // Store the user record in the DB 132 res, err := d.db.NamedExec("InsertUser", insertUser, &cadbuser.Record{ 133 Name: user.Name, 134 Pass: pwd, 135 Type: user.Type, 136 Affiliation: user.Affiliation, 137 Attributes: string(attrBytes), 138 State: user.State, 139 MaxEnrollments: user.MaxEnrollments, 140 Level: user.Level, 141 IncorrectPasswordAttempts: 0, 142 }) 143 144 if err != nil { 145 return errors.Wrapf(err, "Error adding identity '%s' to the database", user.Name) 146 } 147 148 numRowsAffected, err := res.RowsAffected() 149 if err != nil { 150 return err 151 } 152 153 if numRowsAffected == 0 { 154 return errors.Errorf("Failed to add identity %s to the database", user.Name) 155 } 156 157 if numRowsAffected != 1 { 158 return errors.Errorf("Expected to add one record to the database, but %d records were added", numRowsAffected) 159 } 160 161 log.Debugf("Successfully added identity %s to the database", user.Name) 162 163 return nil 164 165 } 166 167 // DeleteUser deletes user from database 168 func (d *Accessor) DeleteUser(id string) (user.User, error) { 169 log.Debugf("DB: Delete identity %s", id) 170 171 result, err := d.doTransaction(d.deleteUserTx, id, ocsp.CessationOfOperation) // 5 (cessationofoperation) reason for certificate revocation 172 if err != nil { 173 return nil, err 174 } 175 176 userRec := result.(*cadbuser.Record) 177 user := cadbuser.New(userRec, d.db) 178 179 return user, nil 180 } 181 182 func (d *Accessor) deleteUserTx(tx *sqlx.Tx, args ...interface{}) (interface{}, error) { 183 id := args[0].(string) 184 reason := args[1].(int) 185 186 var userRec cadbuser.Record 187 err := tx.Get(&userRec, tx.Rebind(getUser), id) 188 if err != nil { 189 return nil, cadbutil.GetError(err, "User") 190 } 191 192 _, err = tx.Exec(tx.Rebind(deleteUser), id) 193 if err != nil { 194 return nil, caerrors.NewHTTPErr(500, caerrors.ErrDBDeleteUser, "Error deleting identity '%s': %s", id, err) 195 } 196 197 record := &db.CertRecord{ 198 ID: id, 199 } 200 record.Reason = reason 201 202 _, err = tx.NamedExec(tx.Rebind(updateRevokeSQL), record) 203 if err != nil { 204 return nil, caerrors.NewHTTPErr(500, caerrors.ErrDBDeleteUser, "Error encountered while revoking certificates for identity '%s' that is being deleted: %s", id, err) 205 } 206 207 return &userRec, nil 208 } 209 210 // UpdateUser updates user in database 211 func (d *Accessor) UpdateUser(user *cadbuser.Info, updatePass bool) error { 212 if user == nil { 213 return errors.New("User is not defined") 214 } 215 216 log.Debugf("DB: Update identity %s", user.Name) 217 err := d.checkDB() 218 if err != nil { 219 return err 220 } 221 222 attributes, err := json.Marshal(user.Attributes) 223 if err != nil { 224 return errors.Wrap(err, "Failed to marshal user attributes") 225 } 226 227 // Hash the password before storing it 228 pwd := []byte(user.Pass) 229 if updatePass { 230 pwd, err = bcrypt.GenerateFromPassword(pwd, bcrypt.DefaultCost) 231 if err != nil { 232 return errors.Wrap(err, "Failed to hash password") 233 } 234 } 235 236 // Store the updated user entry 237 res, err := d.db.NamedExec("UpdateUser", updateUser, cadbuser.Record{ 238 Name: user.Name, 239 Pass: pwd, 240 Type: user.Type, 241 Affiliation: user.Affiliation, 242 Attributes: string(attributes), 243 State: user.State, 244 MaxEnrollments: user.MaxEnrollments, 245 Level: user.Level, 246 IncorrectPasswordAttempts: user.IncorrectPasswordAttempts, 247 }) 248 249 if err != nil { 250 return errors.Wrap(err, "Failed to update identity record") 251 } 252 253 numRowsAffected, err := res.RowsAffected() 254 255 if numRowsAffected == 0 { 256 return errors.New("No identity records were updated") 257 } 258 259 if numRowsAffected != 1 { 260 return errors.Errorf("Expected one identity record to be updated, but %d records were updated", numRowsAffected) 261 } 262 263 return err 264 265 } 266 267 // GetUser gets user from database 268 func (d *Accessor) GetUser(id string, attrs []string) (user.User, error) { 269 log.Debugf("DB: Getting identity %s", id) 270 271 err := d.checkDB() 272 if err != nil { 273 return nil, err 274 } 275 276 var userRec cadbuser.Record 277 err = d.db.Get("GetUser", &userRec, d.db.Rebind(getUser), id) 278 if err != nil { 279 return nil, cadbutil.GetError(err, "User") 280 } 281 282 return cadbuser.New(&userRec, d.db), nil 283 } 284 285 // InsertAffiliation inserts affiliation into database 286 func (d *Accessor) InsertAffiliation(name string, prekey string, level int) error { 287 log.Debugf("DB: Add affiliation %s", name) 288 err := d.checkDB() 289 if err != nil { 290 return err 291 } 292 dbType := d.db.DriverName() 293 // InnoDB store engine for MySQL does not allow more than 767 bytes 294 // in a 'UNIQUE' column. To work around this, the UNIQUE constraint was removed 295 // from the 'name' column in the affiliations table for MySQL to allow for up to 1024 296 // characters to be stored. In doing this, a check is needed on MySQL to check 297 // if the affiliation exists before adding it to prevent duplicate entries. 298 if dbType == "mysql" { 299 aff, _ := d.GetAffiliation(name) 300 if aff != nil { 301 log.Debugf("Affiliation '%s' already exists", name) 302 return nil 303 } 304 } 305 _, err = d.db.Exec("InsertAffiliation", d.db.Rebind(insertAffiliation), name, prekey, level) 306 if err != nil { 307 if (!strings.Contains(err.Error(), "UNIQUE constraint failed") && dbType == "sqlite3") || (!strings.Contains(err.Error(), "duplicate key value") && dbType == "postgres") { 308 return err 309 } 310 log.Debugf("Affiliation '%s' already exists", name) 311 return nil 312 } 313 log.Debugf("Affiliation '%s' added", name) 314 315 return nil 316 } 317 318 // DeleteAffiliation deletes affiliation from database. Using the force option with identity removal allowed 319 // this will also delete the identities associated with removed affiliations, and also delete the certificates 320 // for the identities removed 321 func (d *Accessor) DeleteAffiliation(name string, force, identityRemoval, isRegistrar bool) (*user.DbTxResult, error) { 322 log.Debugf("DB: Delete affiliation %s", name) 323 324 _, err := d.GetAffiliation(name) 325 if err != nil { 326 return nil, err 327 } 328 329 result, err := d.doTransaction(d.deleteAffiliationTx, name, force, identityRemoval, isRegistrar) 330 if err != nil { 331 return nil, err 332 } 333 334 deletedInfo := result.(*user.DbTxResult) 335 336 return deletedInfo, nil 337 } 338 339 func (d *Accessor) deleteAffiliationTx(tx *sqlx.Tx, args ...interface{}) (interface{}, error) { 340 var err error 341 342 name := args[0].(string) 343 force := args[1].(bool) 344 identityRemoval := args[2].(bool) 345 isRegistar := args[3].(bool) 346 347 subAffName := name + ".%" 348 query := "SELECT * FROM users WHERE (affiliation = ? OR affiliation LIKE ?)" 349 ids := []cadbuser.Record{} 350 err = tx.Select(&ids, tx.Rebind(query), name, subAffName) 351 if err != nil { 352 return nil, caerrors.NewHTTPErr(500, caerrors.ErrRemoveAffDB, "Failed to select users with sub-affiliation of '%s': %s", name, err) 353 } 354 355 idNames := []string{} 356 for _, id := range ids { 357 idNames = append(idNames, id.Name) 358 } 359 idNamesStr := strings.Join(idNames, ",") 360 361 // First check that all settings are correct 362 if len(ids) > 0 { 363 if !isRegistar { 364 return nil, caerrors.NewAuthorizationErr(caerrors.ErrUpdateConfigRemoveAff, "Removing affiliation affects identities, but caller is not a registrar") 365 } 366 if !identityRemoval { 367 return nil, caerrors.NewAuthorizationErr(caerrors.ErrUpdateConfigRemoveAff, "Identity removal is not allowed on server") 368 } 369 if !force { 370 // If force option is not specified, only delete affiliation if there are no identities that have that affiliation 371 return nil, caerrors.NewAuthorizationErr(caerrors.ErrUpdateConfigRemoveAff, "Cannot delete affiliation '%s'. The affiliation has the following identities associated: %s. Need to use 'force' to remove identities and affiliation", name, idNamesStr) 372 } 373 } 374 375 allAffs := []db.AffiliationRecord{} 376 err = tx.Select(&allAffs, tx.Rebind(getAllAffiliationsQuery), name, subAffName) 377 if err != nil { 378 return nil, cadbutil.GetError(err, "Affiliation") 379 } 380 381 affNames := []string{} 382 for _, aff := range allAffs { 383 affNames = append(affNames, aff.Name) 384 } 385 affNamesStr := strings.Join(affNames, ",") 386 387 if len(allAffs) > 1 { 388 if !force { 389 // If force option is not specified, only delete affiliation if there are no sub-affiliations 390 return nil, caerrors.NewAuthorizationErr(caerrors.ErrUpdateConfigRemoveAff, "Cannot delete affiliation '%s'. The affiliation has the following sub-affiliations: %s. Need to use 'force' to remove affiliation and sub-affiliations", name, affNamesStr) 391 } 392 } 393 394 // Now proceed with deletion 395 396 // delete any associated identities and certificates 397 if len(ids) > 0 { 398 log.Debugf("IDs '%s' to be removed based on affiliation '%s' removal", idNamesStr, name) 399 400 // Delete all the identities in one database request 401 query := "DELETE FROM users WHERE (id IN (?))" 402 inQuery, args, err := sqlx.In(query, idNames) 403 if err != nil { 404 return nil, caerrors.NewHTTPErr(500, caerrors.ErrRemoveAffDB, "Failed to construct query '%s': %s", query, err) 405 } 406 _, err = tx.Exec(tx.Rebind(inQuery), args...) 407 if err != nil { 408 return nil, caerrors.NewHTTPErr(500, caerrors.ErrRemoveAffDB, "Failed to execute query '%s' for multiple identity removal: %s", query, err) 409 } 410 411 // Revoke all the certificates associated with the removed identities above with reason of "affiliationchange" (3) 412 query = "UPDATE certificates SET status='revoked', revoked_at=CURRENT_TIMESTAMP, reason = ? WHERE (id IN (?) AND status != 'revoked')" 413 inQuery, args, err = sqlx.In(query, ocsp.AffiliationChanged, idNames) 414 if err != nil { 415 return nil, caerrors.NewHTTPErr(500, caerrors.ErrRemoveAffDB, "Failed to construct query '%s': %s", query, err) 416 } 417 _, err = tx.Exec(tx.Rebind(inQuery), args...) 418 if err != nil { 419 return nil, caerrors.NewHTTPErr(500, caerrors.ErrRemoveAffDB, "Failed to execute query '%s' for multiple certificate removal: %s", query, err) 420 } 421 } 422 423 log.Debugf("All affiliations to be removed: %s", allAffs) 424 425 // Delete the requested affiliation and it's subaffiliations 426 _, err = tx.Exec(tx.Rebind(deleteAffAndSubAff), name, subAffName) 427 if err != nil { 428 return nil, caerrors.NewHTTPErr(500, caerrors.ErrRemoveAffDB, "Failed to delete affiliation '%s': %s", name, err) 429 } 430 431 // Return the identities and affiliations that were removed 432 result := d.getResult(ids, allAffs) 433 434 return result, nil 435 } 436 437 // GetAffiliation gets affiliation from database 438 func (d *Accessor) GetAffiliation(name string) (spi.Affiliation, error) { 439 log.Debugf("DB: Get affiliation %s", name) 440 err := d.checkDB() 441 if err != nil { 442 return nil, err 443 } 444 445 var affiliationRecord db.AffiliationRecord 446 447 err = d.db.Get("GetAffiliation", &affiliationRecord, d.db.Rebind(getAffiliationQuery), name) 448 if err != nil { 449 return nil, cadbutil.GetError(err, "affiliation") 450 } 451 452 affiliation := spi.NewAffiliation(affiliationRecord.Name, affiliationRecord.Prekey, affiliationRecord.Level) 453 454 return affiliation, nil 455 } 456 457 // GetAffiliationTree returns the requested affiliation and affiliations below 458 func (d *Accessor) GetAffiliationTree(name string) (*user.DbTxResult, error) { 459 log.Debugf("DB: Get affiliation tree for '%s'", name) 460 461 if name != "" { 462 _, err := d.GetAffiliation(name) 463 if err != nil { 464 return nil, err 465 } 466 } 467 468 result, err := d.doTransaction(d.getAffiliationTreeTx, name) 469 if err != nil { 470 return nil, caerrors.NewHTTPErr(409, caerrors.ErrGettingAffiliation, "Failed to complete database transaction: %s", err) 471 } 472 473 getResult := result.(*user.DbTxResult) 474 475 return getResult, nil 476 } 477 478 // GetAffiliation gets affiliation from database 479 func (d *Accessor) getAffiliationTreeTx(tx *sqlx.Tx, args ...interface{}) (interface{}, error) { 480 name := args[0].(string) 481 482 log.Debugf("DB: Get affiliation tree for %s", name) 483 err := d.checkDB() 484 if err != nil { 485 return nil, err 486 } 487 488 // Getting affiliations 489 allAffs := []db.AffiliationRecord{} 490 if name == "" { // Requesting all affiliations 491 err = tx.Select(&allAffs, tx.Rebind("SELECT * FROM affiliations")) 492 if err != nil { 493 return nil, caerrors.NewHTTPErr(500, caerrors.ErrGettingAffiliation, "Failed to get affiliation tree for '%s': %s", name, err) 494 } 495 } else { 496 err = tx.Select(&allAffs, tx.Rebind("Select * FROM affiliations where (name LIKE ?) OR (name = ?)"), name+".%", name) 497 if err != nil { 498 return nil, caerrors.NewHTTPErr(500, caerrors.ErrGettingAffiliation, "Failed to get affiliation tree for '%s': %s", name, err) 499 } 500 } 501 502 ids := []cadbuser.Record{} // TODO: Return identities associated with these affiliations 503 result := d.getResult(ids, allAffs) 504 return result, nil 505 } 506 507 // GetUserLessThanLevel returns all identities that are less than the level specified 508 // Otherwise, returns no users if requested level is zero 509 func (d *Accessor) GetUserLessThanLevel(level int) ([]user.User, error) { 510 if level == 0 { 511 return []user.User{}, nil 512 } 513 514 rows, err := d.db.Queryx("GetUserLessThanLevel", d.db.Rebind("SELECT * FROM users WHERE (level < ?) OR (level IS NULL)"), level) 515 if err != nil { 516 return nil, errors.Wrap(err, "Failed to get identities that need to be updated") 517 } 518 519 allUsers := []user.User{} 520 521 for rows.Next() { 522 var user cadbuser.Record 523 rows.StructScan(&user) 524 cadbuser := cadbuser.New(&user, d.db) 525 allUsers = append(allUsers, cadbuser) 526 } 527 528 return allUsers, nil 529 } 530 531 // GetAllAffiliations gets the requested affiliation and any sub affiliations from the database 532 func (d *Accessor) GetAllAffiliations(name string) (*sqlx.Rows, error) { 533 log.Debugf("DB: Get affiliation %s", name) 534 err := d.checkDB() 535 if err != nil { 536 return nil, err 537 } 538 539 if name == "" { // Requesting all affiliations 540 rows, err := d.db.Queryx("GetAllAffiliations", d.db.Rebind("SELECT * FROM affiliations")) 541 if err != nil { 542 return nil, err 543 } 544 return rows, nil 545 } 546 547 rows, err := d.db.Queryx("GetAllAffiliations", d.db.Rebind(getAllAffiliationsQuery), name, name+".%") 548 if err != nil { 549 return nil, err 550 } 551 552 return rows, nil 553 } 554 555 // GetFilteredUsers returns all identities that fall under the affiliation and types 556 func (d *Accessor) GetFilteredUsers(affiliation, types string) (*sqlx.Rows, error) { 557 log.Debugf("DB: Get all identities per affiliation '%s' and types '%s'", affiliation, types) 558 err := d.checkDB() 559 if err != nil { 560 return nil, err 561 } 562 563 typesArray := strings.Split(types, ",") 564 for i := range typesArray { 565 typesArray[i] = strings.TrimSpace(typesArray[i]) 566 } 567 568 // If root affiliation, allowed to get back users of all affiliations 569 if affiliation == "" { 570 if util.ListContains(types, "*") { // If type is '*', allowed to get back of all types 571 query := "SELECT * FROM users" 572 rows, err := d.db.Queryx("GetFilteredUsers", d.db.Rebind(query)) 573 if err != nil { 574 return nil, errors.Wrapf(err, "Failed to execute query '%s' for affiliation '%s' and types '%s'", query, affiliation, types) 575 } 576 return rows, nil 577 } 578 579 query := "SELECT * FROM users WHERE (type IN (?))" 580 query, args, err := sqlx.In(query, typesArray) 581 if err != nil { 582 return nil, errors.Wrapf(err, "Failed to construct query '%s' for affiliation '%s' and types '%s'", query, affiliation, types) 583 } 584 rows, err := d.db.Queryx("GetFilteredUsers", d.db.Rebind(query), args...) 585 if err != nil { 586 return nil, errors.Wrapf(err, "Failed to execute query '%s' for affiliation '%s' and types '%s'", query, affiliation, types) 587 } 588 return rows, nil 589 } 590 591 subAffiliation := affiliation + ".%" 592 if util.ListContains(types, "*") { // If type is '*', allowed to get back of all types for requested affiliation 593 query := "SELECT * FROM users WHERE ((affiliation = ?) OR (affiliation LIKE ?))" 594 rows, err := d.db.Queryx("GetFilteredUsers", d.db.Rebind(query), affiliation, subAffiliation) 595 if err != nil { 596 return nil, errors.Wrapf(err, "Failed to execute query '%s' for affiliation '%s' and types '%s'", query, affiliation, types) 597 } 598 return rows, nil 599 } 600 601 query := "SELECT * FROM users WHERE ((affiliation = ?) OR (affiliation LIKE ?)) AND (type IN (?))" 602 inQuery, args, err := sqlx.In(query, affiliation, subAffiliation, typesArray) 603 if err != nil { 604 return nil, errors.Wrapf(err, "Failed to construct query '%s' for affiliation '%s' and types '%s'", query, affiliation, types) 605 } 606 rows, err := d.db.Queryx("GetFilteredUsers", d.db.Rebind(inQuery), args...) 607 if err != nil { 608 return nil, errors.Wrapf(err, "Failed to execute query '%s' for affiliation '%s' and types '%s'", query, affiliation, types) 609 } 610 611 return rows, nil 612 613 } 614 615 // ModifyAffiliation renames the affiliation and updates all identities to use the new affiliation depending on 616 // the value of the "force" parameter 617 func (d *Accessor) ModifyAffiliation(oldAffiliation, newAffiliation string, force, isRegistrar bool) (*user.DbTxResult, error) { 618 log.Debugf("DB: Modify affiliation from '%s' to '%s'", oldAffiliation, newAffiliation) 619 err := d.checkDB() 620 if err != nil { 621 return nil, err 622 } 623 624 // Check to see if the affiliation being modifies exists in the affiliation table 625 _, err = d.GetAffiliation(oldAffiliation) 626 if err != nil { 627 return nil, err 628 } 629 630 // Check to see if the new affiliation being requested exists in the affiliation table 631 _, err = d.GetAffiliation(newAffiliation) 632 if err == nil { 633 return nil, errors.WithMessagef(err, "Affiliation '%s' already exists", newAffiliation) 634 } 635 636 result, err := d.doTransaction(d.modifyAffiliationTx, oldAffiliation, newAffiliation, force, isRegistrar) 637 if err != nil { 638 return nil, err 639 } 640 641 modifiedInfo := result.(*user.DbTxResult) 642 643 return modifiedInfo, nil 644 } 645 646 func (d *Accessor) modifyAffiliationTx(tx *sqlx.Tx, args ...interface{}) (interface{}, error) { 647 oldAffiliation := args[0].(string) 648 newAffiliation := args[1].(string) 649 force := args[2].(bool) 650 isRegistar := args[3].(bool) 651 652 // Get the affiliation records including all sub affiliations 653 var allOldAffiliations []db.AffiliationRecord 654 err := tx.Select(&allOldAffiliations, tx.Rebind(getAllAffiliationsQuery), oldAffiliation, oldAffiliation+".%") 655 if err != nil { 656 return nil, err 657 } 658 659 log.Debugf("Affiliations to be modified %+v", allOldAffiliations) 660 661 // Iterate through all the affiliations found and update to use new affiliation path 662 idsUpdated := []string{} 663 for _, affiliation := range allOldAffiliations { 664 var idsWithOldAff []cadbuser.Record 665 oldPath := affiliation.Name 666 oldParentPath := affiliation.Prekey 667 newPath := strings.Replace(oldPath, oldAffiliation, newAffiliation, 1) 668 newParentPath := strings.Replace(oldParentPath, oldAffiliation, newAffiliation, 1) 669 log.Debugf("oldPath: %s, newPath: %s, oldParentPath: %s, newParentPath: %s", oldPath, newPath, oldParentPath, newParentPath) 670 671 // Select all users that are using the old affiliation 672 err = tx.Select(&idsWithOldAff, tx.Rebind(getIDsWithAffiliation), oldPath) 673 if err != nil { 674 return nil, err 675 } 676 if len(idsWithOldAff) > 0 { 677 if !isRegistar { 678 return nil, caerrors.NewAuthorizationErr(caerrors.ErrMissingRegAttr, "Modifying affiliation affects identities, but caller is not a registrar") 679 } 680 // Get the list of names of the identities that need to be updated to use new affiliation 681 ids := []string{} 682 for _, id := range idsWithOldAff { 683 ids = append(ids, id.Name) 684 } 685 686 if force { 687 log.Debugf("Identities %s to be updated to use new affiliation of '%s' from '%s'", ids, newPath, oldPath) 688 689 query := "Update users SET affiliation = ? WHERE (id IN (?))" 690 inQuery, args, err := sqlx.In(query, newPath, ids) 691 if err != nil { 692 return nil, errors.Wrapf(err, "Failed to construct query '%s'", query) 693 } 694 _, err = tx.Exec(tx.Rebind(inQuery), args...) 695 if err != nil { 696 return nil, errors.Wrapf(err, "Failed to execute query '%s' for multiple certificate removal", query) 697 } 698 699 // If user's affiliation is being updated, need to also update 'hf.Affiliation' attribute of user 700 for _, userRec := range idsWithOldAff { 701 user := cadbuser.New(&userRec, d.db) 702 currentAttrs, _ := user.GetAttributes(nil) // Get all current user attributes 703 userAff := cadbuser.GetAffiliation(user) // Get the current affiliation 704 newAff := strings.Replace(userAff, oldAffiliation, newAffiliation, 1) // Replace old affiliation with new affiliation 705 userAttrs := cadbuser.GetNewAttributes(currentAttrs, []api.Attribute{ // Generate the new set of attributes for user 706 api.Attribute{ 707 Name: attr.Affiliation, 708 Value: newAff, 709 }, 710 }) 711 712 attrBytes, err := json.Marshal(userAttrs) 713 if err != nil { 714 return nil, err 715 } 716 717 // Update attributes 718 query := "UPDATE users SET attributes = ? where (id = ?)" 719 id := user.GetName() 720 res, err := tx.Exec(tx.Rebind(query), string(attrBytes), id) 721 if err != nil { 722 return nil, err 723 } 724 725 numRowsAffected, err := res.RowsAffected() 726 if err != nil { 727 return nil, errors.Wrap(err, "Failed to get number of rows affected") 728 } 729 730 if numRowsAffected == 0 { 731 return nil, errors.Errorf("No rows were affected when updating the state of identity %s", id) 732 } 733 734 if numRowsAffected != 1 { 735 return nil, errors.Errorf("%d rows were affected when updating the state of identity %s", numRowsAffected, id) 736 } 737 } 738 } else { 739 // If force option is not specified, can only modify affiliation if there are no identities that have that affiliation 740 idNamesStr := strings.Join(ids, ",") 741 return nil, caerrors.NewHTTPErr(400, caerrors.ErrUpdateConfigModifyAff, "The request to modify affiliation '%s' has the following identities associated: %s. Need to use 'force' to remove identities and affiliation", oldAffiliation, idNamesStr) 742 } 743 744 idsUpdated = append(idsUpdated, ids...) 745 } 746 747 // Update the affiliation record in the database to use new affiliation path 748 res := tx.MustExec(tx.Rebind(updateAffiliation), newPath, newParentPath, oldPath) 749 numRowsAffected, err := res.RowsAffected() 750 if err != nil { 751 return nil, errors.Errorf("Failed to get number of rows affected") 752 } 753 if numRowsAffected == 0 { 754 return nil, errors.Errorf("Failed to update any affiliation records for '%s'", oldPath) 755 } 756 } 757 758 // Generate the result set that has all identities with their new affiliation and all renamed affiliations 759 var idsWithNewAff []cadbuser.Record 760 if len(idsUpdated) > 0 { 761 query := "Select * FROM users WHERE (id IN (?))" 762 inQuery, args, err := sqlx.In(query, idsUpdated) 763 if err != nil { 764 return nil, errors.Wrapf(err, "Failed to construct query '%s'", query) 765 } 766 err = tx.Select(&idsWithNewAff, tx.Rebind(inQuery), args...) 767 if err != nil { 768 return nil, errors.Wrapf(err, "Failed to execute query '%s' for getting users with new affiliation", query) 769 } 770 } 771 772 allNewAffs := []db.AffiliationRecord{} 773 err = tx.Select(&allNewAffs, tx.Rebind("Select * FROM affiliations where (name LIKE ?) OR (name = ?)"), newAffiliation+".%", newAffiliation) 774 if err != nil { 775 return nil, caerrors.NewHTTPErr(500, caerrors.ErrGettingAffiliation, "Failed to get affiliation tree for '%s': %s", newAffiliation, err) 776 } 777 778 // Return the identities and affiliations that were modified 779 result := d.getResult(idsWithNewAff, allNewAffs) 780 781 return result, nil 782 } 783 784 func (d *Accessor) doTransaction(doit func(tx *sqlx.Tx, args ...interface{}) (interface{}, error), args ...interface{}) (interface{}, error) { 785 err := d.checkDB() 786 if err != nil { 787 return nil, err 788 } 789 tx := d.db.MustBegin() 790 result, err := doit(tx, args...) 791 if err != nil { 792 err2 := tx.Rollback() 793 if err2 != nil { 794 log.Errorf("Error encounted while rolling back transaction: %s", err2) 795 return nil, err 796 } 797 return nil, err 798 } 799 800 err = tx.Commit() 801 if err != nil { 802 return nil, errors.Wrap(err, "Error encountered while committing transaction") 803 } 804 805 return result, nil 806 } 807 808 // Returns the identities and affiliations that were modified 809 func (d *Accessor) getResult(ids []cadbuser.Record, affs []db.AffiliationRecord) *user.DbTxResult { 810 // Collect all the identities that were modified 811 identities := []user.User{} 812 for _, id := range ids { 813 identities = append(identities, cadbuser.New(&id, d.db)) 814 } 815 816 // Collect the name of all affiliations that were modified 817 affiliations := []spi.Affiliation{} 818 for _, aff := range affs { 819 newAff := spi.NewAffiliation(aff.Name, aff.Prekey, aff.Level) 820 affiliations = append(affiliations, newAff) 821 } 822 823 return &user.DbTxResult{ 824 Affiliations: affiliations, 825 Identities: identities, 826 } 827 }