Oracle数据库用户管理：从创建到认证的完整指南

创建数据库用户

在Oracle数据库中，创建数据库用户是管理数据库访问和控制的重要步骤。用户是在数据库中拥有权限和资源的实体。创建用户时，需要指定用户名和密码，并可以设置默认表空间、临时表空间等属性。

示例

CREATE USER example_user IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 10M ON users;

该命令创建一个名为example_user的用户，设置密码为password，指定默认表空间为users，临时表空间为temp，并为用户在users表空间中分配10M的配额。

各种认证

Oracle数据库提供了多种认证用户的方法，确保只有经过授权的用户才能访问数据库。

1. 密码认证

这是最常见的认证方法，用户在创建时设置密码，登录时需要提供相应密码。

CREATE USER example_user IDENTIFIED BY password;

2. 操作系统认证（OS认证）

通过操作系统认证，用户可以使用操作系统账号直接登录数据库，通常用于管理员。

SQL> CONNECT / AS SYSDBA;

3. 密码文件认证

用于认证具有SYSDBA或SYSOPER权限的用户，通过存储在密码文件中的用户名和密码进行认证。

创建密码文件：

orapwd FILE=orapwDB SID=DB PASSWORD=password ENTRIES=10;

设置密码文件认证：

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE;

授予SYSDBA权限：

GRANT SYSDBA TO example_user;

4. 强认证（Kerberos、SSL或目录认证）

使用Kerberos、SSL或目录服务进行强认证，提高安全性。

Kerberos认证示例：

SQL> ALTER SYSTEM SET OS_AUTHENT_PREFIX='ops$';
SQL> CREATE USER ops$example_user IDENTIFIED EXTERNALLY;

为用户分配配额

配额是指用户在特定表空间中可以使用的磁盘空间。通过分配配额，可以控制每个用户在数据库中的存储资源。

示例：

创建用户并分配配额：

CREATE USER example_user IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 10M ON users;

增加用户配额：

ALTER USER example_user QUOTA 20M ON users;

详细示例

需要创建一个新的用户john_doe，并进行如下操作：

1. 设置密码为securePass123
2. 指定默认表空间为data_ts，临时表空间为temp_ts
3. 为用户在data_ts表空间分配50M的配额
4. 使用密码认证方式

SQL命令：

-- 创建用户
CREATE USER john_doe IDENTIFIED BY securePass123
DEFAULT TABLESPACE data_ts
TEMPORARY TABLESPACE temp_ts
QUOTA 50M ON data_ts;

-- 授予连接权限
GRANT CONNECT TO john_doe;

-- 设置密码过期策略
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 30;

通过上述命令，成功创建了用户john_doe，并设置了相关属性和权限。
在创建Oracle数据库用户时，需要定义多个属性来确保用户的正确配置和安全性。这些属性包括唯一用户名、认证方法、默认表空间、临时表空间、用户配置文件、初始消费组、账户状态和模式对象。以下是每个属性的详细解释和示例：

唯一用户名（Unique username）

每个数据库用户都需要一个唯一的用户名，确保在同一个数据库中没有两个用户具有相同的名称。

示例：

CREATE USER unique_user IDENTIFIED BY password;

这个命令创建了一个用户名为unique_user的新用户。

默认表空间（Default tablespace）

默认表空间是用户创建对象（如表和索引）时，如果没有指定其他表空间，将使用的存储位置。

示例：

CREATE USER tablespace_user IDENTIFIED BY password
DEFAULT TABLESPACE users;

这个命令创建了一个用户tablespace_user，其默认表空间为users。

临时表空间（Temporary tablespace）

临时表空间用于存储用户在执行查询时需要的临时数据，如排序操作。

示例：

CREATE USER temp_user IDENTIFIED BY password
TEMPORARY TABLESPACE temp;

这个命令为用户temp_user指定了临时表空间temp。

用户配置文件（User profile）

用户配置文件定义了用户的资源限制和安全策略，如密码有效期、会话资源限制等。

示例：

创建配置文件：

CREATE PROFILE custom_profile LIMIT
PASSWORD_LIFE_TIME 30
SESSIONS_PER_USER 2;

创建用户并分配配置文件：

CREATE USER profile_user IDENTIFIED BY password
PROFILE custom_profile;

初始消费组（Initial consumer group）

消费组用于资源管理，确保数据库资源的公平分配。初始消费组定义了用户连接时默认所属的资源消费组。

示例：

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    CONSUMER_GROUP => 'custom_group',
    COMMENT => 'Custom Consumer Group');
END;
/

CREATE USER consumer_user IDENTIFIED BY password;
ALTER USER consumer_user DEFAULT CONSUMER GROUP custom_group;

