Title
#general
j

Julia Cox

11/19/2021, 2:06 PM
Hey! I know some people (cc @puffycid @Seth Hanford) have gotten ATCs to work for safari history, but every time I've tried I get errors like
I1119 08:43:21.214730 306961920 virtual_sqlite_table.cpp:91] Cannot open specified database: SQLITE_CANTOPEN
I1119 08:43:21.214769 306961920 auto_constructed_tables.cpp:38] ATC Table: Unable to detect journal mode, applying default locking policy for path /Users/xxx/Library/Safari/History.db
I1119 08:43:21.215098 306961920 virtual_sqlite_table.cpp:91] Cannot open specified database: SQLITE_CANTOPEN
Is this potentially related to the extra security on the
Library/Safari
directory, or the locking if Safari is running? Just curious if anyone had encountered similar errors. I don't have my own Mac so debugging is challenging!
a

alessandrogario

11/19/2021, 2:11 PM
osquery was not supposed to allow accessing the browser history 😞 it's one of the many ways ATC can be misused to go against the osquery guidelines (respect user privacy)
j

Julia Cox

11/19/2021, 2:36 PM
I thought that was just why it's relegated to ATC instead of in core tables? which totally makes sense!
puffycid

puffycid

11/19/2021, 5:30 PM
Hey @Julia Cox I just tested my basic safari atc config on macOS Monterey and it stilled seemed to work fine (using safari 15.1).
osquery> select * from safari_history;
                                path = /Users/puffycid/Library/Safari/History.db
                                   id = 103
                                  url = <http://cnn.com/>
                     domain_expansion = cnn
                          visit_count = 1
                   daily_visit_counts = d
                  weekly_visit_counts =
                autocomplete_triggers =
should_recompute_derived_visit_counts = 0
                    visit_count_score = 100
                          status_code = 0

                                 path = /Users/puffycid/Library/Safari/History.db
                                   id = 104
                                  url = <https://www.cnn.com/>
                     domain_expansion = cnn
                          visit_count = 1
                   daily_visit_counts = d
                  weekly_visit_counts =
                autocomplete_triggers =
should_recompute_derived_visit_counts = 0
                    visit_count_score = 100
                          status_code = 0

                                 path = /Users/puffycid/Library/Safari/History.db
                                   id = 105
                                  url = <https://www.google.com/search?client=safari&rls=en&q=osquery&ie=UTF-8&oe=UTF-8>
                     domain_expansion = google
                          visit_count = 2
                   daily_visit_counts = d
                  weekly_visit_counts =
                autocomplete_triggers =
should_recompute_derived_visit_counts = 0
                    visit_count_score = 100
                          status_code = 0

                                 path = /Users/puffycid/Library/Safari/History.db
                                   id = 106
                                  url = <https://osquery.io/>
                     domain_expansion =
                          visit_count = 1
                   daily_visit_counts = d
                  weekly_visit_counts =
                autocomplete_triggers =
should_recompute_derived_visit_counts = 0
                    visit_count_score = 100
                          status_code = 0

                                 path = /Users/puffycid/Library/Safari/History.db
                                   id = 107
                                  url = <https://www.google.com/search?client=safari&rls=en&q=honestsecurity&ie=UTF-8&oe=UTF-8>
                     domain_expansion = google
                          visit_count = 2
                   daily_visit_counts = d
                  weekly_visit_counts =
                autocomplete_triggers =
should_recompute_derived_visit_counts = 0
                    visit_count_score = 100
                          status_code = 0

                                 path = /Users/puffycid/Library/Safari/History.db
                                   id = 108
                                  url = <https://honest.security/>
                     domain_expansion =
                          visit_count = 1
                   daily_visit_counts = d
                  weekly_visit_counts =
                autocomplete_triggers =
should_recompute_derived_visit_counts = 0
                    visit_count_score = 100
                          status_code = 0
this worked with both safari opened and closed Can u share ur atc config file? In regards to browser history access, I think viewing browser history can be useful for investigations and if analyst is trying to figure out if an attacker/compromised account accessed any internal websites (ex: Sharepoint, source code, web apps, etc) or the account downloaded any malware, or uploaded any files to external services (or if the account visited them) During an incident response (IR) investigation browser history is likely going to be collected in order to do full investigations, and if an organization is trying to use osquery to investigate having the option query it (either native, extension, atc, etc) would probably be nice Just my 2 cents
zwass

zwass

11/19/2021, 5:46 PM
j

Julia Cox

11/19/2021, 8:09 PM
@puffycid my atc config:
"safari_history":{
      "query":"SELECT url, domain_expansion AS title, MAX(visit_count) AS visit_count, datetime(MAX(visit_time)+978307200, \"unixepoch\") AS last_visited FROM history_items JOIN history_visits GROUP BY url, domain_expansion",
      "path":"/Users/%/Library/Safari/History.db",
      "columns":[
        "last_visited",
        "url",
        "title",
        "visit_count"
      ]
    }
8:11 PM
and thanks @zwass that looks very helpful! I am actually running in interactive mode, rather than using the daemon, so the terminal permissions could definitely be the issue
8:20 PM
or I guess I should say, I'm running from the terminal rather than as a service. And I haven't granted anything full disk access so I bet that's the problem
puffycid

puffycid

11/20/2021, 12:17 AM
just to follow up, i tried ur config julia and it worked for me so i agree may be full disk access issue
j

Julia Cox

11/22/2021, 1:49 PM
thanks so much! really appreciate it. I'll try with full disk access