SEO

Hi,

Is there a method to get more information about the query tree then I can
get from simply querying the sysprocedures table?

For instance:
select * from sysprocedures where id=OBJECT_ID("test2");
type id sequence status number version
------ ----------- ----------- ------ ------ -----------
2 655442478 0 1152 1 12500
2 655442478 1 1152 1 12500
2 655442478 2 1152 1 12500
2 655442478 3 1152 1 12500
2 655442478 4 1152 1 12500
2 655442478 5 1152 1 12500
2 655442478 6 1152 1 12500
2 655442478 7 1152 1 12500
2 655442478 8 1152 1 12500
2 655442478 9 1152 1 12500
2 655442478 10 1152 1 12500
2 655442478 11 1152 1 12500
This doesn't tell me a whole lot about the query tree!

Thanks,

Hans.

The information in sysprocedures is stored in a kind of 'hidden' column,
i.e. not accessible through SQL. Even if it were accessible, it would not be
useful at all, since there is no way to display what's in there. Note that
it doesn't contain a query plan BTW -- that only exists in the procedure
cache, and is generated by the optimizer after the query tree has been read
from disk.

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase Replication Server Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

"Hans Kramer" <noms@xs4all.nl> wrote in message
newsan.2006.02.17.10.58.13.453947@xs4all.nl...
> Hi,
>
> Is there a method to get more information about the query tree then I can
> get from simply querying the sysprocedures table?
>
> For instance:
> select * from sysprocedures where id=OBJECT_ID("test2");
> type id sequence status number version
> ------ ----------- ----------- ------ ------ -----------
> 2 655442478 0 1152 1 12500
> 2 655442478 1 1152 1 12500
> 2 655442478 2 1152 1 12500
> 2 655442478 3 1152 1 12500
> 2 655442478 4 1152 1 12500
> 2 655442478 5 1152 1 12500
> 2 655442478 6 1152 1 12500
> 2 655442478 7 1152 1 12500
> 2 655442478 8 1152 1 12500
> 2 655442478 9 1152 1 12500
> 2 655442478 10 1152 1 12500
> 2 655442478 11 1152 1 12500
> This doesn't tell me a whole lot about the query tree!
>
> Thanks,
>
> Hans.
>

Thanks Rob,

If it is not accessible thru SQL I guess I am out of luck.

I wanted to use this information from the client side to make sure
that I am executing the correct version of the stored procedure in
question.

The syscomments table can be edited so is no good (but will do for most
purposes including perhaps mine) and just using the OBJECT_ID could help
as well. However, the query tree should uniquely describe the stored
procedure (actually I was hoping that, perhaps naively). Producing a
strong hash value of that data would be enough.

This sounds silly, however, it is useful in some cases in
which "we cannot" (better phrased: want to) trust the DBA (how is that
posssible ;-). Of course the question that immediately rises, can we trust
the application side :-) :-) Well it is more legal mumbo jumbo (or in my
case Good Clinical Practice 21 CFR Part 11)

Hans.

On Fri, 17 Feb 2006 12:18:58

On Fri, 17 Feb 2006 12:18:58 +0100, Rob Verschoor wrote:

> The information in sysprocedures is stored in a kind of 'hidden' column,
> i.e. not accessible through SQL. Even if it were accessible, it would not
> be useful at all, since there is no way to display what's in there. Note
> that it doesn't contain a query plan BTW -- that only exists in the
> procedure cache, and is generated by the optimizer after the query tree
> has been read from disk.
>
> HTH,
>
> Rob
> ------------------------------------------------------------- Rob
> Verschoor
>
> Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0 and
> Replication Server 12.5 / TeamSybase
>
> Author of Sybase books (order online at www.sypron.nl/shop): "Tips, Tricks
> & Recipes for Sybase ASE" "The Complete Sybase Replication Server Quick
> Reference Guide" "The Complete Sybase ASE Quick Reference Guide"
>
> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME http://www.sypron.nl
> Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> -------------------------------------------------------------
>
> "Hans Kramer" <noms@xs4all.nl> wrote in message
> newsan.2006.02.17.10.58.13.453947@xs4all.nl...
>> Hi,
>>
>> Is there a method to get more information about the query tree then I
>> can get from simply querying the sysprocedures table?
>>
>> For instance:
>> select * from sysprocedures where id=OBJECT_ID("test2");
>> type id sequence status number version ------ -----------
>> ----------- ------ ------ -----------
>> 2 655442478 0 1152 1 12500 2 655442478
>> 1 1152 1 12500 2 655442478 2
>> 1152 1 12500 2 655442478 3 1152 1
>> 12500 2 655442478 4 1152 1 12500 2
>> 655442478 5 1152 1 12500 2 655442478
>> 6 1152 1 12500 2 655442478 7 1152
>> 1 12500 2 655442478 8 1152 1
>> 12500 2 655442478 9 1152 1 12500 2
>> 655442478 10 1152 1 12500 2 655442478
>> 11 1152 1 12500
>> This doesn't tell me a whole lot about the query tree!
>>
>> Thanks,
>>
>> Hans.
>>
>>

I don't think you can implement that sort of check with 100% certainty if
you assume the syscomments text could be modified. In any case, the query
tree would not be unique: different stored procs could well have the same
query tree.

Rob V.

"Hans Kramer" <noms@xs4all.nl> wrote in message
newsan.2006.02.17.11.56.48.193388@xs4all.nl...
> Thanks Rob,
>
> If it is not accessible thru SQL I guess I am out of luck.
>
> I wanted to use this information from the client side to make sure
> that I am executing the correct version of the stored procedure in
> question.
>
> The syscomments table can be edited so is no good (but will do for most
> purposes including perhaps mine) and just using the OBJECT_ID could help
> as well. However, the query tree should uniquely describe the stored
> procedure (actually I was hoping that, perhaps naively). Producing a
> strong hash value of that data would be enough.
>
> This sounds silly, however, it is useful in some cases in
> which "we cannot" (better phrased: want to) trust the DBA (how is that
> posssible ;-). Of course the question that immediately rises, can we trust
> the application side :-) :-) Well it is more legal mumbo jumbo (or in my
> case Good Clinical Practice 21 CFR Part 11)
>
> Hans.
>
> On Fri, 17 Feb 2006 12:18:58
>
> On Fri, 17 Feb 2006 12:18:58 +0100, Rob Verschoor wrote:
>
> > The information in sysprocedures is stored in a kind of 'hidden' column,
> > i.e. not accessible through SQL. Even if it were accessible, it would
not
> > be useful at all, since there is no way to display what's in there. Note
> > that it doesn't contain a query plan BTW -- that only exists in the
> > procedure cache, and is generated by the optimizer after the query tree
> > has been read from disk.
> >
> > HTH,
> >
> > Rob
> > ------------------------------------------------------------- Rob
> > Verschoor
> >
> > Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0 and
> > Replication Server 12.5 / TeamSybase
> >
> > Author of Sybase books (order online at www.sypron.nl/shop): "Tips,
Tricks
> > & Recipes for Sybase ASE" "The Complete Sybase Replication Server Quick
> > Reference Guide" "The Complete Sybase ASE Quick Reference Guide"
> >
> > mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME http://www.sypron.nl
> > Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> > -------------------------------------------------------------
> >
> > "Hans Kramer" <noms@xs4all.nl> wrote in message
> > newsan.2006.02.17.10.58.13.453947@xs4all.nl...
> >> Hi,
> >>
> >> Is there a method to get more information about the query tree then I
> >> can get from simply querying the sysprocedures table?
> >>
> >> For instance:
> >> select * from sysprocedures where id=OBJECT_ID("test2");
> >> type id sequence status number
version ------ -----------
> >> ----------- ------ ------ -----------
> >> 2 655442478 0 1152 1 12500 2 655442478
> >> 1 1152 1 12500 2 655442478 2
> >> 1152 1 12500 2 655442478 3 1152 1
> >> 12500 2 655442478 4 1152 1 12500 2
> >> 655442478 5 1152 1 12500 2 655442478
> >> 6 1152 1 12500 2 655442478 7 1152
> >> 1 12500 2 655442478 8 1152 1
> >> 12500 2 655442478 9 1152 1 12500 2
> >> 655442478 10 1152 1 12500 2 655442478
> >> 11 1152 1 12500
> >> This doesn't tell me a whole lot about the query tree!
> >>
> >> Thanks,
> >>
> >> Hans.
> >>
> >>
>