账户状态（Account status）

账户状态可以是OPEN（启用）或LOCKED（锁定），用于控制用户账户的访问权限。

示例：

创建并锁定用户：

CREATE USER locked_user IDENTIFIED BY password ACCOUNT LOCK;

解锁用户：

ALTER USER locked_user ACCOUNT UNLOCK;

模式对象（Schema objects）

模式对象是指用户在其模式（schema）中拥有的数据库对象，如表、视图、索引等。每个用户都有一个默认的模式，与用户名相同。

示例：

创建用户并创建模式对象：

CREATE USER schema_user IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO schema_user;

-- 切换到schema_user模式
ALTER SESSION SET CURRENT_SCHEMA = schema_user;

-- 创建表
CREATE TABLE schema_user.example_table (
  id NUMBER,
  name VARCHAR2(50)
);

上述命令创建了用户schema_user，并在该用户的模式中创建了一个名为example_table的表。

综合示例

以下是一个综合示例，展示如何创建一个用户并配置上述所有属性：

-- 创建用户并设置所有属性
CREATE USER complete_user IDENTIFIED BY securePass123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
PROFILE custom_profile
QUOTA 50M ON users
ACCOUNT UNLOCK;

-- 设置初始消费组
ALTER USER complete_user DEFAULT CONSUMER GROUP custom_group;

-- 授予必要权限
GRANT CONNECT, RESOURCE TO complete_user;

-- 切换到complete_user模式
ALTER SESSION SET CURRENT_SCHEMA = complete_user;

-- 创建表
CREATE TABLE complete_user.example_table (
  id NUMBER,
  name VARCHAR2(50)
);

以上命令创建了一个名为complete_user的用户，设置了密码、默认表空间、临时表空间、用户配置文件、配额、初始消费组和账户状态，并在该用户的模式中创建了一个表。
为确保数据库用户不能直接登录到数据库实例，可以创建仅模式账户（Schema Only Account）。这种账户用于通过应用程序强制数据访问，并保护模式对象，防止连接的模式删除对象。仅模式账户不能通过数据库链接连接。Oracle提供的模式账户在创建时使用NO AUTHENTICATION子句。以下是详细解释和示例：

确保用户不能登录实例

通过创建仅模式账户，用户将无法直接登录到数据库实例。这确保了所有数据访问必须通过应用程序进行。

示例：

CREATE USER schema_noauth NO AUTHENTICATION;

这个命令创建了一个名为schema_noauth的用户，使用NO AUTHENTICATION子句，确保该用户不能直接登录数据库。

通过应用程序强制数据访问

仅模式账户设计为只能通过应用程序访问数据，增加了数据访问的安全性。

应用场景：

有一个Web应用程序，需要访问数据库中的用户数据，但不希望数据库用户直接登录和操作数据。可以为该应用程序创建一个仅模式账户，所有数据库操作通过该应用程序的特定接口执行。

保护模式对象

通过防止连接的模式删除对象来保护模式对象。仅模式账户不能删除其模式中的数据库对象。

示例：

CREATE USER app_user NO AUTHENTICATION;
GRANT CONNECT TO app_user;

-- 切换到app_user模式
ALTER SESSION SET CURRENT_SCHEMA = app_user;

-- 创建表
CREATE TABLE app_user.data_table (
  id NUMBER,
  name VARCHAR2(50)
);

这里创建了一个名为app_user的仅模式账户，并在其模式中创建了一个表data_table。

仅模式账户不能通过数据库链接连接

这种账户不能通过数据库链接连接，进一步限制了其访问范围。

示例：

即使创建了仅模式账户schema_noauth，该账户不能通过数据库链接进行连接和操作。

Oracle提供的模式账户使用NO AUTHENTICATION子句

Oracle提供的一些模式账户在创建时使用NO AUTHENTICATION子句，这些账户只能用于特定的应用场景，不允许直接登录。

示例：

创建一个用于特定应用的模式账户：

CREATE USER reporting_schema NO AUTHENTICATION;
GRANT CONNECT TO reporting_schema;

-- 创建一个只读视图
CREATE VIEW reporting_schema.view_reports AS
SELECT * FROM main_schema.reports;

在这个示例中，创建了一个名为reporting_schema的仅模式账户，并在其模式中创建了一个视图view_reports。该视图可以通过应用程序访问，但reporting_schema用户本身不能直接登录数据库。

综合示例

以下是一个综合示例，展示如何创建一个仅模式账户，并配置相关属性以确保其不能直接登录，只能通过应用程序访问数据，并保护其模式对象：

-- 创建仅模式账户
CREATE USER app_schema NO AUTHENTICATION;

