GaussDB(DWS)优秀实践之赋权管理系统
【摘要】 在DROP USER的场景下,如果被drop的用户涉及创建外表,在多个数据库创建表等情况中,由于对象依赖没有被drop,因此会出现drop用户失败的情况。本文提供了对于此类用户的DROP的具体函数,也就是先REASSIGN对应user的依赖对象,再DROP USER
【问题背景】对于赋权较为复杂的数据库,单用户创建的对象种类较多,涉及多数据库对象和外表,此时如果直接drop user一定会报错有依赖未被删除,而drop owned by操作又会由于横跨多个数据库因此执行起来不方便,此脚本直接扫描所有pg_object对象进行reassign,之后再drop,保证drop user执行成功且user下挂数据的owner迁移至其他用户
【报错示例】
执行drop user时,报错some objects depend on it:
【脚本内容】
1.reassign.sql
CREATE OR REPLACE FUNCTION public.reassign(old_user character varying, new_user character varying)
RETURNS character varying
LANGUAGE plpgsql
NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
BEGIN
--dba_object have cases checking
IF EXISTS(select * from dba_objects where owner = ''||old_user||'') THEN
IF new_user IS NOT NULL OR new_user <> '' THEN
EXECUTE 'REASSIGN OWNED BY "' || old_user || '" to "'|| new_user ||'"';
RETURN 'REASSIGNED SUCCESSFULLY';
ELSE
RETURN 'ERROR: TARGET USER DOESNT EXIEST';
END IF;
END IF;
RETURN 'ERROR No Object to Reassign';
EXCEPTION
WHEN OTHERS THEN
RETURN SQLERRM;
END$$;
2. drop_user.sql:
CREATE OR REPLACE FUNCTION public.drop_user(old_user character varying)
RETURNS character varying
LANGUAGE plpgsql
NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
BEGIN
--dba_object have cases checking
IF EXISTS(select * from dba_objects where owner = ''||old_user||'' and object_type not in ('TYPE','INDEX')) THEN
RETURN 'ERROR DROP FAILED: USER CONTAINS OBJECT, PLEASE REASSIGN BEFORE DROP.';
END IF;
--pg_user have cases checking
IF EXISTS(select * from pg_default_acl where defaclrole in (select usesysid from pg_user where usename = ''||old_user||'')) THEN
EXECUTE 'drop owned by "'|| old_user ||'"';
END IF;
EXECUTE 'drop user "'|| old_user ||'"';
RETURN '"'||old_user||'" DROPPED SUCESSFULLY';
EXCEPTION
WHEN OTHERS THEN
RETURN SQLERRM;
END$$;
【执行方式】
1.先选定迁移依赖对象的目标用户,为new_user, 被drop的用户为old_user:
2.执行reassign脚本
select * from public.reassign('old_user','new_user');
3.执行drop脚本
select * from public.drop_user('old_user');
【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱:
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论(0)