Bigquery - write only authorization

Question

I am new to Google's Bigquery, so apologize my ignorance.

The idea was to use Bigquery to store events that happen inside applications that are installed at different sites. The sites are completely isolated entities and therefore one site should not be able to access the data from another site.

At the same time consolidated views should also be possible, assuming a user with more permissions (e.g. a manager in some head quarter facility)

The amount of sites can run into hundreds so it seems difficult to give each of them a separate dataset/table and associated (service) authentication (.p12)

Is there any way I can set this up in Bigquery

All tips welcomed.

Many thanks!

Peter

Taphole

Answer 1

Write only authorization (specifically for streaming inserts to bigquery tables only) has been added recently to BigQuery. You can use the https://www.googleapis.com/auth/bigquery.insertdata scope when you authenticate instead of the https://www.googleapis.com/auth/bigquery scope, and the only method the user will be able to use will be tabledata.insertall().

With regards to the type of authorization you want for views, this is a high-priority feature request that we would love to enable. Can you give more details about the scenario you'd like to see work?

Twoseater

Jordan. First thanks for ur reaction.The scenario is as follows:the application deployed on hundreds of unrelated machines push data. Then three types of users will use a BI tool (e.g. tableau online) to view the data. Three types being (1) the user originally pushing the data (2) regional office,grouping a set of (1)'s (3)HQ seeing all data. I guess we can implement the three level viewing using the BI tool,but I want to avoid that the application pushing would have sufficient authorization to read all data (including these of other instances of the application).Hope this clarifies. – Taphole

in the meantime, have you tried using a proxy (like App Engine) to handle the incoming data? – Pyrognostics

Not tried no. I can see how that would work obviously, but I do want to avoid extra code to write and infrastructure hubs to configure. – Taphole

Answer 2

As of June 2017, the best approach to storing events with "insert only" access in BigQuery is to send the events through Stackdriver Logging (now part of Google). Configure an Export in Stackdriver to send the logs to BigQuery. This effectively produces an "insert only" event storage system. You can then configure user roles in BigQuery to aggregate data across multiple sites.

The approach Jordan described is good for preventing an application from accidentally over-writing data. Unfortunately, it does not address the scenario of audit or security logs. There is still no pre-defined IAM role for BigQuery that supports "insert only" access, and custom IAM roles are still in "alpha" status. If a hacker has gained privileged access to a server that can write directly to BigQuery, the hacker will also have access to the BigQuery keyfile that allows them to over-write data in the logs, allowing them to cover their tracks. This defeats the purpose of the logs.

You can push events to Stackdriver directly via the REST API or a client library. You can also configure the Stackdriver Logging Agent to monitor any text-based log on a Linux system.

Admire