Re: Whats Wrong With Invoker Rights

Khurram wrote:
> 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

*PLEASE* don't assume your queries are unique. In fact it is being asked almost every day, by persons which don't, like you, search the archives prior to posting.
*PLEASE* search the archives, *ALWAYS*

As to your questions
1 roles are ignored during compilation of a stored procedure, as roles are volatile.
Documented issue and policy.
You have privilege through a role, not direct privilege. 2 With authid current_user, privileges are checked at runtime. Please read the docs!
3 See question 1. You can workaround that by getting direct privilege.

Also creating synonyms on the fly using stored procedures will likely result in a mess.
For performance reasons it is also best not to use synonyms.

Question for you: why didn't you search the archives? People here aren't getting paid per answer, in fact they aren't paid at all.

-- 
Sybrand Bakker
Senior Oracle DBA