Mysql和Postgresql创建用户和授权命令

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