Privs thru Roles (was Re: sys.dbms_pipe & triggers)

Thomas J Kyte <tkyte_at_us.oracle.com> wrote:
>tcox_at_netcom.com (Thomas B. Cox) wrote:
>> <bevans_at_pinpoint.avl.com> wrote:
 

>>>I can execute one of these functions, so I know the
>>>sys.dbms_pipe package is installed:
 

>> I'm surprised this worked.
>I'm not, it's correct syntax
 

>> You have a syntax error.
>No it isn't, you can qualify owner.package.routine in pl/sql

Tom (the other one, not me) is right on this one. When I had this problem last, I put in two "fixes" simultaneously: granted the execute permission and stopped putting the 'sys.' on the front.

Only one of these -- the grant of Execute -- was needed. To quote Homer Simposon: D'oh!

>What the real problem is is in permissions. In order to stored a
 compile object
>(view, procedure, function, package, trigger) you must have priveleges
 granted
>directly to you.

In my 7.2.2 instance (which I should have checked before posting the first time), the only permission on DBMS_OUTPUT is a grant of EXECUTE by SYS to PUBLIC.

Permissions on DBMS_OUTPUT may be granted through a role. See the output at the end of this posting.

There are some things you have to have granted to you directly -- i.e. you can't get them through a role and have things work correctly -- but EXECUTE isn't one of them, at least on DBMS_OUTPUT. I can't recall what database operations have this limitation (and I'm done guessing, at least for this thread).

>grant execute on dbms_pipe to USERNAME
>when logged in as sys and your trigger will compile.

Yup.

Here is a demo. I've edited it slightly (removed white space, hidden passwords, etc.)

	SQL*Plus: Release 3.2.2.0.1
	Oracle7 Server Release 7.2.2.4.0 
	PL/SQL Release 2.2.2.3.1 

SQL> connect scott/****
Connected.
SQL> select *
  2 from all_tab_privs
  3 where table_name like 'DBMS_P%';

GRANTOR GRANTEE TABLE_SCHEMA TABLE_NAME PRIVILEGE GRA --------- --------- ------------ ------------- ---------- --- SYS PUBLIC SYS DBMS_OUTPUT EXECUTE NO SQL> connect sys/******
Connected.
SQL> revoke execute on dbms_output from public ; Revoke succeeded.

SQL> connect scott/****
Connected.
SQL> show serveroutput
serveroutput OFF
SQL> set serveroutput on
ERROR:

ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_OUTPUT' must be declared
ORA-06550: line 1, column 7:

SQL> connect sys/******
Connected.
SQL> create role foobar ;
Role created.

SQL> grant foobar to scott ;
Grant succeeded.

SQL> grant execute on dbms_output to foobar ; Grant succeeded.

SQL> connect scott/****
Connected.
SQL> set serveroutput on
SQL> begin
  2 sys.dbms_output.put_line ( 'Foo.' ) ;   3 end ;
  4 /
Foo.
PL/SQL procedure successfully completed.

-- 
Thomas B. Cox   <tcox_at_netcom.com>  TrueNorth Consulting, Inc.

Author of _Oracle Workgroup Server Handbook_, ISBN 0-07-881186-4
          Osborne/McGraw-Hill, 1995, Berkeley, CA.