Search code examples
privilegesdatabase-administrationnetezza

User cannot see view, only sees tables


These are the object privileges for a user on a netezza database. It says (S)elect, but the user cannot see views, only tables. Why is that?

Object Privileges
    (L)ist (S)elect (I)nsert (U)pdate (D)elete (T)runcate (L)ock
    (A)lter (D)rop a(B)ort (L)oad (G)enstats Gr(O)om (E)xecute
    Label-A(C)cess Label-(R)estrict Label-E(X)pand Execute-(A)s
Administration Privilege
    (D)atabase (G)roup (U)ser (S)chema (T)able T(E)mp E(X)ternal
    Se(Q)uence S(Y)nonym (V)iew (M)aterialized View (I)ndex (B)ackup
    (R)estore va(C)uum (S)ystem (H)ardware (F)unction (A)ggregate
    (L)ibrary (P)rocedure U(N)fence (S)ecurity Scheduler (R)ule

Solution

  • LIST permission allows you to see an object, and a TABLE is a different object class than a VIEW.

    If you want a user to see a view then you must grant LIST on that view (or the general object class of VIEW). Being granted LIST or SELECT on a table (or the object class of TABLE) has nothing to do with whether a user can see a VIEW.

    If you had two views called VIEW_1 and VIEW_2 you could either do something like:

    GRANT LIST, SELECT on VIEW_1, VIEW_2 to USER1234
    

    or if you wanted them to have those permissions for all views in the database scope you grant to VIEW which is a reference to the object class for all views.

    GRANT LIST, SELECT on VIEW to USER1234
    

    Similarly you could grant to TABLE (which represents the table object class), and that would apply to all tables in the scope.