On Fri, 17 Feb 2006 13:04:37 +0100, Rob Verschoor wrote:

> I don't think you can implement that sort of check with 100% certainty if
yep, tough cookie!

> you assume the syscomments text could be modified. In any case, the
> query tree would not be unique: different stored procs could well have
> the same query tree.
Okay, if that's the case then it looks my effort is doomed!
Unless you mean that a different stored procedure could be defined the
same and thus produce the same query tree. I wouldn't care about that. I
only care that the stored procedure I will call hasn't been "patched" with
for instance an extra delete, insert or whatever statement!

I will try to retrieve the "hidden" column from the CT lib calls and
experiment a bit... it is Friday anyway!

Thanks again Rob!

Hans.


>
> Rob V.
>
> "Hans Kramer" <noms@xs4all.nl> wrote in message
> newsan.2006.02.17.11.56.48.193388@xs4all.nl...
>> Thanks Rob,
>>
>> If it is not accessible thru SQL I guess I am out of luck.
>>
>> I wanted to use this information from the client side to make sure that
>> I am executing the correct version of the stored procedure in question.
>>
>> The syscomments table can be edited so is no good (but will do for most
>> purposes including perhaps mine) and just using the OBJECT_ID could
>> help as well. However, the query tree should uniquely describe the
>> stored procedure (actually I was hoping that, perhaps naively).
>> Producing a strong hash value of that data would be enough.
>>
>> This sounds silly, however, it is useful in some cases in which "we
>> cannot" (better phrased: want to) trust the DBA (how is that posssible
>> ;-). Of course the question that immediately rises, can we trust the
>> application side :-) :-) Well it is more legal mumbo jumbo (or in my
>> case Good Clinical Practice 21 CFR Part 11)
>>
>> Hans.
>>
>> On Fri, 17 Feb 2006 12:18:58
>>
>> On Fri, 17 Feb 2006 12:18:58 +0100, Rob Verschoor wrote:
>>
>> > The information in sysprocedures is stored in a kind of 'hidden'
>> > column, i.e. not accessible through SQL. Even if it were accessible,
>> > it would
> not
>> > be useful at all, since there is no way to display what's in there.
>> > Note that it doesn't contain a query plan BTW -- that only exists in
>> > the procedure cache, and is generated by the optimizer after the
>> > query tree has been read from disk.
>> >
>> > HTH,
>> >
>> > Rob
>> > ------------------------------------------------------------- Rob
>> > Verschoor
>> >
>> > Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0 and
>> > Replication Server 12.5 / TeamSybase
>> >
>> > Author of Sybase books (order online at www.sypron.nl/shop): "Tips,
> Tricks
>> > & Recipes for Sybase ASE" "The Complete Sybase Replication Server
>> > Quick Reference Guide" "The Complete Sybase ASE Quick Reference
>> > Guide"
>> >
>> > mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME http://www.sypron.nl Sypron
>> > B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
>> > -------------------------------------------------------------
>> >
>> > "Hans Kramer" <noms@xs4all.nl> wrote in message
>> > newsan.2006.02.17.10.58.13.453947@xs4all.nl...
>> >> Hi,
>> >>
>> >> Is there a method to get more information about the query tree then
>> >> I can get from simply querying the sysprocedures table?
>> >>
>> >> For instance:
>> >> select * from sysprocedures where id=OBJECT_ID("test2");
>> >> type id sequence status number
> version ------ -----------
>> >> ----------- ------ ------ -----------
>> >> 2 655442478 0 1152 1 12500 2
>> >> 655442478
>> >> 1 1152 1 12500 2 655442478 2
>> >> 1152 1 12500 2 655442478 3 1152
>> >> 1
>> >> 12500 2 655442478 4 1152 1 12500 2
>> >> 655442478 5 1152 1 12500 2 655442478
>> >> 6 1152 1 12500 2 655442478 7
>> >> 1152
>> >> 1 12500 2 655442478 8 1152 1
>> >> 12500 2 655442478 9 1152 1 12500 2
>> >> 655442478 10 1152 1 12500 2 655442478
>> >> 11 1152 1 12500
>> >> This doesn't tell me a whole lot about the query tree!
>> >>
>> >> Thanks,
>> >>
>> >> Hans.
>> >>
>> >>
>> >>
>>

