Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146

    Unanswered: System Privileges Issue: CREATE PROCEDURE

    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,
    - KR

  2. #2
    Join Date
    Apr 2003
    Posts
    6
    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

  3. #3
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146
    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,
    - KR

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •