Today one of our developer came to me and informed me that he is getting below error:
ERROR at line 1:
ORA-01031: insufficient privileges
He could select the table from the user where he wanted to create the view he also informed me that the user had given DBA privilege.
I was wondering what could be the reason ?
If any user has DBA privilege it will get both SELECT ANY TABLE & CREATE ANY VIEW privileges.
So there is no way that the view creation would fail.
Then I had simulated the whole process as below:
SQL> conn / as sysdba
Connected.
SQL>
SQL> create user user1 identified by user1;
User created.
SQL> grant create session to user1;
Grant succeeded.
SQL> grant create table to user1;
Grant succeeded.
SQL> alter user user1 quota 5M on users;
User altered.
SQL> create user dbauser identified by dbauser;
User created.
SQL> conn user1
Enter password:
Connected.
SQL> create table tbl(names varchar2(10)) tablespace users;
Table created.
SQL> insert into tbl values('aaa');
1 row created.
SQL> insert into tbl values('bbb');
1 row created.
SQL> insert into tbl values('ccc');
1 row created.
SQL> select * from tbl;
NAMES
----------
aaa
bbb
ccc
SQL> conn / as sysdba
Connected.
SQL> grant dba to dbauser;
Grant succeeded.
SQL> conn dbauser
Enter password:
Connected.
SQL> select * from user1.tbl;
NAMES
----------
aaa
bbb
ccc
SQL> create view usertbl as select * from user1.tbl;
create view usertbl as select * from user1.tbl
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> grant select any table to dbauser;
Grant succeeded.
SQL> conn dbauser
Enter password:
Connected.
SQL> create view usertbl as select * from user1.tbl;
View created.
SQL>
(after revoking SELECT ANY TABLE, i tried with 'GRANT SELECT ON USER1.TBL TO DBAUSER' it allowed DBAUSER to create view)
Above was surprising to me. Then searching the web I found below:
"In order to create a view in a schema, that schema must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The view owner must be granted these privileges directly, rather than through a role. The reason is that privileges granted to roles cannot be inherited via objects."
