RESOURCE Role

[farrokhp]

What is exactly predefine RESOURCE Role ? Looks like CONNECT is enough. Backward compatibily ?

[marist89]

If you query dba_role_privs, this will tell you the difference.

[farrokhp]

My question is what RESOURCE role add up to user authorities which CONNECT doesn't ?

[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

[shawish_sababa]

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

[marist89]

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...

[farrokhp]

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.

[tansdot]

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

[farrokhp]

Yup, you are right. Thanks

[jdorlon]

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