MariaDB简单的用户管理

MySQL存储引擎
全表查看

mysql >SHOW ENGINES;

单表查看

mysql >SHOW TABLES STATUS IN TABLEBNAME;

用户账户管理:

MariaDB [(none)]> HELP CONTENTS

MariaDB [(none)]> HELP Account Management
You asked for help about help category: "Account Management"
For more information, type 'help <item>', where <item> is one of the following

topics:
CREATE USER 创建
DROP USER 删除
RENAME USER 修改账户名称
SET PASSWORD

权限管理:
GRANT
REVOKE
授权

MariaDB [(none)]> CREATE USER linuxea@'192.168.%.%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.03 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 

登陆查看

[root@mysql-master ~]# mysql -ulinuxea -h192.168.0.99 -ppassword
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.44-MariaDB-log Source distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> SHOW TABLES FROM information_schema;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| CLIENT_STATISTICS                     |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |

查看自己的权限

MariaDB [(none)]> SHOW GRANTS FOR linuxea@'192.168.%.%';
+------------------------------------------------------------------------------------------------------------------+
| Grants for linuxea@192.168.%.%                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'linuxea'@'192.168.%.%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 

修改授权过的用户信息:修改完成后不会立即退出,重新登陆则需要新的信息验证

MariaDB [(none)]> RENAME USER 'linuxea'@'192.168.%.%' to 'linux'@'192.168.%.%';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> 

如下所示:

[root@mysql-master ~]# mysql -ulinuxea -h192.168.0.99 -ppassword
ERROR 1045 (28000): Access denied for user 'linuxea'@'192.168.0.99' (using password: YES)
[root@mysql-master ~]# mysql -ulinux -h192.168.0.99 -ppassword
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.44-MariaDB-log Source distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

MySQL权限类型:
库,表,字段,管理

查看线程:

MariaDB [(none)]> SHOW PROCESSLIST;
+----+-------+--------------------+------+---------+------+-------+------------------+----------+
| Id | User  | Host               | db   | Command | Time | State | Info             | Progress |
+----+-------+--------------------+------+---------+------+-------+------------------+----------+
|  6 | linux | 192.168.0.99:54201 | NULL | Query   |    0 | NULL  | SHOW PROCESSLIST |    0.000 |
+----+-------+--------------------+------+---------+------+-------+------------------+----------+
1 row in set (0.00 sec)

MariaDB [(none)]> 

施加锁

MariaDB [(none)]> use zabbix
MariaDB [zabbix]> LOCK TABLES history_log READ;
Query OK, 0 rows affected (0.00 sec)
MariaDB [zabbix]> SHOW PROCESSLIST;
+--------+-------+--------------------+--------+---------+-------+-------+------------------+----------+
| Id     | User  | Host               | db     | Command | Time  | State | Info             | Progress |
+--------+-------+--------------------+--------+---------+-------+-------+------------------+----------+
|  83773 | zuser | 10.215.60.55:56991 | zabbix | Sleep   |    22 |       | NULL             |    0.000 |
|  83774 | zuser | 10.215.60.55:56992 | zabbix | Sleep   |     1 |       | NULL             |    0.000 |
|  83775 | zuser | 10.215.60.55:56993 | zabbix | Sleep   |     4 |       | NULL             |    0.000 |
|  83776 | zuser | 10.215.60.55:56994 | zabbix | Sleep   |    27 |       | NULL             |    0.000 |
|  83777 | zuser | 10.215.60.55:56995 | zabbix | Sleep   |    12 |       | NULL             |    0.000 |
|  83778 | zuser | 10.215.60.55:56996 | zabbix | Sleep   |     2 |       | NULL             |    0.000 |
|  83779 | zuser | 10.215.60.55:56997 | zabbix | Sleep   |     1 |       | NULL             |    0.000 |
|  83781 | zuser | 10.215.60.55:56999 | zabbix | Sleep   |     7 |       | NULL             |    0.000 |
|  83787 | zuser | 10.215.60.55:57005 | zabbix | Sleep   |    40 |       | NULL             |    0.000 |
|  83788 | zuser | 10.215.60.55:57006 | zabbix | Sleep   |    19 |       | NULL             |    0.000 |
|  83843 | zuser | 10.215.60.55:57076 | zabbix | Sleep   |    33 |       | NULL             |    0.000 |
| 122824 | zuser | 10.215.60.55:58775 | zabbix | Sleep   | 27664 |       | NULL             |    0.000 |
| 149732 | zuser | 10.215.60.55:57764 | zabbix | Sleep   |     4 |       | NULL             |    0.000 |
| 157414 | root  | localhost          | zabbix | Query   |     0 | NULL  | SHOW PROCESSLIST |    0.000 |
+--------+-------+--------------------+--------+---------+-------+-------+------------------+----------+
14 rows in set (0.00 sec)

MariaDB [zabbix]> 

