Privs thru Roles (was Re: sys.dbms_pipe & triggers)
Date: 1996/03/29
Message-ID: <tcoxDp0Gqr.Fw7_at_netcom.com>#1/1
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.
>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:
PL/SQL: Statement ignored
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.Received on Fri Mar 29 1996 - 00:00:00 CET