mysql用户与授权

视图

create view 视图名 as 查询语句；

MariaDB [hellodb]> create view view_left as select name from students;

Query OK,  rows affected (0.00 sec)

MariaDB [hellodb]> create view view_students as select stuid,name from students;Query OK,  rows affected (0.00 sec)



MariaDB [hellodb]> select * from view_students;

+-------+---------------+

| stuid | name          |

+-------+---------------+

|      | Shi Zhongyu   |

|      | Shi Potian    |

|      | Xie Yanke     |

|      | Ding Dian     |

|      | Yu Yutong     |

|      | Shi Qing      |

|      | Xi Ren        |

|      | Lin Daiyu     |

|      | Ren Yingying  |

|     | Yue Lingshan  |

|     | Yuan Chengzhi |

|     | Wen Qingqing  |

|     | Tian Boguang  |

|     | Lu Wushuang   |

|     | Duan Yu       |

|     | Xu Zhu        |

|     | Lin Chong     |

|     | Hua Rong      |

|     | Xue Baochai   |

|     | Diao Chan     |

|     | Huang Yueying |

|     | Xiao Qiao     |

|     | Ma Chao       |

|     | Xu Xian       |

|     | Sun Dasheng   |

+-------+---------------+

 rows in set (0.00 sec)

用户管理

1、用户账号

用户的账号由用户名和HOST俩部分组成（'USERNAME'@'HOST'）

HOST的表示：

主机名
具体IP地址
网段/掩码

可以使用通配符表示，%和_；192.168.%即表示这个网段的所有主机

2、增加删除账号

主要：在数据库中修改了用户信息需要执行FLUSH PRIVILEGES;来刷新授权表使其生效

创建

create user '用户名'@'主机名' identified by '密码';

 MariaDB [mysql]> CREATE USER 'user1'@'192.168.%';

  MariaDB [mysql]> CREATE USER 'user2'@'192.168.%' IDENTIFIED BY 'your_password';

  MariaDB [mysql]> SELECT user,host,password FROM user;

  +-------+-----------+-------------------------------------------+

  | user  | host      | password                                  |

  +-------+-----------+-------------------------------------------+

  | root  | localhost | *4A54C3F37C03C7FBACE31591D6A8C546F93DF5C5 |

  | root  | centos7   |                                           |

  | root  | 127.0.0.1 |                                           |

  | root  | ::       |                                           |

  |       | localhost |                                           |

  |       | centos7   |                                           |

  | user1 | 192.168.% |                                           |

  | user2 | 192.168.% | *9E72259BA9214F692A85B240647C4D95B0F2E08B |

  +-------+-----------+-------------------------------------------+

删除

drop user '用户名'@'主机名';

 MariaDB [mysql]> DROP USER user2@'192.168.%';

  MariaDB [mysql]> SELECT user,host,password FROM user;

  +-------+-----------+-------------------------------------------+

  | user  | host      | password                                  |

  +-------+-----------+-------------------------------------------+

  | root  | localhost | *4A54C3F37C03C7FBACE31591D6A8C546F93DF5C5 |

  | root  | centos7   |                                           |

  | root  | 127.0.0.1 |                                           |

  | root  | ::       |                                           |

  |       | localhost |                                           |

  |       | centos7   |                                           |

  | user1 | 192.168.% |                                           |

  +-------+-----------+-------------------------------------------+

重命名

MariaDB [mysql]> RENAME USER user1@'192.168.%' TO testuser@'%';

  MariaDB [mysql]> SELECT user,host,password FROM mysql.user;

  +----------+-----------+-------------------------------------------+

  | user     | host      | password                                  |

  +----------+-----------+-------------------------------------------+

  | root     | localhost | *4A54C3F37C03C7FBACE31591D6A8C546F93DF5C5 |

  | root     | centos7   |                                           |

  | root     | 127.0.0.1 |                                           |

  | root     | ::       |                                           |

  |          | localhost |                                           |

  |          | centos7   |                                           |

  | testuser | %         |                                           |

  +----------+-----------+-------------------------------------------+

