SQL Query to get list of S3 buckets exposed to internet

SQL Query to get list of S3 buckets exposed to internet

  2. WITH nested_acl_grants as (
  3.   select
  4.     *,
  5.     contains(
  6.       transform(
  7.         CAST(acl_grants AS ARRAY < JSON >),
  8.         a -> json_extract_scalar(a, '$.Grantee.URI') in (
  9.           'http://acs.amazonaws.com/groups/global/AllUsers',
  10.           'http://acs.amazonaws.com/groups/global/AuthenticatedUsers'
  11.         )
  12.       ),
  13.       true
  14.     ) as acl_flag
  15.   from
  16.     aws_s3_bucket_current
  17. ),
  18. unnested_acl_grants as(
  19.   select
  20.     *,
  21.     CASE WHEN ignore_public_acls = true
  22.     and restrict_public_buckets = true THEN false WHEN (
  23.       restrict_public_buckets = false
  24.       or restrict_public_buckets is null
  25.     )
  26.     and is_bucket_policy_status_public = true THEN true WHEN (
  27.       ignore_public_acls is null
  28.       or ignore_public_acls = false
  29.     )
  30.     and acl_flag = true
  31.     and acl_flag is not null THEN true else false end as public_access
  32.   from
  33.     nested_acl_grants
  34. ),
  35. s3 AS (
  36.   select
  37.     a.region_code AS regionCode,
  38.     a.bucket_name AS bucketName,
  39.     a.customer_id AS customerId,
  40.     a.acl_grants AS aclGrants,
  41.     a.acl_owner_display_name AS aclOwnerDisplayName,
  42.     a.acl_owner_id AS aclOwnerId,
  43.     a.block_public_acls AS blockPublicAcls,
  44.     a.block_public_policy AS blockPublicPolicy,
  45.     a.bucket_accelerate_status AS bucketAccelerateStatus,
  46.     a.bucket_cors_enabled AS bucketCorsEnabled,
  47.     a.bucket_creation_date AS bucketCreationDate,
  48.     a.bucket_logging_enabled AS bucketLoggingEnabled,
  49.     a.bucket_logging_target_bucket AS bucketLoggingTargetBucket,
  50.     a.bucket_logging_target_grants AS bucketLoggingTargetGrants,
  51.     a.bucket_logging_target_prefix AS bucketLoggingTargetPrefix,
  52.     a.bucket_policy_serverside_encryption_enabled AS bucketPolicyServersideEncryptionEnabled,
  53.     a.bucket_public_access_block_enabled AS bucketPublicAccessBlockEnabled,
  54.     a.bucket_region AS bucketRegion,
  55.     a.bucket_request_payer AS bucketRequestPayer,
  56.     a.bucket_ssl_access_only AS bucketSslAccessOnly,
  57.     a.bucket_versioning_status_enabled AS bucketVersioningStatusEnabled,
  58.     a.cors_rules AS corsRules,
  59.     a.ignore_public_acls AS ignorePublicAcls,
  60.     a.is_bucket_policy_status_public AS isBucketPolicyStatusPublic,
  61.     a.mfa_delete_status_enabled AS mfaDeleteStatusEnabled,
  62.     a.metrics_configuration_list AS metricsConfigurationList,
  63.     a.notification_config_event_bridge_config AS notificationConfigEventBridgeConfig,
  64.     a.notification_config_lambda_function_configs AS notificationConfigLambdaFunctionConfigs,
  65.     a.notification_config_queue_configs AS notificationConfigQueueConfigs,
  66.     a.notification_config_topic_configs AS notificationConfigTopicConfigs,
  67.     a.object_lock_configuration_rule AS objectLockConfigurationRule,
  68.     a.object_lock_enabled AS objectLockEnabled,
  69.     a.object_ownership_control_rules AS objectOwnershipControlRules,
  70.     a.public_access_block_enabled AS publicAccessBlockEnabled,
  71.     a.restrict_public_buckets AS restrictPublicBuckets,
  72.     a.server_side_encryption_rules AS serverSideEncryptionRules,
  73.     a.serverside_encryption_enabled AS serversideEncryptionEnabled,
  74.     a.tags AS tags,
  75.     a.website_enabled AS websiteEnabled,
  76.     a.account_id AS accountId,
  77.     a.region AS region,
  78.     b.public_access AS isBucketPublic
  79.   FROM
  80.     aws_s3_bucket_current a
  81.     INNER JOIN unnested_acl_grants b ON a.bucket_name = b.bucket_name
  82. )
  83. SELECT
  84.     *
  85. FROM
  86.     s3
  87. WHERE
  88.   
  89.   isBucketPublic=true

    • Related Articles

    • Query to get Installed date of apps on apps table

      Following query can be used to get the installed date of apps The installed date of apps on 'apps' table will be available from 5.10.x Uptycs Osquery release. select path, datetime(date_added_time,'unixepoch') as app_installed_time from apps Here ...

    • SQL query to fetch all the details of assets

      SQL query to fetch the instance id, tags ,last activity ,OS, OS version ,Last enrolled ,OSQ version, Gateway IP ,Interface Name ,IP ,asset group select ua.host_name as Hostname, ua.id as InstanceID, ua.tags as TAGS, ua.last_activity_at as ...

    • Sql query to find the asset-activity within the given time stamps

      please find the below query for finding the list of commands executed along with the user and cmd line and key attributes you can add multiple tables based on your needs ,in this query added only 5 tables which are mostly used you can also modify the ...

    • List the vulnerabilities in the fleet which is having remediation

      please find the below query you can modify the query based on the need and conditons required with vulners as (SELECT *, ROW_NUMBER() OVER(PARTITION BY upt_asset_id, cve_list, package_name, package_version, os, os_version ORDER BY IF(upt_type = ...

    • Queries in Uptycs Global Investigation Page from Detection and Alerts table

      Leveraging SQL queries within the Global Investigation page allows users to extract precise insights regarding detections. Below, we've compiled a set of SQL queries tailored to fulfill various investigative needs: 1. Query for Total Assets by ...