MySQL用户管理与PostgreSQL用户管理的区别说明

mysql -uroot -p -hlocalhost test

mysql -uroot -p -hlocalhost test -e "DESC person;"

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

GRANT SELECT,UPDATE ON *.* TO 'testUser'@'localhost' IDENTIFIED BY 'testpwd'; /*创建账户并授予权限*/
SELECT Host,User,Select_priv,Update_priv, FROM mysql.user where user='testUser'; /*查看账户权限信息*/

INSERT INTO user (Host,User,Password) VALUES('localhost','customer1',PASSWORD('customer1'));

DROP USER 'jeffrey'@'localhost';

DELETE FROM mysql.user WHERE host='localhost' and user='customer1';

mysqladmin -u root -p password "123456"

UPDATE mysql.user set Password=password("rootpwd2")
WHERE User="root" and Host="localhost";

SET PASSWORD=password("rootpwd3");

SET PASSWORD FOR 'testUser'@'localhost'=password("newpwd");

UPDATE mysql.user set Password=PASSWORD("newpwd2")
WHERE User="testUser" and Host="localhost";

SET PASSWORD = PASSWORD("newpwd4");

MySQL> GRANT SELECT,INSERT ON *.* TO 'grantUser'@'localhost' IDENTIFIED BY 'grantpwd' WITH GRANT OPTION;
Query OK, 0 rows affected (0.03 sec)

MySQL> SELECT Host,User,Select_priv,Insert_priv, Grant_priv FROM mysql.user where user='grantUser';
+-----------+------------+-------------+--------------+-------------+
| Host | User | Select_priv | Insert_priv | Grant_priv |
+-----------+------------+-------------+--------------+--------------+
| localhost | testUser2 | Y  | Y  | Y  |
+-----------+------------+-------------+--------------+--------------+
1 row in set (0.00 sec)

MySQL> REVOKE UPDATE ON *.* FROM 'testUser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

MySQL> SELECT Host,User,Select_priv,Update_priv,Grant_priv FROM MySQL.user where user='testUser';

MySQL> SHOW GRANTS FOR 'testUser'@'localhost';

C:\&;mysql -u root -p
Enter password: **

MySQL> use mysql;
Database changed

MySQL> GRANT SELECT, UPDATE(id, name, age)
 -> ON test_db.person_old
 -> TO 'newAdmin'@'localhost' IDENTIFIED BY 'pw1'
 -> WITH MAX_CONNECTIONS_PER_HOUR 30;
SELECT host, user, select_priv, update_priv FROM user WHERE user='newAdmin';
SELECT host, db, user, table_name, table_priv, column_priv 
FROM tables_priv WHERE user='newAdmin';
SELECT host, db, user, table_name, column_name, column_priv 
FROM columns_priv WHERE user='newAdmin';

MySQL> SELECT host, user, select_priv, update_priv FROM user WHERE user='newAdmin';
MySQL> SELECT host, db, user, table_name, table_priv, column_priv
 -> FROM tables_priv WHERE user='newAdmin';
MySQL> SELECT host, db, user, table_name, column_name, column_priv
 -> FROM columns_priv WHERE user='newAdmin';

SHOW GRANTS FOR 'newAdmin'@'localhost';

MySQL> exit
Bye

C:\&;MySQL -u newAdmin -p
Enter password: ***

MySQL> SELECT * FROM test_db.person_dd LIMIT 5;

INSERT INTO test_db.person_old(name, age,info) VALUES('gaga', 30);

ERROR 1142 (42000): INSERT command denied to user 'newAdmin'@'localhost' for table 'person'

REVOKE SELECT, UPDATE ON test.person FROM 'newAdmin'@'localhost';

MySQL> REVOKE SELECT, UPDATE ON test.person FROM 'newAdmin'@'localhost';
Query OK, 0 rows affected (0.00 sec)

DROP USER 'newAdmin'@'localhost';

CREATE ROLE post2;

SELECT rolname FROM pg_roles;

ALTER ROLE post1 RENAME TO post3;

DROP ROLE post3;

CREATE ROLE post4 LOGIN;

CREATE ROLE post5 SUPERUSER;

CREATE ROLE post6 CREATEDB;

CREATE ROLE name CREATEROLE;

CREATE ROLE post7 CREATEROLE;

CREATE ROLE name 

CREATE ROLE post8 PASSWORD '123456';

CREATE USER postgre02 PASSWORD '123456789' 
CREATEDB CREATEROLE ;

DROP USER postgre02;

ALTER USER postgre01 PASSWORD '123123';

ALTER ROLE post1 CREATEDB CREATEROLE;

ALTER USER postgre01 CREATEDB CREATEROLE;

ALTER ROLE post1 NOCREATEDB NOCREATEROLE;

ALTER USER postgre01 NOCREATEDB NOCREATEROLE;

ALTER DATABASE mytest OWNER TO post2;

GRANT UPDATE ON ppo1 TO postgres;

GRANT UPDATE ON ppo1 TO PUBLIC;

GRANT ALL ON ppo1 TO postgres;