서비스 프린시펄 (Service Principal) 상세

# Account 수준 SP 생성
databricks account service-principals create \
  --display-name "etl-pipeline-sp" \
  --active true

# 결과 예시
# {
#   "id": "7890123456",
#   "application_id": "abcd-1234-efgh-5678",
#   "display_name": "etl-pipeline-sp",
#   "active": true
# }

curl -X POST \
  "https://accounts.cloud.databricks.com/api/2.0/accounts/<account-id>/scim/v2/ServicePrincipals" \
  -H "Authorization: Bearer <admin-token>" \
  -H "Content-Type: application/json" \
  -d '{
    "displayName": "etl-pipeline-sp",
    "active": true
  }'

resource "databricks_service_principal" "etl_pipeline" {
  display_name = "etl-pipeline-sp"
  active       = true
}

# OAuth Secret 생성
databricks account service-principals create-oauth2-secret \
  --service-principal-id 7890123456

# 결과 예시
# {
#   "id": "secret-abc-123",
#   "secret": "dose_xxxxxxxxxxxxxxxxxxxxxxxx",  ← 이 값을 안전하게 저장하세요!
#   "status": "ACTIVE",
#   "create_time": "2025-01-15T10:30:00Z"
# }

from databricks.sdk import WorkspaceClient

# Service Principal로 인증
w = WorkspaceClient(
    host="https://dbc-abc123.cloud.databricks.com",
    client_id="abcd-1234-efgh-5678",       # SP의 application_id
    client_secret="dose_xxxxxxxxxxxxxxxx"   # OAuth secret
)

# API 호출 테스트
current_user = w.current_user.me()
print(f"인증된 SP: {current_user.display_name}")

# CLI 프로필에 SP 인증 설정
cat >> ~/.databrickscfg << 'EOF'
[etl-pipeline]
host          = https://dbc-abc123.cloud.databricks.com
client_id     = abcd-1234-efgh-5678
client_secret = dose_xxxxxxxxxxxxxxxx
EOF

# 프로필로 CLI 사용
databricks --profile etl-pipeline clusters list

export DATABRICKS_HOST="https://dbc-abc123.cloud.databricks.com"
export DATABRICKS_CLIENT_ID="abcd-1234-efgh-5678"
export DATABRICKS_CLIENT_SECRET="dose_xxxxxxxxxxxxxxxx"

# SDK가 자동으로 환경 변수를 인식합니다
databricks clusters list

# SP를 Account 그룹에 추가
databricks account groups patch <group-id> \
  --json '{
    "Operations": [{
      "op": "add",
      "path": "members",
      "value": [{"value": "7890123456"}]
    }]
  }'

-- SP에 직접 권한 부여
GRANT USE CATALOG ON CATALOG production TO `etl-pipeline-sp`;
GRANT USE SCHEMA ON SCHEMA production.ecommerce TO `etl-pipeline-sp`;
GRANT SELECT, MODIFY ON SCHEMA production.ecommerce TO `etl-pipeline-sp`;

-- 또는 그룹을 통해 간접 부여 (권장)
-- SP를 data_engineers 그룹에 추가한 후, 그룹에 권한 부여
GRANT ALL PRIVILEGES ON SCHEMA production.bronze TO `data_engineers`;

# SP를 특정 워크스페이스에 할당
databricks account workspace-assignment update \
  --workspace-id 12345 \
  --principal-id 7890123456 \
  --permissions USER

from databricks.sdk import WorkspaceClient

w = WorkspaceClient()

# Job 생성 시 SP를 실행 주체로 지정
job = w.jobs.create(
    name="daily-etl-pipeline",
    run_as={"service_principal_name": "etl-pipeline-sp"},
    tasks=[{
        "task_key": "extract",
        "notebook_task": {
            "notebook_path": "/Repos/production/etl/extract"
        },
        "new_cluster": {
            "spark_version": "15.4.x-scala2.12",
            "num_workers": 4,
            "node_type_id": "i3.xlarge"
        }
    }]
)

print(f"Job 생성 완료: {job.job_id}")

# 모든 SP 목록 조회
databricks account service-principals list

# 특정 SP 정보 조회
databricks account service-principals get --id 7890123456

# SP 비활성화 (삭제하지 않고 비활성화)
databricks account service-principals update \
  --id 7890123456 \
  --active false

# 새 Secret 생성
databricks account service-principals create-oauth2-secret \
  --service-principal-id 7890123456

# 기존 Secret 삭제
databricks account service-principals delete-oauth2-secret \
  --service-principal-id 7890123456 \
  --secret-id old-secret-id

-- SP 인벤토리 테이블 (수동 관리 또는 자동화)
CREATE TABLE IF NOT EXISTS admin.governance.sp_inventory (
    sp_name STRING,
    sp_application_id STRING,
    owner_team STRING,
    purpose STRING,
    created_date DATE,
    last_secret_rotation DATE,
    environments ARRAY<STRING>,
    uc_grants STRING,
    status STRING  -- 'ACTIVE', 'DEPRECATED', 'DEACTIVATED'
);

-- 미사용 SP 탐지 (시스템 테이블 활용)
SELECT
    sp.display_name,
    sp.application_id,
    MAX(al.event_time) AS last_activity
FROM system.access.audit AS al
RIGHT JOIN admin.governance.sp_inventory AS sp
    ON al.user_identity.email = sp.sp_name
WHERE sp.status = 'ACTIVE'
GROUP BY sp.display_name, sp.application_id
HAVING MAX(al.event_time) < CURRENT_DATE() - INTERVAL 90 DAYS
    OR MAX(al.event_time) IS NULL
ORDER BY last_activity;

# 매 90일마다 실행되는 Secret 로테이션 Job
from databricks.sdk import AccountClient
import json

def rotate_sp_secret(sp_id: str, sp_name: str):
    """SP의 OAuth Secret을 다운타임 없이 교체합니다."""

    account_client = AccountClient(
        host="https://accounts.cloud.databricks.com",
        # 로테이션 Job 자체도 SP로 실행 (관리자 SP)
        client_id=dbutils.secrets.get("admin-secrets", "admin-sp-client-id"),
        client_secret=dbutils.secrets.get("admin-secrets", "admin-sp-secret")
    )

    # Step 1: 새 Secret 생성 (이 시점에서 기존 + 새 Secret 모두 유효)
    new_secret = account_client.service_principals.create_oauth2_secret(
        service_principal_id=sp_id
    )

    # Step 2: Vault/Secret Manager에 새 Secret 저장
    # (실제로는 AWS Secrets Manager, Azure Key Vault 등에 저장)
    dbutils.secrets.put(
        scope="prod-sp-secrets",
        key=f"{sp_name}-client-secret",
        string_value=new_secret.secret
    )

    # Step 3: 새 Secret이 전파될 시간을 대기 (보수적으로 5분)
    import time
    time.sleep(300)

    # Step 4: 기존 Secret 삭제
    old_secrets = account_client.service_principals.list_oauth2_secrets(
        service_principal_id=sp_id
    )
    for old in old_secrets:
        if old.id != new_secret.id:
            account_client.service_principals.delete_oauth2_secret(
                service_principal_id=sp_id,
                secret_id=old.id
            )

    print(f"✅ {sp_name} Secret 로테이션 완료")