인덱스를 사용하지 않고 중복 삽입 방지

인덱스를 사용하지 않고 중복 삽입 방지

이 있습니다.MariaDB 은 MariaDB입니다.users을 사용하다

id INT PRIMARY KEY AUTOINCREMENT,
email_hash INT, -- indexed
encrypted_email TEXT,
other_stuff JSON

개인 정보 보호를 위해 실제 이메일을 데이터베이스에 저장할 수 없습니다.

이메일에 사용되는 암호화는 1:1이 아닙니다. 즉, 하나의 이메일을 여러 암호화된 표현으로 암호화할 수 있습니다. 하면 단순히 .encrypted_email중복되지 않기 때문에 컬럼을 지정합니다.

데이터베이스에 이미 데이터가 있으므로 암호화 방법 또는 해시 방법을 변경할 수 없습니다.

email_hash중복 검사 속도를 높이기 위한 짧은 해시이므로 열에도 고유한 인덱스를 포함할 수 없습니다.모든 사생활 보장이 무효가 되기 때문에 너무 독특할 수 없습니다.

같은 이메일의 2개의 엔트리가 데이터베이스에 표시되지 않도록 하려면 어떻게 해야 합니까?

또 다른 제한 사항:아마 못 쓸 것 같아LOCK TABLE매뉴얼에 따르면http://https://mariadb.com/kb/en/library/lock-tables/ 를 참조해 주세요.

LOCK TABLES갈레라Galera와 함께 사용하면 충돌이나 잠금이 발생할 수 있습니다.

LOCK TABLES 는 액티브한 트랜잭션이 있는 경우 암묵적으로 커밋합니다.하면, , 취득한 됩니다.LOCK TABLES.

(저는 Galera를 사용하고 있으며, 새로운 사용자를 삽입할 때 다른 삽입 및 업데이트가 수반되므로 트랜잭션이 필요합니다.)

---

백엔드 어플리케이션 서버(모놀리스)는 개인정보(이메일 메시지 송신, 로그인 확인 등)를 저장할 수 있기 때문에 어플리케이션에서 중복 체크를 합니다.

현재 다음과 같은 작업을 수행하고 있습니다(의사 코드).

perform "START TRANSACTION"
h := hash(new_user.email)
conflicts := perform "SELECT encrypted_email FROM users WHERE email_hash = ?", h
for conflict in conflicts :
    if decrypt(conflict) == new_user.email :
        perform "ROLLBACK"
        return DUPLICATE
e := encrypt(new_user.email)
s := new_user.other_stuff
perform "INSERT INTO users (email_hash, encrypted_email, other_stuff) VALUES (?,?,?)", h, e, s
perform some other inserts as part of the transaction
perform "COMMIT"
return OK

두 번의 시도가 시간 내에 분리되면 효과가 있습니다.그러나 두 개의 스레드가 동시에 동일한 사용자를 추가하려고 하면 두 트랜잭션이 동시에 실행되어 선택을 수행하고 중복되는 중복 항목이 없는지 확인한 후 두 개의 스레드가 모두 사용자를 추가합니다.어떻게 하면 예방할 수 있을까요, 아니면 적어도 즉시 원만하게 회복할 수 있을까요?

---

레이스는 다음과 같이 심플하게 표시됩니다.

• 두 개의 스레드가 트랜잭션을 시작합니다.

• 두 스레드 모두 선택을 수행하며 두 경우 모두 0 행을 반환합니다.

• 두 스레드 모두 중복이 없다고 가정합니다.

• 두 스레드 모두 사용자를 추가합니다.

• 두 스레드 모두 트랜잭션을 커밋합니다.

• 이제 동일한 전자 메일을 사용하는 사용자가 두 명 있습니다.

»FOR UPDATESELECT.

갈레라를에 갈레라 뒤에 해야 합니다.COMMIT(본격적으로)

코드를 강제로 연속적으로 실행할 수 없는 한 의사 코드의 레이스 상태는 위험합니다.즉, 한 번에 하나의 요청만 전자 메일을 삽입할 수 있습니다.의사 코드에 표시되는 코드 블록 전체가 크리티컬 섹션에 있어야 합니다.

LOCK TABLES를 사용할 수 없는 경우 MariaDB의 GET_LOCK() 함수를 사용해 보십시오.그게 갈레라와 호환되는지 잘 모르겠네요, 당신이 연구해야 할 부분이죠.

그게 불가능하면, 그 코드 블록을 강제로 실행할 다른 방법을 찾아야 할 거야.프로그래밍 언어나 애플리케이션 배포 아키텍처를 설명하지 않았습니다.Redis에 분산 잠금 서버 같은 걸 쓰시면 될 것 같아요

