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

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

在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;