Difference between DBA,CONNECT,RESOURCE roles in Oracle

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

    Expert Comment

    by:Anthony Perkins
    LVL 5

    Accepted Solution


    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.

    LVL 5

    Expert Comment


    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    Oracle SQL Sub Query 5 40
    SAP and ODBC links 2 64
    percentage remaining 6 28
    CREATE TABLE syntax 4 31
    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now