Home » RDBMS Server » Security » Privileges on create procedure (10.2.0.2.0 ,windows)
Privileges on create procedure [message #436866] Tue, 29 December 2009 04:09 Go to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member

Hi all,

Can somebody tell what privilege is required for user to create procedure in database ?



Thanks
Re: Privileges on create procedure [message #436867 is a reply to message #436866] Tue, 29 December 2009 04:24 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> conn system/tejajun20@satya
Connected.
SQL> create user test_orafaq identified by tejajun20 account unlock;

User created.


SQL> grant create session to test_orafaq;

Grant succeeded.

SQL> conn test_orafaq/tejajun20
Connected.
SQL> desc rev
ERROR:
ORA-04043: object rev does not exist


SQL> create or replace procedure  rev(x in varchar2) as
  2     c char(1);
  3     i number;
  4    begin
  5     for i in 1..length(x) loop
  6      select substr(x,length(x)-i+1,1) into c from dual;
  7      dbms_output.put(c);
  8     end loop;
  9    dbms_output.put_line(' ');
 10    end;
 11  /
create or replace procedure  rev(x in varchar2) as
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> conn system/tejajun20
Connected.
SQL> grant create procedure to test_orafaq;

Grant succeeded.

SQL> conn test_orafaq/tejajun20
Connected.
SQL>  create or replace procedure  rev(x in varchar2) as
  2      c char(1);
  3      i number;
  4     begin
  5      for i in 1..length(x) loop
  6       select substr(x,length(x)-i+1,1) into c from dual;
  7       dbms_output.put(c);
  8      end loop;
  9     dbms_output.put_line(' ');
 10     end;
 11   /

Procedure created.

SQL> desc rev
PROCEDURE rev
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 X                              VARCHAR2                IN


SQL> exec rev('SRIRAM');

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL>  exec rev('SRIRAM');
MARIRS

PL/SQL procedure successfully completed.

SQL>


For more info Read this....system_privs

[Updated on: Tue, 29 December 2009 04:26]

Report message to a moderator

Re: Privileges on create procedure [message #436868 is a reply to message #436867] Tue, 29 December 2009 04:40 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
Thanks for explaining with examples.

I have one more question to ask.

I created a procedure 'PROC_1' in my schema 'DRAM'. I want to grant
CREATE PROCEDURE,execute procedure privileges to other users.Can you tell me how to grant those ?

or

Is this correct ?

show user
dram
SQL>grant create procedure,execute procedure to A; (other user)
grant succeed


Thanks
Re: Privileges on create procedure [message #436870 is a reply to message #436868] Tue, 29 December 2009 04:56 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> grant create procedure,execute procedure to test_orafaq;
grant create procedure,execute procedure to test_orafaq
                       *
ERROR at line 1:
ORA-00990: missing or invalid privilege


SQL> grant create procedure,execute on scott.rev to test_orafaq;
grant create procedure,execute on scott.rev to test_orafaq
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege


SQL> grant create procedure to test_orafaq;

Grant succeeded.

SQL> grant execute on scott.rev to test_orafaq;

Grant succeeded.

SQL> grant execute procedure  to test_orafaq;
grant execute procedure  to test_orafaq
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege


SQL> grant execute any  procedure  to test_orafaq;

Grant succeeded.


Please read this

Thats why forum has some rules..like dont spoonfeed the user...In the previous reply i posted one link...and again you are asking the same type of questions...So it is better if you read the documents.
from the first post onwards Michel & me giving you the document link ?
Have you read any of those links ?

I suggest you to read the documents.That will help you Smile

Goodluck Smile
Sriram Smile

[Updated on: Tue, 29 December 2009 04:59]

Report message to a moderator

Re: Privileges on create procedure [message #436872 is a reply to message #436870] Tue, 29 December 2009 05:01 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member

Thanks.
Re: Privileges on create procedure [message #436912 is a reply to message #436866] Tue, 29 December 2009 08:08 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your topic "Revoke privilege" last month:
cookiemonster wrote on Tue, 10 November 2009 12:07
...See here for the full list of prvileges:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9013.htm#i2077938


ramoradba wrote on Tue, 10 November 2009 12:39
...http://www.psoug.org/reference/object_privs.html

Read the above ! Atleast

And First Read the Oracle documents provided properly...

Sriram.

Michel Cadot wrote on Tue, 10 November 2009 13:12
And BEFORE your next question, you should REALLY read the whole SQL Reference book, most of your questions are answered there.

Regards
Michel



Previous Topic: Assigning roles
Next Topic: DBA_REGISTRY_HISTORY is not in database
Goto Forum:
  


Current Time: Thu Mar 28 09:42:59 CDT 2024