Stored Procedure Security 
Author Message
 Stored Procedure Security
Is it possible to disable viewing of the text of a stored procedure when
it is requested with the sp_helptext stored procedure or the defncopy
command.
Any one who can access syscomments can see text of stored procedure.
If I delete text from syscomments does this destroy the stored
procedure?
If I can hide the text, how can I get it back?


Fri, 25 Apr 2003 15:12:27 GMT
 Stored Procedure Security

As far as I know you can delete the text from syscomments without any
problems. But ofcourse you could try carefully with a simple stored
procedure.



Quote:
> Is it possible to disable viewing of the text of a stored procedure when
> it is requested with the sp_helptext stored procedure or the defncopy
> command.
> Any one who can access syscomments can see text of stored procedure.
> If I delete text from syscomments does this destroy the stored
> procedure?
> If I can hide the text, how can I get it back?



Fri, 25 Apr 2003 03:00:00 GMT
 Stored Procedure Security
In 11.5 and beyond there is a stored procedure called sp_hidetext that
encrypts the text of an sproc.  Once encrypted there is no way to un-encrypt
the text.  Repeat, no way to do it.

Deleting is a possiblity, however in future versions the text of an sproc,
encrypted or not, will need to exist in syscomments so that the sproc can be
rebuilt during the upgrade process.  If the rows have been deleted dbcc's
will return an error stating so, and at some point (12.5) the upgrade will
fail.

Don't delete.  Encrypt.

E

Quote:
> As far as I know you can delete the text from syscomments without any
> problems. But ofcourse you could try carefully with a simple stored
> procedure.



> > Is it possible to disable viewing of the text of a stored procedure when
> > it is requested with the sp_helptext stored procedure or the defncopy
> > command.
> > Any one who can access syscomments can see text of stored procedure.
> > If I delete text from syscomments does this destroy the stored
> > procedure?
> > If I can hide the text, how can I get it back?



Fri, 25 Apr 2003 03:00:00 GMT
 Stored Procedure Security

NO!!!!

There is a configuration parameter in 11.5 and beyond called 'select on
syscomments.text'.  It is set to 1 by default, to enable extract and
viewing of stored procedure, trigger and view text.  This is helpful for
when you want to extract and recompile code, or modify it, or whatever.
If you don't want anyone to look at the code, simply go into isql and
execute the following command:

          sp_configure 'select on syscomments.text',0

Then you can change it back to 1 when you need to upgrade your server,
modify a procedure, etc.  This is definitely preferable to deleting or
encrypting your text.

Hope this helps,

                                    Larry Burns



Quote:
> In 11.5 and beyond there is a stored procedure called sp_hidetext that
> encrypts the text of an sproc.  Once encrypted there is no way to
un-encrypt
> the text.  Repeat, no way to do it.

> Deleting is a possiblity, however in future versions the text of an
sproc,
> encrypted or not, will need to exist in syscomments so that the sproc
can be
> rebuilt during the upgrade process.  If the rows have been deleted
dbcc's
> will return an error stating so, and at some point (12.5) the upgrade
will
> fail.

> Don't delete.  Encrypt.

> E


> > As far as I know you can delete the text from syscomments without
any
> > problems. But ofcourse you could try carefully with a simple stored
> > procedure.



> > > Is it possible to disable viewing of the text of a stored
procedure when
> > > it is requested with the sp_helptext stored procedure or the
defncopy
> > > command.
> > > Any one who can access syscomments can see text of stored
procedure.
> > > If I delete text from syscomments does this destroy the stored
> > > procedure?
> > > If I can hide the text, how can I get it back?

Sent via Deja.com http://www.deja.com/
Before you buy.


Fri, 25 Apr 2003 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. stored Procedure security issue

2. v7 Stored Procedure Security

3. DTS via stored procedure security problem

4. store procedure security

5. DTS via stored procedure security problem

6. Stored Procedure security

7. Stored Procedure security??

8. Stored procedure security

9. Stored Procedure Security

10. Oracle Stored Procedure Security

11. Java Stored Procedure security problem


 
Powered by phpBB® Forum Software