Checking the Validity of a Table Audit

Fyi... Instead of having to create the output file as I did in in the example above, you can stream the output from VSQL to WC to get the output size in bytes:

Example:

[dbadmin@s18384357 ~]$ /opt/vertica/bin/vsql -c "SELECT audit('public.big_table2', 0, 100);"
   audit
------------
5916352340
(1 row)

[dbadmin@s18384357 ~]$ /opt/vertica/bin/vsql -F '' -R '' -At -c "SELECT * FROM public.big_table2;" -q | wc -c
5916352341

Thanks!

Question:
My vsq returns, no data.
sankarmn=> SELECT AUDIT('', 'database');

AUDIT

0
(1 row)
How do I know if audit is enabled in database?

Do you have any tables in your database with data?

Check out the link below that lists what is not audited:
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AdministratorsGuide/Licensing/CalculatingTheDatabaseSize.htm

Yes, there are tables with data in database.

How do I know if audit for database is enabled?
sankarmn=> SELECT AUDIT('',25,90);

AUDIT

0
(1 row)

Not sure I understand your question. You are calling the AUDIT function so it's always available. The function is returning 0 because you have no data that auditable. If you are looking for the results of a user audit, check the USER_AUDITS system table.

Example:

dbadmin=> \d
No relations found.

dbadmin=> SELECT audit('', 'DATABASE');
 audit
-------
 0
(1 row)

dbadmin=> \x
Expanded display is on.

dbadmin=> SELECT * FROM user_audits;
-[ RECORD 1 ]-------------------------+------------------------------
size_bytes                            | 0
user_id                               | 45035996273704962
user_name                             | dbadmin
object_id                             | 45035996273704976
object_type                           | DATABASE
object_schema                         |
object_name                           | rename_me
audited_schema_name                   |
audited_object_name                   | rename_me
license_name                          | vertica
audit_start_timestamp                 | 2020-04-30 14:52:50.708464-04
audit_end_timestamp                   | 2020-04-30 14:52:50.71799-04
confidence_level_percent              | 99
error_tolerance_percent               | 5
used_sampling                         | f
confidence_interval_lower_bound_bytes | 0
confidence_interval_upper_bound_bytes | 0
sample_count                          | 0
cell_count                            | 0

Ok, I got it. So when you create a table T1 and populate data, then the table T1 is auditable by default. Then if you run, SELECT audit('', 'DATABASE');, you'll get an output. Correct me if I am wrong.

Also to run the audit function do you need dbadmin privileges or pseudosuperuser role? I don't have either, would sysmonitor help. I need to run audit on other schemas/objects as well. So what privileges do I need?

Table data is not audited when you insert data A table is only audited when you audit it manually! Anyone can run the AUDIT function. You will only be able to audit tables that you own or have been granted access to.

I think the command to audit table is,
=> SELECT AUDIT('online_sales', 'schema');

No, when you specify "SCHEMA", you are auditing a SCHEMA...

Example:

dbadmin=> CREATE SCHEMA sankarmn;
CREATE SCHEMA

dbadmin=> CREATE TABLE sankarmn.tab1 (c INT);
CREATE TABLE

dbadmin=> CREATE TABLE sankarmn.tab2 (c INT);
CREATE TABLE

dbadmin=> INSERT INTO sankarmn.tab1 SELECT 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO sankarmn.tab2 SELECT 1 UNION SELECT 2;
 OUTPUT
--------
      2
(1 row)

dbadmin=> SELECT audit('sankarmn.tab1', 'TABLE');
 audit
-------
 1
(1 row)

dbadmin=> SELECT audit('sankarmn.tab2', 'TABLE');
 audit
-------
 2
(1 row)

dbadmin=> SELECT audit('sankarmn', 'SCHEMA');
 audit
-------
 3
(1 row)

You really don't have to specify the object type (i.e. table or schema). Vertica can figure it out.

dbadmin=> SELECT audit('sankarmn.tab1');
 audit
-------
 1
(1 row)

dbadmin=> SELECT audit('sankarmn.tab2');
 audit
-------
 2
(1 row)

dbadmin=> SELECT audit('sankarmn');
 audit
-------
 3
(1 row)

Thanks a lot Jim_Knicely.
Your inputs are so informative to understand AUDIT clearly.