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
  • 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, 2013 at 20:53

2 Answers 2

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;
2
  • Hmm, but i was able to drop a view. Is Drop assigned by default? How can i check?
    – bjan
    Dec 27, 2013 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. Dec 27, 2013 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.

3
  • I have modified the question, please see
    – bjan
    Dec 27, 2013 at 12:24
  • Maybe select stmt in your view is using tables from schemes which are not available for this particular user. Dec 27, 2013 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, 2013 at 12:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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