Whats Wrong With Invoker Rights

hi alls
i am new to this group and found it very valuable.Its my first post here i go ,thanx for alls in advance.

My first question is When i can create synonym from SQL why not from procedure??

SQL> CREATE SYNONYM t_syn FOR dept;

Synonym created.

SQL> create or replace procedure cpr (p in varchar2)   2 as
  3 begin
  4 execute immediate 'create synonym syn_'||p||' for a';   5 end;
  6 .
SQL> / Procedure created.

SQL> execute cpr('A');
BEGIN cpr('A'); END;

*
ERROR at line 1:

ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.CPR", line 4
ORA-06512: at line 1

Second question is when i use invoker's right AUTHID CURRENT_USER it executes why ???

SQL> create or replace procedure cpr (p in varchar2)   2 AUTHID CURRENT_USER
  3 as
  4 begin
  5 execute immediate 'create synonym syn_'||p||' for a';   6 end;
  7 .
SQL> / Procedure created.

SQL> execute cpr('A');

PL/SQL procedure successfully completed.

My third question is when i Grant CREATE SYNONYM right myself i mean scott himself gives the CREATE SYNONOYM privilege it code executes why ???

SQL> GRANT CREATE SYNONYM TO scott;

Grant succeeded.

SQL> create or replace procedure cpr (p in varchar2)   2 as
  3 begin
  4 execute immediate 'create synonym syn_'||p||' for a';   5 end;
  6 .
SQL> / Procedure created.

SQL> execute cpr('B');

PL/SQL procedure successfully completed.

Thanx alls i hope you alls will get my query its puzzling please bear it :)

Khurram Received on Tue Apr 04 2006 - 04:50:00 CDT