Re: audit table containing Select statements submitted

Hogan, James F. Jr. wrote:
> I am trying desperately to find a way to create an audit table to
> log...who SELECTED what... as I am dealing with HR data and Health Care
> Patient Data
>
> Any help would be greatly appreciated and put to good use in the Austin
> Texas Hospitals.

Use statement logging and make sure each person is logging in with their
own role.

If you can't do that, then don't allow anyone to select anything but set
returning functions, then you can log the application user with that.

Sincerely,

Joshua D. Drake

>
> Thanks
>
> Jim
>
>
>
> -----Original Message-----
> From: Hogan, James F. Jr.
> Sent: Thursday, May 04, 2006 12:46 PM
> To: 'pgsql-sql(at)postgresql(dot)org'; 'pgsql-general(at)postgresql(dot)org'
> Subject: audit table containing Select statements submitted
>
> No response from the pgsql-admin list so I though I would try cross
> posting here:
> pgsql-sql(at)postgresql(dot)org
> pgsql-general(at)postgresql(dot)org
>
>
>
> I just know I am not the first to try and do this
>
> Jim
>
> *********************
> Can anyone point me in a direction that may help me populate in real
> time a table that holds?
>
> Current_user
> Timestamp
> "The Select Statement Submitted by the User"
>
> I need to be able to determine who viewed what and when they viewed it.
>
> I have considered the fact that the result from SELECT yesterday may be
> different than the result set returned by the SAME SELECT statement
> today, but when used in conjunction with the INSERT, UPDATE, DELETE
> audit logging I have already created, the answers to who viewed, what
> and when would be readily available.
>
> I have been searching all morning and...
>
> The only thing I find on logging of Select statements is that the
> information can be held in the Log Files...if Logging is enabled.
>
> As I am only interested in the statements presented against certain
> tables...
>
> Turning on logging gives me more than I need or care to look through.
>
> I could write a script to parses the Log Files into a Database Table but
> would prefer to avoid enabling the file logging of statements if
> possible.
>
> Thanks for any reference or help you may be able to provide.
>
> Jim
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/