Re: Two simple questions.

>
> Let me be the first to acknowledge that it does work ... at least in
> 10g. Can anyone confirm when this change took place?
>
> Thanks.

This is from 8.1.7.4.1 using invoker_rights procedure.

SQL> connect sys/password
Connected.
SQL> create user foo identified by foo;

User created.

SQL> create role foo_role not identified;

Role created.

SQL> create or replace procedure foo_proc   2 authid current_user
  3 as
  4 begin
  5 execute immediate 'create database link TEST connect to USER identified by PWD using '||chr(39)||'TEST'||chr(39);   6 end foo_proc;
  7 /

Procedure created.

SQL> -- Grants to foo_role except CREATE DATABASE LINK SQL> grant create session, alter session to foo_role;

Grant succeeded.

SQL> grant execute on foo_proc to foo_role;

Grant succeeded.

SQL> grant foo_role to foo;

Grant succeeded.

SQL> connect foo/foo;
Connected.
SQL> execute sys.foo_proc;
BEGIN sys.foo_proc; END;

*
ERROR at line 1:

ORA-01031: insufficient privileges
ORA-06512: at "SYS.FOO_PROC", line 5
ORA-06512: at line 1

SQL> connect sys/password
Connected.
SQL> -- Now grant CREATE DATABASE LINK to foo_role SQL> grant create database link to foo_role;

Grant succeeded.

SQL> connect foo/foo
Connected.
SQL> execute sys.foo_proc;

PL/SQL procedure successfully completed.

SQL> select object_name,object_type,status   2 from user_objects
  3 where object_type = 'DATABASE LINK';

OBJECT_NAME

------------------ -------
TEST
DATABASE LINK      VALID

Regards
/Rauf Received on Tue Oct 12 2004 - 11:26:54 CDT