[RastogiKamesh]

Hi,
I am using Oracle9i on SUN OS Solaris-5.8.

My sequence of Issue goes like this:

1. I created a new user and given system priv. CREATE PROCEDURE
2. The user created a procedure "XYZ" in its default schema & able to modify it also and of course able to execute it.
3. I then revoked the system priv. CREATE PROCEDURE from that user.
4. But, he is still able to issue command "create or replace procedure xyz" and able to modify the procedure.
5. But, he is not able to issue command "create procedure"

How can i restrict the modification of procedure by the user. I want he should able to only execute it once created initially and should not able to modify it.

Could anyone help me please. Thanks in advace.

Regards,

[aarroyob]

I think you couldn't do that. If at some time the use had the create procedure it could modify that procedure.

One option is create the procedure, packages ... with one user which only you have the password and grant execute on that procedure to other users.

Cheers

[RastogiKamesh]

Originally posted by aarroyob
I think you couldn't do that. If at some time the use had the create procedure it could modify that procedure.

One option is create the procedure, packages ... with one user which only you have the password and grant execute on that procedure to other users.

Cheers

Hi,
That is not the case for Trigger. The User had "Create Trigger" privilege and created the trigger. And Now, that privilege has been revoked from that user. Now, he is not able to issue the command "create or replace trigger" for the Trigger which was created by him earlier. It is giving ORA-01031: Insufficient privileges.

Why it is not happening with Procedure.? It is going beyond my imagination....Do u have any clue to this...or anybody having any tips...? Please suggest.

Thanks in advance....

Regards,