CREATE TABLE mysql.user ( Host CHAR(60) NOT NULL, User CHAR(16) NOT NULL, Password CHAR(41) NOT NULL, Select_priv ENUM('N','Y') NOT NULL, Insert_priv ENUM('N','Y') NOT NULL, Update_priv ENUM('N','Y') NOT NULL, Delete_priv ENUM('N','Y') NOT NULL, Create_priv ENUM('N','Y') NOT NULL, Drop_priv ENUM('N','Y') NOT NULL, Grant_priv ENUM('N','Y') NOT NULL, References_priv ENUM('N','Y') NOT NULL, Index_priv ENUM('N','Y') NOT NULL, Alter_priv ENUM('N','Y') NOT NULL, Create_tmp_table_priv ENUM('N','Y') NOT NULL, Lock_tables_priv ENUM('N','Y') NOT NULL, Create_view_priv ENUM('N','Y') NOT NULL, Show_view_priv ENUM('N','Y') NOT NULL, Create_routine_priv ENUM('N','Y') NOT NULL, Alter_routine_priv ENUM('N','Y') NOT NULL, Execute_priv ENUM('N','Y') NOT NULL, Event_priv ENUM('N','Y') NOT NULL, Trigger_priv ENUM('N','Y') NOT NULL, Create_user_priv ENUM('N','Y') NOT NULL, ssl_type ENUM('','ANY','X509','SPECIFIED') NOT NULL, ssl_cipher BLOB NOT NULL, x509_issuer BLOB NOT NULL, x509_subject BLOB NOT NULL, max_updates INT(11) UNSIGNED NOT NULL DEFAULT 0, max_connections INT(11) UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (Host,User) ) CHARACTER SET utf8 COLLATE utf8_bin COMMENT '用户及其访问权限表';
上述代码是创建授权表(mysql.user)的SQL语句,其中包含了诸多字段,列举如下:
- Host:主机名
- User:用户名
- Password:用户密码
- Select_priv:是否具有SELECT权限
- Insert_priv:是否具有INSERT权限
- Update_priv:是否具有UPDATE权限
- Delete_priv:是否具有DELETE权限
- Create_priv:是否具有CREATE权限
- Drop_priv:是否具有DROP权限
- Grant_priv:是否具有GRANT权限
- …
通过对授权表的操作,可以为用户设置相应的权限,从而实现对数据库的安全控制。需要注意的是,用户与权限是一一对应的,一个用户可以拥有多个权限,但是一个权限只能被授予给一个用户。