joungmin/vpd-permission-poc
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
vpd-permission-poc/run.sh
devmrko 1f4a9c7e64 Wire DDS variant into run.sh as optional subcommands 
Adds dds / dds-setup / dds-tests / dds-teardown subcommands so the
26ai Deep Data Security variant can be run from the same one-click
entry point. Not part of `./run.sh all` since DDS requires 26ai
(23.26.2+) which not every ADB has.

- sql/adb/14_tests_dds_user.sql: shared verification script for all
  4 ddsuser_*; uses WHENEVER SQLERROR CONTINUE so the expected
  ORA-00942 (deny-by-hiding) doesn't abort the script. Includes
  bypass attempts against the underlying VPD views, raw DB Links,
  and the VPD permission tables.
- sql/adb/15_dds_cleanup.sql: idempotent teardown for DDS objects
  (data grants, end users, data roles, dds_db_role, DDS-only views).
- run.sh: do_dds_prereq / do_dds_setup / do_dds_tests /
  do_dds_teardown helpers; dispatch case extended.

Also fixes a pre-existing secrets-leak gap: both 07_end_users.sql
and 13_dds_variant.sql had SET DEFINE ON without SET VERIFY OFF,
which causes sqlplus to echo the substituted DDL (including the
IDENTIFIED BY <password> clause) on the `new 1:` line. Added
SET VERIFY OFF.

E2E re-verified on ADB 23.26.2.2.0: matrix identical to manual run
(MY=17 / PG=12 / BOTH=12+17 / NONE=ORA-00942 on both), no password
in logs, dds-teardown leaves no residue.

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
2026-05-26 15:53:15 +09:00

243 lines
9.5 KiB
Bash
Executable File
Raw Permalink Blame History

