Oracle 에는 GRANT TRUNCATE ON tbl TO user 같은 객체 권한이 없다. 다른 스키마의 테이블을 truncate 하려면 DROP ANY TABLE 시스템 권한이 필요한데, 이름과 달리 범위가 지나치게 넓어 운영 계정에 주기 어렵다.
소유자 스키마에 정의자 권한(AUTHID DEFINER, 기본값) 프로시저를 만들고 실행 권한만 준다.
CREATE OR REPLACE PROCEDURE owner_user.truncate_my_table AS
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE owner_user.my_table';
END;
/
GRANT EXECUTE ON owner_user.truncate_my_table TO target_user;
대상 테이블을 파라미터로 받는 범용 프로시저를 만들 때는 SQL injection 을 막기 위해 DBMS_ASSERT.SQL_OBJECT_NAME 으로 검증하거나 허용 목록으로 제한한다.
GRANT DELETE ON owner_user.my_table TO target_user;
redo · undo 가 더 생기고 high water mark 가 내려가지 않는다는 차이가 있다.
GTT 도 일반 테이블과 같아 TRUNCATE 를 직접 GRANT 할 수 없다. 다만 GTT 는 세션마다 데이터가 격리되어 있어 truncate 든 delete 든 내 세션의 내 데이터만 지워진다. 그래서 DELETE 로 대체해도 사실상 같은 결과이고 부담도 작다. GTT 는 temp tablespace 에 있어 redo 도 적다.
애초에 비울 시점이 정해져 있으면 생성 옵션으로 해결하는 편이 낫다.
CREATE GLOBAL TEMPORARY TABLE my_gtt (
id NUMBER,
data VARCHAR2(100)
) ON COMMIT DELETE ROWS; -- 커밋마다 자동 비움. 세션 단위면 ON COMMIT PRESERVE ROWS
트랜잭션 단위로 쓰면 ON COMMIT DELETE ROWS, 세션 단위로 쓰면 ON COMMIT PRESERVE ROWS 로 두고 세션이 끝나면 자동으로 비워진다. 한 세션 안에서 여러 번 비워야 하는 경우에만 위의 프로시저 방식을 쓴다.