If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > Database Server Software > Oracle > "Grant Resource to user" problem

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Dec 2011
Posts: 4
"Grant Resource to user" problem

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,
Reply With Quote
  #2 (permalink)  
Registered User
Join Date: Mar 2007
Posts: 615
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
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On