MySQL中GRANT会覆盖原有权限吗？如何为用户追加S3写入权限？

Ah, I've run into this exact issue with Aurora before—let me break down what happened and how to fix it properly.

Why Your Permissions Got Wiped #

When you ran GRANT SELECT INTO S3 ON *.* TO 'user'@'domain-or-ip-address';, there are a couple of likely reasons your existing permissions vanished:

• Global Permission Override: If your user previously had global-level permissions (like ALL PRIVILEGES ON *.*), some Aurora/MySQL versions can treat a new global GRANT as a replacement instead of an append (this is rare but possible if you're not explicitly including existing permissions).
• Hidden IDENTIFIED BY Clause: If you accidentally included IDENTIFIED BY 'password' in your command (even if you didn't mean to), this resets the user's entire permission set to only the ones specified in the GRANT statement.
• Permission Scope Conflict: If your user's original permissions were database/table-specific, adding a global *.* permission might have caused the permission system to prioritize the global scope, hiding the more granular permissions (though this usually doesn't revoke them entirely).

Step 1: Restore Lost Permissions #

First, let's get your user's basic access back. If you remember the original permissions, re-grant them directly. For example, if they had SELECT access to mydb.*:

GRANT SELECT ON mydb.* TO 'user'@'domain-or-ip-address';

If you don't remember, you can check the Aurora permission tables (note: you'll need admin access for this):

-- Check global permissions
SELECT * FROM mysql.user WHERE User = 'user' AND Host = 'domain-or-ip-address';

-- Check database-specific permissions
SELECT * FROM mysql.db WHERE User = 'user' AND Host = 'domain-or-ip-address';

Use the results to re-grant any missing permissions.

Step 2: Properly Append SELECT INTO S3 Permissions #

To avoid overwriting permissions again, follow these safe practices:

Option 1: Append Global SELECT INTO S3 Access #

If you need the user to export data from any database to S3:

-- First, confirm current permissions to avoid mistakes
SHOW GRANTS FOR 'user'@'domain-or-ip-address';

-- Append the S3 export permission
GRANT SELECT INTO S3 ON *.* TO 'user'@'domain-or-ip-address';

-- Flush permissions (Aurora usually auto-refreshes, but this ensures changes take effect)
FLUSH PRIVILEGES;

Option 2: Grant Granular (Database/Table-Specific) Access #

For better security, only grant SELECT INTO S3 for the specific databases/tables the user needs:

-- For a single database
GRANT SELECT, SELECT INTO S3 ON mydb.* TO 'user'@'domain-or-ip-address';

-- For a single table
GRANT SELECT, SELECT INTO S3 ON mydb.mytable TO 'user'@'domain-or-ip-address';

Note: The user needs SELECT permission on the target data to export it to S3, so include that if they don't already have it.

Prevent This From Happening Again #

• Always Check Existing Permissions: Run SHOW GRANTS FOR 'user'@'domain-or-ip-address'; before any GRANT or REVOKE command.
• Avoid Unnecessary Global Permissions: Granular permissions reduce the risk of accidental overrides and improve security.
• Combine Permissions in One Command: If you need to add multiple permissions, list them all in a single GRANT statement to ensure nothing gets lost.

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