[Oracle/오라클] 특정 계정의 모든 테이블 조회 권한(SELECT) 한 번에 일괄 부여하기

수십, 수백 개의 테이블 권한... 언제 다 주고 있나요?
실무에서 데이터베이스(DB)를 관리하다 보면, 특정 개발자나 분석가에게 운영 DB의 데이터를 조회할 수 있는 '읽기 전용(SELECT)' 권한만 따로 부여해야 하는 경우가 매우 빈번하게 발생합니다.

테이블이 1~2개라면 GRANT SELECT ON 테이블명 TO 계정명; 명령어를 손수 타이핑해서 권한을 주면 그만입니다. 하지만 운영 시스템의 테이블이 100개, 1,000개가 넘어간다면 어떨까요? 일일이 테이블 이름을 찾아서 권한 부여 쿼리를 1,000번 치는 것은 엄청난 시간 낭비이자 노가다(?)가 아닐 수 없습니다.

오늘은 이런 비효율적인 단순 반복 작업을 클릭 한 번으로 해결할 수 있는, 오라클 시스템 뷰(Dictionary View)를 활용한 '권한 부여 스크립트 자동 생성' 꿀팁을 알아보겠습니다.

 

---

1. 오라클의 특징: 왜 한 번에 권한을 주는 명령어가 없을까?
MySQL이나 MariaDB 같은 다른 데이터베이스를 쓰시던 분들은 오라클에 오면 답답함을 느낄 수 있습니다. 타 DB에서는 GRANT SELECT ON 데이터베이스명.* TO 계정명; 처럼 와일드카드(*)를 사용하여 특정 스키마 안의 모든 테이블 권한을 단 한 줄로 쉽게 부여할 수 있기 때문입니다.

하지만 데이터 보안을 매우 중요하게 생각하는 오라클(Oracle)은 이런 방식의 와일드카드 권한 부여를 기본적으로 지원하지 않습니다. (물론 SELECT ANY TABLE이라는 시스템 권한이 있긴 하지만, 이는 DB 전체의 모든 유저 테이블을 조회할 수 있는 막강한 권한이므로 보안상 절대 남용해서는 안 됩니다.)

따라서 오라클에서는 내가 권한을 주고자 하는 유저의 테이블 목록을 먼저 뽑아낸 뒤, 그 목록을 바탕으로 GRANT 쿼리문 여러 개를 문자열 결합(||)으로 만들어내는 우회 방식을 사용해야 합니다.

 

---

2. 일괄 권한 부여 스크립트 생성 쿼리 만들기
오라클에는 현재 DB에 존재하는 모든 테이블의 정보를 담고 있는 ALL_TABLES라는 시스템 뷰가 있습니다. 이를 활용하여 쿼리문을 작성해 보겠습니다.

예를 들어, 'TEST1'이라는 계정이 소유한 모든 테이블의 조회 권한을 **'APPLE'**이라는 계정에게 넘겨주고 싶다면 아래와 같이 쿼리를 작성합니다.

SELECT 'GRANT SELECT ON TEST1.' || TABLE_NAME || ' TO APPLE;'  FROM ALL_TABLES  WHERE OWNER = 'TEST1';

 

쿼리 상세 설명:

ALL_TABLES: 사용자가 접근할 수 있는 모든 테이블의 정보를 보여줍니다. (DBA 권한이 있다면 DBA_TABLES를 사용하셔도 됩니다.)

WHERE OWNER = 'TEST1': 무수히 많은 테이블 중, 소유자가 'TEST1'인 테이블만 조건으로 걸러냅니다.

|| (파이프 연산자): 오라클에서 문자열을 하나로 합쳐주는 연산자입니다. 엑셀의 & 기호와 같은 역할을 합니다.

결과적으로 이 쿼리는 테이블 개수만큼 GRANT SELECT ON TEST1.테이블명 TO APPLE; 이라는 텍스트(문자열) 형태의 결과 행을 만들어냅니다.

---

3. 실제 권한 부여 실행하기 (주의사항)
여기서 초보자분들이 가장 많이 하는 실수가 있습니다. 위 2번의 쿼리를 실행했다고 해서 'APPLE' 계정에게 권한이 실제로 들어간 것은 아닙니다! 위 쿼리는 단지 우리가 직접 쳐야 할 수백 줄의 GRANT 명령어를 텍스트로 대신 써준 것뿐입니다.

실제로 권한을 적용하려면 다음 단계를 거쳐야 합니다.

앞서 작성한 SELECT 쿼리를 실행(Run)합니다.

하단에 출력된 결과물(수십~수백 줄의 GRANT ... ; 텍스트 목록)을 마우스로 전체 드래그하여 복사(Ctrl+C) 합니다.

복사한 텍스트를 SQL 편집창(새 창)에 그대로 붙여넣기(Ctrl+V) 합니다.

붙여넣은 전체 스크립트를 선택한 후 한 번에 일괄 실행(F5) 해줍니다.

이렇게 하면 "Grant을(를) 성공했습니다"라는 메시지가 연달아 출력되며, TEST1의 모든 테이블에 대한 SELECT 권한이 APPLE 계정으로 깔끔하게 넘어갑니다.

 

---

4. 보너스 팁: 뷰(View) 조회 권한도 같이 줘야 한다면?
보통 테이블을 조회할 수 있게 열어줄 때, 가공된 데이터를 보여주는 뷰(View) 객체에 대한 조회 권한도 함께 필요한 경우가 많습니다.

이럴 때는 당황하지 마시고, 정보를 읽어오는 출처를 ALL_TABLES에서 ALL_VIEWS로 살짝만 바꿔주시면 됩니다.

 

-- 뷰(View)에 대한 조회 권한 스크립트 생성 쿼리 SELECT 'GRANT SELECT ON TEST1.' || VIEW_NAME || ' TO APPLE;'  FROM ALL_VIEWS  WHERE OWNER = 'TEST1';

 

오늘은 오라클 DB에서 수많은 테이블의 권한을 일일이 부여하는 번거로움을 덜어주는 스크립트 자동 생성 기법에 대해 알아보았습니다.

이처럼 오라클의 딕셔너리 뷰(ALL_TABLES, ALL_VIEWS, ALL_SEQUENCES 등)와 문자열 결합(||)을 적절히 활용하면, 권한 부여뿐만 아니라 Synonym(동의어) 일괄 생성, 테이블 일괄 Drop 등 다양한 반복 작업을 획기적으로 줄일 수 있습니다. 실무에서 꼭 한 번 응용해 보시길 추천해 드립니다!