1. Create users using the CREATE USER command, for example MySQL: CREATE USER 'new_user'@'host' IDENTIFIED BY 'password'; PostgreSQL: CREATE USER new_user WITH PASSWORD 'password'; 2. Grant permissions to use the GRANT command, such as GRANT SELECT ON database_name. TO 'new_user'@'host'; 3. Revoke permissions to use the REVOKE command, such as REVOKE DELETE ON database_name. FROM 'new_user'@'host'; 4. Delete users using the DROP USER command; 5. Permission management is recommended to follow the principle of minimum permissions, regularly review permissions, and use roles to simplify management. After the operation is completed, it is recommended to refresh the permissions to ensure that they take effect and pay attention to the impact of permission changes on the system.

Creating a user and granting permissions in SQL is the basic operation in database management. Whether for team collaboration or system security, permissions need to be reasonably allocated according to actual needs. The following are some common scenarios to operate.

Common ways to create users

Different database systems (such as MySQL, PostgreSQL, and SQL Server) have slightly different syntax, but the basic logic is similar.
Taking MySQL as an example, the basic commands for creating a user are as follows:

 CREATE USER 'new_user'@'host' IDENTIFIED BY 'password';

• new_user is the user name you want to create;
• host indicates which host the user can connect from, usually using localhost or % (represents any host);
• password is the login password.

If you are using PostgreSQL, the command will be:

 CREATE USER new_user WITH PASSWORD 'password';

Several common types of granting permissions

After creating a user, the next step is to authorize. You can grant different permission types as needed, such as:

• SELECT : Allows reading of data;
• INSERT : Allows insertion of new records;
• UPDATE : Allows update of existing data;
• DELETE : Allows deletion of data;
• ALL PRIVILEGES : All permissions.

For example, in MySQL, a user is granted query permissions to all tables under a database:

 GRANT SELECT ON database_name.* TO 'new_user'@'host';

After executing the authorization statement, it is recommended to run:

 FLUSH PRIVILEGES;

Ensure permissions take effect immediately.

How to revoke or modify permissions

If the user no longer needs some permissions, you can use the REVOKE command to reclaim the permissions. For example:

 REVOKE DELETE ON database_name.* FROM 'new_user'@'host';

If you want to completely delete the user:

 DROP USER 'new_user'@'host';

Note: When revoking permission, you must confirm whether it affects other functions or applications that rely on this permission.

Some practical suggestions for permission management

• Minimum permission principle : only the minimum permissions required to complete tasks are granted to avoid abuse of ALL PRIVILEGES ;

• Regular review of authority : especially in a multi-person collaboration environment to prevent authority from getting out of control;

• Use roles to simplify management (for PostgreSQL and some supported roles databases):

   CREATE ROLE data_reader;
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO data_reader;
  GRANT data_reader TO new_user;

  This will make permissions more centralized and easier to maintain.

  ---

  Basically, this is all. Creating users and authorization is not complicated, but details are easy to ignore, such as host name, refresh permissions, permission recycling and other links. Just be careful when operating.

  The above is the detailed content of How to create a user and grant permissions in SQL. For more information, please follow other related articles on the PHP Chinese website!