素人がプログラミングを勉強していたブログ

プログラミング、セキュリティ、英語、Webなどのブログ since 2008

連絡先: すかいぷ:javascripter_  か javascripter あっと tsukkun.net skypeのほうがいいです

DBにおけるウェブシステムのアカウントの権限管理とセキュリティについて

下記にセキュリティに関する追記あり。読了後、そちらもチェックしてください

ユーザーと管理者アカウントがあるウェブサイトで、管理者がユーザーのパスワードをリセットできたりといった風に、アカウントによって権限が違う場合のセキュリティについての考察である。

/loginにアクセスし, emailとパスワードでuser/adminにアクセスできるとする。 (ログインページは1つであり、user/admin間のemailは重複してはいけない)

論理的構造を考えると account->user, account->admin といった階層構造になっているので、自然に設計すると accountテーブルにuser/admin情報をもたせて、

pragma foreign_keys=1;

CREATE TABLE account(
  id INTEGER PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  password TEXT NOT NULL,
  type INT NOT NULL,
  FOREIGN KEY(type) REFERENCES type(type)
);

CREATE TABLE type(
  type INTEGER NOT NULL PRIMARY KEY,
  type_name TEXT UNIQUE NOT NULL
);

INSERT INTO type VALUES
  (0, 'user'),
  (1, 'admin');

INSERT INTO account VALUES
  (0, 'user@example.com', 'pass1', 0),
  (1, 'admin@example.com', 'pass1', 1);

と書きたくなる(簡略のため生パスワードを使っているが、もちろん真似してはいけない)。これは、セキュリティの観点からは好ましくない。ログインページの実装を考えると、ログイン処理は

SELECT * FROM account WHERE email=$email AND password=$password;

になると思う。 $emailにSQL Injectionがあったとすると、$emailが

0 OR type=1 --

であった場合

SELECT * FROM account WHERE email=0 OR type=1 -- AND password=$password;

が実行され、管理者権限のアカウントが乗っ取られてしまう。 また、ログインに使用するemailアドレスを変更する機能を考えると

UPDATE account SET email=$email WHERE id=$id;

になるが、ここで、$emailにエスケープ漏れがありSQL Injectionがあるとする。$emailが

'a@example.com' type=1

であったとき、

UPDATE account SET email='a@example.com' type=1 WHERE id=$id

が実行されてしまい、ユーザーが管理者に昇格してしまう。これらは重大なセキュリティホールなので、SQL Injectionがあったとしても特権昇格ができないようなDB設計が好ましい。

具体的には、userとadminのテーブルを分けるのが好ましい。ただし、仕様上idとemailはuser/admin両方で一意であることを保証しなければならないので、完全に分けることは難しい。ここでは、id_tableを使った方法ともう一つの方法を考える(あまりSQLに詳しくないので、いい方法を知っている人がいたら教えてください)。

pragma foreign_keys=1;

CREATE TABLE id_email(
  id INTEGER NOT NULL,
  email TEXT NOT NULL UNIQUE,
  PRIMARY KEY(id, email)
);

CREATE TABLE user(
  id INTEGER NOT NULL UNIQUE PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  password TEXT NOT NULL,
  FOREIGN KEY(id, email) REFERENCES id_email(id, email)
);

CREATE TABLE admin(
  id INTEGER NOT NULL UNIQUE PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  password TEXT NOT NULL,
  FOREIGN KEY(id, email) REFERENCES id_email(id, email)
);

BEGIN;
INSERT OR ROLLBACK INTO id_email(0, 'user@example.com');
INSERT OR ROLLBACK INTO user(0, 'user@example.com', 'pass');
COMMIT;

emailから権限を判断する手段がないので、ログインはadmin, userの順に試行する。

上記ではidとemailのシステム内での一意性を保証するためにid_emailテーブルを定義したが、この方法もセキュリティ上よろしくない。

ユーザーがログインemailを変更するとき、userテーブルではなくuser/admin共通のid_email自体を参照しなければいけないので、

UPDATE id_email SET email=$new_email WHERE id=$id;

のようなコードを書くことになる。

この時、$new_emailでSQL Injectionが可能である場合、$new_emailが

'attacker@example.com' WHERE id=1 --

だとすると

UPDATE id_email SET email='attacker@example.com' WHERE id=1 --WHERE id=$id;

となり、管理者権限のアカウントのemailを攻撃者のものに書き換えられる可能性がある。書き換えが成功した場合、パスワードリセット機能によって管理者権限を奪われてしまう。

よって、id_emailを触るときには鬼のように気をつけるか、そもそもid_emailを使わないように定義するしかない。

