How to hide view definition?

[beth]

Howdy all!

Is there a way that I can hide a view's definition from a user that has only been granted just the SELECT privilege on it?

Code: Select all

dbadmin=> create view public.dual_view as select * from dual;
CREATE VIEW

dbadmin=> create user beth;
CREATE USER

dbadmin=> \c dbadmin beth
You are now connected to database "dbadmin" as user "beth".

dbadmin=> select * from public.dual_view;
ERROR 4367:  Permission denied for relation dual_view

dbadmin=> \c dbadmin dbadmin
You are now connected to database "dbadmin" as user "dbadmin".

dbadmin=> grant select on public.dual_view to beth;
GRANT PRIVILEGE

dbadmin=> \c dbadmin beth
You are now connected to database "dbadmin" as user "beth".

dbadmin=> select * from public.dual_view;
 dummy
-------
 X
(1 row)

dbadmin=> select view_definition from views where table_name = 'dual_view';
            view_definition
---------------------------------------
 SELECT dual.dummy FROM v_catalog.dual
(1 row)

I have several views that contain some proprietary logic that I don't want the average user to be able to see!

Thank in advance!

[JimKnicely]

You could create another view that queries the view you want hidden

Code: Select all

dbadmin=> create user jim;
CREATE USER
dbadmin=> create view public.dual_vw as select * from dual;
CREATE VIEW
dbadmin=> create view public.dual_vw2 as select * from public.dual_vw;
CREATE VIEW
dbadmin=> grant select on public.dual_vw2 to jim;
GRANT PRIVILEGE
dbadmin=> \c dbadmin jim
You are now connected to database "dbadmin" as user "jim".
dbadmin=> select view_definition from views where table_name = 'dual_vw';
 view_definition
-----------------
(0 rows)

dbadmin=> select view_definition from views where table_name = 'dual_vw2';
             view_definition
------------------------------------------
 SELECT dual_vw.dummy FROM public.dual_vw
(1 row)

[beth]

Cool, thanks, Jim! That's a brilliant quick solution