CON_ID NAME OPEN_MODE
—— ——— ———-
2 PDB$SEED READ ONLY
3 SALESPDB READ WRITE
4 HRPDB READ WRITE
5 APP_ROOT READ WRITE
6 APP_PDB1 READ WRITE

# 切换到PDB
SQL> ALTER SESSION SET CONTAINER = fgfgfgsalespdb;

Session altered.

# 查看本地用户
SQL> SELECT username, account_status FROM dba_users ORDER BY username;

USERNAME ACCOUNT_STATUS
—————————— —————
AUDSYS OPEN
DBSFWUSER EXPIRED & LOCKED
GGSYS EXPIRED & LOCKED
HR_ADMIN OPEN
SALES_ADMIN OPEN
SYS OPEN
SYSTEM OPEN

# 创建本地用户
SQL> CREATE USER fgfgfgsales_user IDENTIFIED BY “Sales123#”
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users;

User created.

SQL> CREATE USER fgfgfgsales_report IDENTIFIED BY “Report123#”
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 50M ON users;

User created.

# 查看本地用户
SQL> SELECT username, account_status FROM dba_users WHERE username IN (‘SALES_USER’, ‘SALES_REPORT’) ORDER BY username;

USERNAME ACCOUNT_STATUS
—————————— —————
SALES_REPORT OPEN
SALES_USER OPEN

# 验证本地用户创建成功

Session altered.

# 查看本地角色
SQL> SELECT role FROM dba_roles ORDER BY role;

ROLE
——————————
CONNECT
DBA
EXP_FULL_DATABASE
IMP_FULL_DATABASE
RESOURCE
SALES_READ_ROLE
SALES_WRITE_ROLE

# 创建本地角色
SQL> CREATE ROLE fgfgfgsales_read_role;

Role created.

SQL> CREATE ROLE fgfgfgsales_write_role;

Role created.

# 查看本地角色
SQL> SELECT role FROM dba_roles WHERE role LIKE ‘SALES%’ ORDER BY role;

ROLE
——————————
SALES_READ_ROLE
SALES_REPORT_ROLE
SALES_WRITE_ROLE

# 验证本地角色创建成功

Session altered.

# 授予本地角色权限
SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO fgfgfgsales_read_role;

Grant succeeded.

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, INSERT, UPDATE, DELETE TO fgfgfgsales_write_role;

Grant succeeded.

# 授予本地用户角色
SQL> GRANT fgfgfgsales_read_role TO fgfgfgsales_user;

Grant succeeded.

SQL> GRANT fgfgfgsales_write_role TO fgfgfgsales_report;

Grant succeeded.

# 查看本地用户权限
SQL> SELECT grantee, granted_role FROM dba_role_privs WHERE grantee IN (‘SALES_USER’, ‘SALES_REPORT’) ORDER BY grantee, granted_role;

GRANTEE GRANTED_ROLE
———- ——————–
SALES_REPORT SALES_WRITE_ROLE
SALES_USER SALES_READ_ROLE

# 验证本地用户权限配置成功

Session altered.

# 配置本地用户密码
SQL> ALTER USER fgfgfgsales_user IDENTIFIED BY “NewSales123#”;

User altered.

SQL> ALTER USER fgfgfgsales_report IDENTIFIED BY “NewReport123#”;

User altered.

# 配置本地用户密码策略
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 90;

Profile altered.

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME 365;

Profile altered.

SQL> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 5;

Profile altered.

# 查看本地用户
SQL> SELECT username, account_status, profile FROM dba_users WHERE username IN (‘SALES_USER’, ‘SALES_REPORT’) ORDER BY username;

USERNAME ACCOUNT_STATUS PROFILE
—————————— ————— ———
SALES_REPORT OPEN DEFAULT
SALES_USER OPEN DEFAULT

# 验证本地用户管理配置成功

Session altered.

# 查看本地用户
SQL> SELECT username, account_status FROM dba_users WHERE username IN (‘SALES_USER’, ‘SALES_REPORT’) ORDER BY username;

USERNAME ACCOUNT_STATUS
—————————— —————
SALES_REPORT OPEN
SALES_USER OPEN

# 测试本地用户登录
$ sqlplus fgfgfgsales_user/”NewSales123#”@fgfgfgsalespdb

SQL*Plus: Release 19.0.0.0.0 – Production on Fri Mar 31 10:00:00 2026
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0

SQL> SHOW USER;

USER is “SALES_USER”

SQL> SELECT con_id, name FROM v$pdbs;

CON_ID NAME
—— ———
3 SALESPDB

# 验证本地用户成功

Session altered.

# 测试本地用户权限
$ sqlplus fgfgfgsales_user/”NewSales123#”@fgfgfgsalespdb

SQL*Plus: Release 19.0.0.0.0 – Production on Fri Mar 31 10:00:00 2026
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0

SQL> SHOW USER;

USER is “SALES_USER”

SQL> CREATE TABLE test_local_user (
id NUMBER,
name VARCHAR2(100)
);

Table created.

SQL> INSERT INTO test_local_user (id, name) VALUES (1, ‘Local User Test’);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM test_local_user;

ID NAME
———- ——————-
1 Local User Test

# 验证本地用户权限成功