May just broken since Catalina 😞 (REF: https://osquery.slack.com/archives/C08VA3XQU/p1612388283040600)

Bah, closer I feel but still seeing errors

W1022 11:15:11.491048 -379981248 auto_constructed_tables.cpp:57] ATC Table: Error Code: 1 Could not generate data: Could not prepare database for path /Library/Application Support/com.apple.TCC/TCC.db

still have a gut feeling this is the right cirection to be heading in just missing perhaps the final piece

🤔 looks like fleet has a table

Indeed GH pr for related work is here

Uses

fleetd

instead

(fleetd being a premium product )

Oh, yeah, it's me again ... Soooooo looks like I've found a way through ATC to read TCC.db There's still work to be done as some of the columns have changed; this however is working "ish" section from my ATC.json file

...
{
  "auto_table_construction": {
    "tcc_system_entries": {
      "query": "SELECT service, client, auth_value, last_modified FROM access;",
      "path": "/Library/Application Support/com.apple.TCC/TCC.db",
      "columns": [
        "service",
        "client",
        "auth_value",
        "last_modified"
      ],
      "platform": "darwin"
    },
...

Results in :

~/osqueryi --config_path /tmp/atc/tcc.json --json                            
Using a virtual database. Need help, type '.help'
osquery> select * from tcc_system_entries limit 1;
[
  {"auth_value":"2","client":"com.objective-see.blockblock","last_modified":"1686399016","path":"/Library/Application Support/com.apple.TCC/TCC.db","service":"kTCCServiceEndpointSecurityClient"}
]
osquery> ^C

Converting the last_modified unix timestamp to something readable and denoting what auth_value means are going to be my next steps on this. UPDATE: because I forgot about the --json flag:

~/osqueryi --config_path /tmp/atc/tcc.json       
Using a virtual database. Need help, type '.help'
osquery> select * from tcc_system_entries limit 1;
+-----------------------------------+------------------------------+------------+---------------+---------------------------------------------------+
| service                           | client                       | auth_value | last_modified | path                                              |
+-----------------------------------+------------------------------+------------+---------------+---------------------------------------------------+
| kTCCServiceEndpointSecurityClient | com.objective-see.blockblock | 2          | 1686399016    | /Library/Application Support/com.apple.TCC/TCC.db |
+-----------------------------------+------------------------------+------------+---------------+---------------------------------------------------+

So getting closer finally

auth_value

can be one of denied(0), unknown(1), allowed(2), or limited(3) so by using

CASE

SQL syntax we can map this to something useful.

SELECT service, client, auth_value, last_modified, CASE auth_value WHEN 0 THEN 'denied' WHEN 1 THEN 'unknown' WHEN 2 THEN 'allowed' WHEN 3 THEN 'limited' ELSE 'undefined' END AS auth_status FROM your_table_name;

🆗 🤞 let's see if this gets us anywhere ...

Closer still but the

CASE

syntax isn't behaving as expected it seems:

osquery> select * from tcc_system_entries limit 1;
+-----------------------------------+------------------------------+------------+---------------------+---------------------------------------------------+
| service                           | client                       | auth_value | last_modified_date  | path                                              |
+-----------------------------------+------------------------------+------------+---------------------+---------------------------------------------------+
| kTCCServiceEndpointSecurityClient | com.objective-see.blockblock | 2          | 2023-06-10 12:10:16 | /Library/Application Support/com.apple.TCC/TCC.db |
+-----------------------------------+------------------------------+------------+---------------------+---------------------------------------------------+

Looks like I need to dust off some SQLfu to get this to where it's needed 🤔

🤦‍♂️ or I should just ensure the ATC configuration actually maps to the columns being stated in the

CASE

statement

{
  "auto_table_construction": {
    "tcc_system_entries": {
      "query": "SELECT service, client, auth_value, datetime(last_modified, 'unixepoch') AS last_modified_date, CASE auth_value WHEN 0 THEN 'denied' WHEN 1 THEN 'unknown' WHEN 2 THEN 'allowed' WHEN 3 THEN 'limited' ELSE 'undefined' END AS auth_status FROM access;",
      "path": "/Library/Application Support/com.apple.TCC/TCC.db",
      "columns": [
        "service",
        "client",
        "auth_status",
        "last_modified_date"
      ],
      "platform": "darwin"
    },
...

Results in:

osquery> select * from tcc_system_entries limit 1;
+-----------------------------------+------------------------------+-------------+---------------------+---------------------------------------------------+
| service                           | client                       | auth_status | last_modified_date  | path                                              |
+-----------------------------------+------------------------------+-------------+---------------------+---------------------------------------------------+
| kTCCServiceEndpointSecurityClient | com.objective-see.blockblock | allowed     | 2023-06-10 12:10:16 | /Library/Application Support/com.apple.TCC/TCC.db |
+-----------------------------------+------------------------------+-------------+---------------------+---------------------------------------------------+

Knew ATC was the path to getting to read TCC.db once again.