Welcome to 深ちゃんのホームページ
Prev	MySQL のインストールと基本的な設定	Next

MySQL のデータベースの初期状態

MySQL のデータベースの初期状態を見てみましょう。

# mysqlshow
+-----------+
| Databases |
+-----------+
| mysql     |
| test      |
+-----------+

# mysqldump mysql (←この行の # は root の prompt です)
# MySQL dump 7.1
#
# Host: localhost    Database: mysql
#--------------------------------------------------------
# Server version	3.22.32-log

#
# Table structure for table 'columns_priv'
#
CREATE TABLE columns_priv (
  Host char(60) DEFAULT '' NOT NULL,
  Db char(60) DEFAULT '' NOT NULL,
  User char(16) DEFAULT '' NOT NULL,
  Table_name char(60) DEFAULT '' NOT NULL,
  Column_name char(60) DEFAULT '' NOT NULL,
  Timestamp timestamp(14),
  Column_priv set('Select','Insert','Update','References') DEFAULT '' NOT NULL,
  PRIMARY KEY (Host,Db,User,Table_name,Column_name)
);

#
# Dumping data for table 'columns_priv'
#


#
# Table structure for table 'db'
#
CREATE TABLE db (
  Host char(60) DEFAULT '' NOT NULL,
  Db char(32) DEFAULT '' NOT NULL,
  User char(16) DEFAULT '' NOT NULL,
  Select_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Update_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Delete_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Create_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Drop_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Grant_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  References_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Index_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Alter_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  PRIMARY KEY (Host,Db,User),
  KEY User (User)
);

#
# Dumping data for table 'db'
#

INSERT INTO db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y');
INSERT INTO db VALUES ('%','test\\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y');

#
# Table structure for table 'func'
#
CREATE TABLE func (
  name char(64) DEFAULT '' NOT NULL,
  ret tinyint(1) DEFAULT '0' NOT NULL,
  dl char(128) DEFAULT '' NOT NULL,
  type enum('function','aggregate') DEFAULT 'function' NOT NULL,
  PRIMARY KEY (name)
);

#
# Dumping data for table 'func'
#


#
# Table structure for table 'host'
#
CREATE TABLE host (
  Host char(60) DEFAULT '' NOT NULL,
  Db char(32) DEFAULT '' NOT NULL,
  Select_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Update_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Delete_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Create_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Drop_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Grant_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  References_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Index_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Alter_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  PRIMARY KEY (Host,Db)
);

#
# Dumping data for table 'host'
#


#
# Table structure for table 'tables_priv'
#
CREATE TABLE tables_priv (
  Host char(60) DEFAULT '' NOT NULL,
  Db char(60) DEFAULT '' NOT NULL,
  User char(16) DEFAULT '' NOT NULL,
  Table_name char(60) DEFAULT '' NOT NULL,
  Grantor char(77) DEFAULT '' NOT NULL,
  Timestamp timestamp(14),
  Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter') DEFAULT '' NOT NULL,
  Column_priv set('Select','Insert','Update','References') DEFAULT '' NOT NULL,
  PRIMARY KEY (Host,Db,User,Table_name),
  KEY Grantor (Grantor)
);

#
# Dumping data for table 'tables_priv'
#


#
# Table structure for table 'user'
#
CREATE TABLE user (
  Host char(60) DEFAULT '' NOT NULL,
  User char(16) DEFAULT '' NOT NULL,
  Password char(16) DEFAULT '' NOT NULL,
  Select_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Update_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Delete_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Create_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Drop_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Reload_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Shutdown_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Process_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  File_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Grant_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  References_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Index_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  Alter_priv enum('N','Y') DEFAULT 'N' NOT NULL,
  PRIMARY KEY (Host,User)
);

#
# Dumping data for table 'user'
#

INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO user VALUES ('elena.fml.org','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO user VALUES ('localhost','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N');
INSERT INTO user VALUES ('elena.fml.org','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N');

この状態で beth.fml.org からアクセスすると弾かれます。デフォルト設定はそういうものです。

% mysql -h elena.fml.org
ERROR 1130: Host 'beth.fml.org' is not allowed to connect to this MySQL server

ちなみに、このホストの上でなら root さんは何でもできることを確認してみます。

% mysqlaccess localhost root mysql
mysqlaccess Version 2.04-alpha2, 09 Feb 2000
By RUG-AIV, by Yves Carlier (Yves.Carlier@rug.ac.be)
Changes by Steve Harvey (sgh@vex.net)
This software comes with ABSOLUTELY NO WARRANTY.
+++USING FULL WHERE CLAUSE+++
+++USING FULL WHERE CLAUSE+++
+++USING FULL WHERE CLAUSE+++

Access-rights
for USER 'root', from HOST 'localhost', to DB 'mysql'
	+-----------------+---+	+-----------------+---+
	| Select_priv     | Y |	| Shutdown_priv   | Y |
	| Insert_priv     | Y |	| Process_priv    | Y |
	| Update_priv     | Y |	| File_priv       | Y |
	| Delete_priv     | Y |	| Grant_priv      | Y |
	| Create_priv     | Y |	| References_priv | Y |
	| Drop_priv       | Y |	| Index_priv      | Y |
	| Reload_priv     | Y |	| Alter_priv      | Y |
	+-----------------+---+	+-----------------+---+
BEWARE:	 Everybody can access your DB as user `root' from host `localhost'
      :	 WITHOUT supplying a password.
      :	 Be very careful about it!!

The following rules are used:
 db    : 'No matching rule'
 host  : 'Not processed: host-field is not empty in db-table.'
 user  : 'localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'

BUGs can be reported by email to Yves.Carlier@rug.ac.be

Prev	Home	Next
MySQL のインストールと基本的な設定	Up	MySQL でユーザ fukachan を作る