忽視細節導致簡單問題的複雜化 關於PUPBLD.SQL

簡單問題的出現：

使用SCOTT使用者登陸時出現問題:

SQL> conn scott/tiger

訪問 PRODUCT_USER_PROFILE 時出錯

警告: 未載入產品使用者概要檔案資訊!

您需要將 PUPBLD.SQL 作為 SYSTEM 執行

已連線。

想法很簡單，因為是剛手工建立的庫，使用system跑一遍PUPBLD.SQL指令碼就好。

SQL> conn system/oracle as sysdba

已連線。

 

SQL> @D:\oraclexe\app\oracle\product\10.2.0\server\sqlplus\admin\pupbld.sql

跑指令碼成功後，再次使用SCOTT使用者登陸：

SQL> conn scott/tiger

訪問 PRODUCT_USER_PROFILE 時出錯

警告: 未載入產品使用者概要檔案資訊!

您需要將 PUPBLD.SQL 作為 SYSTEM 執行

已連線。

問題依舊，開始疑惑，直接檢視pupbld.sql指令碼：

*省略註釋*

DROP SYNONYM PRODUCT_USER_PROFILE;

 

CREATE TABLE SQLPLUS_PRODUCT_PROFILE AS

  SELECT PRODUCT, USERID, ATTRIBUTE, SCOPE, NUMERIC_VALUE, CHAR_VALUE,

  DATE_VALUE FROM PRODUCT_USER_PROFILE;

        

ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG);

 

-- Create SQLPLUS_PRODUCT_PROFILE from scratch

 

CREATE TABLE SQLPLUS_PRODUCT_PROFILE

(

  PRODUCT        VARCHAR2 (30) NOT NULL,

  USERID         VARCHAR2 (30),

  ATTRIBUTE      VARCHAR2 (240),

  SCOPE          VARCHAR2 (240),

  NUMERIC_VALUE  DECIMAL (15,2),

  CHAR_VALUE     VARCHAR2 (240),

  DATE_VALUE     DATE,

  LONG_VALUE     LONG

);

 

-- Remove SQL*Plus V3 name for sqlplus_product_profile

 

DROP TABLE PRODUCT_PROFILE;

 

-- Create the view PRODUCT_PRIVS and grant access to that

 

DROP VIEW PRODUCT_PRIVS;

CREATE VIEW PRODUCT_PRIVS AS

  SELECT PRODUCT, USERID, ATTRIBUTE, SCOPE,

         NUMERIC_VALUE, CHAR_VALUE, DATE_VALUE, LONG_VALUE

  FROM SQLPLUS_PRODUCT_PROFILE

  WHERE USERID = 'PUBLIC' OR USER LIKE USERID;

 

GRANT SELECT ON PRODUCT_PRIVS TO PUBLIC;

DROP PUBLIC SYNONYM PRODUCT_PROFILE;

CREATE PUBLIC SYNONYM PRODUCT_PROFILE FOR SYSTEM.PRODUCT_PRIVS;

DROP SYNONYM PRODUCT_USER_PROFILE;

CREATE SYNONYM PRODUCT_USER_PROFILE FOR SYSTEM.SQLPLUS_PRODUCT_PROFILE;

DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE;

CREATE PUBLIC SYNONYM PRODUCT_USER_PROFILE FOR SYSTEM.PRODUCT_PRIVS;

 

-- End of pupbld.sql

手動執行sql語句，建立表和檢視及同義詞：

SQL> DROP SYNONYM PRODUCT_USER_PROFILE;

SQL> DROP TABLE PRODUCT_USER_PROFILE;

SQL> CREATE TABLE SQLPLUS_PRODUCT_PROFILE(PRODUCT VARCHAR2 (30) NOT NULL,USERID VARCHAR2 (30),ATTRIBUTE VARCHAR2 (240),SCOPE VARCHAR2 (240),NUMERIC_VALUE DECIMAL (15,2),CHAR_VALUE VARCHAR2 (240),DATE_VALUE DATE,LONG_VALUE LONG);

表已建立。

SQL> DROP VIEW PRODUCT_PRIVS;

檢視已刪除。

SQL> CREATE VIEW PRODUCT_PRIVS AS

  2   SELECT PRODUCT, USERID, ATTRIBUTE, SCOPE,

  3   NUMERIC_VALUE, CHAR_VALUE, DATE_VALUE, LONG_VALUE

  4  FROM SQLPLUS_PRODUCT_PROFILE WHERE USERID = 'PUBLIC' OR USER LIKE USERID;

檢視已建立。

SQL> GRANT SELECT ON PRODUCT_PRIVS TO PUBLIC;

授權成功。

SQL> DROP PUBLIC SYNONYM PRODUCT_PROFILE;

