How to inherit object_schema's grants to VIEW and SEQUENCE?

edited September 14 in General Discussion

Hello, Our team is managing user and object's grants,
By the way, it was found that the table inherited the grants of the schema, while the views and sequences did not inherit the grants of the schema.
So, our team gives each user grants to view and sequence, and this is causing inconvenience.
How to solve this inconvenience?

Answers

  • You can use, ALTER VIEW view1 INCLUDE SCHEMA PRIVILEGES;

  • DaveTDaveT Employee

    Yes, views should inherit the same way tables do. I suspect you might have an issue with view/table ownership. For example, if the view was created by a different owner than the table then the view owner needs SELECT WITH GRANT OPTION on the base table(s) (or inherited through schema) to have another user select from that view. The key to that is "select WITH GRANT OPTION".

    Sequences don't inherit through the schema privileges like tables and views. You can use ALL SEQUENCES IN SCHEMA on the GRANT for sequences to help. However, I believe it only works for existing sequences and not for future sequences so you would have to reissue after creating new ones.

  • Why is the message occured?? When I use ALTER VIEW view1 INCLUDE SCHEMA PRIVILEGES;
    "View "view" is already set to inherit privileges"

  • HibikiHibiki Employee

    I guess you have already enabled Schema Inheritance.

    => CREATE SCHEMA s1 DEFAULT INCLUDE SCHEMA PRIVILEGES;
    CREATE SCHEMA
    => CREATE TABLE s1.t1 (id INTEGER);
    WARNING 6978:  Table "t1" will include privileges from schema "s1"
    CREATE TABLE
    => CREATE VIEW s1.v1 AS SELECT id FROM s1.t1;
    WARNING 7070:  View "v1" will include privileges from schema "s1"
    CREATE VIEW
    => ALTER VIEW s1.v1 INCLUDE SCHEMA PRIVILEGES;
    ROLLBACK 7069:  View "v1" is already set to inherit privileges
    
    => CREATE SCHEMA s2;
    CREATE SCHEMA
    => CREATE TABLE s2.t2 (id INTEGER);
    CREATE TABLE
    => CREATE VIEW s2.v2 AS SELECT id FROM s2.t2;
    CREATE VIEW
    => ALTER VIEW s2.v2 INCLUDE SCHEMA PRIVILEGES;
    ALTER VIEW
    
    => SELECT table_schema, table_name, inherit_privileges FROM views WHERE table_name IN ('v1', 'v2');
     table_schema | table_name | inherit_privileges
    --------------+------------+--------------------
     s1           | v1         | t
     s2           | v2         | t
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.