"Hans Kramer" <noms@xs4all.nl> wrote in message
newsan.2006.02.17.12.29.15.49500@xs4all.nl...
> On Fri, 17 Feb 2006 13:04:37 +0100, Rob Verschoor wrote:
>
> > I don't think you can implement that sort of check with 100% certainty
if
> yep, tough cookie!
>
> > you assume the syscomments text could be modified. In any case, the
> > query tree would not be unique: different stored procs could well have
> > the same query tree.
> Okay, if that's the case then it looks my effort is doomed!
> Unless you mean that a different stored procedure could be defined the
> same and thus produce the same query tree. I wouldn't care about that. I
> only care that the stored procedure I will call hasn't been "patched" with
> for instance an extra delete, insert or whatever statement!
>
> I will try to retrieve the "hidden" column from the CT lib calls and
> experiment a bit... it is Friday anyway!
>

Don't bother... you can't. When I said 'hidden' that was more to illustrate
the concept -- not suggesting you could 'find' it in anyway...

Rob V.

On Fri, 17 Feb 2006 15:04:45 +0100, Rob Verschoor wrote:

> "Hans Kramer" <noms@xs4all.nl> wrote in message
> newsan.2006.02.17.12.29.15.49500@xs4all.nl...
>> On Fri, 17 Feb 2006 13:04:37 +0100, Rob Verschoor wrote:
>>
>> > I don't think you can implement that sort of check with 100% certainty
> if
>> yep, tough cookie!
>>
>> > you assume the syscomments text could be modified. In any case, the
>> > query tree would not be unique: different stored procs could well have
>> > the same query tree.
>> Okay, if that's the case then it looks my effort is doomed! Unless you
>> mean that a different stored procedure could be defined the same and
>> thus produce the same query tree. I wouldn't care about that. I only
>> care that the stored procedure I will call hasn't been "patched" with
>> for instance an extra delete, insert or whatever statement!
>>
>> I will try to retrieve the "hidden" column from the CT lib calls and
>> experiment a bit... it is Friday anyway!
>>
>>
> Don't bother... you can't. When I said 'hidden' that was more to
> illustrate the concept -- not suggesting you could 'find' it in anyway...

Bummer :-)

Hans.
>
> Rob V.

Hans Kramer wrote:
.... snip ...
> Okay, if that's the case then it looks my effort is doomed!
> Unless you mean that a different stored procedure could be defined the
> same and thus produce the same query tree. I wouldn't care about that. I
> only care that the stored procedure I will call hasn't been "patched" with
> for instance an extra delete, insert or whatever statement!

A few ideas ...

1 - utilize the sp_hidetext stored proc to hide/encrypt the source code in
syscomments; 'course, if someone were to load their own proc and run
sp_hidetext you wouldn't be able to 'see' what they had loaded