修改密码

 MariaDB [mysql]> SET PASSWORD FOR testuser@'%' =PASSWORD('testpass');

 MariaDB [mysql]> SELECT user,host,password FROM mysql.user;

  +----------+-----------+-------------------------------------------+

  | user     | host      | password                                  |

  +----------+-----------+-------------------------------------------+

  | root     | localhost | *4A54C3F37C03C7FBACE31591D6A8C546F93DF5C5 |

  | root     | centos7   |                                           |

  | root     | 127.0.0.1 |                                           |

  | root     | ::       |                                           |

  |          | localhost |                                           |

  |          | centos7   |                                           |

  | testuser | %         | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |

  +----------+-----------+-------------------------------------------+

其他修改密码的方法：

UPDATE user SET password=PASSWORD('testpass') WHERE user='testuser';

# mysqladmin -uroot -poldpass password 'newpass'

3、破解管理账号密码

空数据库的情况下恢复密码

# systemctl stop mariadb

  # rm -rf /var/lib/mysql/*  #删库跑路

  # systemctl start mariadb

有数据的情况下恢复密码

  ）在/etc/my.cnf配置文件的[mydqld]下添加skip-grant-tables和skip-networking参数

  ）# systemctl restart mariadb 重启服务

  ）执行mysql登录到数据库

  ）MariaDB [(none)]> UPDATE mysql.user SET password=PASSWORD('newpassword') WHERE user='root' AND host='localhost';  #更新密码

  ）MariaDB [(none)]> FLUSH PRIVILEGES;  #刷新授权表

  ）退出，修改配置文件，删除skip-grant-tables和skip-networking参数，重启服务

也可以在启动mysqld进程时，为其使用如下选项：
--skip-grant-tables

--skip-networking

授权管理

1、授权

grant all on 库名.标名(*表示所有) to 用户名@'主机名(%所有)' identified by '密码';

授权时如果用户不存在则创建，所以我们一般不会单独去创建一个用户，而是授权创建一块完成。

priv_type 授权类型

SELECT

INSERT

UPDATE

DELETE

CREATE

DROP

INDEX

ALTER

SHOW DATABASES

CREATE TEMPORARY TABLES

LOCK TABLES

CREATE VIEW

SHOW VIEW

CREATE USER

ALL PRIVILEGES 或 ALL

object_type 授权对象
```
TABLE

FUNCTION

PROCEDURE
```

priv_level 授权级别

*或*.* 表示所有库

db_name.* 表示指定库中的所有表

db_name.tbl_name 指定库中的指定表

tbl_name 表示当前库的表

db_name.routine_name 表示指定库的函数,存储过程,触发器

WITH GRANT OPTION

MAX_QUERIES_PER_HOUR count

MAX_UPDATES_PER_HOUR count

MAX_CONNECTIONS_PER_HOUR count

MAX_USER_CONNECTIONS count

MariaDB [school]> GRANT SELECT(stuid,name) ON TABLE school.students TO admin@'%' IDENTIFIED BY 'admin';  #把students表的stuid和name字段的查询权限授权于admin@'%'用户

MariaDB [school]> FLUSH PRIVILEGES;  #刷新授权表

2、查询授权

MariaDB [school]> SHOW GRANTS FOR admin@'%'\G  #查看指定用户的权限

*************************** . row ***************************

Grants for admin@%: GRANT USAGE ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441'

*************************** . row ***************************

Grants for admin@%: GRANT SELECT (stuid, name) ON `school`.`students` TO 'admin'@'%'

[root@working ~]# mysql -uadmin -padmin -h192.168.0.

MariaDB [(none)]> SHOW GRANTS FOR CURRENT_USER()\G  #查询自己的权限

*************************** . row ***************************

Grants for admin@%: GRANT USAGE ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441'

*************************** . row ***************************

Grants for admin@%: GRANT SELECT (stuid, name) ON `school`.`students` TO 'admin'@'%'

3、收回授权

MariaDB [school]> REVOKE SELECT(stuid) ON school.students FROM admin@'%';  #收回admin@'%'用户对stuid字段的查询权限

巴特西

mysql用户与授权

视图

用户管理

1、用户账号

2、增加删除账号

3、破解管理账号密码

授权管理

1、授权

2、查询授权

3、收回授权

最新文章

热门文章