dba权限用户创建视图提示无权限？

dosimple

dba权限用户创建视图提示无权限？

sys@LUBX> create user user01 identified by user01 account unlock;
User created.
sys@LUBX> grant dba to user01;
Grant succeeded.
sys@LUBX> conn user01/user01;

create or replace view ord_v
as select o.order_id,o.order_date,count(i.line_item_id) "NO_OF_ITEMS"
from oe.orders o join oe.order_items i
on (o.order_id=i.order_id)
group by o.order_id,o.order_date

ERROR at line 3:ORA-01031: insufficient privileges
这会是啥子原因？
可直接查询：
select o.order_id,o.order_date,count(i.line_item_id) "NO_OF_ITEMS"from oe.orders o join oe.order_items ion (o.order_id=i.order_id)group by o.order_id,o.order_date

dosimple

使用 sys 登录执行却可正常。

guoyJoe

dosimple 发表于 2013-4-5 14:02
使用 sys 登录执行却可正常。

conn user01/user01
select * from session_privs;

dosimple

guoyJoe 发表于 2013-4-5 14:14
conn user01/user01
select * from session_privs;

user01@LUBX> select * from session_privs;

PRIVILEGE
----------------------------------------
ALTER SYSTEM
AUDIT SYSTEM
CREATE SESSION
ALTER SESSION
RESTRICTED SESSION
CREATE TABLESPACE
ALTER TABLESPACE
MANAGE TABLESPACE
DROP TABLESPACE
UNLIMITED TABLESPACE
CREATE USER
BECOME USER
ALTER USER
DROP USER
CREATE ROLLBACK SEGMENT
ALTER ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT
CREATE TABLE
CREATE ANY TABLE
ALTER ANY TABLE
BACKUP ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
COMMENT ANY TABLE
SELECT ANY TABLE
INSERT ANY TABLE
UPDATE ANY TABLE
DELETE ANY TABLE
CREATE CLUSTER
CREATE ANY CLUSTER
ALTER ANY CLUSTER
DROP ANY CLUSTER
CREATE ANY INDEX
ALTER ANY INDEX
DROP ANY INDEX
CREATE SYNONYM
CREATE ANY SYNONYM
DROP ANY SYNONYM
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE VIEW
CREATE ANY VIEW
DROP ANY VIEW
CREATE SEQUENCE
CREATE ANY SEQUENCE
ALTER ANY SEQUENCE
DROP ANY SEQUENCE
SELECT ANY SEQUENCE
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
DROP PUBLIC DATABASE LINK
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
ALTER ANY ROLE
AUDIT ANY
ALTER DATABASE
FORCE TRANSACTION
FORCE ANY TRANSACTION
CREATE PROCEDURE
CREATE ANY PROCEDURE
ALTER ANY PROCEDURE
DROP ANY PROCEDURE
EXECUTE ANY PROCEDURE
CREATE TRIGGER
CREATE ANY TRIGGER
ALTER ANY TRIGGER
DROP ANY TRIGGER
CREATE PROFILE
ALTER PROFILE
DROP PROFILE
ALTER RESOURCE COST
ANALYZE ANY
GRANT ANY PRIVILEGE
CREATE MATERIALIZED VIEW
CREATE ANY MATERIALIZED VIEW
ALTER ANY MATERIALIZED VIEW
DROP ANY MATERIALIZED VIEW
CREATE ANY DIRECTORY
DROP ANY DIRECTORY
CREATE TYPE
CREATE ANY TYPE
ALTER ANY TYPE
DROP ANY TYPE
EXECUTE ANY TYPE
UNDER ANY TYPE
CREATE LIBRARY
CREATE ANY LIBRARY
ALTER ANY LIBRARY
DROP ANY LIBRARY
EXECUTE ANY LIBRARY
CREATE OPERATOR
CREATE ANY OPERATOR
ALTER ANY OPERATOR
DROP ANY OPERATOR
EXECUTE ANY OPERATOR
CREATE INDEXTYPE
CREATE ANY INDEXTYPE
ALTER ANY INDEXTYPE
DROP ANY INDEXTYPE
UNDER ANY VIEW
QUERY REWRITE
GLOBAL QUERY REWRITE
EXECUTE ANY INDEXTYPE
UNDER ANY TABLE
CREATE DIMENSION
CREATE ANY DIMENSION
ALTER ANY DIMENSION
DROP ANY DIMENSION
MANAGE ANY QUEUE
ENQUEUE ANY QUEUE
DEQUEUE ANY QUEUE
CREATE ANY CONTEXT
DROP ANY CONTEXT
CREATE ANY OUTLINE
ALTER ANY OUTLINE
DROP ANY OUTLINE
ADMINISTER RESOURCE MANAGER
ADMINISTER DATABASE TRIGGER
MERGE ANY VIEW
ON COMMIT REFRESH
RESUMABLE
SELECT ANY DICTIONARY
DEBUG CONNECT SESSION
DEBUG ANY PROCEDURE
FLASHBACK ANY TABLE
GRANT ANY OBJECT PRIVILEGE
CREATE EVALUATION CONTEXT
CREATE ANY EVALUATION CONTEXT
ALTER ANY EVALUATION CONTEXT
DROP ANY EVALUATION CONTEXT
EXECUTE ANY EVALUATION CONTEXT
CREATE RULE SET
CREATE ANY RULE SET
ALTER ANY RULE SET
DROP ANY RULE SET
EXECUTE ANY RULE SET
EXPORT FULL DATABASE
IMPORT FULL DATABASE
CREATE RULE
CREATE ANY RULE
ALTER ANY RULE
DROP ANY RULE
EXECUTE ANY RULE
ANALYZE ANY DICTIONARY
ADVISOR
CREATE JOB
CREATE ANY JOB
EXECUTE ANY PROGRAM
EXECUTE ANY CLASS
MANAGE SCHEDULER
SELECT ANY TRANSACTION
DROP ANY SQL PROFILE
ALTER ANY SQL PROFILE
ADMINISTER SQL TUNING SET
ADMINISTER ANY SQL TUNING SET
CREATE ANY SQL PROFILE
MANAGE FILE GROUP
MANAGE ANY FILE GROUP
READ ANY FILE GROUP
CHANGE NOTIFICATION
CREATE EXTERNAL JOB
CREATE ANY EDITION
DROP ANY EDITION
ALTER ANY EDITION
CREATE ASSEMBLY
CREATE ANY ASSEMBLY
ALTER ANY ASSEMBLY
DROP ANY ASSEMBLY
EXECUTE ANY ASSEMBLY
EXECUTE ASSEMBLY
CREATE MINING MODEL
CREATE ANY MINING MODEL
DROP ANY MINING MODEL
SELECT ANY MINING MODEL
ALTER ANY MINING MODEL
COMMENT ANY MINING MODEL
CREATE CUBE DIMENSION
ALTER ANY CUBE DIMENSION
CREATE ANY CUBE DIMENSION
DELETE ANY CUBE DIMENSION
DROP ANY CUBE DIMENSION
INSERT ANY CUBE DIMENSION
SELECT ANY CUBE DIMENSION
CREATE CUBE
ALTER ANY CUBE
CREATE ANY CUBE
DROP ANY CUBE
SELECT ANY CUBE
UPDATE ANY CUBE
CREATE MEASURE FOLDER
CREATE ANY MEASURE FOLDER
DELETE ANY MEASURE FOLDER
DROP ANY MEASURE FOLDER
INSERT ANY MEASURE FOLDER
CREATE CUBE BUILD PROCESS
CREATE ANY CUBE BUILD PROCESS
DROP ANY CUBE BUILD PROCESS
UPDATE ANY CUBE BUILD PROCESS
UPDATE ANY CUBE DIMENSION
ADMINISTER SQL MANAGEMENT OBJECT
FLASHBACK ARCHIVE ADMINISTER

