Oracle存储过程中如何向TST用户追加授予所有表的Truncate权限

适用12cR1及以上版本（支持对象级TRUNCATE权限） #

直接在原有GRANT语句的权限列表中追加TRUNCATE即可，修改后的完整存储过程如下：

CREATE OR REPLACE PROCEDURE "TBL_MER"."PROCEDURE_GRANT_PRIV"
IS
BEGIN
    FOR tab IN (SELECT table_name
                FROM   all_tables
                WHERE  owner = USER
                ORDER BY table_name) LOOP
        -- 追加TRUNCATE权限到授权列表
        EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON '||tab.table_name||' TO TST';          
    END LOOP;
    -- 提示：GRANT属于DDL语句，执行后自动提交，此处COMMIT可省略
    -- COMMIT;
END;
/

12c以下版本替代方案 #

低版本Oracle没有对象级别的TRUNCATE权限，直接给TST用户授予DROP ANY TABLE权限风险过高，建议采用封装存储过程的方式实现可控的TRUNCATE授权：

1. 先在当前用户下创建统一的TRUNCATE调用存储过程：

CREATE OR REPLACE PROCEDURE "TBL_MER"."PROC_TRUNCATE_TABLE"(p_table_name VARCHAR2)
IS
BEGIN
    -- 校验传入的表名属于当前用户，避免SQL注入和越权操作
    EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || DBMS_ASSERT.SIMPLE_SQL_NAME(p_table_name);
END;
/

2. 将该存储过程的执行权限授予TST用户：

GRANT EXECUTE ON TBL_MER.PROC_TRUNCATE_TABLE TO TST;

3. TST用户需要TRUNCATE表时，调用该存储过程即可，示例：

EXEC TBL_MER.PROC_TRUNCATE_TABLE('目标表名');

注意事项 #

• 授予TRUNCATE权限后，TST用户可以直接清空表数据且无法回滚，请提前评估业务风险
• 如果表名包含小写、特殊字符，建议在拼接表名时使用DBMS_ASSERT.ENQUOTE_NAME(tab.table_name)包裹，避免执行报错

内容的提问来源于stack exchange，提问作者Marcus