DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: RESOURCE Role

  1. #1
    Join Date
    Nov 2000
    Posts
    416
    What is exactly predefine RESOURCE Role ? Looks like CONNECT is enough. Backward compatibily ?

    An ounce of prevention is worth a pound of cure

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    If you query dba_role_privs, this will tell you the difference.
    Jeff Hunter

  3. #3
    Join Date
    Nov 2000
    Posts
    416
    My question is what RESOURCE role add up to user authorities which CONNECT doesn't ?
    An ounce of prevention is worth a pound of cure

  4. #4
    Join Date
    Apr 2001
    Posts
    112
    DBA_ROLE_PRIVS shows which users have been granted what roles ; it doesnt show the privileges of the roles. To see that query the ROLE_SYS_PRIVS & you can figure the difference.

    Tansdot

  5. #5
    Join Date
    Jul 2000
    Posts
    243
    Hi

    resource grants the user:
    create cluster, create indextype,
    create operator, create
    procedure, create sequence,
    create table, create trigger,
    create type.

    session grant the use:

    alter session, create cluster,
    create database link, create
    sequence, create session, create
    synonym, create table, create
    view.

    for more reading look at chapter 23 "Managing User Privileges and Roles" in Administrator’s Guide

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by tansdot
    DBA_ROLE_PRIVS shows which users have been granted what roles ; it doesnt show the privileges of the roles. To see that query the ROLE_SYS_PRIVS & you can figure the difference.

    Tansdot
    My mistake, it's dba_sys_privs and not dba_role_privs...
    Jeff Hunter

  7. #7
    Join Date
    Nov 2000
    Posts
    416
    ROLE_SYS_PRIVS is the correct DD table but to my surprise it has only explanation of this roles :
    DBA
    EXP_FULL_DATABASE
    IMP_FULL_DATABASE

    No row for CONNECT and RESOURCE ??? Why ?? Try it.

    An ounce of prevention is worth a pound of cure

  8. #8
    Join Date
    Apr 2001
    Posts
    112
    Thats because the user that you are connected as, in your case as a DBA ,doesnt have those roles ,connect & resource , assigned to them . Try issuing the same query from a different user.

    Tansdot

  9. #9
    Join Date
    Nov 2000
    Posts
    416
    Yup, you are right. Thanks
    An ounce of prevention is worth a pound of cure

  10. #10
    Join Date
    Nov 2000
    Posts
    344
    One little sneaky thing to be aware of when granting RESOURCE that does not happen when you grant CONNECT -

    When you grant RESOURCE to a user, Oracle automatically
    also grants UNLIMITED TABLESPACE to that user. So if you don't want to be granting UNLIMITED TABLESPACE, be sure to revoke it after you grand RESOURCE. (The same thing happens when you grant DBA to a user).

    -John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width