주요 내용
코스: 컴퓨터 프로그래밍 > 단원 3
단원 4: SQL을 이용하여 데이터베이스 수정하기안전한 SQL 만들기
SQL은 아름답지만 위험하기도 합니다. 만약 SQL을 사용하여 수 백, 수 천, 심지어 수백만 명의 유저가 사용하는 응용프로그램의 데이터베이스에 접근한다면 매우 조심해야 할 필요가 있습니다. 실수로 모든 데이터를 손상하거나 지울 수도 있으니까요. 하지만 SQL을 더 안전하게 만들어주는 여러 가지 기술이 존재합니다.
나쁜 업데이트/삭제 방지
UPDATE를 실행하기 전에 올바른 행과 열을 업데이트 하는지 확인하기 위해 먼저 WHERE 구가 똑같은 상태에서 SELECT를 실행합니다.예를 들면 다음과 같은 코드를 실행하기 전에
UPDATE users SET deleted = true WHERE id = 1;다음을 실행 해볼 수 있습니다.
SELECT id, deleted FROM users WHERE id = 1;업데이트를 실행하려고 한다면 LIMIT 명령을 사용하여 실수로 너무 많은 행을 업데이트하는 것을 방지할 수 있습니다.
UPDATE users SET deleted = true WHERE id = 1 LIMIT 1;만약 삭제를 하려면 다음과 같이 할 수 있습니다.
DELETE users WHERE id = 1 LIMIT 1;트랜잭션(transactions) 사용
데이터베이스를 어떤식으로든 변경하는 SQL 명령어를 실행할 때 "트랜잭션"이라는 것을 합니다. 트랜잭션은 하나의 논리적인 일로 취급되는 명령의 순서적 집합입니다 (은행 송금 처럼). 데이터베이스의 세계에서 신뢰성을 위해 트랜잭션은 "ACID" 원칙에 따라야 합니다.
CREATE, UPDATE, INSERT, DELETE 같은 명령어를 실행할 때, 트랜잭션은 항상 자동으로 시작됩니다. 하지만 만약 원한다면 큰 트랜잭션 안에 여러 개의 명령들을 묶을 수도 있습니다. 예를 들어 만약에 다른 UPDATE 명령이 수행될 때만 어떤 UPDATE 명령이 통과하도록 하려면 두 명령을 같은 트랜잭션에 넣어야
합니다.이럴 경우, 명령어를 다음과 같이
BEGIN TRANSACTION과 COMMIT으로 감쌀 수 있습니다.BEGIN TRANSACTION;
UPDATE people SET husband = "Winston" WHERE user_id = 1;
UPDATE people SET wife = "Winnefer" WHERE user_id = 2;
COMMIT;만약 데이터베이스가 두
UPDATE를 어떠한 이유로 모두 실행하지 못했다면, 트랜잭션은 롤 백 되고 데이터베이스는 기존 상태로 돌아갑니다.또, 트랜잭션은 모든 명령어를 실행할 때, 일련의 데이터를 똑같은 방식으로 처리하고 싶을 때 사용합니다. 다시 말하자면, 트랜잭션을 실행할 때는 어떤 순서대로 명령어를 실행하는 중에 해당 데이터에 대해 다른 트랜잭션이 작업을 하지 못하게 만들고 싶을 때 씁니다. 실행하고 싶은 명령 시퀀스가 있는데 만약 다른 사람이 동시에 어떤 명령어를 실행했다고 생각해 보세요. 이때 데이터가 어중간하게 처리될 것 같은가요? 만약 그렇게 될 것 같다면 트랜잭션을 사용하세요.
한 가지 예를 들어봅시다. 다음과 같은 명령은 사용자가 얻은 배지를 표시하는 행을 생성하고, 그 뒤 새로 얻은 배지를 사용자의 최근 활동에 업데이트합니다.
INSERT INTO user_badges VALUES (1, "SQL Master", "4pm");
UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;동시에, 다른 사용자나 프로세스가 해당 사용자에게 두 번째 배지를 줄 수 있습니다.
INSERT INTO user_badges VALUES (1, "Great Listener", "4:05pm");
UPDATE user SET recent_activity = "Earned Great Listener badge" WHERE id = 1;해당 명령들은 다음과 같은 순서로 실행될 수 있습니다.
INSERT INTO user_badges VALUES (1, "SQL Master");
INSERT INTO user_badges VALUES (1, "Great Listener");
UPDATE user SET recent_activity = "Earned Great Listener badge" WHERE id = 1;
UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;가장 최근에 추가된 배지가 "훌륭한 청취자"임에도 불구하고 사용자의 최근 활동은 이제 "SQL 마스터 배지 획득"이 될 것입니다. 뭔가 엄청난 오류는 아니지만 그렇다고 분명 우리 예상대로 나오지도 않네요.
이렇게 하는 대신 같이 트랜잭션을 이용하여 다른 트랜잭션이 중간에 끼어들지 않도록 만들 수 있습니다.
BEGIN TRANSACTION;
INSERT INTO user_badges VALUES (1, "SQL Master");
UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;
COMMIT;백업 만들기
위의 팁들을 모두 따르는 것이 맞지만, 실수는 가끔이라도 발생하기 마련입니다. 그럴 때를 대비해서 대부분의 회사는 데이터베이스의 백업을 생성합니다. 데이터베이스의 크기와 여유공간에 따라 시간, 일, 주 단위로 생성합니다. 만약 나쁜 일이 발생하면, 이전 데이터베이스로부터 손상되거나 잃어버린 테이블의 데이터를 불러올 수 있습니다. 데이터가 약간 오래됐을 수도 있지만 아무것도 없는 것보다는 대게 나으니까요.
복제
백업과 비슷한 방법 중 하나는 복제입니다. 복제는 항상 여러 개의 데이터베이스 복사본을 다른 장소에 보관하는 것입니다. 만약 어떤 이유로 인하여 특정 데이터베이스가 사용이 불가능하다면 (번개가 건물에 칠 수도 있어요. 저한테도 일어난 일이에요!), 미리 복제해둔 또 다른 데이터베이스로 복구할 수도 있습니다. 만약 매우 중요한 데이터라면 항상 접근이 가능한 상태를 유지하기 위해 복제를 해야 합니다. 예를 들면, 만약 의사가 위급 상황에서 환자들의 알레르기를 치료할 방법을 결정하기 위해 그 목록을 가져오려고 한다면, 엔지니어가 데이터를 백업으로부터 다시 가져올 수 있을 때까지 기다릴 수 없습니다. 그 데이터가 당장 필요합니다.
하지만 데이터베이스를 복제하는 데는 훨씬 더 많은 노력이 들어갑니다. 쓰기 명령은 복제된 데이터베이스에서 모두 적용되기 때문에 종종 성능이 저하되기도 합니다. 따라서 회사는 복제의 이점이 기회비용을 초과하는지 결정하고, 각자의 환경에 맞는 최고의 방법을 강구해야 합니다.
권한 부여
많은 데이터베이스 시스템은 서버에 저장되고 여러 명의 사용자가 접근하기 때문에, 사용자와 각자의 권한이 내장되어 있습니다. 칸아카데미에서 사용자/권한에 대한 개념은 학습하지 않습니다. 왜냐하면, SQLite은 일반적으로 한 명의 사용자가 사용하는 시나리오를 가지고 있고, 따라서 내용이 저장된 드라이브에 직접 접근이 가능할 때만 쓰기가 가능합니다.
만약 공유 서버에서 데이터베이스를 사용해야 하는 경우가 생기면 사용자와 권한 속성을 처음에 확실히 설정하세요. 일반적으로 데이터베이스에 대한 완전한 접근 권한이 있는 사용자(백엔드 엔지니어 등)의 수는 최소화해야 합니다.
예를 들면, 다음과 같이 완전한 접근 권한을 특정 사용자에게 줄 수 있습니다.
GRANT FULL ON TABLE users TO super_admin;다음과 같이 또 다른 사용자에게 SELECT 권한만 줄 수 있습니다.
GRANT SELECT ON TABLE users TO analyzing_user;큰 회사에서는 대부분 사용자에게
SELECT 접근 권한조차도 주지 않습니다. 테이블에 이메일 주소나 사용자 이름 같은 개인정보가 있을 수도 있기 때문입니다. 많은 회사는 개인정보에 접근하는 걱정 없이 쿼리를 사용할 수 있는 데이터베이스의 익명 버전이 존재합니다.