Oracle 创建用户并分配权限指南

创建用户

在Oracle数据库中创建新用户的基本语法如下：

CREATE USER username IDENTIFIED BY password
[DEFAULT TABLESPACE tablespace_name]
[TEMPORARY TABLESPACE temp_tablespace_name]
[QUOTA size ON tablespace_name]
[PROFILE profile_name]
[PASSWORD EXPIRE]
[ACCOUNT {LOCK | UNLOCK}];

示例：

-- 创建用户并设置密码
CREATE USER scott IDENTIFIED BY tiger;

-- 创建用户并指定表空间和配额
CREATE USER hr IDENTIFIED BY hr_password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users;

分配权限

Oracle中的权限分为系统权限和对象权限两种。

1. 系统权限

系统权限允许用户执行特定的数据库操作或访问特定类型的对象。

常用系统权限：

• CREATE SESSION: 允许用户连接到数据库
• CREATE TABLE: 允许用户创建表
• CREATE VIEW: 允许用户创建视图
• CREATE PROCEDURE: 允许用户创建存储过程
• CREATE SEQUENCE: 允许用户创建序列
• CREATE SYNONYM: 允许用户创建同义词
• UNLIMITED TABLESPACE: 允许用户在所有表空间上使用无限空间

授予系统权限：

GRANT system_privilege [, system_privilege...] 
TO username [WITH ADMIN OPTION];

示例：

-- 授予连接数据库和创建表的权限
GRANT CREATE SESSION, CREATE TABLE TO scott;

-- 授予多个权限并允许用户将这些权限授予其他用户
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO hr WITH ADMIN OPTION;

2. 对象权限

对象权限控制用户对特定数据库对象（如表、视图、序列等）的操作。

常用对象权限：

• SELECT: 查询数据
• INSERT: 插入数据
• UPDATE: 更新数据
• DELETE: 删除数据
• ALTER: 修改对象结构
• REFERENCES: 创建外键约束
• INDEX: 创建索引
• ALL: 所有权限

授予对象权限：

GRANT object_privilege [, object_privilege...] 
ON object_name 
TO username [WITH GRANT OPTION];

示例：

-- 授予用户对特定表的所有权限
GRANT ALL ON employees TO scott;

-- 授予用户对特定表的查询和插入权限
GRANT SELECT, INSERT ON departments TO hr WITH GRANT OPTION;

创建角色并分配权限

为了简化权限管理，可以创建角色并将权限授予角色，然后将角色授予用户。

创建角色：

CREATE ROLE role_name [NOT IDENTIFIED | IDENTIFIED BY password];

授予角色权限：

GRANT system_privilege [, system_privilege...] 
TO role_name [WITH ADMIN OPTION];

GRANT object_privilege [, object_privilege...] 
ON object_name 
TO role_name [WITH GRANT OPTION];

将角色授予用户：

GRANT role_name [, role_name...] 
TO username [WITH ADMIN OPTION];

示例：

-- 创建角色
CREATE ROLE app_user;

-- 授予角色系统权限
GRANT CREATE SESSION, CREATE TABLE TO app_user;

-- 授予角色对象权限
GRANT SELECT, INSERT, UPDATE ON employees TO app_user;

-- 将角色授予用户
GRANT app_user TO scott;

查看用户权限

-- 查看用户被授予的系统权限
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'SCOTT';

-- 查看用户被授予的角色
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'SCOTT';

-- 查看角色被授予的权限
SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'APP_USER';
SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'APP_USER';

修改用户密码

ALTER USER username IDENTIFIED BY new_password;

锁定/解锁用户

-- 锁定用户
ALTER USER username ACCOUNT LOCK;

-- 解锁用户
ALTER USER username ACCOUNT UNLOCK;

删除用户

DROP USER username [CASCADE];

使用CASCADE选项会删除用户拥有的所有对象。

最佳实践

1. 遵循最小权限原则，只授予用户完成工作所需的最小权限
2. 使用角色来简化权限管理
3. 定期审查用户权限
4. 为重要用户设置密码过期策略
5. 考虑使用概要文件(Profile)来限制资源使用

通过以上步骤，您可以有效地在Oracle数据库中创建用户并分配适当的权限。