526互联

opengauss 数据库对表进行授权

发布时间 2024-01-11 13:33:44作者: testway

opengauss数据库导入后，新增的用户没有权限，需要对schema下所有表进行批量授权，sql如下

#高斯对单表进行授权
GRANT Delete, Insert, References, Select, Trigger, Truncate, Update ON TABLE "db_test"."api_access_log" TO "usertest" WITH GRANT OPTION;
GRANT Delete, Insert, References, Select, Trigger, Truncate, Update ON TABLE "db_test"."api_access_log_merge_his" TO "usertest" WITH GRANT OPTION;
....

单个表进行授权比较麻烦，可以写一个存储过程批量授权，就方便很多了

-- 提取变量
CREATE OR REPLACE FUNCTION grant_permissions_to_user(p_database_name text, p_schema_name text, p_user_name text)
RETURNS VOID AS $$
DECLARE
    loop_table_name text;
BEGIN
    -- 遍历表列表
    FOR loop_table_name IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = p_schema_name
    LOOP
        -- 构造授权语句并执行
        BEGIN
            EXECUTE 'GRANT DELETE, INSERT, REFERENCES, SELECT, TRIGGER, TRUNCATE, UPDATE ON TABLE "' || p_database_name || '"."' || p_schema_name || '"."' || loop_table_name || '" TO "' || p_user_name || '" WITH GRANT OPTION;';
        EXCEPTION
            WHEN others THEN
                RAISE NOTICE 'Error processing table %: %', loop_table_name, SQLERRM;
        END;
    END LOOP;

    -- 删除函数
    DROP FUNCTION IF EXISTS grant_permissions_to_user(text, text, text);
END;
$$ LANGUAGE plpgsql;

-- 调用授权存储过程，第一个参数  db_test 为数据库名称，第一个参数  db_test 为schema名称，第三个参数tryaaccount 为授权用户名称
CALL grant_permissions_to_user('db_test', 'db_test','tryaaccount');