RE: Default user permissions

From: Don Granaman <DonGranaman_at_solutionary.com>
Date: Tue, 8 Nov 2011 11:23:38 -0600
Message-ID: <FD98CB0EE75EEA438CAF4DA2E6071C420EAD4F8EA4_at_MAIL.solutionary.com>



Yes. If he can create them, he can drop them. There is no simple declarative way to restrict a user's privilege on his own objects.

Don Granaman | Phone: 402-361-3073 | Cell: 402-960-6955 | Fax: 402-361-3173 | Solutionary | Relevant . Intelligent . Security

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Leo Drobnis Sent: Tuesday, November 08, 2011 10:33 AM To: Stephane Faroult
Cc: ORACLE-L
Subject: RE: Default user permissions

No difference.  

On the other hand, can a user drop tables in his own schema without the drop table privilege?  


From: Stephane Faroult [mailto:sfaroult_at_roughsea.com] Sent: Tuesday, November 08, 2011 11:09 AM To: Leo Drobnis
Cc: ORACLE-L
Subject: Re: Default user permissions  

It comes from role CONNECT, and the reason is compatibility with Oracle 5, when CONNECT was a privilege and not a role (roles and privileges were introduced with Oracle 6).
Actually, it comes from the combination of CONNECT (which grants CREATE TABLE) with the unlimited quota (which gives the "physical possibility" of using the system privilege).
Grant CREATE SESSION instead of CONNECT. No need for quotas.

Oh, and RESOURCE is even worse ....

HTH
--

Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>

On 11/08/2011 04:44 PM, Leo Drobnis wrote:

I am a bit puzzled, maybe I am getting rusty.    

I need to create a user with bare minimum permissions:      

CREATE USER bb_stage  

IDENTIFIED BY "password"  

DEFAULT TABLESPACE users  

TEMPORARY TABLESPACE TEMP;   GRANT CONNECT TO bb_stage;  

ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";      

Connect role only has create session.  

Public has no privileges.      

However the newly created user can create and drop tables.      

I am trying to find where it's coming from.      

Any idea???    

--

http://www.freelists.org/webpage/oracle-l        

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue Nov 08 2011 - 11:23:38 CST

Original text of this message