概述
本文将比较全面详细的介绍mysql数据库上面关于user的各种设置.
一: 查看当前所有用户list以及状态
mysql> select user from mysql.user;
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
查看具体某个用户的权限:
mysql> select * from user where user = 'root';
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| ubuntu | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| 127.0.0.1 | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| ::1 | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
4 rows in set (0.00 sec)
二: 添加新用户
方法一: 利用insert into user方式添加
step1 添加
mysql>use mysql;
mysql> insert into user(host,user,password) values("localhost","test1",password("1234"));
Query OK, 1 row affected, 3 warnings (0.00 sec)
或者
mysql> insert into **mysql.**user(host,user,password) values("localhost","test1",password("1234"));
Query OK, 1 row affected, 3 warnings (0.00 sec)
注意:此处的”localhost”,是指该用户只能在本地登录,不能在另外一台机器上远程登录。如果想远程登录的话,将”localhost”改为”%”,表示在任何一台电脑上都可以登录。也可以指定某台机器可以远程登录,例如某个网段192.168.1.%.
step2 刷新系统权限表
mysql> FLUSH PRIVILEGES;
注意:在注意需要执行 FLUSH PRIVILEGES 语句。 这个命令执行后会重新载入授权表。否则无法使用新创建的用户来连接mysql服务器,除非你重启mysql服务器。
step3 查看
mysql> select host,user,password from user where user = 'test1';
+-----------+-------+-------------------------------------------+
| host | user | password |
+-----------+-------+-------------------------------------------+
| localhost | test1 | *A4B6157319038724E3560894F7F932C8886EBFCF |
+-----------+-------+-------------------------------------------+
1 row in set (0.00 sec)
注意: 在添加用户时,请注意使用MySQL提供的 PASSWORD() 函数来对密码进行加密。 你可以在以上实例看到用户密码加密后为:那一个长串.
step4 登录
mysql> exit
Bye
# mysql -utest1 -p1234
Welcome to the MySQL monitor. Commands end with ; or g.
注意: 此时该用户仅能在本机(localhost)上使用密码登录, 无法远程登录. 并且很多其他的权限也是N的状态, 需要后面用grant来添加权限.
remote login failed
$ mysql -h myhostip -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'test1'@'romote server' (using password: YES)
mysql> select * from user where user = 'test1';
+-----------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+-----------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| localhost | test1 | *A4B6157319038724E3560894F7F932C8886EBFCF | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | | NULL |
+-----------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
1 row in set (0.00 sec)
方法二: 利用grant的方法添加
mysql> grant all privileges on *.* to test2@'%' identified by '1234';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where user = 'test2';
+------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| % | test2 | *A4B6157319038724E3560894F7F932C8886EBFCF | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | NULL |
+------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
1 row in set (0.00 sec)
注意:
对所有库的所有表赋予了全部权限,不需要使用flush privilege刷新系统权限表,改用户立即生效。
但是我后面发现虽然赋予了权限, 可是我在其他的ubuntu上面依然无法连接本机的数据库.
$ mysql -h myhostip -u test2 -p
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on 'myhostip' (111)
后面发现了问题所在, 我需要my.cnf文件进行修改. Grant remote access privileges
https://stackoverflow.com/questions/1420839/cant-connect-to-mysql-server-error-111
< #skip-external-locking
---
> skip-external-locking
47,48c47
< #bind-address = 127.0.0.1
< #bind-address = 0.0.0.0
---
> bind-address = 127.0.0.1
修改过后重启mysql, 更新配置. 现在就可以了.
方法三: 利用create user来创建
mysql> create user 'test3'@'localhost' identified by '1234';
Query OK, 0 rows affected (0.00 sec)
这种方法创建出来的用户的权限和方法一中的是一样的.
grant all privileges on *.* to test3@'localhost' identified by '1234';
后面发现如果你想更改用户权限,用grant的时候要注意test3@’localhost’ identified by ‘1234’;
的部分钥匙和原来的一样的,否则你会发现你创建了两个名叫test3的不同权限的用户.
三: 用户的权限添加与删除
usefull reference:
https://dev.mysql.com/doc/refman/5.5/en/grant.html 官方文件关于各个权限的列表.
所有权限列表 Permissible Privileges for GRANT and REVOKE
所有权限列表 Permissible Privileges for GRANT and REVOKE
Privilege | Privilege |
---|---|
ALL [PRIVILEGES] | Grant all privileges at specified access level except GRANT OPTION and PROXY. |
ALTER | Enable use of ALTER TABLE. Levels: Global, database, table. |
ALTER ROUTINE | Enable stored routines to be altered or dropped. Levels: Global, database, procedure. |
CREATE | Enable database and table creation. Levels: Global, database, table. |
CREATE ROUTINE | Enable stored routine creation. Levels: Global, database. |
CREATE TABLESPACE | Enable tablespaces and log file groups to be created, altered, or dropped. Level: Global. |
CREATE TEMPORARY TABLES | Enable use of CREATE TEMPORARY TABLE. Levels: Global, database. |
CREATE USER | Enable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES. Level: Global. |
CREATE VIEW | Enable views to be created or altered. Levels: Global, database, table. |
DELETE | Enable use of DELETE. Level: Global, database, table. |
DROP | Enable databases, tables, and views to be dropped. Levels: Global, database, table. |
EVENT | Enable use of events for the Event Scheduler. Levels: Global, database. |
EXECUTE | Enable the user to execute stored routines. Levels: Global, database, table. |
FILE | Enable the user to cause the server to read or write files. Level: Global. |
GRANT OPTION | Enable privileges to be granted to or removed from other accounts. Levels: Global, database, table, procedure, proxy. |
INDEX | Enable indexes to be created or dropped. Levels: Global, database, table. |
INSERT | Enable use of INSERT. Levels: Global, database, table, column. |
LOCK TABLES | Enable use of LOCK TABLES on tables for which you have the SELECT privilege. Levels: Global, database. |
PROCESS | Enable the user to see all processes with SHOW PROCESSLIST. Level: Global. |
PROXY | Enable user proxying. Level: From user to user. |
REFERENCES | Enable foreign key creation. Levels: Global, database, table, column. |
RELOAD | Enable use of FLUSH operations. Level: Global. |
REPLICATION CLIENT | Enable the user to ask where master or slave servers are. Level: Global. |
REPLICATION SLAVE | Enable replication slaves to read binary log events from the master. Level: Global. |
SELECT | Enable use of SELECT. Levels: Global, database, table, column. |
SHOW DATABASES | Enable SHOW DATABASES to show all databases. Level: Global. |
SHOW VIEW | Enable use of SHOW CREATE VIEW. Levels: Global, database, table. |
SHUTDOWN | Enable use of mysqladmin shutdown. Level: Global. |
SUPER | Enable use of other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command. Level: Global. |
TRIGGER | Enable trigger operations. Levels: Global, database, table. |
UPDATE | Enable use of UPDATE. Levels: Global, database, table, column. |
USAGE | Synonym for “no privileges” |
关于用户权限的添加有很多种, 如下我就按照官方文件的分类来解释:
Global Privileges
grant all on *.* to 'someuser'@'somehost';
授权test用户拥有所有数据库的所有权限.
grant select,delete,update,create,drop on *.* to 'someuser'@'somehost';
test用户对所有数据库都有select,delete,update,create,drop 权限。
Database Privileges
GRANT ALL ON mydb.* TO 'someuser'@'somehost';
授权test用户拥有testDB数据库的所有权限
GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';
授权test用户拥有testDB数据库的某些权限
下面的以此类推:
Table Privileges
GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';
Column Privileges
GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';
Stored Routine Privileges
GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';
Proxy User Privileges
GRANT PROXY ON 'localuser'@'localhost' TO 'externaluser'@'somehost';
至于取消用户的权限的话, grant和 revoke的格式是相同的, 只需要把原来句式中的grant换成revoke就好了.
四: 用户密码重置
CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password;
SET old_passwords = 0;
SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass');
五: 用户重命名
rename user 'test'@'localhost' to 'testnew'@'hostname';
六: 删除用户名
方法一:
drop user 'test4'@'localhost';
mysql> select * from user where user = 'test4';
Empty set (0.00 sec)
方法二:
delete from user where user='test4' and host='localhost' ;
最后
以上就是缓慢戒指为你收集整理的mysql用户管理(account management)创建/删除/权限配置的全部内容,希望文章能够帮你解决mysql用户管理(account management)创建/删除/权限配置所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错,欢迎将靠谱客网站推荐给程序员好友。
发表评论 取消回复