그러나 이를 달성하고 코드를 연속적으로 실행해도 앱에 병목현상이 발생할 수 있습니다.한 번에 하나의 스레드만 새 전자 메일을 삽입할 수 있으며 글로벌 잠금을 기다리는 대기열이 나타날 수 있습니다.

죄송합니다.그것은 이 시스템의 제약에 의한 결과입니다.그것은, 독자 키를 사용해 실장할 수 없기 때문입니다.그것이 적절한 방법입니다.

행운을 빌어요.

이건 코멘트 하기에는 너무 길다.

그럴수는 없어요.하나의 전자 메일에 여러 개의 값이 입력되는 필드가 하나 있습니다.중복된 값을 식별해도 소용이 없습니다.

여러 개의 전자 메일이 동일한 값을 갖는 다른 필드가 있습니다.그러면 중복에 잘못된 오류가 발생할 뿐입니다.

중복을 방지하려면 충돌을 크게 줄일 수 있는 보다 견고한 해시 메커니즘을 제안합니다.그렇지 않으면 PII 벽 뒤에서 검증을 수행해야 합니다.

또한 코멘트를 하기에는 너무 길다.

테이블에서 중복 항목을 방지하려면 고유한 색인을 사용해야 합니다. 그러면 MariaDB가 중복 항목을 탐지할 수 있습니다.

해시4 바이트)INT는하지 않고 수 체크섬 대신 암호화된 패스워드(예를 들어 AES-256-CTR 또는 기타 블록암호를 사용하여 암호화)를 테이블에 저장하고 키와 iv(초기화 벡터)를 클라이언트에 저장해야 합니다.각 암호화 값은 고유하며 보안 암호화 값과 키/iv는 서로 다른 위치에 저장됩니다.

/* Don't send plain password, e.g. by using MariaDB's aes_encryot function
   we encrypt it already on client*/
encrypted_unique_email= aes_256_ctr_encrypt(plain_pw);
encrypted_email=encrypt(user.email);
execute("INSERT INTO users VALUES (NULL, encrypted_unique_email, encrypted_email, other_stuff) ...

그러나 이 솔루션은 빈 테이블에서만 작동합니다.이는 기존 레코드를 복호화할 수 없기 때문입니다.

이 경우 제안서가 최적의 솔루션일 수 있지만 다음 방법으로 사용자 테이블을 잠글 필요가 있습니다.LOCK TABLE users WRITE로 잠금을 해제합니다.UNLOCK TABLES모순을 방지합니다.

전자 메일에서 복구 불가능한 일대일 투영을 유사한 출력에 저장하려면 다른 열을 추가하고 이 열을 사용해야 합니다.비대칭 암호화 알고리즘을 사용하여 공개 키와 개인 키 쌍을 생성한 후 개인 키를 파기하고 공용 키를 저장하여 전자 메일을 암호화합니다.비대칭 크라이토그래피가 작동하는 방식에서는 공격자가 전자 메일 암호화에 사용하는 공용 키를 손에 넣더라도 개인 키를 복구할 수 없습니다.

단, 이 접근법에는 소금에 절이지 않은 해시를 저장하는 것과 같은 취약성이 있습니다.즉, 공격자가 데이터베이스, 공개 키 및 알고리즘 전체를 입수하면 이미 알려진 전자 메일 사전을 사용하여 무차별 공격을 실행하고 암호화된 형식으로 일치하는 전자 메일을 성공적으로 검색하여 시스템의 계정을 일치시킬 수 있습니다.o 실제 이메일이 상황이 실제 보안 위험인지 아닌지는 귀하와 귀사의 ITSec 부서에 달려 있습니다. 하지만 그렇게 해서는 안 됩니다.decrypt기능을 사용할 수 있으므로 공격자가 이미 데이터베이스 및 시스템 내부에 액세스할 수 있는 경우 저장된 전자 메일을 해독할 수 있습니다.

한 걸음 더 나아가 암호화된 전자 메일을 사용자와 관계 없이 별도의 테이블에 저장할 수 있습니다.새 행이 삽입되는 경우users이 테이블에도 행이 삽입되어 있는지 확인합니다.고유 인덱스 및 트랜잭션과 결합하면 중복이 발생하지 않지만 변경 및 삭제 관리는 더욱 번거로워집니다.잠재적인 공격자는 말 그대로 알려진 이메일 중 일부가 시스템에 등록되어 있다는 것을 아는 것 외에는 아무것도 얻을 수 없습니다.

않은 경우에는 하면 됩니다.users테이블은 항상 DB보다 먼저 소프트웨어 계층에서 직렬화됩니다.하고 수정하지 않도록 합니다.users무슨 수를 써서라도

언급URL : https://stackoverflow.com/questions/58242378/preventing-insertion-of-duplicates-without-using-indices