RE: Default user permissions

From: Sheehan, Jeremy <JEREMY.SHEEHAN_at_nexteraenergy.com>
Date: Tue, 8 Nov 2011 11:08:03 -0500
Message-ID: <C3F905167E081B418BFC63B8668D52FF20C4704DD7_at_GOXEXVS03.fplu.fpl.com>

Did you check DBA_ROLE_PRIVS for connect and BB_STAGE? Maybe something is granted there?

Jeremy

-----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:58 AM To: Joel.Patterson_at_crowley.com; oracle-l_at_freelists.org Subject: RE: Default user permissions

Yes, I did:

select * from dba_sys_privs where grantee='BB_STAGE'

no rows selected.

GRANTEE                        GRANTED_ROLE

ADMIN_OPTION
------------------------------ ------------------------------

---

DEFAULT_ROLE

---

BB_STAGE                       CONNECT                        NO

YES         
 

1 row selected.

select * from dba_sys_privs where grantee='CONNECT'

GRANTEE                        PRIVILEGE                               


------------------------------ ----------------------------------------

ADMIN_OPTION

---

CONNECT                        CREATE SESSION                          
NO          
 

1 row selected.

select * from dba_role_privs where grantee='CONNECT'

no rows selected.

Also, public has no privileges...

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

From: Joel.Patterson_at_crowley.com [mailto:Joel.Patterson_at_crowley.com] Sent: Tuesday, November 08, 2011 10:51 AM To: Leo Drobnis; oracle-l_at_freelists.org Subject: RE: Default user permissions

Personally, I skip connect role and just grant create session; but that's besides the point. If you want minimum of permissions, why not say quota 0 on users.

Are you sure you checked dba_role_privs where grantee = 'CONNECT' and dba_sys_privs? Because what you describe should not allow bb_stage to create tables without CREATE TABLE somewhere.

Joel Patterson
Database Administrator
904 727-2546

-----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:44 AM To: ORACLE-L
Subject: Default user permissions

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 - 10:08:03 CST