-- 授予必要权限
GRANT CONNECT TO app_schema;

-- 切换到app_schema模式
ALTER SESSION SET CURRENT_SCHEMA = app_schema;

-- 创建表
CREATE TABLE app_schema.secure_data (
  id NUMBER,
  data VARCHAR2(100)
);

-- 为应用程序用户授予表的访问权限
GRANT SELECT, INSERT, UPDATE ON app_schema.secure_data TO app_user;

-- 创建仅模式账户并确保其不能删除对象
CREATE USER secure_schema NO AUTHENTICATION;

-- 创建一个表并测试权限
CREATE TABLE secure_schema.test_data (
  id NUMBER,
  value VARCHAR2(50)
);

-- 确保secure_schema不能通过数据库链接连接
--（这个特性内置于NO AUTHENTICATION账户）

这个综合示例展示了如何创建仅模式账户，授予必要的权限，并确保数据访问只能通过应用程序进行，同时保护模式对象不被删除。

密码认证详解

1. 创建用户时设置密码

在Oracle数据库中创建用户时，可以通过指定密码来保证用户在连接数据库时需要进行认证。这样可以确保只有知道密码的用户才能访问数据库资源。

示例：

CREATE USER app_user IDENTIFIED BY password123;

在这个示例中，创建了一个名为app_user的用户，密码为password123。用户在尝试连接数据库时需要提供此密码。

2. 设置密码时，可以立即使密码过期

为了提高安全性，可以在创建用户时立即使密码过期，这样用户在首次登录时必须更改密码。这可以防止新创建的密码被长期使用，强制用户设置一个新的密码。

示例：

ALTER USER app_user PASSWORD EXPIRE;

在这个示例中，通过ALTER USER命令使app_user的密码过期，用户在首次登录时必须更改密码。

3. Oracle数据库中创建的所有密码默认区分大小写

Oracle数据库默认情况下区分密码中的大小写字母。这意味着password123和Password123将被视为不同的密码。这种特性增加了密码的复杂性和安全性。

示例：

CREATE USER case_test_user IDENTIFIED BY "SecurePass123";

在这个示例中，创建了一个名为case_test_user的用户，密码为SecurePass123，其中包含大写字母。用户必须使用准确的大小写字母输入密码才能成功连接。

4. 密码可以包含多字节字符，长度限制为30个字节

Oracle数据库允许密码包含多字节字符，例如汉字、特殊字符等。但密码的总长度不能超过30个字节。

示例：

CREATE USER multi_byte_user IDENTIFIED BY "密碼123Secure";

在这个示例中，创建了一个名为multi_byte_user的用户，密码包含汉字和数字密碼123Secure。该密码中包含的多字节字符使其更难被破解。

5. 使用高级加密标准（AES）算法自动和透明地加密密码

Oracle数据库在存储和传输密码时使用高级加密标准（AES）算法进行加密。这意味着密码在网络上传输时不会以明文形式出现，从而提高了安全性。

示例：

-- 此过程在创建用户时自动执行，无需额外命令
CREATE USER encrypted_user IDENTIFIED BY "SecurePass@2024";

在这个示例中，创建了一个名为encrypted_user的用户，密码为SecurePass@2024。尽管没有显示加密过程，但在数据库内部和传输过程中，密码将被AES算法加密。

综合示例

以下是一个综合示例，展示如何创建一个新用户并设置其密码，立即使密码过期，确保密码区分大小写，并允许多字节字符，同时保证密码在传输过程中被加密：

-- 创建用户并设置密码
CREATE USER secure_user IDENTIFIED BY "安全Pass2024";

-- 使密码立即过期
ALTER USER secure_user PASSWORD EXPIRE;

-- 检查用户和密码设置
SELECT username, account_status FROM dba_users WHERE username = 'SECURE_USER';

在这个示例中：

1. 创建了一个名为secure_user的用户，密码为安全Pass2024，包含多字节字符和大小写字母。
2. 使用ALTER USER命令使密码立即过期，强制用户首次登录时更改密码。
3. 使用SELECT语句检查用户secure_user的账户状态，确认密码已过期。

这种综合示例展示了如何在Oracle数据库中使用密码认证方法，确保用户安全性和密码的复杂性，并通过加密技术保护密码在网络传输过程中的安全。

非常感谢您读到这里！如果您觉得这篇文章对您有帮助，可以关注一下博主。关注后，您将第一时间获得最新的AI、云计算、运维（Linux、数据库，容器等）技术，以及更多实用的技能干货。
​
​

​
像0-1的学习，提供全面的学习资源和社群，多种副业选择，总有一个适合你。
​

​