MySQL用户与权限管理(二)
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string:
1 row in set (0.00 sec)
--说明，本文中描述的mysql提示符为user@hostname[(dbname)]，不同的帐户，不同的主机登录会显示不同。
--其次，不同的提示符下所代表的用户身份及权限。
--查看当前的连接用户
root@localhost[(none)]> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
--查看当前帐户的权限
root@localhost[(none)]> show grants; --该账户用于最高权限，带有WITH GRANT OPTION
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
suse11b:~ # mysql -ufred -p
Enter password:
fred@localhost[(none)]> show grants;
+------------------------------------------+
| Grants for fred@localhost |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'fred'@'localhost' |
+------------------------------------------+
--下面使用root账户给fred赋予权限all privileges
root@localhost[(none)]> grant all privileges on *.* to 'fred'@'localhost';
Query OK, 0 rows affected (0.01 sec)
root@localhost[(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
fred@localhost[(none)]> show grants;
+---------------------------------------------------+
| Grants for fred@localhost |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'fred'@'localhost' |
+---------------------------------------------------+
fred@localhost[(none)]> use tempdb
fred@localhost[tempdb]> create table tb_isam(id int,value varchar(20)) engine=myisam;
Query OK, 0 rows affected (0.10 sec)
fred@localhost[tempdb]> insert into tb_isam values (1,'jack'),(2,'robin');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
fred@localhost[tempdb]> commit;
--下面的授权收到了错误提示，不能授权
fred@localhost[tempdb]> grant select on tempdb.* to 'frank'@'%';
ERROR 1044 (42000): Access denied for user 'fred'@'localhost' to database 'tempdb'
--下面从root session来给之前创建的frank授权
--授予frank在数据库tempdb上所有对象的select权限
root@localhost[(none)]> grant select on tempdb.* to 'frank'@'%';
Query OK, 0 rows affected (0.00 sec)
--更新cache中的权限
root@localhost[(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
--从另外的主机使用frank账户登录
suse11a:~ # mysql -ufrank -p -h172.16.6.89
Enter password:
--此时frank，此时已经可以访问了tempdb上的表tb_isam
frank@172.16.6.89[(none)]> select * from tempdb.tb_is
| 评论 |
|
|