#!/usr/bin/env bash
# ============================================================
# run.sh — VPD Permission POC 원클릭 오케스트레이터
#
# 사용법:
# ./run.sh # = ./run.sh all
# ./run.sh prereq # 도구 존재 / .env 변수만 검증
# ./run.sh source # 원격 PG + MySQL 에 customers 테이블/seed 생성
# ./run.sh adb # ADB 측 cleanup → dblinks → perm/ctx/view/policy → end_users
# ./run.sh tests # 4-user (my/pg/both/none) 로 접속해서 행 필터 검증
# ./run.sh audit # admin 으로 정책/뷰/유저 상태 점검
# ./run.sh all # source → adb → tests → audit
# ./run.sh teardown # ADB 측 객체 + 원격 link/cred 만 정리 (원격 PG/MySQL 데이터는 보존)
#
# (선택) 26ai Deep Data Security 변형 — VPD 데모와 공존하며 같은 4-user 매트릭스를 재구현:
# ./run.sh dds-setup # 13_dds_variant.sql 적용 (END USER + DATA ROLE + DATA GRANT)
# ./run.sh dds-tests # 4명 ddsuser_* 로 매트릭스 검증
# ./run.sh dds # dds-setup → dds-tests
# ./run.sh dds-teardown # DDS 객체만 정리 (VPD 데모는 보존)
#
# 환경설정:
# cp .env.example .env → 값 채우고 → ./run.sh
# ============================================================
set -Eeuo pipefail
ROOT="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
cd "$ROOT"
# --- 1) .env 로드 ---
if [[ ! -f "$ROOT/.env" ]]; then
echo "[FAIL] .env 가 없습니다. cp .env.example .env 후 값을 채워주세요." >&2
exit 1
fi
# shellcheck disable=SC1091
set -a; . "$ROOT/.env"; set +a
# --- 2) 공용 함수 로드 ---
# shellcheck disable=SC1091
. "$ROOT/scripts/lib/common.sh"
CMD="${1:-all}"
# ============================================================
# 헬퍼: ADB sqlplus 한 번 호출
# 인자: $1 = SQL 파일 경로 (DEFINE 변수는 환경변수에서 합성)
# ADB_PASSWORD 가 비어있으면 prompt.
# ============================================================
run_sqlplus_file() {
local sql_file="$1"
[[ -f "$sql_file" ]] || die "SQL 파일 없음: $sql_file"
log "sqlplus ← $sql_file"
# heredoc 으로 DEFINE 주입 후 @sql_file 실행.
# 비번은 stdin 으로 흘려보내고 셸 트레이스/echo 에 안 남게 한다.
sqlplus -S -L "${ADB_USER}/${ADB_PASSWORD}@${ADB_TNS}" <<SQLEOF
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET DEFINE ON
DEFINE DBLINK_PG_NAME = "${DBLINK_PG_NAME}"
DEFINE DBLINK_MY_NAME = "${DBLINK_MY_NAME}"
DEFINE PG_HOST = "${PG_HOST}"
DEFINE PG_PORT = ${PG_PORT}
DEFINE PG_DB = "${PG_DB}"
DEFINE PG_USER = "${PG_USER}"
DEFINE PG_PASSWORD = "${PG_PASSWORD}"
DEFINE MY_HOST = "${MY_HOST}"
DEFINE MY_PORT = ${MY_PORT}
DEFINE MY_DB = "${MY_DB}"
DEFINE MY_USER = "${MY_USER}"
DEFINE MY_PASSWORD = "${MY_PASSWORD}"
DEFINE VPDUSER_MY_PASSWORD = "${VPDUSER_MY_PASSWORD}"
DEFINE VPDUSER_PG_PASSWORD = "${VPDUSER_PG_PASSWORD}"
DEFINE VPDUSER_BOTH_PASSWORD = "${VPDUSER_BOTH_PASSWORD}"
DEFINE VPDUSER_NONE_PASSWORD = "${VPDUSER_NONE_PASSWORD}"
DEFINE DDSUSER_MY_PASSWORD = "${DDSUSER_MY_PASSWORD:-}"
DEFINE DDSUSER_PG_PASSWORD = "${DDSUSER_PG_PASSWORD:-}"
DEFINE DDSUSER_BOTH_PASSWORD = "${DDSUSER_BOTH_PASSWORD:-}"
DEFINE DDSUSER_NONE_PASSWORD = "${DDSUSER_NONE_PASSWORD:-}"
@${sql_file}
SQLEOF
}
# ============================================================
# 헬퍼: 엔드유저 (vpduser_my / pg / both / none) 로 sqlplus 호출
# 인자: $1 = USER, $2 = PASSWORD, $3 = SQL 파일
# ============================================================
run_sqlplus_as() {
local user="$1" pw="$2" sql_file="$3"
[[ -f "$sql_file" ]] || die "SQL 파일 없음: $sql_file"
log "sqlplus(${user}) ← $sql_file"
sqlplus -S -L "${user}/${pw}@${ADB_TNS}" <<SQLEOF
WHENEVER SQLERROR CONTINUE
@${sql_file}
SQLEOF
}
# ============================================================
# 단계별 함수
# ============================================================
do_prereq() {
log "=== prereq: 도구 및 환경변수 검증 ==="
need_cmd sqlplus "Oracle Instant Client (sqlplus) PATH 등록 필요"
need_cmd psql "PostgreSQL client (psql) 설치 필요 — brew install libpq 등"
need_cmd mysql "MySQL client 설치 필요 — brew install mysql-client 등"
require_env \
TNS_ADMIN ADB_TNS ADB_USER \
VPDUSER_MY_PASSWORD VPDUSER_PG_PASSWORD \
VPDUSER_BOTH_PASSWORD VPDUSER_NONE_PASSWORD \
PG_HOST PG_PORT PG_DB PG_USER \
MY_HOST MY_PORT MY_DB MY_USER \
DBLINK_PG_NAME DBLINK_MY_NAME
prompt_password_if_empty ADB_PASSWORD "ADB ADMIN"
prompt_password_if_empty PG_PASSWORD "PostgreSQL (${PG_USER}@${PG_HOST})"
prompt_password_if_empty MY_PASSWORD "MySQL (${MY_USER}@${MY_HOST})"
[[ -d "$TNS_ADMIN" ]] || die "TNS_ADMIN 디렉토리가 존재하지 않음: $TNS_ADMIN"
[[ -f "$TNS_ADMIN/tnsnames.ora" ]] || warn "tnsnames.ora 가 $TNS_ADMIN 에 없음 — 확인 필요"
ok "prereq 통과"
}
do_source() {
log "=== source: 원격 Postgres / MySQL 에 customers 테이블 + seed ==="
log "-- Postgres ($PG_HOST:$PG_PORT/$PG_DB) --"
PGPASSWORD="$PG_PASSWORD" psql \
-h "$PG_HOST" -p "$PG_PORT" -U "$PG_USER" -d "$PG_DB" \
-v ON_ERROR_STOP=1 \
-f "$ROOT/sql/source/postgres_setup.sql"
ok "Postgres source 준비 완료"
log "-- MySQL ($MY_HOST:$MY_PORT/$MY_DB) --"
mysql \
-h "$MY_HOST" -P "$MY_PORT" -u "$MY_USER" "-p${MY_PASSWORD}" \
"$MY_DB" < "$ROOT/sql/source/mysql_setup.sql"
ok "MySQL source 준비 완료"
}
do_adb() {
log "=== adb: ADB 측 cleanup → dblinks → perm/ctx/view/policy → end_users ==="
run_sqlplus_file "$ROOT/sql/adb/00_cleanup.sql"
run_sqlplus_file "$ROOT/sql/adb/01_dblinks.sql"
run_sqlplus_file "$ROOT/sql/adb/02_perm_tables.sql"
run_sqlplus_file "$ROOT/sql/adb/03_seed.sql"
run_sqlplus_file "$ROOT/sql/adb/04_secure_ctx.sql"
run_sqlplus_file "$ROOT/sql/adb/05_views.sql"
run_sqlplus_file "$ROOT/sql/adb/06_policy.sql"
run_sqlplus_file "$ROOT/sql/adb/06a_redaction.sql"
run_sqlplus_file "$ROOT/sql/adb/07_end_users.sql"
ok "ADB setup 완료"
}
do_tests() {
log "=== tests: 4-user access matrix 검증 ==="
run_sqlplus_as vpduser_my "$VPDUSER_MY_PASSWORD" "$ROOT/sql/adb/08_tests_user_my.sql"
run_sqlplus_as vpduser_pg "$VPDUSER_PG_PASSWORD" "$ROOT/sql/adb/09_tests_user_pg.sql"
run_sqlplus_as vpduser_both "$VPDUSER_BOTH_PASSWORD" "$ROOT/sql/adb/10_tests_user_both.sql"
run_sqlplus_as vpduser_none "$VPDUSER_NONE_PASSWORD" "$ROOT/sql/adb/11_tests_user_none.sql"
ok "4명 (MY / PG / BOTH / NONE) 테스트 실행 완료"
}
do_audit() {
log "=== audit: admin 으로 정책 / 뷰 / 매트릭스 점검 ==="
run_sqlplus_file "$ROOT/sql/adb/12_tests_admin_audit.sql"
ok "audit 완료"
}
do_teardown() {
log "=== teardown: ADB 측 객체 + dblink/credential 정리 ==="
warn "원격 PG/MySQL 의 customers 테이블은 건드리지 않습니다 (수동으로 DROP 하세요)"
run_sqlplus_file "$ROOT/sql/adb/00_cleanup.sql"
ok "teardown 완료"
}
# ============================================================
# (선택) DDS 단계 — Oracle 26ai Deep Data Security
# 메인 VPD 데모와 공존. 26ai 미지원 ADB 에서는 ORA-00942 등으로 실패함.
# ============================================================
do_dds_prereq() {
require_env \
DDSUSER_MY_PASSWORD DDSUSER_PG_PASSWORD \
DDSUSER_BOTH_PASSWORD DDSUSER_NONE_PASSWORD
log "DDS 변형용 비밀번호 4개 확인됨"
}
do_dds_setup() {
log "=== dds-setup: 13_dds_variant.sql (END USER + DATA ROLE + DATA GRANT) ==="
# 멱등성: 부분 적용 상태를 먼저 청소한 뒤 재생성
run_sqlplus_file "$ROOT/sql/adb/15_dds_cleanup.sql"
run_sqlplus_file "$ROOT/sql/adb/13_dds_variant.sql"
ok "DDS 셋업 완료"
}
do_dds_tests() {
log "=== dds-tests: 4-user (ddsuser_*) 매트릭스 검증 ==="
# END USER 이름이 lowercase 라 사용자명을 큰따옴표로 감싸서 전달.
run_sqlplus_as '"ddsuser_my"' "$DDSUSER_MY_PASSWORD" "$ROOT/sql/adb/14_tests_dds_user.sql"
run_sqlplus_as '"ddsuser_pg"' "$DDSUSER_PG_PASSWORD" "$ROOT/sql/adb/14_tests_dds_user.sql"
run_sqlplus_as '"ddsuser_both"' "$DDSUSER_BOTH_PASSWORD" "$ROOT/sql/adb/14_tests_dds_user.sql"
run_sqlplus_as '"ddsuser_none"' "$DDSUSER_NONE_PASSWORD" "$ROOT/sql/adb/14_tests_dds_user.sql"
ok "DDS 4명 (MY / PG / BOTH / NONE) 테스트 실행 완료"
}
do_dds_teardown() {
log "=== dds-teardown: DDS 객체만 정리 (VPD 데모는 보존) ==="
run_sqlplus_file "$ROOT/sql/adb/15_dds_cleanup.sql"
ok "DDS teardown 완료"
}
# ============================================================
# 디스패치
# ============================================================
case "$CMD" in
prereq) do_prereq ;;
source) do_prereq; do_source ;;
adb) do_prereq; do_adb ;;
tests) do_prereq; do_tests ;;
audit) do_prereq; do_audit ;;
teardown) do_prereq; do_teardown ;;
all)
do_prereq
do_source
do_adb
do_tests
do_audit
ok "=== ALL DONE — VPD POC 전체 파이프라인 통과 ==="
;;
dds-setup) do_prereq; do_dds_prereq; do_dds_setup ;;
dds-tests) do_prereq; do_dds_prereq; do_dds_tests ;;
dds-teardown) do_prereq; do_dds_teardown ;;
dds)
do_prereq; do_dds_prereq
do_dds_setup
do_dds_tests
ok "=== DDS DONE — Deep Data Security 변형 셋업 + 검증 통과 ==="
;;
*)
die "알 수 없는 명령: $CMD (사용: prereq|source|adb|tests|audit|all|teardown | dds|dds-setup|dds-tests|dds-teardown)"
;;
esac
Reference in New Issue View Git Blame Copy Permalink