Home » SQL & PL/SQL » SQL & PL/SQL » Procedure(Authid)
Procedure(Authid) [message #225145] Sun, 18 March 2007 08:07 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

I created a procedure with DEFINER rights

SQL>conn rit/t

Create Or Replace Procedure proc
AS
Begin
Execute Immediate 'Create Table A(A Varchar2(100))';
End;

Procedure created.

SQL>exec proc

It said

BEGIN PROC; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "RIT.PROC", line 3
ORA-06512: at line 1

SQL>conn system/manager

SQL>Grant create table to rit;

SQL>conn rit/t

Then i executed proc

SQL>exec proc

PL/SQL procedure successfully completed.

SQL>conn system/manager

SQL>Revoke create table to rit;

SQL>conn rit/t

SQL>Create Or Replace Procedure proc
Authid CURRENT_USER
AS
Begin
Execute Immediate 'Create Table A(A Varchar2(100))';
End;
/

Procedure created.

Then i excuted it

SQL> EXEC PROC
BEGIN PROC; END;

*
ERROR at line 1:
ORA-00955: name is already used by an existing object
ORA-06512: at "RIT.PROC", line 5
ORA-06512: at line 1

Why it said like this?

SQL> DROP TABLE A;

Table dropped.

Then again i executed procedure proc

SQL> EXEC PROC

PL/SQL procedure successfully completed.

What happens when i include

Authid CURRENT_USER
in procedure proc when i recreated it?

Please note that rit is having RESOURCE role.
Please tell me what is relation between roles/prviliges granted to user and AUTHID DEFINER/CURRENT_USER taking above example
Re: Procedure(Authid) [message #225149 is a reply to message #225145] Sun, 18 March 2007 09:22 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at:

http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html

Regards
Michel
Previous Topic: NEED OPTIMAL SOLUTION FOR COMMON WILDCARD SEARCH IN ORACLE DATABASE AGAINST 2-4 MILLION RECORDS
Next Topic: NOCOPY
Goto Forum:
  


Current Time: Fri Dec 02 23:02:33 CST 2016

Total time taken to generate the page: 0.04886 seconds