mylog:MySQL - livedoor Blog（ブログ）

MySQL

June 13, 2008

mysql - skip-show-database

MySQLでのSHOW DATABASESコマンド権限の管理。

バージョンの表示と、テスト用データベース'sample'の作成。

$ mysql -u root

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.0.51-log | 
+------------+
1 row in set (0.00 sec)

mysql> CREATE DATABASE sample;
Query OK, 1 row affected (0.00 sec)

fooユーザを作成し、SELECTのみ許可する。

$ mysql -u root

mysql> GRANT Select ON *.* TO foo@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM mysql.user WHERE `User` = 'foo'\G
*************************** 1. row ***************************
                 Host: localhost
                 User: foo
             Password: 
          Select_priv: Y
          Insert_priv: N
          Update_priv: N

          ...

         Show_db_priv: N
           Super_priv: N

          ...

1 row in set (0.00 sec)

fooユーザにShow_dbの権限は無いはずなのに、SHOW DATABASESコマンドが実行でき、全てのデータベースが見れてしまう。

$ mysql -u foo

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
| sample             | 
| test               | 
+--------------------+
4 rows in set (0.00 sec)

fooユーザを再作成し、'sample'データベースでのSELECTのみ許可する。

$ mysql -u root

mysql> DELETE FROM mysql.user WHERE `User` = 'foo';
Query OK, 1 row affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT Select ON sample.* TO foo@localhost;
Query OK, 0 rows affected (0.00 sec)

先程と同様fooユーザにShow_dbの権限は無く、SHOW DATABASESコマンドも実行できる。しかし、権限の無い'mysql'データベースは見えなくなる。('information_schema'や'test'が見えるのはよくわからない。)

$ mysql -u foo

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| sample             | 
| test               | 
+--------------------+
3 rows in set (0.00 sec)

次に、my.cnfに'skip-show-database'を追加し、mysqlを再起動する。

$ vi /path/to/my.cnf

[mysqld]

...

skip-show-database

...

fooユーザでSHOW DATABASESコマンドを実行すると、拒否するようになったことが分かる。'skip-show-database'がONで、Show_db権限がない場合に、SHOW DATABASESコマンドは拒否される模様。

$ mysql -u foo

mysql> SHOW DATABASES;
ERROR 1227 (42000): Access denied; you need the SHOW DATABASES privilege for this operation

確認のために、Show_db権限をfooユーザに与える。

$ mysql -u root

mysql> UPDATE mysql.user SET Show_db_priv = 'Y' WHERE `User` = 'foo';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

SHOW DATABASESコマンドを実行すると、全データベースが表示される。

$ mysql -u foo

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
| sample             | 
| test               | 
+--------------------+
4 rows in set (0.00 sec)

一般ユーザはSHOW DATABASESコマンドは必要ない(自分が使用するデータベースだけ知っていればいい)ので、'skip-show-database'は常にONにしておいたほうが良さそう。で、Show_db権限は与えないこと。

SHOW PROCESSLISTやSHOW VARIABLESとかも禁止にするにはどうするんだろう。小一時間情報を探してみたけれど、わからなかった。できないのかもしれない。

タグ: MySQL

driveool at 18:47 │コメント(0) │トラックバック(0)

June 09, 2008

Use sequence in each database

MySQLはカラムをAUTO_INCREMENTで定義すれば、新規連番を振ってくれる。振られた連番はLAST_INSERT_ID()関数で取得できる。

CREATE TABLE foo
(
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(24) NOT NULL
)

-------------------------------------------

mysql_query("INSERT INTO foo(name) VALUES('test')", $conn);
$res = mysql_query("SELECT LAST_INSERT_ID() AS id", $conn);
$row = mysql_fetch_assoc($res);
var_dump($row["id"]);

PostgreSQLはカラムをSERIAL、またはBIGSERIALで定義すれば、新規連番を振ってくれる。振られた連番はLASTVAL()関数で取得できる。

CREATE TABLE foo
(
  id SERIAL NOT NULL,
  name VARCHAR(24) NOT NULL
)

-------------------------------------------

pg_query($conn, "INSERT INTO foo(name) VALUES('test')");
$res = pg_query($conn, "SELECT LASTVAL() AS id");
$row = pg_fetch_assoc($res);
var_dump($row["id"]);

SQLiteはカラムを"INTEGER PRIMARY KEY"または"INTEGER NOT NULL PRIMARY KEY"で定義すれば、新規連番を振ってくれる。振られた連番はPDOのlastInsertId()メソッドで取得できる。

CREATE TABLE foo
(
  id INTEGER PRIMARY KEY,
  name VARCHAR(24) NOT NULL
)

