Hello,

As part of PA-DSS (Payment Application-Data Security Standard)
compliance, we are required to log every time someone accesses a table
containing a credit card number. The ideal way to accomplish this
would be thru triggers. However, triggers are not supported on
"select" statements. In SQL Anywhere, is there another way to
accomplish functionality similar to the following (Note: as mentioned,
this is not a valid trigger. I'm using this to illustrate what I'm
trying to accomplish):
CREATE TRIGGER log_card_access AFTER SELECT ON dba.creditcard_info
FOR EACH STATEMENT
BEGIN
INSERT INTO audit_log VALUES( Now(*) )
END

Thanks in advance,
Tim

You could restrict access to this table so that nobody can SELECT from it.

Then you could create a stored procedure that logs the access and then
retrieves the data through a SELECT statement. All users would then call
this stored procedure instead of the select statement.


SalePoint wrote:

We may have to end up going with a solution similar to your
suggestion. The drawback to this is that we will need to change client/
application-level code and there may be drawbacks to changing the
current "select" statements (many of which are located in datawindows)
to procedures. To your knowledge, there's no way to add select-based
trigger-like functionality to a Sybase database?

Tim

On Sep 28, 2:40*pm, "Kory Hodgson (Sybase iAnywhere)"
<khodgson (AT) A_SPAM_FREE_sybase (DOT) com> wrote:

SQL Anywhere does not offer triggers on SELECT statements; neither does
Microsoft, DB2, MySQL, or Oracle.

Even if SQL Anywhere DID offer that support, you would be forced to
modify the application directly in any event. This is because if the
application queried the table, and the trigger fired, the application
would have to COMMIT to write the audit rows to the audit table - and
there is no guarantee that the application would do that. In fact, many
read-only applications simply disconnect when they complete, which
results in an implicit ROLLBACK.

SQL Anywhere does offer an audit feature that write access information
to the transaction log. However, this has disadvantages in that the
transaction log can grow significantly, and accessing the log to verify
an audit is cumbersome (though it can be done).

Glenn

SalePoint wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

On Sep 28, 4:16*pm, "Glenn Paulley [Sybase iAnywhere]"
<paul... (AT) ianywhere (DOT) com> wrote:
We are exploring the the following option:
=== Quote ===
"SQL Anywhere does offer an audit feature that write access
information to the transaction log. However, this has disadvantages in
that the
transaction log can grow significantly, and accessing the log to
verify
an audit is cumbersome (though it can be done)."
=== End Quote ===

Can you point me towards documentation that discusses how to implement/
enable this functionality?

Thanks,
Tim

In the help, look in the index under "audit".

Glenn

SalePoint wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

On Oct 29, 3:22*pm, "Glenn Paulley [Sybase iAnywhere]"
<paul... (AT) ianywhere (DOT) com> wrote:
I do not see the SELECT statements in the audit. I run a test where I
connect to a database server using dbisql (I throw a failed logon
attempt in there for good measure). I then run the following (in
dbisql):
==========
SET OPTION PUBLIC.AUDITING = 'ON';
CALL sa_enable_auditing_type('all');
CALL sa_audit_string( '------>Start Audit.' );
UPDATE "customer_master" SET "first_name" = 'Dana' WHERE
"customer_number" = '00999001305134870018';
COMMIT;
SELECT * FROM "customer_master" ORDER BY "customer_number";
CALL sa_audit_string( '<------End Audit.' );
==========