id_emailを使わない方法についてだが、emailをuser/admin内で定義したい場合、テーブルにまたがった制約は外部キーや複合キーなどではうまく表現できないので、トリガーを使う(いい方法を知ってる人がいたら教えてください)。 また、システム内のidはaccountテーブルを使って一意性を保証しているが、アカウント作成時にidを取得する以外では使わないので特に問題はない。

pragma foreign_keys=1;

CREATE TABLE account(
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE user(
  id INTEGER NOT NULL,
  email TEXT UNIQUE NOT NULL,
  password TEXT NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(id) REFERENCES account(id)
);

CREATE TABLE admin(
  id INTEGER NOT NULL,
  email TEXT UNIQUE NOT NULL,
  password TEXT NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(id) REFERENCES account(id)
);

-- user内でemailがUNIQUEであることは保証されているのでadminのみをチェックする
CREATE TRIGGER insert_user_email AFTER
  INSERT ON user
  FOR EACH ROW WHEN (SELECT email FROM admin WHERE email=new.email) IS NOT NULL
  BEGIN
    SELECT RAISE(ROLLBACK, 'email must be unique');
  END;

CREATE TRIGGER update_user_email AFTER
  UPDATE OF email ON user
  FOR EACH ROW WHEN (SELECT email FROM admin WHERE email=new.email) IS NOT NULL
  BEGIN
    SELECT RAISE(ROLLBACK, 'email must be unique');
  END;

-- 同様に、adminにおいてはuser内に同じemailが存在しないかをチェックする
CREATE TRIGGER insert_admin_email AFTER
  INSERT ON admin
  FOR EACH ROW WHEN (SELECT email FROM user WHERE email=new.email) IS NOT NULL
  BEGIN
    SELECT RAISE(ROLLBACK, 'email must be unique');
  END;

CREATE TRIGGER update_admin_email AFTER
  UPDATE OF email ON admin
  FOR EACH ROW WHEN (SELECT email FROM user WHERE email=new.email) IS NOT NULL
  BEGIN
    SELECT RAISE(ROLLBACK, 'email must be unique');
  END;

BEGIN;
INSERT OR ROLLBACK INTO account VALUES(null);
INSERT OR ROLLBACK INTO user VALUES (last_insert_rowid(), 'user1@example.com', 'pass');
COMMIT;

下記では$adminなどの変数が正しいメールアドレスであると想定して、読みやすくなるよう$emailのまま使用しているが、 外部に公開するサイトでは決してやってはならない。必ず、SQLプレースホルダを使用し、(できれば)ペネトレーションテストも行うべきである。

'attacker@example.com' WHERE id=1; UPDATE admin SET email='attacker@example.com';--

ログインするには、最初はadminとしてログインを試行して

SELECT * FROM admin where email=$email AND password=$password;

をして、失敗したらuserとして

SELECT * FROM user where email=$email AND password=$password;

で再試行する。ユーザのemailの更新は

UPDATE user SET email=$new_email WHERE id=$id;

なので、たとえSQL Injectionができても管理者権限を奪うことはできない(ただし、テーブルのアクセス権限を正しく設定している場合)。パスワードの変更などについても同様である。このように、権限の異なるアカウントは特権昇格攻撃を未然に防ぐためテーブルごと完全に分けたほうがよい。

さらに言うと、そもそも1つのログイン画面で複数の権限のアカウントの管理をすること自体、実装が複雑になるのでセキュリティ上好ましくない。

一般ユーザのログインは /login にして、 管理者のログインは /admin/login にし、idはadmin_id, user_idなど関連性がないことが明らかになるように名前をつけadmin/user間のemailの重複は無視し、userとadminを完全に分けるのが、安全な設計だと思われる。

「ORMを使ってまともに書いていたらSQL Injectionは起きないので関係ないし、親クラスに相当するものがあるほうが自然だよ」という考え方もあるが、最近「権限の異なるアカウントのテーブルは必ず分けないといけない」と断言されたので、いい機会なので考えてみた次第である。断言口調で書いたものの、絶対そうでなければならないかと言われると、うーんという感じではある。

おまけで一応書いておくが、生パスワードをDBに保存してはいけない。

hashed_password = password + salt; // saltが後ろである必要はない
for (i = 0; i < 1000; i++) {
  hashed_password = hash(hashed_password)
}

このようなニュアンスのものを使うべきである。詳しくは「パスワード ソルト」で検索してほしい。

実はあまりSQLに詳しくないので、他にidとemailの一意性を保証する上手い方法があるような気がしてならないので、いい方法があったらコメントで教えてください。