Oracle 19c New features schema only account

其實早在18c開始，就可以建立schema only account，但是功能上還會有些限制與bug，從19c 19.2開始之後，已有大幅改善。

從19c開始，大部分Oracle-providedschemas，除了SYS，SYSTEM以及Sample Schema User Accounts都是Schema Only Account。也就是說，這些帳戶都是在沒有密碼的情況下建立的，好處是管理員無需週期性的維護這些密碼，同時也降低了攻擊者使用預設密碼侵入這些帳戶的安全風險。可以透過dba_users資料字典的authentication_type欄位來判斷，如果是NONE，表示該account是Schema Only的。當有需要的時候，可以為這些帳戶分配密碼，但是為了更好的安全性，Oracle 建議使用完畢後將它們再設定為Schema Only。

關於Schema Only Account的一些說明：
     1）可以是 administrator 和 non-administratoraccounts
     2）這種account只能在database instance中建立，不支援 ASM instance
     3）可以授予 system privileges (比如CREATE ANY TABLE)和管理員角色（比如DBA）
     4）可以建立物件比如tables 、procedures等（根據所授許可權決定）
     5）可以配置透過 single session proxy 的方式連線訪問
     6）不能透過 database link 來使用

1. 建立schema-only account

-- login as sysdba

$ sqlplus / as sysdba
> CREATE USER schema_only NO AUTHENTICATION;
-- or
> CREATE USER schema_only NO AUTHENTICATION DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

> CREATE USER user1 IDENTIFIED BY user1 default tablespace users;
> SELECT username, password, password_versions, account_status, authentication_type FROM dba_users WHERE username in ('SCHEMA_ONLY','USER1');

USERNAME	PASSWORD	     PASSWORD_VERSIONS ACCOUNT_STATUS	    AUTHENTI
--------------- -------------------- ----------------- -------------------- --------
SCHEMA_ONLY					       OPEN		    NONE
USER1				     11G 12C	       OPEN		    PASSWORD

2. 切換 AUTHENTICATION or NO AUTHENTICATION

> alter user schema_only identified by ...;
> alter user schema_only no authentication;

3. 授權 schema_only 部份權限 (create session, resource, tablespace quota)

> grant create session, resource to schema_only;
> alter user schema_only quota unlimited on users;

-- Test grant sysdba
> grant sysdba to schema_only;

4. 透過 single session proxy 連接

-- create Test Lab
$ sqlplus / as sysdba
> grant connect, resource to user1;
> conn user1/user1;
> create table t1 (c1 int);
> select table_name from user_tables;

TABLE_NAME
---------------
T1

-- create proxy connections (schema_only is the proxy for user1)

$ sqlplus / as sysdba
> ALTER USER schema_only GRANT CONNECT THROUGH user1;

5. 連線測試

$ sqlplus user1[schema_only]/user1

SELECT sys_context('USERENV','SESSION_USER') AS session_user,
       sys_context('USERENV','SESSION_SCHEMA') AS session_schema,
       sys_context('USERENV','PROXY_USER') AS proxy_id,
       USER
FROM dual;

SESSION_USER	     SESSION_SCHEMA	  PROXY_ID	       USER
-------------------- -------------------- -------------------- --------------------
SCHEMA_ONLY	     SCHEMA_ONLY	  USER1 	       SCHEMA_ONLY

> create table t2 (c2 int);
> select table_name from user_tables; 

TABLE_NAME
---------------
T2

6. 收回 single session proxy 連接

$ sqlplus / as sysdba
> select * from proxy_users;

PROXY		     CLIENT		  AUT FLAGS
-------------------- -------------------- --- -----------------------------------
USER1		     SCHEMA_ONLY	  NO  PROXY MAY ACTIVATE ALL CLIENT ROLES

> ALTER USER schema_only REVOKE CONNECT THROUGH user1;

> select * from proxy_users;

no rows selected

> conn user1[schema_only]/user1
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.