[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Difference between DBA,CONNECT,RESOURCE roles in Oracle

Posted on 2005-04-27
Medium Priority
Last Modified: 2008-03-04
Discuss how DBA,CONNECT,RESOURCE roles differ in Oracle?
  • 2
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13881077

Accepted Solution

helpneed earned 2000 total points
ID: 13883526

Oracle provides several roles that are built into the database. Some of them are DBA, RESOURCE, and CONNECT. Most DBAs use them to make their tasks easier and simpler, but each of them is a security nightmare.

Let's examine RESOURCE. This is generally given to schema owners. Did you know that it has UNLIMITED TABLESPACE system privilege, making it able to create any table anywhere in the database – including the SYSTEM tablespace? Obviously, this is not what you want. You would want to restrict the tablespaces to specific users only.  

Similarly the role CONNECT, by default, has the CREATE TABLE/SEQUENCE/SYNONYM and a few more options. The name CONNECT somehow conveys the impression of the ability to connect only, not anything else. As you can see, however, the ability is much more than that. Another privilege, ALTER SESSION system privilege, allows the grantee to issue sql_trace = TRUE in their session. This can have far reaching consequences.  

Therefore, it is not prudent to use built-in roles. Rather, identify the privileges users will need, put them in the appropriate roles which you have created, and use them to control authorization.

If possible, try not to use the Oracle built-in roles like RESOURCE and CONNECT. Create your own roles.


Expert Comment

ID: 13883531

connect, resource, dba
These might not be created anymore in future versions of Oracle.
Oracle 9.2 grants create session, alter session, create synonym, create view, create database link, create table, create cluster and create sequence to connect.
It also grants create table, create cluster, create sequence, create trigger, create procedure, create type, create indextype and create operator to resource.
The role dba gets basically everything and that with admin option.


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month19 days, 14 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question