管理类权限

     create temporary tables 临时表
     create user
     file 允许用户读或者写某些文件
     lock tables 添加显式锁
     process:查看用户的线程
     reload:相当于执行flush和reset
     replication client 查询有哪些复制客户端
     replication slave 赋予用户复制权限
     show databases
     shutdown
     super
 数据库访问权限
     alter 
     alter routine 存储历程
     create
     create routine 存储过程,存储函数
     create view
     delete
     drop
     execute
     grant option 将自己的权限复制给别的用户
     index 索引
     show view

 数据操作类权限(表级别):
    select
    insert
    update
    delete

字段级别:
   select(col1,....)
   update(col1,....)
   insert(col1,....)
所有权限:
    ALL.ALL

//授权linux用户对linux表有创建修改任何的权限:
MariaDB [(none)]> GRANT CREATE ON linuxea.* To 'linux'@'192.168.%.%';
Query OK, 0 rows affected (0.00 sec)

使用linux登陆

[root@mysql-master ~]# mysql -ulinux -h192.168.0.99 -ppassword

查看权限

MariaDB [test]> SHOW GRANTS FOR linux@'192.168.%.%';
+----------------------------------------------------------------------------------------------------------------+
| Grants for linux@192.168.%.%                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'linux'@'192.168.%.%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT CREATE ON `linuxea`.* TO 'linux'@'192.168.%.%'                                                           |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

创建linux表

MariaDB [test]> CREATE DATABASE linuxea;
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linuxea            |
| test               |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [test]> 

创建表,查看需要查看权限(上面授权REATE表),否则不能查看等其他命令的权限使用,如下:

MariaDB [test]> use linuxea;
Database changed
MariaDB [linuxea]> CREATE TABLE t1 (ID TINYINT UNSIGNED);
Query OK, 0 rows affected (0.00 sec)

MariaDB [linuxea]> DESC t1;
ERROR 1142 (42000): SELECT command denied to user 'linux'@'192.168.0.99' for table 't1'
MariaDB [linuxea]> 

如果需要查看,或者删除其他权限,则需要授权,如下:
授权删除

MariaDB [(none)]> GRANT DROP ON linuxea.* To 'linux'@'192.168.%.%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 

在查看,则DROP删除权限会附加到CREATE

MariaDB [linuxea]> SHOW GRANTS FOR linux@'192.168.%.%';
+----------------------------------------------------------------------------------------------------------------+
| Grants for linux@192.168.%.%                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'linux'@'192.168.%.%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT CREATE, DROP ON `linuxea`.* TO 'linux'@'192.168.%.%'                                                     |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [linuxea]> 

重新登陆,验证DROP权限

[root@mysql-master ~]# mysql -ulinux -h192.168.0.99 -ppassword
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.44-MariaDB-log Source distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> DROP DATABASE linuxea;
Query OK, 1 row affected (0.05 sec)

MariaDB [(none)]> 

///查看,插入,修改,删除,创建
如果需要对表进行查看,插入,修改,删除,创建,则需要添加如下:

    MariaDB [(none)]> GRANT SELECT,INSERT,UPDATE,DELETE ON linuxea.* To 'linux'@'192.168.%.%';
    Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 

在查看

   [root@mysql-master ~]# mysql -ulinux -h192.168.0.99 -ppassword
    MariaDB [(none)]> SHOW GRANTS FOR linux@'192.168.%.%';
    +----------------------------------------------------------------------------------------------------------------+
    | Grants for linux@192.168.%.%                                                                                   |
    +----------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'linux'@'192.168.%.%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `linuxea`.* TO 'linux'@'192.168.%.%'                     |
    +----------------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    MariaDB [(none)]> 

这里的授权权限是无法授权给其他人。

*收回权限
收回单个权限

MariaDB [(none)]> REVOKE CREATE ON linuxea.* FROM 'linux'@'192.168.%.%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 

查看

[root@mysql-master ~]# mysql -ulinux -h192.168.0.99 -ppassword
MariaDB [(none)]> SHOW GRANTS FOR linux@'192.168.%.%';
+----------------------------------------------------------------------------------------------------------------+
| Grants for linux@192.168.%.%                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'linux'@'192.168.%.%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON `linuxea`.* TO 'linux'@'192.168.%.%'                             |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> 

收回所有权限

MariaDB [(none)]> REVOKE ALL ON linuxea.* FROM 'linux'@'192.168.%.%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 

查看

[root@mysql-master ~]# mysql -ulinux -h192.168.0.99 -ppassword
MariaDB [(none)]> SHOW GRANTS FOR linux@'192.168.%.%';
+----------------------------------------------------------------------------------------------------------------+
| Grants for linux@192.168.%.%                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'linux'@'192.168.%.%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 

MySQL表

MariaDB [(none)]> use mysql;
Database changed
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

MariaDB [mysql]> 

用户授权相关的表
DB:库级别权限
HOST:主机级别(已经废弃)
tables_priv:表级别权限
colomns_priv:列级别权限
procs_priv:存储过程和存储函数相关的权限
proxies_priv:代理用户

0 分享

您可以选择一种方式赞助本站

支付宝扫码赞助

支付宝扫码赞助

日期: 2016-01-12分类: Mariadb

标签: mariadb

发表评论