Hello All,

I have a question about "grant resource to user".
Usually I use below SQL to create schema.

create user USER_NAME identified by PASSWOPRD default tablespace TABLESPACE_NAME temporary tablespace TEMP quota unlimited on TABLESPACE_NAME;
grant resource to USER_NAME;
grant connect to USER_NAME;

Recently I realized "grant resource to user" impliedly gives UNLIMITED TABLESPACE to user. Means user has write permisson to other tablespaces. This is not what I intend. To avoid this, I take another way.
SQL> "grant resource to user";
SQL> "revoke unlimited tablespace from user";
SQL> "alter user username quota unlimited on tablespace";
the tablespace here is the one to that schema can only access to.

Can you please confirm if this way is proper or not to meet the requirement? Or is there any potential problem?

Thanks and regards,
Alfa

Better way would be just following Oracle recommendation and stop using CONNECT and RESOURCE Oracle-defined roles - they will be deprecated. Either create your own roles or GRANT all required privileges from the underlying ones (CREATE SESSION, CREATE TABLE, ...) directly. Also note the change of CONNECT role content in 10gR2.
Here is an article about those roles in 11gR2: http://docs.oracle.com/cd/E11882_01/...s.htm#CHDGIEJE