ITPub博客

首页 > 数据库 > Oracle > 忽视细节导致简单问题的复杂化 关于PUPBLD.SQL

忽视细节导致简单问题的复杂化 关于PUPBLD.SQL

原创 Oracle 作者:spectre2 时间:2011-04-01 23:08:22 0 删除 编辑

简单问题的出现:

使用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_PRIVSSELECT权限设置为PUBLIC,创建了视图PRODUCT_PRIVS的同义词PRODUCT_PROFILE,创建了表SQLPLUS_PRODUCT_PROFILE的同义词PRODUCT_USER_PROFILE,后用创建了视图PRODUCT_PRIVSPUBLIC同义词PRODUCT_USER_PROFILE

SQLPLUS_PRODUCT_PROFILE(基表)->PRODUCT_USER_PROFILE(同义词)

PRODUCT_PRIVS(视图,授权SELECTPUBLIC)->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的,才可以由其他用户访问。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14184018/viewspace-691502/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-02-27

  • 博文量
    196
  • 访问量
    1848209