MySQL如何为用户授予ALTER DATABASE权限？修改数据库字符集操作权限问题排查

Let's sort this out for you—your problem boils down to a common misunderstanding of how MySQL's permission scoping works, especially for database-level operations. Here's the breakdown:

Why Your Current GRANT Isn't Working #

The GRANT ALL PRIVILEGES ON dbname.* TO 'user_name'@'localhost' statement gives your user full access to all tables, views, and other objects inside the dbname database—but it doesn't include permissions for modifying the database itself (like changing its default character set).

ALTER DATABASE requires the ALTER permission specifically on the database object, not just the objects within it.

Debunking the "GRANT USAGE ON SCHEMA" Myth #

That suggestion you saw is likely confused with PostgreSQL syntax—MySQL doesn't support GRANT ... ON SCHEMA ... at all (even in 8.0, as you confirmed via the docs). While MySQL lets you use SCHEMA as a synonym for DATABASE in some commands (like CREATE SCHEMA), the GRANT syntax doesn't recognize SCHEMA in that context. Plus, USAGE is just a login permission—it doesn't grant any ability to modify databases or tables.

The Correct Fix #

To let your user run ALTER DATABASE, grant them the ALTER permission directly on the database. Follow these steps:

1. Log into MySQL as a user with super privileges (like root).
2. Run the correct grant statement:

   GRANT ALTER ON `dbname` TO 'user_name'@'localhost';
   

   If you want to grant additional database-level permissions (like creating tables or dropping the database later), you can combine them:

   GRANT ALTER, CREATE, DROP ON `dbname` TO 'user_name'@'localhost';
   

3. (Optional but safe) Flush privileges to ensure the changes take effect immediately:

   FLUSH PRIVILEGES;
   

Once you've done this, your user should be able to execute:

ALTER DATABASE `dbname` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

without any issues.

内容的提问来源于stack exchange，提问作者Denis BUCHER