RE: Default user permissions
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <Richard.Goulet@xxxxxxxxxxx>, <sfaroult@xxxxxxxxxxxx>, <leo.drobnis@xxxxxxxxxxxxxxx>
- Date: Wed, 9 Nov 2011 13:28:53 -0500
I'm glad to see (not surprisingly) that I am not alone.
I believe I created this system (sounds bigger than it is) around 1995 (oracle
7), or as soon after as I realized that I needed better control over these
'default' recommendations, (especially the culprit RESOURCE). I believe just
about every DBA did something similar, and for the same reasons.
The other great third party vendor recommendations is to grant DBA, (or
actually both at same time, thus giving the DBA an immediate 'ah oh, somebody
doesn't know oracle moment'. Third parties want their product to seem easy to
install, maintain and use, and appear to work out of the box: -- security and
safety not a concern for them, selling the product is the goal. 'God' rights
work pretty good for that, and last long enough to settle it.
16 years later, nothing has changed (a generality).
SQLServer has its analogous recommendations; parties request sa or dbo.
Best Regards,
Joel Patterson
Database Administrator
904 727-2546
-----Original Message-----
From: Goulet, Richard [mailto:Richard.Goulet@xxxxxxxxxxx]
Sent: Wednesday, November 09, 2011 10:27 AM
To: Patterson, Joel; sfaroult@xxxxxxxxxxxx; leo.drobnis@xxxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Default user permissions
Been doing that for the last few years, ever since roles were introduced.
Richard Goulet
Senior Oracle DBA/Na Team Leader
-----Original Message-----
From: Joel.Patterson@xxxxxxxxxxx [mailto:Joel.Patterson@xxxxxxxxxxx]
Sent: Wednesday, November 09, 2011 8:06 AM
To: Goulet, Richard; sfaroult@xxxxxxxxxxxx; leo.drobnis@xxxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Default user permissions
I create a developer role that combines what used to be in CONNECT and what is
in RESOURCE without UNLIMITED TABLESPACE.
I always grant CREATE SESSION and do not use CONNECT -- as is seems silly to
have a role with one privilege -- at least in this case.
So grant CREATE SESSION and the role and your pretty much have your generic
start, and easier to administrate.
Joel Patterson
Database Administrator
904 727-2546
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Goulet, Richard
Sent: Tuesday, November 08, 2011 11:29 AM
To: sfaroult@xxxxxxxxxxxx; leo.drobnis@xxxxxxxxxxxxxxx
Cc: ORACLE-L
Subject: RE: Default user permissions
Stephen,
Nope, that was dropped back in 9i. However the unlimited tablespace
option is still attached to resource which I wish Oracle would drop.
Richard Goulet
Senior Oracle DBA/Na Team Leader
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Stephane Faroult
Sent: Tuesday, November 08, 2011 11:09 AM
To: Leo.Drobnis@xxxxxxxxxxxxxxx
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???
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
