Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Auditing statements executed from within stored procedures

3 posts in General Discussion Last posting was on 2011-06-29 13:03:47.0Z
Jon Saxton Posted on 2011-06-28 20:39:28.0Z
Sender: 3109.4e0a3492.1804289383@sybase.com
From: Jon Saxton
Newsgroups: sybase.public.ase.general
Subject: Auditing statements executed from within stored procedures
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e0a3c00.3286.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 28 Jun 2011 13:39:28 -0700
X-Trace: forums-1-dub 1309293568 10.22.241.41 (28 Jun 2011 13:39:28 -0700)
X-Original-Trace: 28 Jun 2011 13:39:28 -0700, 10.22.241.41
Lines: 35
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30292
Article PK: 72471

If I turn on "cmdtext" auditing I get to see the SQL text
for commands issued directly by users and for commands
internal to system procedures (e.g. sp_who, sp_help and so
on). However I have not found a way to see commands issued
inside other stored procedures.

Suppose I create a stored procedure:

use customers
go
create procedure browse as
select name, address, account, expiry_date, scc from cc_info
go

Now in my session I run

browse
go

I want the audit trail to show more than just an event 92
with the text "browse". I want it to show the select
statement as well. Is that possible?

I know that I can audit exec_procedure but that is not
interesting and doesn't give any extra information. What I
want is the same behaviour as I see for system stored
procedures. In fact I'd rather see the statements executed
by user-defined stored procedures instead of the ones in the
system SPs.

I am pretty sure there is some magic incantation to get this
to work because I have seen stored procedures being expanded
in the past. Trouble is I cannot reproduce the behaviour
now and my web searches have not helped. (That may say
something about my search techniques.)


Bret Halford Posted on 2011-06-28 22:09:59.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.18) Gecko/20110616 Thunderbird/3.1.11
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Auditing statements executed from within stored procedures
References: <4e0a3c00.3286.1681692777@sybase.com>
In-Reply-To: <4e0a3c00.3286.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e0a5137$1@forums-1-dub>
Date: 28 Jun 2011 15:09:59 -0700
X-Trace: forums-1-dub 1309298999 10.22.241.152 (28 Jun 2011 15:09:59 -0700)
X-Original-Trace: 28 Jun 2011 15:09:59 -0700, vip152.sybase.com
Lines: 78
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30293
Article PK: 72470


On 6/28/2011 2:39 PM, Jon Saxton wrote:
> If I turn on "cmdtext" auditing I get to see the SQL text
> for commands issued directly by users and for commands
> internal to system procedures (e.g. sp_who, sp_help and so
> on). However I have not found a way to see commands issued
> inside other stored procedures.
>
> Suppose I create a stored procedure:
>
> use customers
> go
> create procedure browse as
> select name, address, account, expiry_date, scc from cc_info
> go
>
> Now in my session I run
>
> browse
> go
>
> I want the audit trail to show more than just an event 92
> with the text "browse". I want it to show the select
> statement as well. Is that possible?
>
> I know that I can audit exec_procedure but that is not
> interesting and doesn't give any extra information. What I
> want is the same behaviour as I see for system stored
> procedures. In fact I'd rather see the statements executed
> by user-defined stored procedures instead of the ones in the
> system SPs.
>
> I am pretty sure there is some magic incantation to get this
> to work because I have seen stored procedures being expanded
> in the past. Trouble is I cannot reproduce the behaviour
> now and my web searches have not helped. (That may say
> something about my search techniques.)

Hi Jon,

The "cmdtext" auditing audits what passes through the
parser. The body of procedures has been pre-parsed
into a query tree, so cmdtext auditing doesn't show
you the innards.

The exception to this, which I think is what you are
seeing when you say you see system stored procedures
being audited, is when procedures use dynamic sql
such as 'execute ("select @@version")'. The dynamic sql
is itself not parsed and compiled until it is actually
executed, and auditing picks it up when it passes through
the parser.

Many of the Sybase-provided system procedures make use of
a utility subprocedure called sp_autoformat which uses
dynamic sql to generate cleaned-up output (tables with
columns only as wide as they have to be for their contents).

I haven't set up an auditing system to check that, but
if you look closely, I think you will find your auditing
is not showing every statement in the system stored procedure.

So you can get your own procedures to show up in auditing
if you write them to pass everything through EXECUTE()
as dynamic sql, but doing so pretty much kills all the performance
benefits of pre-parsed procedures with precompiled
query plans resident in procedure cache. (does still give
you the advantage of encapsulating standard code for tasks).

But to modify your example:

create procedure browse as
execute("select name, address, account, expiry_date, scc from cc_info")
go

Anyway, I don't think there is a way to do what you want.

-bret


Jon Saxton Posted on 2011-06-29 13:03:47.0Z
Sender: 3109.4e0a3492.1804289383@sybase.com
From: Jon Saxton
Newsgroups: sybase.public.ase.general
Subject: Re: Auditing statements executed from within stored procedures
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e0b22b3.59a0.1681692777@sybase.com>
References: <4e0a5137$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 29 Jun 2011 06:03:47 -0700
X-Trace: forums-1-dub 1309352627 10.22.241.41 (29 Jun 2011 06:03:47 -0700)
X-Original-Trace: 29 Jun 2011 06:03:47 -0700, 10.22.241.41
Lines: 5
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30295
Article PK: 72473

Thanks Bret. That was very informative.

> Anyway, I don't think there is a way to do what you want.
>
> -bret