Home » RDBMS Server » Security » Restrict user from updating stored procedure
Restrict user from updating stored procedure [message #143346] Thu, 20 October 2005 01:07 Go to next message
misha603
Messages: 20
Registered: July 2005
Location: India
Junior Member

Hi,
Is it possible to lock a stored procedure such a way that no user must be able to update it when it is locked, but the user must be able to execute it. Also if there is away to lock please tell me how to unlokc it.
I'm trying to create a version control for the stored procedures.
Please help.

Thanks
Regards
Raj

Re: Restrict user from updating stored procedure [message #143354 is a reply to message #143346] Thu, 20 October 2005 02:04 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member
Hi misha,

I am a bit confused regarding the question ( or i didnot understand it properly) but whenever a user creates a procedure in Oracle , only the user who has created the procedure can change the definition of the procedure and i suppose there is no update on a procedure, if you want to update a procedure then you have to recreate it.

lets take this as an example

SQL> show user 
USER is "TARUN"

SQL> create procedure check1( n1 number) as
  2  begin
  3  dbms_output.put_line(n1);
  4  end;
  5  /
Procedure created.

SQL> set serveroutput on;
SQL> exec check1(3)
3

PL/SQL procedure successfully completed.

SQL> show user
USER is "SCOTT"

SQL> exec tarun.check1(3);
BEGIN tarun.check1(3); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'TARUN.CHECK1' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL> show user 
USER is "TARUN"

SQL> grant execute on check1 to scott;

Grant succeeded.


SQL> show user
USER is "SCOTT"

SQL> exec tarun.check1(4);
 4

------And now i am creating a procedure in scott's schema with --------the same name

SQL> create procedure check1 as
  2  begin
  3  dbms_output.put_line('hello');
  4  end;
  5  /

Procedure created.

SQL> exec check1;
hello

PL/SQL procedure successfully completed.

SQL> exec tarun.check1(5);
5

PL/SQL procedure successfully completed.



was i able to make it clear to you? Did i explained the right thing that you wanted ?

regards,
tarun
Re: Restrict user from updating stored procedure [message #143365 is a reply to message #143346] Thu, 20 October 2005 02:48 Go to previous messageGo to next message
misha603
Messages: 20
Registered: July 2005
Location: India
Junior Member

Hi Arun,

Thanks a lot for your quick response.

Actually I want to restrict a user from changing the procedure.

Lets say there is a procedure called SP_EMP. I do not want ANY of the user to change the code for this procedure. (ie, CREATE OR REPLACE should throw an error Insuffient priviledge), but the catch here is I can allow the user to create a new procedure but not change this procedure SP_EMP.

Thanks

Regards
Raj
Re: Restrict user from updating stored procedure [message #143375 is a reply to message #143365] Thu, 20 October 2005 04:07 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

Hi raj,

There are two priviliges ;

--> CREATE PROCEDURE -- a normal user usually have this privilege, user can create procedure in his own schema and not in other's schema.

--> CREATE ANY PROCEDURE -- generally sys have this privilege, USer with this privilege can create procedure in his own schema as well as in other's schema.

so dont assign CREATE ANY PROCEDURE privilege to any of the user.

you can check the privileges of a schema.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER

PRIVILEGE
----------------------------------------
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

14 rows selected.


Is this information helping for you ?
regards,
tarun


Re: Restrict user from updating stored procedure [message #143391 is a reply to message #143375] Thu, 20 October 2005 05:00 Go to previous messageGo to next message
misha603
Messages: 20
Registered: July 2005
Location: India
Junior Member

Hi tarun,

Thanks a lot for your quick responses. I really appreciate that.
CREATE PROCEDURE privilege is for all the procedures, but I want the user should not change only one stored procedure say SP_EMP. The user can be allowed to change the orher procedures.

Actually I'm just thinking if I can create a version control for the procedures in the DB.

Thanks again

Regards
Raj
Re: Restrict user from updating stored procedure [message #143395 is a reply to message #143391] Thu, 20 October 2005 05:18 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member
oops! for a single procedure

Well raj, i dont have any idea right now but if i anything comes to my knowledge then i do let you know.

[EDIT -- But i am afraid that its not possible because CREATE PROCEDURE is a system privilege and not an object privilege so we can not categorise it as we can do with object privileges like select, insert , delete etc ]


regards,
tarun

[Updated on: Thu, 20 October 2005 05:19]

Report message to a moderator

Re: Restrict user from updating stored procedure [message #143396 is a reply to message #143346] Thu, 20 October 2005 05:30 Go to previous messageGo to next message
misha603
Messages: 20
Registered: July 2005
Location: India
Junior Member

OK. Thanks a lot for your response. If anything comes to me I shall also tell you.

Thanks again.

Regards
Raj
Re: Restrict user from updating stored procedure [message #143428 is a reply to message #143396] Thu, 20 October 2005 07:36 Go to previous message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Misha603 wrote:
>>Actually I'm just thinking if I can create a version control for the procedures in the DB.

IMHO, keep version control kind of operations away from database.
Because databases are not designed for it by default.
There are too many version control tools available in open source /GNU or commercial.

To answer your question, did you try a ddl trigger?


Previous Topic: I Need a help
Next Topic: New User Getting privileges without granting
Goto Forum:
  


Current Time: Sat Sep 25 22:11:32 CDT 2021