mysql 8.0给数据库添加用户和赋权

mysql 8.0给数据库添加用户和赋权 - 四海骄阳

zhoading 2021-08-13 原文

mysql 8.0给数据库添加用户和赋权

— 使用mysql 数据库正式环境
USE mysql
— 为mysql创建用户：waiyan 密码为：XXXXX
CREATE USER waiyan IDENTIFIED BY \’XXXXX\’;
— 查看下用户waiyan的权限
SELECT * FROM user WHERE user=\’waiyan\’ ;
SHOW GRANTS FOR waiyan;
— 给用户waiyan在数据库名为prescription上赋EXECUTE(执行存储过程),INSERT,SELECT,UPDATE,DELETE权限，@\’%\’表示从任意ip都可以访问到这个数据库
–GRANT EXECUTE,INSERT,SELECT,UPDATE,DELETE ON prescription.* TO \’waiyan\’@\’%\’;

grant all privileges ON prescription.* TO \’waiyan\’@\’%\’;

— 生效
FLUSH PRIVILEGES;
— 再次查询下权限
SELECT * FROM user WHERE user=\’waiyan\’ ;
SHOW GRANTS FOR waiyan;

执行这些sql语句后，使用waiyan登录后，就能查看到数据库 prescription 并且不能执行删除命令

如果要删除该用户，使用语句：

DROP USER waiyan@\’%\’;

ALTER USER \’waiyan\’@\’%\’ IDENTIFIED WITH mysql_native_password BY \’XXXXX\’;

— 使用mysql 数据库测试环境
USE mysql
— 为mysql创建用户：waiyan_test 密码为：XXXXX
CREATE USER waiyan_test IDENTIFIED BY \’XXXXX\’;
— 查看下用户waiyan_test的权限
SELECT * FROM user WHERE user=\’waiyan_test\’ ;
SHOW GRANTS FOR waiyan_test;
— 给用户waiyan_test在数据库名为prescription_test上赋EXECUTE(执行存储过程),INSERT,SELECT,UPDATE,DELETE权限，@\’%\’表示从任意ip都可以访问到这个数据库
GRANT EXECUTE,INSERT,SELECT,UPDATE,DELETE ON prescription_test.* TO \’waiyan_test\’@\’%\’;

grant all privileges ON prescription_test.* TO \’waiyan_test\’@\’%\’;

— 生效
FLUSH PRIVILEGES;
— 再次查询下权限
SELECT * FROM user WHERE user=\’waiyan_test\’ ;
SHOW GRANTS FOR waiyan_test;

执行这些sql语句后，使用waiyan_test登录后，就能查看到数据库 prescription_test 并且不能执行删除命令

如果要删除该用户，使用语句：

DROP USER waiyan_test@\’%\’;

ALTER USER \’waiyan_test\’@\’%\’ IDENTIFIED WITH mysql_native_password BY \’XXXXX\’;

–万维账号权限分配

数据库名为:chisdb_wanwei

— 为mysql创建用户：wanwei 密码为： @@@_wanwei

USE mysql

CREATE USER wanwei IDENTIFIED BY \’@@@_wanwei\’;
— 查看下用户wanwei的权限
SELECT * FROM user WHERE user=\’wanwei\’ ;
SHOW GRANTS FOR wanwei;
— 给用户wanwei在数据库名为chisdb_wanwei上赋EXECUTE(执行存储过程),INSERT,SELECT,UPDATE,DELETE权限，@\’%\’表示从任意ip都可以访问到这个数据库
–GRANT EXECUTE,INSERT,SELECT,UPDATE,DELETE ON chisdb_wanwei.* TO \’wanwei\’@\’%\’;

grant all privileges ON chisdb_wanwei.* TO \’wanwei\’@\’%\’;

— 生效
FLUSH PRIVILEGES;
— 再次查询下权限
SELECT * FROM user WHERE user=\’wanwei\’ ;
SHOW GRANTS FOR wanwei;

执行这些sql语句后，使用wanwei登录后，就能查看到数据库 chisdb_wanwei 并且不能执行删除命令

如果要删除该用户，使用语句：

–DROP USER wanwei\’%\’;

ALTER USER \’wanwei\’@\’%\’ IDENTIFIED WITH mysql_native_password BY \’@@@_wanwei\’;