How to Grant All Privileges (or Specific Privileges) on a MySQL Database?

MySQL: Grant All Privileges on Database

You have created a database and a user, but you are unable to create tables. This is because you have not granted the user the necessary privileges to create tables.

To grant the user all privileges on the database, you can use the following SQL statement:

GRANT ALL PRIVILEGES
ON mydb.*
TO 'myuser'@'%'
WITH GRANT OPTION;

This statement will grant the user all privileges on the mydb database, including the privilege to create tables.

Important Note:

The GRANT OPTION privilege allows the user to grant privileges to other users. This can be a security risk, so you should only grant this privilege to users who need it.

For security reasons, it is recommended that you create a separate user with only the privileges that are necessary for the task at hand. For example, if you only need to grant a user the ability to create and modify tables, you can use the following SQL statement:

GRANT CREATE, ALTER, DROP
ON mydb.*
TO 'myuser'@'%'

This statement will grant the user the ability to create, alter, and drop tables in the mydb database, but will not grant the user the GRANT OPTION privilege.

The above is the detailed content of How to Grant All Privileges (or Specific Privileges) on a MySQL Database?. For more information, please follow other related articles on the PHP Chinese website!