2 - incorporate an encryption/hash key into the source code of the proc;
would be a little tricky ... basically something like ... generate hash key
and/or encryption signature for source code file (eg, my_proc.sql), embed
hash key/encryption signature in source code (as a T-SQL comment), load
code into dataserver; (keep hash key in a secure location somewhere); to
check the hash code, extract source from syscomments, remove hash key,
rebuild my_proc.sql, generate new hash key, see if new key is same as 'old'
key kept in your 'security location'; (sounds nasty but very doable with
some scripting); 'course, how do you keep your master set of keys (in that
'secure location') intact, ie, how do you make sure they are not modified
by devious dude(tte), ie, same 'problem' of how do you normally maintain
security codes/passwords/passphrases

3 - don't use stored procs; perform all of your work via dynamic SQL
compiled within the application executable

4 - run sp_hidetext to remove text from syscomments; periodically run a
test execution of your proc with a set of dummy data; key is to instrument
the run so that you collect optimizer info (set showplan on, set fmtonly,
dbcc traceon(3604,302,310), etc); from here you compare the results against
a known set of valid results; 'course, comparing the 2 sets of output is
another issue, not to mention making sure your 'valid results' are
maintained in a secure location where they can't be modified

In the end though, there's nothing to keep a DBA (or someone else with the
right permissions, or access to passwords) from a) manually modifying data,
b) running their own version of stored procs to modify data, or c) creating
(modifying?) a trigger on a table to cause extra DML statements to fire.

No matter how much 'security' you put in place there's always the chance
that the person with the keys (or abilities) can circumvent the security
system.

On Fri, 17 Feb 2006 18:07:36 +0100, Mark A. Parsons <user@example.net>
wrote:

> 2 - incorporate an encryption/hash key into the source code of the proc;
> would be a little tricky ... basically something like ... generate hash
> key and/or encryption signature for source code file (eg, my_proc.sql),
> embed hash key/encryption signature in source code (as a T-SQL comment),
> load code into dataserver; (keep hash key in a secure location
> somewhere); to check the hash code, extract source from syscomments,
> remove hash key, rebuild my_proc.sql, generate new hash key, see if new
> key is same as 'old' key kept in your 'security location'; (sounds nasty
> but very doable with some scripting); 'course, how do you keep your
> master set of keys (in that 'secure location') intact, ie, how do you
> make sure they are not modified by devious dude(tte), ie, same 'problem'
> of how do you normally maintain security codes/passwords/passphrases

I don't see the need to store the hash value somewhere in the database
itself. You could as well just hash the procedure before creating it on
the database and store that hash in a secure location. Then later extract
the source code from syscomments, hash again and compare to the previously
calculated hash.

Jochen

true

Jochen Schug wrote:
> On Fri, 17 Feb 2006 18:07:36 +0100, Mark A. Parsons <user@example.net>
> wrote:
>
>> 2 - incorporate an encryption/hash key into the source code of the
>> proc; would be a little tricky ... basically something like ...
>> generate hash key and/or encryption signature for source code file
>> (eg, my_proc.sql), embed hash key/encryption signature in source code
>> (as a T-SQL comment), load code into dataserver; (keep hash key in a
>> secure location somewhere); to check the hash code, extract source
>> from syscomments, remove hash key, rebuild my_proc.sql, generate new
>> hash key, see if new key is same as 'old' key kept in your 'security
>> location'; (sounds nasty but very doable with some scripting);
>> 'course, how do you keep your master set of keys (in that 'secure
>> location') intact, ie, how do you make sure they are not modified by
>> devious dude(tte), ie, same 'problem' of how do you normally maintain
>> security codes/passwords/passphrases
>
>
> I don't see the need to store the hash value somewhere in the database
> itself. You could as well just hash the procedure before creating it on
> the database and store that hash in a secure location. Then later
> extract the source code from syscomments, hash again and compare to the
> previously calculated hash.
>
> Jochen