奇怪的ORA-01031: insufficient privileges
对某个视图赋予select权限之后，还是不能访问，查询时报错：ORA-01031: insufficient privileges具体情况是现在有2个用户：NPBSPROD_AFFINIUM和NPBSPROD_DATA
视图的定义：
create or replace view NPBSPROD_AFFINIUM.VW_OPT_SCORE_CALCULATOR as
SELECT MMV_ID,
ss.BUSINESS_STRATEGY,
bs.WEIGHT AS BUSINESS_STRATEGY_SCORE,
ct.CAMPAIGN_TYPE,
ct.WEIGHT AS CAMPAIGN_TYPE_SCORE,
ss.MEMBER_SEGMENT,
ss.SEGMENT_STRATEGY_SCORE,
(ss.SEGMENT_STRATEGY_SCORE * bs.WEIGHT * ct.WEIGHT)
AS DA_OPTIMISE_SCORE
FROM NPBSPROD_DATA.MEMBER m
INNER JOIN NPBSPROD_AFFINIUM.OPT_SEGMENT_STRATEGY_SCORE ss
ON NVL (m.SEGMENT, 'UNKNOWN') = ss.MEMBER_SEGMENT
CROSS JOIN NPBSPROD_AFFINIUM.OPT_CAMPAIGN_TYPE_SCORE ct
CROSS JOIN NPBSPROD_AFFINIUM.OPT_BUSINESS_STRATEGY_SCORE bs
WHERE ss.BUSINESS_STRATEGY = bs.BUSINESS_STRATEGY
相应的权限：
SQL> select grantee from dba_tab_privs where table_name='OPT_SEGMENT_STRATEGY_SCORE' and owner ='NPBSPROD_AFFINIUM';
GRANTEE
------------------------------
NPBS_RONLY
NPBSPROD_DATA
SQL> select grantee from dba_tab_privs where table_name='OPT_CAMPAIGN_TYPE_SCORE' and owner ='NPBSPROD_AFFINIUM';
GRANTEE
------------------------------
NPBS_RONLY
NPBSPROD_DATA
SQL> select grantee from dba_tab_privs where table_name='OPT_BUSINESS_STRATEGY_SCORE' and owner ='NPBSPROD_AFFINIUM';
GRANTEE
------------------------------
NPBS_RONLY
NPBSPROD_DATA
然后将 NPBSPROD_AFFINIUM.VW_OPT_SCORE_CALCULATOR 的select权限给NPBSPROD_DATA
SQL> conn / as sysdba
Connected.
Session altered.
SQL> grant select on NPBSPROD_AFFINIUM.VW_OPT_SCORE_CALCULATOR to npbsprod_data;
Grant succeeded.
然后登陆查询：
SQL> select count(*) from NPBSPROD_AFFINIUM.VW_OPT_SCORE_CALCULATOR;
select count(*) from NPBSPROD_AFFINIUM.VW_OPT_SCORE_CALCULATOR
*
ERROR at line 1:
ORA-01031: insufficient privileges
但是可以desc：
SQL> desc NPBSPROD_AFFINIUM.VW_OPT_SCORE_CALCULATOR
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
CUSTOMERID VARCHAR2(20)
BUSINESS_STRATEGY VARCHAR2(30)
BUSINESS_STRATEGY_SCORE NUMBER
CAMPAIGN_TYPE VARCHAR2(30)
CAMPAIGN_TYPE_SCORE NUMBER
MEMBER_SEGMENT VARCHAR2(50)
SEGMENT_STRATEGY_SCORE NUMBER
DA_OPTIMISE_SCORE
全是是在sys下给的，在 NPBSPROD_AFFINIUM用户下给视图权限的会报错，应该是赋予表权限的时候没加WITH GRANT OPTION。
SQL> grant select on NPBSPROD_AFFINIUM.VW_OPT_SCORE_CALCULATOR to NPBSPROD_DATA;
grant select on NPBSPROD_AFFINIUM.VW_OPT_SCORE_CALCULATOR to NPBSPROD_DATA
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'NPBSPROD_DATA.MEMBER'
最后我是给了select any table 的权限让应用跑下去了。。。
难道是因为视图的基表没加WITH GRANT OPTION的原因才导致这个问题吗，但是用sys明明显示已经成功了啊。
求指教。
谢谢
页:
[1]