怎样创建数据库的用户并分配权限？

本文目录导读：

1. MySQL / MariaDB
2. PostgreSQL
3. SQL Server
4. Oracle
5. ❗ 关键注意事项

创建数据库用户并分配权限是数据库管理的基础操作，不同数据库（如 MySQL、PostgreSQL、SQL Server）的语法略有不同,以下是主流数据库的操作方式：

MySQL / MariaDB

使用 CREATE USER 创建用户，然后用 GRANT 赋予权限。

步骤：

1. 登录 MySQL（使用 root 或拥有创建用户权限的账户）：

   mysql -u root -p

2. 创建用户：

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

   • username：用户名。
   • host：允许登录的主机。'localhost' 表示仅本地， 表示任意主机，'192.168.1.%' 表示特定网段。
   • password：登录密码。
   • 示例：CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongP@ss123';

3. 分配权限：

   GRANT privilege_type ON database_name.table_name TO 'username'@'host';

   • privilege_type：权限类型，如 ALL PRIVILEGES（所有权限）、SELECT、INSERT、UPDATE、DELETE、CREATE、DROP 等。
   • database_name.table_name：作用范围。 表示所有数据库的所有表（全局权限），mydb.* 表示 mydb 库的所有表。
   • 示例：
     • 赋予 app_user 对 shop_db 库所有表的全部权限：GRANT ALL PRIVILEGES ON shop_db.* TO 'app_user'@'%';
     • 赋予 readonly_user 对所有数据库的只读权限：GRANT SELECT ON *.* TO 'readonly_user'@'localhost';

4. 刷新权限（使更改立即生效）：

   FLUSH PRIVILEGES;

5. 查看用户权限：

   SHOW GRANTS FOR 'username'@'host';

6. 撤销权限：

   REVOKE privilege_type ON database_name.table_name FROM 'username'@'host';

7. 删除用户：

   DROP USER 'username'@'host';

PostgreSQL

使用 CREATE USER 和 GRANT。

步骤：

1. 登录 PostgreSQL（使用 postgres 超级用户）：

   psql -U postgres

2. 创建用户（等同于 CREATE ROLE 并带 LOGIN 属性）：

   CREATE USER username WITH PASSWORD 'password';

   • 示例：CREATE USER app_user WITH PASSWORD 'SecurePass456';

3. 创建数据库（如果需要）：

   CREATE DATABASE shop_db;

4. 分配权限：

   • 赋予数据库的连接权限：

     GRANT CONNECT ON DATABASE database_name TO username;

     • 示例：GRANT CONNECT ON DATABASE shop_db TO app_user;

   • 赋予 Schema 的使用权限（通常为 public）：

     GRANT USAGE ON SCHEMA schema_name TO username;

     • 示例：GRANT USAGE ON SCHEMA public TO app_user;

   • 赋予表的权限（对当前及未来表分别处理）：

     • 对现有表：GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
     • 对未来创建的表（需要设置默认权限）：

       ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

5. 赋予超级用户权限（谨慎使用）：

   ALTER USER username WITH SUPERUSER;

6. 查看用户权限：

   \du

7. 撤销权限：

   REVOKE privilege_type ON ALL TABLES IN SCHEMA public FROM username;

8. 删除用户：

   DROP USER username;

SQL Server

使用 CREATE LOGIN + CREATE USER + GRANT。

步骤：

1. 创建登录名（服务器级别）：

   CREATE LOGIN login_name WITH PASSWORD = 'password';

2. 切换到目标数据库：

   USE database_name;

3. 创建数据库用户（映射到登录名）：

   CREATE USER user_name FOR LOGIN login_name;

4. 分配权限：

   • 赋予数据库角色（推荐，如 db_datareader 只读，db_datawriter 写入）：

     ALTER ROLE db_datareader ADD MEMBER user_name;
     ALTER ROLE db_datawriter ADD MEMBER user_name;

   • 直接赋予特定权限：

     GRANT SELECT, INSERT, UPDATE ON schema_name.table_name TO user_name;
     GRANT EXECUTE ON SCHEMA::schema_name TO user_name;  -- 执行存储过程权限

5. 查看用户权限：

   EXEC sp_helprotect @username = 'user_name';

6. 删除用户：

   DROP USER user_name;
   DROP LOGIN login_name;

Oracle

略复杂,通常涉及表空间配额。

1. 创建用户：

   CREATE USER username IDENTIFIED BY password;

2. 分配表空间配额：

   ALTER USER username QUOTA UNLIMITED ON USERS;

3. 赋予基本连接和资源权限：

   GRANT CONNECT, RESOURCE TO username;

4. 赋予特定对象权限：

   GRANT SELECT, INSERT ON schema.table_name TO username;

❗ 关键注意事项

1. 最小权限原则：只赋予用户作业所需的最小权限（如只读应用只给 SELECT）。
2. 密码安全：使用强密码,避免弱密码或默认密码。
3. 主机限制：MySQL 中，限制用户只能从特定 IP 登录（如 'app'@'192.168.1.100'）可提升安全性。
4. 刷新权限：MySQL 执行 FLUSH PRIVILEGES; 使授权立即生效（但 GRANT 通常已自动触发）。
5. 角色优于直接授权：对于多用户，创建角色（CREATE ROLE）并赋予角色权限，再将用户加入角色,管理更灵活。

需要我针对你使用的具体数据库（MySQL、PostgreSQL、SQL Server）和场景（如仅需只读、写入、DDL）给出更精确的示例吗？