The difference between MySQL user management and PostgreSQL user management

1. MySQL User Management

[Example 1.1] Log in to the test database of the local MySQL server as root user

mysql -uroot -p -hlocalhost test

[Example 1.2] Use the root user to log in to the test database of the local MySQL server and execute a query statement

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

[Example 1.3] Use CREATE USER to create a user with username jeffrey, password mypass, and host name localhost

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

[Example 1.4] Use the GRANT statement to create a new user testUser with a password of testpwd. User testUser has query and update permissions for all data, and is granted SELECT and UPDATE permissions for all data tables

GRANT SELECT,UPDATE ON *.* TO 'testUser'@'localhost' IDENTIFIED BY 'testpwd'; /*Create an account and grant permissions*/
SELECT Host,User,Select_priv,Update_priv, FROM mysql.user where user='testUser'; /*View account privilege information*/

[Example 1.5] Use INSERT to create a new account with the user name customer1, the host name localhost, and the password customer1:

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

[Example 1.6] Use DROP USER to delete user 'jeffrey'@'localhost'

DROP USER 'jeffrey'@'localhost';

[Example 1.7] Use DELETE to delete user 'customer1'@'localhost'

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

[Example 1.8] Use mysqladmin to change the root user's password to "rootpwd"

mysqladmin -u root -p password "123456"

[Example 1.9] Use the UPDATE statement to change the root user's password to "rootpwd2":

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

[Example 1.10] Use the SET statement to change the root user's password to "rootpwd3":

SET PASSWORD=password("rootpwd3");

[Example 1.11] Use the SET statement to change the password of the testUser user to "newpwd":

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

[Example 1.12] Use the UPDATE statement to change the password of the testUser user to "newpwd2":

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

[Example 1.13] Use the GRANT statement to change the password of the testUser user to "newpwd3":

sql

[Example 1.14] The testUser user uses the SET statement to change his password to "newpwd4":

SET PASSWORD = PASSWORD("newpwd4");

[Example 1.15] Use the GRANT statement to create a new user grantUser with the password "grantpwd". User grantUser has query and insert permissions for all data and is granted GRANT permissions. The GRANT statement and its execution results are as follows:

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

The result shows that the execution is successful. Use the SELECT statement to query the permissions of user testUser2:

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)

[Example 1.16] Use the REVOKE statement to revoke the update permission of user testUser. The REVOKE statement and its execution results are as follows:

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

The execution result shows that the execution is successful. Use the SELECT statement to query the permissions of user test:

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

[Example 1.17] Use the SHOW GRANTS statement to query the permission information of user testUser. The SHOW GRANTS statement and its execution results are as follows:

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

Case operation process

Open the MySQL client tool and enter the login command to log in to MySQL.

C:\>mysql -u root -p
Enter password: **

Enter the correct password and press Enter. A welcome message will appear indicating a successful login.

Select mysql database as the current database.

MySQL> use mysql;
Database changed

The Database changed message appears, indicating that the database switch is successful.

Create a new account with username newAdmin and password pw1, allowing it to access MySQL from the local host.

Use the GRANT statement to create a new account. The creation process is as follows:

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

The query results of the three SQL statements are as follows:

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

Run the SHOW GRANTS statement to view the permission information of newAdmin.

To view the permission information of the newAdmin account, enter the following statement:

SHOW GRANTS FOR 'newAdmin'@'localhost';

Log in to MySQL using the newAdmin user.

To exit the current login, use the EXIT command. The statement is as follows:

MySQL> exit
Bye

Log in to MySQL using the newAdmin account. The statement is as follows:

C:\>MySQL -u newAdmin -p
Enter password: ***

After entering the correct password, the "mysql>" prompt appears and the login is successful.

Use the newAdmin user to view the data in the person_dd table in the test_db database.

The newAdmin user is granted query permissions on three fields in the person table in the test database, so he can execute the SELECT statement to view the values ​​of these fields. The execution process is as follows:

MySQL> SELECT * FROM test_db.person_dd LIMIT 5;

Use the newAdmin user to insert a new record into the person_dd table and view the statement execution results.

To insert a new record, enter the following statement:

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

The execution results are as follows:

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

As you can see, the statement cannot be executed, and the error message indicates that the newAdmin user cannot insert an entry into the person table. Therefore, users cannot execute unauthorized operations.

Log out of the current account, log in again as the root user, and revoke the permissions of the newAdmin account.

Enter the exit command: exit

