Mysql和Postgresql創建用戶和授權命令

MySQL/MariaDB/TiDB

mysql -uroot -P3306 -p
輸入密碼：xxx
create user user1@'%' identified by 'xxx';
grant all privileges on *.* to user1@'%';

create user user2@'%' identified by 'xxx';
grant all privileges on *.* to user2@'%';

FLUSH PRIVILEGES;

#查詢用戶
SELECT user, host FROM mysql.user;
?

#修改密碼：
SET PASSWORD FOR 'user1' = '123456';
SET PASSWORD FOR 'user2' = '123456';
FLUSH PRIVILEGES;

#連接

mysql -h10.1.1.11 -uroot -P3306 -pxxx

#如果有ssl加上

mysql -h10.1.1.197 -P3306 -uroot -p123456 --ssl-mode=DISABLED

Postgresql

psql -h10.1.1.44 -Upostgres -dpostgres ?
輸入密碼：xxx

create user user1 ?with password 'xxx';
grant all privileges on database postgres to user1;

create user user2 ?with password 'xxx';
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