Restrict user from updating stored procedure [message #143346] |
Thu, 20 October 2005 01:07  |
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   |
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 #143375 is a reply to message #143365] |
Thu, 20 October 2005 04:07   |
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 #143395 is a reply to message #143391] |
Thu, 20 October 2005 05:18   |
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 #143428 is a reply to message #143396] |
Thu, 20 October 2005 07:36  |
 |
Mahesh Rajendran
Messages: 10708 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?
|
|
|