My name is Tal Olier and I serve as a database expert.
I have found a strange behavior I am not familiar with and would greatly appreciate if you can shed some light on it.
I use the following scenario on both 22.214.171.124 server and 10.2.0.1 server:
create user otal_resource identified by otal_resource default tablespace otal;
grant resource to otal;
grant CREATE SESSION to otal_resource;
SQL> create table t1(c1 number);
SQL> create view v1 as select * from t1;
create view v1 as select * from t1
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> create index t1_ix1 on t1(c1);
I also get:
select * from dba_sys_PRIVS where grantee like 'RESOURCE' order by privilege
/* 9.2 and 10.2:
=> no CREATE VIEW (In 9i it belongs to the CONNECT role and in 10g I do not find it anywhere…)
My questions are:
Is that a bug that create view is not included in the RESOURCE role?
How is that possible that I am able to create an index without having the CREATE INDEX privilege?
This is what I can tell about the issue (which still doesn't mean that this is all true):
Both CONNECT and RESOURCE roles have lost their functionality and are here just for backwards compatibility, and will be deprecated in future Oracle versions (check this link). As of today, CONNECT has only one privilege: CREATE SESSION.
Therefore, to be able to grant all necessary (or, should I rather say, old connect and resource) privileges, you'll have to create your own role(s) and grant it/them to newly created user. Those privileges were:
As of your CREATE INDEX question, I believe that it is implicitly granted along with the CREATE TABLE privilege because you do need a table to create an index, right? BTW, "CREATE INDEX" privilege doesn't exist.