同義詞已刪除。

SQL> CREATE PUBLIC SYNONYM PRODUCT_PROFILE FOR SYSTEM.PRODUCT_PRIVS;

同義詞已建立。

成功。

使用SCOTT使用者登陸，問題依舊，檢查建立的表：

SQL> select * from product_user_profile;

select * from product_user_profile

              *

第 1 行出現錯誤:

ORA-00980: 同義詞轉換不再有效

 

 

SQL> select * from system.product_privs;

select * from system.product_privs

                     *

第 1 行出現錯誤:

ORA-00942: 表或檢視不存在

仔細檢查執行過的SQL語句，發現疑點：conn system/oracle as sysdba  system.product_privs;

SQL> show user

USER 為 "SYS"

問題找到，再次確定一下：

SQL> select * from sys.product_privs;

 

未選定行

確定問題，是細節失誤，在Oracle裡，system如果正常登入，它其實就是一個普通的dba使用者，但是如果以as sysdba登入，其結果實際上它是作為sys使用者登入的，這一點類似Linux裡面的sudo的感覺，從登入資訊裡面可以看出來。因此在as sysdba連線資料庫後，建立的物件實際上都是生成在sys中的。其他使用者也是一樣，如果 as sysdba登入，也是作為sys使用者登入的。

登入到system使用者：

SQL> conn system/oracle

已連線。

檢查使用者：

SQL> show user

USER 為 "SYSTEM"

這次沒有問題了，執行pupbld.sql指令碼成功後，再次使用SCOTT使用者登陸：

SQL> conn scott/tiger

已連線。

OK，問題解決！

總結：這次問題其實很簡單，只要登入system跑一下指令碼就可以了，但是卻因為習慣和不當回事的態度，在登入system使用者時順手加上了as sysdba，導致後面問題的出現，由此可見，在Oracle中，細節是非常非常重要的，來不得半點馬虎，另外一點，出現問題，要有條理的從頭來分析，不要過於發散思維，要有邏輯性，這樣會為解決問題節省很多時間的。

 

補充（以下內容部分轉載自網路）：

關於PUPBLD.SQL

我們可以分析一下PUPBLD.SQL中程式碼，知道它實際上是建立了一個表SQLPLUS_PRODUCT_PROFILE，基於此表建立檢視PRODUCT_PRIVS(包含表中所用欄位)，把檢視PRODUCT_PRIVS的SELECT許可權設定為PUBLIC，建立了檢視PRODUCT_PRIVS的同義詞PRODUCT_PROFILE，建立了表SQLPLUS_PRODUCT_PROFILE的同義詞PRODUCT_USER_PROFILE，後用建立了檢視PRODUCT_PRIVS的PUBLIC同義詞PRODUCT_USER_PROFILE 。

SQLPLUS_PRODUCT_PROFILE(基表)->PRODUCT_USER_PROFILE(同義詞)

PRODUCT_PRIVS(檢視，授權SELECT給PUBLIC)->PRODUCT_PROFILE(同義詞)

 PRODUCT_USER_PROFILE(同義詞，PUBLIC)

 

因為自己對這表、檢視和同義詞和PUBLIC，不是很瞭解。所以，先通過以下實驗來檢驗一下：

首先用普通使用者登入

SQL> conn scott/tiger

Connected.

普通使用者無法訪問SQLPLUS_PRODUCT_PROFILE

SQL> select * from system.SQLPLUS_PRODUCT_PROFILE;

select * from system.SQLPLUS_PRODUCT_PROFILE

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

 

SQL> select * from SQLPLUS_PRODUCT_PROFILE;

select * from SQLPLUS_PRODUCT_PROFILE

              *

ERROR at line 1:

ORA-00942: table or view does not exist

普通使用者可以在加模式字首的前提下訪問PRODUCT_PRIVS

SQL> select * from system.PRODUCT_PRIVS;

no rows selected

SQL> select * from PRODUCT_PRIVS;

select * from PRODUCT_PRIVS

              *

ERROR at line 1:

ORA-00942: table or view does not exist

普通使用者可以直接訪問PRODUCT_PROFILE

SQL> select * from system.PRODUCT_PROFILE;

select * from system.PRODUCT_PROFILE

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> select * from PRODUCT_PROFILE;

no rows selected

普通使用者可以直接訪問PRODUCT_USER_PROFILE

SQL> select * from PRODUCT_USER_PROFILE;

no rows selected

SQL> select * from system.PRODUCT_USER_PROFILE;

select * from system.PRODUCT_USER_PROFILE

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

基本可以得出以下結論：

1、訪問同義詞可以不用使用模式字首(理解為同義詞可以方便我們訪問)。

2、只有被授權為PUBLIC的，才可以由其他使用者訪問。