Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Om.

Asked: January 28, 2003 - 8:09 am UTC

Last updated: November 01, 2017 - 2:47 am UTC

Version: 8.1.7

Viewed 50K+ times! This question is

You Asked

Hi Tom,

I read your book and a article and read this quote where you have quoted that

"connect,resource and DBA should not be used in a system for security reasons".

Could you please elaborate on this.As in our project to perform dba
role we do not make use of any other administrative tool and mostly
we work with sql*plus to carry out our tasks and altough the end users make use of front end tools to access database in most of the applications.

How can this statement is valid for our database.

If you can please elaborate and give us some tips and expalin how it's not safe to use these roles.

Many thanks in advance.

Best regards,
Om

and Connor said...

look at what connect conveys:

ops$tkyte@ORA920> select * from dba_sys_privs where grantee='CONNECT';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE VIEW NO
CONNECT CREATE TABLE NO
CONNECT ALTER SESSION NO
CONNECT CREATE CLUSTER NO
CONNECT CREATE SESSION NO
CONNECT CREATE SYNONYM NO
CONNECT CREATE SEQUENCE NO
CONNECT CREATE DATABASE LINK NO

8 rows selected.



create table? create view? why all of that -- CREATE SESSION should be more then sufficient for a normal user.


How about resource then?
see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:853427230099 <code>
for user_role_hierarchy



sys@ORA920> drop user a cascade;
User dropped.

sys@ORA920> grant create session, resource to a identified by a;
Grant succeeded.

sys@ORA920> grant select on dba_sys_privs to a;
Grant succeeded.

sys@ORA920> @connect a/a

a@ORA920> select distinct PRIVILEGE
2 from dba_sys_privs
3 where grantee in ( select * from sys.user_role_hierarchy )
4 /

PRIVILEGE
----------------------------------------
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
UNLIMITED TABLESPACE

10 rows selected.

unlimited tablespace? do you want joe or josephine develop to really create junk in SYSTEM at will? (granting resource ALWAYS conveys unlimited tablespace)


Did you even know what grants you were giving with connect and resource?


And how about your DBA's. Are all DBA's created equal? No, they are not. Do you even know what DBA conveys? How much power that is? do they all need that? No -- they don't *need* most of it. The concepts of least privs (giving each person the LEAST privs they need) to do their job is always more secure then giving them the keys to the kingdom.

UPDATE 2017-09-17

As of 10.2 the connect role only contains CREATE SESSION.

In 12.1 this expanded to include SET CONTAINER.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Harold, January 28, 2003 - 4:15 pm UTC

If you only give CONNECT (only this role) but don't give users quotas?? they cannot create any objects 'cause they will not have privileges.

Harold

Tom Kyte
January 28, 2003 - 4:30 pm UTC

they can create these:

CONNECT CREATE VIEW NO
CONNECT ALTER SESSION NO
CONNECT CREATE SYNONYM NO
CONNECT CREATE SEQUENCE NO
CONNECT CREATE DATABASE LINK NO

do you want them to?

did you even know they CAN? (many people do not)

and most importantly -- are they supposed to? does your system require they have this? If not, why are you granting it to them?

that is my point -- it is called "least privileges". If you have just enough -- not one more, not one less privilege then you need, you are doing just great. Too many -- problems can arise.

You want to see me fill up your user|background_dump_destination in a psuedo denial of service? grant me connect. don't want me to be able to do that, even accidently (unintentionally) -- grant me just create session and whatever application roles I need.



Example

RobH, September 26, 2006 - 3:16 pm UTC 

I still find this bizarre, but here's an example.  How exactly is the Unlimited Tablespace granted?

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

SQL> select * from dba_sys_privs where grantee = 'RESOURCE';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE TABLE                             NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE PROCEDURE                         NO

8 rows selected.

SQL> select * from dba_sys_privs where PRIVILEGE = 'UNLIMITED TABLESPACE';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
SYS                            UNLIMITED TABLESPACE                     YES
OUTLN                          UNLIMITED TABLESPACE                     NO
SYSTEM                         UNLIMITED TABLESPACE                     YES

3 rows selected.

SQL> create user test identified by test;

User created.

SQL> select * from dba_sys_privs where grantee = 'TEST';

no rows selected

SQL> select * from dba_role_privs where grantee = 'TEST';

no rows selected

SQL> grant resource to test;

Grant succeeded.

SQL> select * from dba_sys_privs where grantee = 'TEST';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
TEST                           UNLIMITED TABLESPACE                     NO

1 row selected.

SQL> select * from dba_role_privs where grantee = 'TEST';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
TEST                           RESOURCE                       NO  YES

1 row selected.

SQL> drop user test;

User dropped.

Tom Kyte
September 26, 2006 - 5:11 pm UTC

It is so documented that when you grant DBA or RESOURCE, the unlimited tablespace privilege (which CANNOT be granted to a role) is granted to the user as well.

It is just the way it works.

RESOURCE and UNLIMITED TABLESPACE

Parthiban Nagarajan, June 10, 2012 - 11:47 am UTC

Hi Tom

RESOURCE is a role
UNLIMITED TABLESPACE cannot be granted to a role

So, why the privilege is always attached to RESOURCE?

Behind the scenes, does Oracle execute a weird procedure like the one below when we just ask for RESOURCE role?
procedure grant_RESOURCE(pi_user_cd)
is
begin
execute immediate 'grant UNLIMITED TABLESPACE to pi_user_cd';
execute immediate 'grant RESOURCE to pi_user_cd';
end grant_RESOURCE;

Tom Kyte
June 11, 2012 - 3:47 am UTC

It is hard coded into the definition of that specific role.

the recommendation going forward is to not use connect or resource - but rather create your own roles.

and avoid things like "unlimited tablespace"

Resource role no more grant UNLIMITED TABLESPACE

Kishor, March 19, 2014 - 11:04 am UTC

I don't know if I should comment here, still wanted to convey on unlimited quota privilege in Oracle Database 12c.

Resource role no more grant UNLIMITED TABLESPACE privilege and it has to be explicitly granted to needed users.

Have a look at link http://docs.oracle.com/cd/E16655_01/network.121/e17607/release_changes.htm#DBSEG941

Seems the Info here is indeed outdated

Martin Ba, September 15, 2017 - 8:58 am UTC

Wrt CONNECT:

Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

select * from dba_sys_privs where grantee='CONNECT';

GRANTEE PRIVILEGE                                ADM COM INH
------- ---------------------------------------- --- --- ---
CONNECT SET CONTAINER                            NO  YES YES
CONNECT CREATE SESSION                           NO  YES YES



Chris Saxon
September 15, 2017 - 2:28 pm UTC

Good point, I've added an update to the original answer.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.