mysql,mariadb,postgresql创建用户和授权的命令

mysql,mariadb,postgresql创建用户和授权的命令

1. 在10.1.1.11上访问，有客户端-->OK
2. mysql -h10.1.1.44 -uroot -P3306 -plianShi20@!
4. 一、mariadb:
5. mysql -h10.1.1.11 -uroot -P3306 -plianShi20@!
6. SELECT user,plugin FROM mysql.user;
7. 一、
8. mysql新增用户和授权(必须在服务器44上)
9. mysql -uroot -P3306 -p
10. 输入密码：lianShi20@!
11. create user user1@'%' identified by 'lianShi2021';
12. grant all privileges on *.* to user1@'%';
14. create user user2@'%' identified by 'lianShi2021';
15. grant all privileges on *.* to user2@'%';
17. FLUSH PRIVILEGES;
19. #查询用户
20. SELECT user, host FROM mysql.user;
22. #mysql的用户登录-->OK  lianShi2021
23. mysql -uuser1 -P3306 -p
24. mysql -uuser2 -P3306 -p
26. #修改密码：
27. SET PASSWORD FOR 'user1' = '123456';
28. SET PASSWORD FOR 'user2' = '123456';
29. FLUSH PRIVILEGES;
31. GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'lianShi20@!' WITH GRANT OPTION;
33. ALTER USER 'user1'@'%' IDENTIFIED BY 'lianShi2021' PASSWORD EXPIRE NEVER;
34. ALTER USER 'user1'@'%' IDENTIFIED WITH mysql_native_password BY 'lianShi2021';
35. FLUSH PRIVILEGES;
37. ALTER USER 'user2'@'%' IDENTIFIED BY 'lianShi2021' PASSWORD EXPIRE NEVER;
38. ALTER USER 'user2'@'%' IDENTIFIED WITH mysql_native_password BY 'lianShi2021';
39. FLUSH PRIVILEGES;
41. alter user  'user1'@'%' identified with mysql_native_password by 'lianShi2021';
42. alter user  'user2'@'%' identified with mysql_native_password by 'lianShi2021';
43. FLUSH PRIVILEGES;
45. GRANT ALL PRIVILEGES ON *.* TO  'user1'@'%'  IDENTIFIED BY 'lianShi2021';
46. FLUSH PRIVILEGES;
48. 二、mariadb:新增用户和授权(必须在服务器11上)
49. mysql -uroot -P3306 -p
50. 输入密码：lianShi20@!
52. create user user1@'%' identified by 'lianShi2021';
53. grant all privileges on *.* to user1@'%';
55. create user user2@'%' identified by 'lianShi2021';
56. grant all privileges on *.* to user2@'%';
58. FLUSH PRIVILEGES;
60. mariadb的用户登录-->OK  lianShi2021
61. mysql -uuser1 -P3306 -p
62. mysql -uuser2 -P3306 -p
66. 三、tidb:新增用户和授权(必须在服务器44上)
67. mysql -h10.1.1.44 -uroot -P4000  -p
68. 输入密码：lianshi
70. CREATE USER 'user1'@'%' IDENTIFIED BY 'lianShi2021';
71. grant all privileges on *.* to user1@'%';
72. CREATE USER 'user2'@'%' IDENTIFIED BY 'lianShi2021';
73. grant all privileges on *.* to user2@'%';
74. FLUSH PRIVILEGES;
76. 例子：
77. CREATE USER 'username'@'host' IDENTIFIED BY 'password';
78. GRANT privileges ON dbname.tablename TO 'username'@'host';
79. GRANT ALL PRIVILEGES ON mydb.* TO 'bob'@'%';
80. GRANT SELECT, INSERT ON mydb.mytable TO 'bob'@'%';
82. tidb的用户登录-->OK
83. mysql -h10.1.1.44 -uuser1 -P4000  -p
84. mysql -h10.1.1.44 -uuser2 -P4000  -p
87. pg:
88. psql -h10.1.1.44 -Upostgres -dpostgres  
89. 输入密码：lianShi20@!
91. create user user1  with password 'lianShi2021';
92. grant all privileges on database postgres to user1;
94. create user user2  with password 'lianShi2021';
95. grant all privileges on database postgres to user2;
96. #授权user1/user2对public下表的所有操作权限
97. GRANT USAGE ON SCHEMA public to user1;
98. GRANT USAGE ON SCHEMA public to user2;
99. GRANT ALL ON users TO user1;
100. GRANT ALL ON users TO user2;
102. #查询用户
103. SELECT usename FROM pg_catalog.pg_user;
104. 数据库权限查看
105. \l
107. #查询数据库
108. \dt
109. #退出
110. \q
112. -- 创建用户
113. CREATE USER 'username' WITH PASSWORD 'password';
115. -- 授权
116. GRANT ALL PRIVILEGES ON DATABASE dbname TO 'username';
119. create role etl_user login password 'ThePassowrd' valid until 'infinity';
120. GRANT CONNECT ON DATABASE mydb TO etl_user;
121. GRANT USAGE ON SCHEMA public TO etl_user;
122. GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO etl_user;
123. GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO etl_user;
125. postgres的用户登录-->   lianShi2021
126. psql -h10.1.1.44 -Uuser1 -dpostgres  
127. psql -h10.1.1.44 -Uuser2 -dpostgres  
129. 命令行：
130. #查询数据库
131. \dt
132. #查询表
133. select * from public.users2;
137. 生成大文件：
138. dd if=/dev/zero of=test69.txt bs=10M count=1
139. df -h
142. #查询es空间命令
143. /home/es/elasticsearch-7.17.5/data/nodes
144. du -sk .
148. mariadb本机无密码登录的问题：
150. mysql -h10.1.1.11 -uroot -P3306 -plianShi20@!
152. mysql -h10.1.1.11 -uroot -P3306 -p
153. SELECT User, Host,plugin FROM mysql.user WHERE User = 'root';
155. ALTER USER root@'%'  IDENTIFIED VIA mysql_native_password;
156. SET PASSWORD FOR 'root'@'%' = PASSWORD('lianShi20@!');
157. ALTER USER root@'localhost'  IDENTIFIED VIA mysql_native_password;
158. SET PASSWORD FOR 'root'@'localhost' = PASSWORD('lianShi20@!');
159. FLUSH PRIVILEGES;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。