1

There is a user as HRS. I am creating a view in HRS as

CREATE VIEW HRS.RNS.....

I am getting

ORA-01031: insufficient privileges

Edit

As i remember, i only execute following commands for the user and then my user can create tables

GRANT UNLIMITED TABLESPACE TO THE_USER;
GRANT CONNECT, RESOURCE TO THE_USER;
GRANT CREATE SESSION TO THE_USER;

How does my user automatically get rights to create table but not view?

  • 1
    Beginning in Oracle Database 10g Release 2, the CONNECT role has only the CREATE SESSION privilege (In previous versions it also contained the CREATE VIEW privilege.). For this reason, your code line GRANT CREATE SESSION TO THE_USER; is not needed. – Shaunyl Dec 27 '13 at 20:53
4

You can create tables because the RESOURCE role has the CREATE TABLE privilege granted. To create a view, you need to have granted the CREATE VIEW privilege additionally, because, by default that privilege is not granted, not even to RESOURCE.

SQL> select privilege from dba_sys_privs where grantee='RESOURCE';

PRIVILEGE
----------------------------------------
CREATE TRIGGER
CREATE SEQUENCE
CREATE TYPE
CREATE PROCEDURE
CREATE CLUSTER
CREATE OPERATOR
CREATE INDEXTYPE
CREATE TABLE

So you need to do the below:

grant create view to hrs;
  • Hmm, but i was able to drop a view. Is Drop assigned by default? How can i check? – bjan Dec 27 '13 at 12:51
  • 2
    A user does not need extra privileges for deleting owned objects, so one can drop already existing views, tables, etc. even when not able to create new ones. – Balazs Papp Dec 27 '13 at 13:06
1

By "default" you can only select data from tables and views in your schema. To create different objects you need appropriate privileges granted.

GRANT CREATE TABLE to hrs;
GRANT CREATE VIEW to hrs;

and so on.

  • I have modified the question, please see – bjan Dec 27 '13 at 12:24
  • Maybe select stmt in your view is using tables from schemes which are not available for this particular user. – Kuba Miazek Dec 27 '13 at 12:38
  • There is another schema involved and rights to select from this schema to the user are already given. Furthermore, select query to create view is executing fine but the error is given when actual view is being created – bjan Dec 27 '13 at 12:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.