I then run dbtran with these cmdline args: "-g -d -ek QA ars4.log
dump6.sql". I get the following in dump6.sql:
==========
--CONNECT-1001-0009441918-failure-2009-10-30 14:54
----AUDIT-1001-0009441933 -- 2009/10/30 14:54:58.247 Connection
attempt (machine (local)) Port SharedMemory - Failed
----ROLLBACK-1001-0009441986
--ROLLBACK WORK
--CONNECT-1002-0009441993-DBA-2009-10-30 14:55
----AUDIT-1002-0009442004 -- 2009/10/30 14:55:02.904 Checking DBA
authority
----AUDIT-1002-0009442028 -- 2009/10/30 14:55:02.904 Connection
attempt (DBA - machine (local)) Port SharedMemory - OK
----AUDIT-1002-0009442064 -- 2009/10/30 14:55:02.914 Checking Select
permission on SYS.SYSINFO - OK
----AUDIT-1002-0009442102 -- 2009/10/30 14:55:02.944 Checking Select
permission on SYS.SYSOPTIONS - OK
----AUDIT-1002-0009442143 -- 2009/10/30 14:55:02.944 Checking if user
is SYS - Yes
----AUDIT-1002-0009442163 -- 2009/10/30 14:55:02.944 Checking if user
is SYS - Yes
----AUDIT-1002-0009442183 -- 2009/10/30 14:55:02.944 Checking Select
permission on SYS.SYSOPTIONS - OK
----AUDIT-1002-0009442224 -- 2009/10/30 14:55:02.944 Checking if user
is SYS - Yes
----AUDIT-1002-0009442244 -- 2009/10/30 14:55:02.944 Checking if user
is SYS - Yes
----AUDIT-1002-0009442264 -- 2009/10/30 14:55:09.573 Attempting to set
public option 'AUDITING'
----AUDIT-1002-0009442288 -- 2009/10/30 14:55:09.573 Checking DBA
authority
----AUDIT-1002-0009442312 -- 2009/10/30 14:55:09.573 Checking DBA
authority
----BEGIN TRANSACTION-1002-0009442336
--BEGIN TRANSACTION
--SQL-1002-0009442337
--set option PUBLIC.Auditing = 'ON'
--AUDIT-1002-0009442380 -- 2009/10/30 14:55:09.573 Operation Succeeded
----COMMIT-1002-0009442395
--COMMIT WORK
--AUDIT-1002-0009442396 -- 2009/10/30 14:55:09.653 Checking DBA
authority
----AUDIT-1002-0009442420 -- 2009/10/30 14:55:09.653 Attempting to set
public option 'Auditing_options'
----AUDIT-1002-0009442452 -- 2009/10/30 14:55:09.653 Checking DBA
authority
----AUDIT-1002-0009442476 -- 2009/10/30 14:55:09.653 Checking DBA
authority
----BEGIN TRANSACTION-1002-0009442500
--BEGIN TRANSACTION
--SQL-1002-0009442501
--set option PUBLIC.Auditing_options = '4294967295'
--AUDIT-1002-0009442560 -- 2009/10/30 14:55:09.653 Operation Succeeded
----AUDIT-1002-0009442575 -- 2009/10/30 14:55:09.653 Auditing options
set to 4294967295
----COMMIT-1002-0009442601
--COMMIT WORK
--AUDIT-1002-0009442602 -- 2009/10/30 14:55:09.663 Checking DBA
authority
----AUDIT-1002-0009442626 -- 2009/10/30 14:55:09.663 ------>Start
Audit.
----AUDIT-1002-0009442661 -- 2009/10/30 14:55:09.673 Checking if user
is DBA - Yes
----AUDIT-1002-0009442681 -- 2009/10/30 14:55:09.673 Checking if user
is DBA - Yes
----BEGIN TRANSACTION-1002-0009442701
--BEGIN TRANSACTION
--UPDATE-1002-0009443060
--UPDATE DBA.customer_master
-- SET first_name='Dana'
-- WHERE customer_number='00999001305134870018'
--COMMIT-1002-0009443154
--COMMIT WORK
--AUDIT-1002-0009443155 -- 2009/10/30 14:55:09.683 Checking if user is
DBA - Yes
----AUDIT-1002-0009443175 -- 2009/10/30 14:55:09.693 Checking DBA
authority
----AUDIT-1002-0009443199 -- 2009/10/30 14:55:09.693 <------End Audit.
----ROLLBACK-1002-0009443232
--ROLLBACK WORK
--CHECKPOINT-0000-0009443239-2009-10-30 14:55
==========

I see the failed logon attempt in the output but not the SELECT
statement.

You're not going to see the select statement itself, but you will see
the permission checks that are done when the select statement is
executed. In this case, you are connecting as the user who is the owner
of the table you're selecting from, so the "Checking if user is DBA -
Yes" line is the one checking the permissions for the select statement.

Graeme Perrow
Senior Software Developer
gperrow _at_ ianywhere _dot_ com
Sybase iAnywhere Engineering

SQL Anywhere Developer Community
http://www.sybase.com/developer/libr...ere-techcorner
SQL Anywhere Blog Center
http://www.sybase.com/sqlanyblogs


SalePoint wrote:

Graeme Perrow [Sybase iAnywhere] wrote:
That said, is seems to be a much more senseful test to connect as a
different user (withour or without permissions on the table) and select
from the same table, and then consult the audit information.

Regards
Volker

On Nov 2, 10:30*am, Volker Barth <No_VBarth (AT) Spam_GLOBAL-FINANZ (DOT) de>
wrote:
Thanks for the info.

This is somewhat related:
Is there a way to automatically start logging anytime dbisql is opened
(i.e.: always runs as if the user typed "START LOGGING 'c:
\filename.log'" at the beginning of any dbisql session)?