创建用户
create user 用户名 indentified '用户密码'；
赋予权限：
grand 权限 on 数据库名.表名称 to 用户名 indentified by '密码'
|
权限 |
意义 |
|
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 DATABASES |
SHOW 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 |
允许授予权限 |
shell> MySQL --user=root MySQL
shell> mysql --user=root mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON bankaccount.*
-> TO 'custom'@'localhost'
-> IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON expenses.*
-> TO 'custom'@'whitehouse.gov'
-> IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON customer.*
-> TO 'custom'@'server.domain'
-> IDENTIFIED BY 'obscure';
这3个账户可以用于：
· 第1个账户可以访问bankaccount数据库，但只能从本机访问。
· 第2个账户可以访问expenses数据库，但只能从主机whitehouse.gov访问。
· 第3个账户可以访问customer数据库，但只能从主机server.domain访问。
a.sql;
"1","zj","male","1"
"7","zj","male","1"
"6","zj","male","1"
"5","zj","male","1"
"4","zj","male","1"
"3","zj","male","1"
"2","zj","male","1"
mysql> desc stdV;
+---------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(32) | NO | | | |
| sex | enum('male','female') | NO | | male | |
| classId | tinyint(3) unsigned | NO | | 0 | |
+---------+-----------------------+------+-----+---------+----------------+
mysql> load data infile '/home/angelmylove/mysql/a.sql' into table stdV fields terminated by ',' enclosed by '"';
Query OK, 7 rows affected (0.11 sec)
Records: 7 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from stdV;
+----+------+------+---------+
| id | name | sex | classId |
+----+------+------+---------+
| 1 | zj | male | 1 |
| 2 | zj | male | 1 |
| 3 | zj | male | 1 |
| 4 | zj | male | 1 |
| 5 | zj | male | 1 |
| 6 | zj | male | 1 |
| 7 | zj | male | 1 |
+----+------+------+---------+
7 rows in set (0.00 sec)