Re: Questions about Roles and Privileges and their management

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Mon, 21 Sep 1998 20:21:37 GMT
Message-ID: <3606b04c.23162716@dcsun4.us.oracle.com>

On Mon, 21 Sep 1998 15:45:37 -0500, roberts_D_at_bms.com (Dan Roberts) wrote:

>Hi All
>
>
>How do I figure out which privileges have been granted to a role??
>

use the query ...

select granted_role privilege
  from sys.dba_role_privs
 where grantee = 'AROLE'
 union
select privilege || ' on ' || decode(owner,null,null,owner||'.') ||

       table_name privilege
  from sys.dba_tab_privs
 where grantee = 'AROLE'

eg.

SQL> create role arole;
Role created.

SQL> grant connect, resource to arole;
Grant succeeded.

SQL> grant select, update on foo to arole; Grant succeeded.

SQL> grant execute on bar to arole;
Grant succeeded.

SQL> select granted_role privilege
  2 from sys.dba_role_privs
  3 where grantee = 'AROLE'
  4 union
  5 select privilege||' on '||decode(owner,null,null,owner||'.') ||   6 table_name privilege
  7 from sys.dba_tab_privs
  8 where grantee = 'AROLE'
  9 /

PRIVILEGE

---

CONNECT
EXECUTE on CLBECK.BAR
RESOURCE
SELECT on CLBECK.FOO
UPDATE on CLBECK.FOO

>Which DBA table would show me such information..
>

sys.dba_role_privs
sys.dba_tab_privs

>Finnally, I would like to create a role which is just short of a DBA..
>Which privileges should I assign to such a role..
>
>I would expect this new role to import/export, create and drop tables,
>make triggers and procedures and etc...also I would like the user to be
>able to load external
>data via PERL and the PERL DBI/DBD modules..
>
>Thanks for the help!!.>>DAn
Received on Mon Sep 21 1998 - 15:21:37 CDT