202 rows selected.

比sys用户少了：
ALTER PUBLIC DATABASE LINK
ALTER DATABASE LINK
EXEMPT IDENTITY POLICY
EXEMPT ACCESS POLICY
SYSDBA
SYSOPER
我看看一个个权限赋予给user01。

guoyJoe

这样应该就可以了。。。。

guoyJoe

guoyJoe 发表于 2013-4-5 14:25
这样应该就可以了。。。。

如果还不行，单独试一下：
grant create any view to user01;

dosimple

guoyJoe 发表于 2013-4-5 14:25
这样应该就可以了。。。。

sys@LUBX> grant sysoper to user01;

Grant succeeded.

sys@LUBX> grant sysdba to user01;

Grant succeeded.

这两权限弄不到user01上去啊。

user01@LUBX> select * from session_privs;
.....
206 rows selected.

还是没法创建视图，奇怪了。

guoyJoe

或着单独建个简单的view试试看行不行
create view v_object as select * from dba_objects;

dosimple

guoyJoe 发表于 2013-4-5 14:29
或着单独建个简单的view试试看行不行
create view v_object as select * from dba_objects;

user01@LUBX> create view v_object as select * from dba_objects;
create view v_object as select * from dba_objects
                                      *
ERROR at line 1:
ORA-01031: insufficient privileges


user01@LUBX> create view v_object as select * from tab;

View created.

user01@LUBX> select * from v_object;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
V_OBJECT                       VIEW

user01@LUBX> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
V_OBJECT                       VIEW

guoyJoe

dosimple 发表于 2013-4-5 14:35
user01@LUBX> create view v_object as select * from dba_objects;
create view v_object as select *  ...

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.