以預設模式在 Oracle 中創建視圖的特殊設置

在 Oracle 中創建視圖時，如果從其中讀取視圖的視圖和表位於兩個不同的模式中，您是否需要為給定使用者進行特殊設置？創建視圖的使用者具有 dba 權限。

以下命令引發以下錯誤。

create or replace VIEW view_patient as select * from DATA.PATIENT_DIMENSION where PATIENT_NUM in (select PATIENT_NUM from patient_list);

錯誤：

Error report -
SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password
          without the appropriate privilege. This error also occurs if
          attempting to install a database without the necessary operating
          system privileges.
          When Trusted Oracle is configure in DBMS MAC, this error may occur
          if the user was granted the necessary privilege at a higher label
          than the current login.
*Action:   Ask the database administrator to perform the operation or grant
          the required privileges.
          For Trusted Oracle users getting this error although granted the
          the appropriate privilege at a higher label, ask the database
          administrator to regrant the privilege at the appropriate label.

DBA僅有角色是不夠的。

創建視圖

包含視圖的模式的所有者必須具有從視圖所基於的所有表或視圖中選擇（READ 或 SELECT 特權）、插入、更新或刪除行所需的特權。所有者必須直接被授予這些權限，而不是通過角色。

引用自：https://dba.stackexchange.com/questions/131997