You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
indicator_views数据表上有一个触发器,从新增记录中提取视图定义语句,然后调用EXECUTE语句在userviews模式创建视图,对应的存储过程如下:
`
CREATE OR REPLACE FUNCTION public.create_indicator_view()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
view_name TEXT;
is_exist BOOL;
BEGIN
view_name := format('%s', NEW.name);
SELECT EXISTS (SELECT * FROM pg_catalog.pg_views WHERE schemaname = 'userviews' AND viewname = view_name) INTO is_exist;
IF NOT is_exist THEN
EXECUTE format(NEW.definition);
END IF;
RETURN NULL;
END $function$
`
在userviews模式下为example用户服务访问视图的权限 ALTER DEFAULT PRIVILEGES IN SCHEMA userviews GRANT SELECT ON TABLES TO example; GRANT USAGE ON SCHEMA userviews TO example; GRANT SELECT ON ALL TABLES IN SCHEMA userviews TO example;
数据库A中有两个模式,一个是默认的public模式,一个是新建的模式userviews;
public 模式中有一张表indicator_views,其中一个字段表示创建视图的SQL语句;
indicator_views数据表上有一个触发器,从新增记录中提取视图定义语句,然后调用EXECUTE语句在userviews模式创建视图,对应的存储过程如下:$function$
`
CREATE OR REPLACE FUNCTION public.create_indicator_view()
RETURNS trigger
LANGUAGE plpgsql
AS
DECLARE
view_name TEXT;
is_exist BOOL;
BEGIN
view_name := format('%s', NEW.name);
END
$function$
`
在userviews模式下为example用户服务访问视图的权限
ALTER DEFAULT PRIVILEGES IN SCHEMA userviews GRANT SELECT ON TABLES TO example; GRANT USAGE ON SCHEMA userviews TO example; GRANT SELECT ON ALL TABLES IN SCHEMA userviews TO example;
现在的问题是,系统往indicator_views写入一条记录后,example用户没有访问userviews模式下的新创建视图的权限,请教一下有没有解决思路。
The text was updated successfully, but these errors were encountered: