Mysql 涉及用户权限的语句有GRANT,REVOKE
通过Mysql自带的help文档可以查看相应的语法:
GRANT
GRANT priv_type,...... ON [obj_type] priv_level TO user_specification [WITH with_option ...]
priv_type对应的有:
ALL | ALTER | ALTER ROUTINE | CREATE | CREATE ROUTINE | CREATE TEMPORARY TABLES | CREATE USER | CREATE VIEW | DELETE | DROP | EXECUTE | FILE | INDEX | INSERT | LOCK TABLES | PROCESS | REFERENCES | RELOAD | REPLICATION CLIENT | REPLICATION SLAVE | SELECT | SHOW DATABASES | SHOW VIEW | SHUTDOWN | SUPER | UPDATE | USAGE权限意义
ALL [PRIVILEGES]设置除GRANT OPTION之外的所有简单权限
ALTER允许使用ALTER TABLE
ALTER ROUTINE更改或取消已存储的子程序
CREATE允许使用CREATE TABLE
CREATE ROUTINE创建已存储的子程序
CREATE TEMPORARY TABLES允许使用CREATE TEMPORARY TABLE
CREATE USER允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW允许使用CREATE VIEW
DELETE允许使用DELETE
DROP允许使用DROP TABLE
EXECUTE允许用户运行已存储的子程序
FILE允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX允许使用CREATE INDEX和DROP INDEX
INSERT允许使用INSERT
LOCK TABLES允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS允许使用SHOW FULL PROCESSLIST
REFERENCES未被实施
RELOAD允许使用FLUSH
REPLICATION CLIENT允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE用于复制型从属服务器(从主服务器中读取二进制日志事件)
SELECT允许使用SELECT
SHOW DATABASESSHOW DATABASES显示所有数据库
SHOW VIEW允许使用SHOW CREATE VIEW
SHUTDOWN允许使用mysqladmin shutdown
SUPER允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句,mysqladmin debug命令;允许您连接(一次),即使已达到max_connections。
UPDATE允许使用UPDATE
USAGE“无权限”的同义词
GRANT OPTION
object_type对应的有:
TABLE | FUNCTION | PROCEDURE
priv_level对应的有:
* | *.* | db_name.* | db_name.tbl_name | tbl_name | db_name.routine_name
user_specification对应内容为:
[ IDENTIFIED BY [PASSWORD] 'password' | IDENTIFIED WITH auth_plugin [AS 'auth_string'] ]
ssl_option对应的有:
SSL | X509 | CIPHER 'cipher' | ISSUER 'issuer' | SUBJECT 'subject'
with_option对应内容为:
GRANT OPTION | MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count
举例:mysql> create database testdb;
mysql> grant all privileges on testdb.* to test@'localhost' identified by 'testdb';
mysql> create user test@'localhost' identified by 'testdb';
通过上述方式,用户被创建在mysql.user表中。mysql> select host,user,authentication_string from mysql.user where user='test';
对于当前用户授权情况可以通过以下语句查询mysql> show grants;
mysql> show grants for test@'localhost';
mysql> show grants for current_user;
mysql> show grants for current_user();
REVOKE
REOVKE priv_type,...... ON [obj_type] priv_level FROM user_specification
举例:
mysql> revoke all on testdb.* from test@‘127.0.0.1’
删除user表中定义的用户mysql> drop user 'dbd'@'127.0.0.1';