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

# 切换到CDB
SQL> ALTER SESSION SET CONTAINER = cdb$root;

Session altered.

# 查看公共用户
SQL> SELECT username, common, account_status FROM cdb_users WHERE common = ‘YES’ ORDER BY username;

USERNAME COMMON ACCOUNT_STATUS
—————————— ——– —————
C##ADMIN YES OPEN
C##MONITOR YES OPEN
SYS YES OPEN
SYSTEM YES OPEN

# 创建公共用户
SQL> CREATE USER c##app_admin IDENTIFIED BY “App123#”
CONTAINER = ALL;

User created.

SQL> CREATE USER c##app_user IDENTIFIED BY “App123#”
CONTAINER = ALL;

User created.

# 查看公共用户
SQL> SELECT username, common, account_status FROM cdb_users WHERE common = ‘YES’ ORDER BY username;

USERNAME COMMON ACCOUNT_STATUS
—————————— ——– —————
C##ADMIN YES OPEN
C##APP_ADMIN YES OPEN
C##FGFGFGAPP_USER YES OPEN
C##MONITOR YES OPEN
SYS YES OPEN
SYSTEM YES OPEN

# 验证公共用户创建成功

Session altered.

# 查看公共角色
SQL> SELECT role, common FROM cdb_roles WHERE common = ‘YES’ ORDER BY role;

ROLE COMMON
—————————— ——–
C##APP_READ_ROLE YES
C##APP_WRITE_ROLE YES
DBA YES
EXP_FULL_DATABASE YES
IMP_FULL_DATABASE YES

# 创建公共角色
SQL> CREATE ROLE c##app_read_role CONTAINER = ALL;

Role created.

SQL> CREATE ROLE c##app_write_role CONTAINER = ALL;

Role created.

# 查看公共角色
SQL> SELECT role, common FROM cdb_roles WHERE common = ‘YES’ ORDER BY role;

ROLE COMMON
—————————— ——–
C##APP_READ_ROLE YES
C##APP_WRITE_ROLE YES
DBA YES
EXP_FULL_DATABASE YES
IMP_FULL_DATABASE YES

# 验证公共角色创建成功

Session altered.

# 授予公共角色权限
SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO c##app_read_role CONTAINER = ALL;

Grant succeeded.

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, INSERT, UPDATE, DELETE TO c##app_write_role CONTAINER = ALL;

Grant succeeded.

# 授予公共用户角色
SQL> GRANT c##app_read_role TO c##app_user CONTAINER = ALL;

Grant succeeded.

SQL> GRANT c##app_write_role TO c##app_admin CONTAINER = ALL;

Grant succeeded.

# 查看公共用户权限
SQL> SELECT grantee, granted_role, common FROM cdb_role_privs WHERE grantee IN (‘C##APP_ADMIN’, ‘C##FGFGFGAPP_USER’) AND common = ‘YES’ ORDER BY grantee, granted_role;

GRANTEE GRANTED_ROLE COMMON
———- ——————– ——–
C##APP_ADMIN C##APP_WRITE_ROLE YES
C##FGFGFGAPP_USER C##APP_READ_ROLE YES

# 验证公共用户权限配置成功

Session altered.

# 配置公共用户密码
SQL> ALTER USER c##app_admin IDENTIFIED BY “NewApp123#”;

User altered.

SQL> ALTER USER c##app_user IDENTIFIED BY “NewApp123#”;

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 cdb_users WHERE common = ‘YES’ AND username IN (‘C##APP_ADMIN’, ‘C##FGFGFGAPP_USER’) ORDER BY username;

USERNAME ACCOUNT_STATUS PROFILE
—————————— ————— ———
C##APP_ADMIN OPEN DEFAULT
C##FGFGFGAPP_USER OPEN DEFAULT

# 验证公共用户管理配置成功

Session altered.

# 查看公共用户
SQL> SELECT username, common, account_status FROM cdb_users WHERE common = ‘YES’ ORDER BY username;

USERNAME COMMON ACCOUNT_STATUS
—————————— ——– —————
C##ADMIN YES OPEN
C##APP_ADMIN YES OPEN
C##FGFGFGAPP_USER YES OPEN
C##MONITOR YES OPEN
SYS YES OPEN
SYSTEM YES OPEN

# 测试公共用户登录
$ sqlplus c##app_admin/”NewApp123#”@orclcdb

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 “C##APP_ADMIN”

SQL> SELECT con_id, name FROM v$containers;

CON_ID NAME
—— ———
1 CDB$ROOT
2 PDB$SEED
3 SALESPDB
4 HRPDB
5 APP_ROOT
6 APP_PDB1

# 验证公共用户成功

Session altered.

# 查看公共用户
SQL> SELECT username, account_status FROM dba_users WHERE username LIKE ‘C##%’ ORDER BY username;

USERNAME ACCOUNT_STATUS
—————————— —————
C##APP_ADMIN OPEN
C##FGFGFGAPP_USER OPEN

# 测试公共用户在PDB中的使用
$ sqlplus c##app_admin/”NewApp123#”@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 “C##APP_ADMIN”

SQL> SELECT con_id, name FROM v$pdbs;

CON_ID NAME
—— ———
3 SALESPDB

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

Table created.

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

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM test_common_user;

ID NAME
———- ——————-
1 Common User Test

# 验证公共用户在PDB中的使用成功