mysql grant创建用户权限

时间：2009-08-18 来源：sss0213

Privilege	说明
ALL [PRIVILEGES]	所有权限，除去GRANT OPTION
ALTER	修改表
ALTER ROUTINE	修改或删除存储过程
CREATE	创建数据库和表
CREATE ROUTINE	创建存储过程
CREATE TEMPORARY TABLES	创建临时表
CREATE USER	创建、删除、重命名用户和激活用户所有权限
CREATE VIEW	创建或修改视图
DELETE	删除
DROP	删除数据库、表和视图
EVENT	使能事件Enable use of events for the Event Scheduler
EXECUTE	使能执行触发器
FILE	Enable the user to cause the server to read or write files
GRANT OPTION	Enable privileges to be granted to or removed from other accounts
INDEX	创建或删除索引
INSERT	插入
LOCK TABLES	在有select权限表上锁定表
PROCESS	用SHOW PROCESSLIST查看所有进程
REFERENCES	Not implemented
RELOAD	刷新操作Enable use of FLUSH operations
REPLICATION CLIENT	Enable the user to ask where master or slave servers are
REPLICATION SLAVE	Enable replication slaves to read binary log events from the master
SELECT	Enable use of SELECT
SHOW DATABASES	Enable SHOW DATABASES to show all databases
SHOW VIEW	Enable use of SHOW CREATE VIEW
SHUTDOWN	Enable use of mysqladmin shutdown
SUPER	Enable use of CHANGE MASTER TO, KILL, PURGE BINARY LOGS, and SET GLOBAL statements, the mysqladmin debug command; allows you to connect (once) even if max_connections is reached
TRIGGER	Enable triggers to be created or dropped
UPDATE	Enable use of UPDATE
USAGE	Synonym for “no privileges”

The EVENT and TRIGGER privileges were added in MySQL 5.1.6. A trigger is associated with a table, so to create or drop a trigger, you must have the TRIGGER privilege for the table, not the trigger. (Before MySQL 5.1.6, the SUPER privilege was required to create or drop triggers.)

创建全局用户：

GRANT ALL ON *.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';
GRANT ALL ON *.* TO 'someuser'@'somehost';

GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';

但是CREATE USER, FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN, and SUPER 这些权限只能全局地赋权限。

数据库级别的：

GRANT ALL ON mydb.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';

表级别的：

GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

列级别的：

GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';

存储过程：

有这ALTER ROUTINE, CREATE ROUTINE, EXECUTE, and GRANT OPTION 几种权限。

GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';

查看用户的权限：

show grants for 你的用户

比如：
show grants for root@'localhost';