-------------------------------------------

$stmt = $pdo->prepare("INSERT INTO foo(name) VALUES('test')");
$stmt->execute();
var_dump($pdo->lastInsertId());

Firebirdではシーケンス(旧ジェネレータ)を作成し、GEN_ID()関数にそのシーケンス名を渡し新規連番を取得する。その値でINSERTする。

CREATE TABLE FOO
(
  ID INTEGER NOT NULL PRIMARY KEY,
  NAME VARCHAR(24) NOT NULL
)

CREATE SEQUENCE FOO_ID_SEQ;

-------------------------------------------

$res = ibase_query($conn, 'SELECT GEN_ID(FOO_ID_SEQ, 1) AS ID FROM RDB$DATABASE');
$row = ibase_fetch_assoc($res);
$lastId = $row["ID"];
ibase_query($conn, "INSERT INTO FOO(ID, NAME) VALUES({$lastId}, 'test')");
var_dump($lastId);

Oracleではシーケンスオブジェクトを作成し、NEXTVALにより新規連番を取得する。その値でINSERTする。

CREATE TABLE FOO
(
  ID INTEGER NOT NULL PRIMARY KEY,
  NAME VARCHAR(24) NOT NULL
)

CREATE SEQUENCE FOO_ID_SEQ;

-------------------------------------------

$stmt = oci_parse($conn, "SELECT FOO_ID_SEQ.NEXTVAL AS ID FROM DUAL");
oci_execute($stmt, OCI_COMMIT_ON_SUCCESS);
$row = oci_fetch_assoc($stmt);
$lastId = $row["ID"];
$stmt = oci_parse($conn, "INSERT INTO FOO(ID, NAME) VALUES({$lastId}, 'test')");
oci_execute($stmt, OCI_COMMIT_ON_SUCCESS);
var_dump($lastId);

SQL ServerはカラムをIDENTIFYで定義すれば、新規連番を振ってくれる。振られた連番はSCOPE_IDENTITY()関数で取得できる。

CREATE TABLE foo
(
  id INTEGER IDENTITY(1, 1) NOT NULL,
  name VARCHAR(24) NOT NULL
)

-------------------------------------------

mssql_query("INSERT INTO foo(name) VALUES('test')", $conn);
$res = mssql_query("SELECT SCOPE_IDENTITY() AS id", $conn);
$row = mssql_fetch_assoc($res);
var_dump($row["id"]);

driveool at 02:26 │コメント(0) │トラックバック(0)

April 22, 2008

PDO_MYSQL::lastInsertId()

トランザクション内(begin～commit)でINSERTされたデータの連番を取得する場合、commitの前にlastInsertId()をコールする必要がある。そうしないと"0"が返される。

CREATE TABLE test
(
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(24) NOT NULL
)

$pdo = new PDO("mysql:host=localhost;dbname=testdb", "root", "");
$stmt = $pdo->prepare("INSERT INTO test(name) VALUES(:name)");
$stmt->bindValue(":name", "somename");
$pdo->beginTransaction();
$pdo->execute();
$pdo->lastInsertId();  // last id
$pdo->commit();

$pdo->lastInsertId();  // "0"

commitの後でも、LAST_INSERT_ID()関数なら取得できる。

$stmt = $pdo->prepare("SELECT LAST_INSERT_ID() AS id");
$stmt->execute();
$row = $stmt->fetch(PDO_::FETCH_ASSOC);
$row["id"]  // last id

タグ: PHP pdo MySQL

driveool at 00:34 │コメント(0) │トラックバック(0)

April 21, 2008

mysql error "Incorrect information"

mysqlで以下のエラーが発生し、再起動できない場合の対処。

ERROR 1033 (HY000): Incorrect information in file: ...

"innodb_log_file_size"の値を増やすとこうなる。
innodbのトランザクションログ(ib_logfile)をどこかに退避するか削除してしまう。

その後、再起動する。

小さくした場合は起動はできるがSELECTすると同様のエラーが発生する模様。
なので、同様の作業をすること。

タグ: MySQL database

driveool at 04:25 │コメント(0) │トラックバック(0)

February 29, 2008

mysql export as csv

セル内の改行コードと行区切りのための改行コードのアレがうまくいかず、とりあえずstr_replace()で対処することに。
改行コードはとりあえずwindowsに合わせ'\r\n'に。

SQLでエクスポート

SELECT * FROM tblname
  INTO OUTFILE '/path/to/output.csv'
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n';

なんかおかしいセル内改行コード置換

$contents = file_get_contents("/path/to/output.csv");
$replaced = str_replace("\\\r\n", "\r\n", $contents);