Log in to MySQL again as the root user and select the mysql database as the current database.

Enter a statement to revoke the permissions of the newAdmin account. The execution process is as follows:

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

The execution results are as follows:

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

Delete the account information of newAdmin.

To delete a specified account, you can use the DROP USER statement and enter the following:

DROP USER 'newAdmin'@'localhost';

2. PostgreSQL User Management

2.1 Group Role Management

[Example 2.1] Create a role named post2. The SQL code is as follows:

CREATE ROLE post2;

[Example 2.2] Check the roles in the system. The SQL code is as follows:

SELECT rolname FROM pg_roles;

[Example 2.3] Change the name of the role post1 to post3. The SQL statement is as follows:

ALTER ROLE post1 RENAME TO post3;

【Example 2.4】Delete the role post3. The SQL statement is as follows:

DROP ROLE post3;

2.2 Various permissions of roles

1. Login

[Example 2.5] Create a role post4, which has login permission. The SQL statement is as follows:

CREATE ROLE post4 LOGIN;

2Super User

[Example 2.6] Create role post5, which has superuser privileges. The SQL statement is as follows:

CREATE ROLE post5 SUPERUSER;

3. Create a database

[Example 2.7] Create a role post6, which has the permission to create a database. The SQL statement is as follows:

CREATE ROLE post6 CREATEDB;

4. Create a character

To create a role, this permission must be explicitly given (except for superusers). Once a role has the CREATEROLE privilege, it can alter and delete other roles, and grant or revoke membership to other roles. Of course, if you want to operate on the super user, this permission alone is not enough, you must have the SUPERUSER permission.

Create a role with the permission to create roles. The SQL syntax is as follows:

CREATE ROLE name CREATEROLE;

[Example 2.8] Create a role post7, which has the permission to create a database. The SQL statement is as follows:

CREATE ROLE post7 CREATEROLE;

5. Password

Password authority is required when the client authentication method requires a connection to the database. Common authentication methods include password, md5, and crypt.

Create a role with password permissions. The SQL syntax is as follows:

CREATE ROLE name

Password authentication method specific password

[Example 2.9] Create role post8, which has password permission. The SQL statement is as follows:

CREATE ROLE post8 PASSWORD '123456';

2.3 Account Management

1. Create a user

[Example 2.10] Create a user named postgre02 with the permissions to create databases and roles, and the login password is "123456789". The SQL statement is as follows:

CREATE USER postgre02 PASSWORD '123456789' 
CREATEDB CREATEROLE ;

2. Delete user

[Example 2.11] Use DROP USER to delete the account "postgre02". The SQL statement is as follows:

DROP USER postgre02;

3. Change user password

[Example 2.12] Change the password of the account "postgre01" to "123123". The SQL statement is as follows:

ALTER USER postgre01 PASSWORD '123123';

2.4 Group Role and User Role Management

1. Authorize group roles

[Example 2.13] Add permissions to create tables and roles to the "post1" role. The SQL statements are as follows:

ALTER ROLE post1 CREATEDB CREATEROLE;

2. User authorization

[Example 2.14] Add permissions for creating tables and roles to the "postgre01" user. The SQL statements are as follows:

ALTER USER postgre01 CREATEDB CREATEROLE;

3. Revoke group role permissions

[Example 2.15] Revoke the create table and create role permissions of the "post1" role. The SQL statement is as follows:

ALTER ROLE post1 NOCREATEDB NOCREATEROLE;

4. Revoke user permissions

[Example 2.16] Revoke the permissions to create tables and roles from the user "postgre01". The SQL statement is as follows:

ALTER USER postgre01 NOCREATEDB NOCREATEROLE;

2.5 Database Permission Management

1. Modify the owner of the database

[Example 2.17] Change the owner of the "mytest" database to post1. The SQL statement is as follows:

ALTER DATABASE mytest OWNER TO post2;

2. Increase the user's data table permissions

[Example 2.18] Where ppo1 is an existing data table, postgres is an existing user, and postgres is allowed to update the ppo1 data table. Use the following command to update the table permissions:

GRANT UPDATE ON ppo1 TO postgres;

If you modify the above statement to:

GRANT UPDATE ON ppo1 TO PUBLIC;

This means that the update permission of the data table ppo1 is granted to all roles in the system.

If you modify the above statement to:

GRANT ALL ON ppo1 TO postgres;

This means that all permissions applicable to the object are granted to the user postgres.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.