Re: proc won't compile - privilege issue?

,niz <niz_at_infidel.freeserve.co.uk> wrote:
: i have a proc, sp_moveacct, in the SLIME schema, which doesnt compile.
: when trying to compile it with the SLIME user, it gives this error:

: PROCEDURE SLIME.SP_MOVEACCT
: On line: 11
: PLS-00201: identifier 'LEMON.EXTERNAL_ID_ACCT_MAP' must be declared

: so it seems it can't see lemon.external_id_acct_map table. but it can!
: that table has select/insert/delete/update permissions to LEMONROLE
: role (a role SLIME belongs to), and the SLIME user has a private
: synonym on this table.

: i'm sure there is some obvious oracle thing i'm missing but i can't
: see what it is....

: here is the proc code:

: CREATE OR REPLACE procedure sp_MoveAcct (ExtId varchar,
: ExtIdType int,
: FromServerId varchar,
: ToServerId varchar) is
: actn number(10);
: r_ctr number;
: begin

: select account_no into actn from external_id_acct_map
: where external_id = ExtId
: and external_id_type = ExtIdType;
: commit;
: end;
: /

Grant privileges on LEMON.EXTERNAL_ID_ACCT_MAP directly to SLIME.

Grants via roles are ignored when creating/altering compiled objects eg packages, procedures, functions, views etc.

Helen

  /\/\ /\/\ /\/\ /\/\ /\/\ /\/\ /\/\ /\/\ /\/\ /\/\ /\/\  ( oo ) ( oo ) ( oo ) ( oo ) ( oo ) ( ** ) ( oo ) ( oo ) ( oo ) ( oo ) ( oo )   =^^= =^^= =^^= =^^= =^^= =^^= =^^= =^^= =^^= =^^= =^^= Received on Mon Apr 26 2004 - 09:22:29 CDT