mysql,mariadb,postgresql创建用户和授权的命令 - ToB企服应用市场:ToB评测及商务社交产业平台

在10.1.1.11上访问，有客户端-->OK
mysql -h10.1.1.44 -uroot -P3306 -plianShi20@!
一、mariadb:
mysql -h10.1.1.11 -uroot -P3306 -plianShi20@!
SELECT user,plugin FROM mysql.user;
一、
mysql新增用户和授权(必须在服务器44上)
mysql -uroot -P3306 -p
输入密码：lianShi20@!
create user user1@'%' identified by 'lianShi2021';
grant all privileges on *.* to user1@'%';
create user user2@'%' identified by 'lianShi2021';
grant all privileges on *.* to user2@'%';
FLUSH PRIVILEGES;
#查询用户
SELECT user, host FROM mysql.user;
#mysql的用户登录-->OK lianShi2021
mysql -uuser1 -P3306 -p
mysql -uuser2 -P3306 -p
#修改密码：
SET PASSWORD FOR 'user1' = '123456';
SET PASSWORD FOR 'user2' = '123456';
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'lianShi20@!' WITH GRANT OPTION;
ALTER USER 'user1'@'%' IDENTIFIED BY 'lianShi2021' PASSWORD EXPIRE NEVER;
ALTER USER 'user1'@'%' IDENTIFIED WITH mysql_native_password BY 'lianShi2021';
FLUSH PRIVILEGES;
ALTER USER 'user2'@'%' IDENTIFIED BY 'lianShi2021' PASSWORD EXPIRE NEVER;
ALTER USER 'user2'@'%' IDENTIFIED WITH mysql_native_password BY 'lianShi2021';
FLUSH PRIVILEGES;
alter user 'user1'@'%' identified with mysql_native_password by 'lianShi2021';
alter user 'user2'@'%' identified with mysql_native_password by 'lianShi2021';
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%' IDENTIFIED BY 'lianShi2021';
FLUSH PRIVILEGES;
二、mariadb:新增用户和授权(必须在服务器11上)
mysql -uroot -P3306 -p
输入密码：lianShi20@!
create user user1@'%' identified by 'lianShi2021';
grant all privileges on *.* to user1@'%';
create user user2@'%' identified by 'lianShi2021';
grant all privileges on *.* to user2@'%';
FLUSH PRIVILEGES;
mariadb的用户登录-->OK lianShi2021
mysql -uuser1 -P3306 -p
mysql -uuser2 -P3306 -p
三、tidb:新增用户和授权(必须在服务器44上)
mysql -h10.1.1.44 -uroot -P4000 -p
输入密码：lianshi
CREATE USER 'user1'@'%' IDENTIFIED BY 'lianShi2021';
grant all privileges on *.* to user1@'%';
CREATE USER 'user2'@'%' IDENTIFIED BY 'lianShi2021';
grant all privileges on *.* to user2@'%';
FLUSH PRIVILEGES;
例子：
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
GRANT privileges ON dbname.tablename TO 'username'@'host';
GRANT ALL PRIVILEGES ON mydb.* TO 'bob'@'%';
GRANT SELECT, INSERT ON mydb.mytable TO 'bob'@'%';
tidb的用户登录-->OK
mysql -h10.1.1.44 -uuser1 -P4000 -p
mysql -h10.1.1.44 -uuser2 -P4000 -p
pg:
psql -h10.1.1.44 -Upostgres -dpostgres
输入密码：lianShi20@!
create user user1 with password 'lianShi2021';
grant all privileges on database postgres to user1;
create user user2 with password 'lianShi2021';
grant all privileges on database postgres to user2;
#授权user1/user2对public下表的所有操作权限
GRANT USAGE ON SCHEMA public to user1;
GRANT USAGE ON SCHEMA public to user2;
GRANT ALL ON users TO user1;
GRANT ALL ON users TO user2;
#查询用户
SELECT usename FROM pg_catalog.pg_user;
数据库权限查看
\l
#查询数据库
\dt
#退出
\q
-- 创建用户
CREATE USER 'username' WITH PASSWORD 'password';
-- 授权
GRANT ALL PRIVILEGES ON DATABASE dbname TO 'username';
create role etl_user login password 'ThePassowrd' valid until 'infinity';
GRANT CONNECT ON DATABASE mydb TO etl_user;
GRANT USAGE ON SCHEMA public TO etl_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO etl_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO etl_user;
postgres的用户登录--> lianShi2021
psql -h10.1.1.44 -Uuser1 -dpostgres
psql -h10.1.1.44 -Uuser2 -dpostgres
命令行：
#查询数据库
\dt
#查询表
select * from public.users2;
生成大文件：
dd if=/dev/zero of=test69.txt bs=10M count=1
df -h
#查询es空间命令
/home/es/elasticsearch-7.17.5/data/nodes
du -sk .
mariadb本机无密码登录的问题：
mysql -h10.1.1.11 -uroot -P3306 -plianShi20@!
mysql -h10.1.1.11 -uroot -P3306 -p
SELECT User, Host,plugin FROM mysql.user WHERE User = 'root';
ALTER USER root@'%' IDENTIFIED VIA mysql_native_password;
SET PASSWORD FOR 'root'@'%' = PASSWORD('lianShi20@!');
ALTER USER root@'localhost' IDENTIFIED VIA mysql_native_password;
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('lianShi20@!');
FLUSH PRIVILEGES;

复制代码