Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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

2 Answers 2

up vote 3 down vote accepted

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;
share|improve this answer
    
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

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.

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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