Why is the error occured?? When I use ALTER VIEW view1 INCLUDE SCHEMA PRIVILEGES;

HyeontaeJuHyeontaeJu Vertica Customer
edited September 2020 in General Discussion

Why is the message occured?? When I use ALTER VIEW view1 INCLUDE SCHEMA PRIVILEGES;
"View "view" is already set to inherit privileges"
I wanna that view inherit schema's grants list and I expect that the grantee list is inherited to view

Answers

  • HibikiHibiki Vertica Employee Employee
    edited September 2020

    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
    
  • HyeontaeJuHyeontaeJu Vertica Customer
    edited September 2020

    if the view inherit privilege of schema ,why is the view doesn't have grantee in grants table?

  • HyeontaeJuHyeontaeJu Vertica Customer

    @Hibiki
    I have a question. If the view inherit the schema's privilege.
    Why is the view don't have grantee list in grants table??

  • HibikiHibiki Vertica Employee Employee

    You can see the following description about GRANTS table.

    GRANTS
    Returns information about privileges that are explicitly granted on database objects. Information about inherited privileges is not included.

    https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/GRANTS.htm
    For inherited privileges information, please check INHERITING_OBJECTS and INHERITED_PRIVILEGES table.

  • HyeontaeJuHyeontaeJu Vertica Customer
    edited September 2020

    @Hibiki
    Oh I see,,
    By the way, Is it possible inherit schema's privileges to sequence??
    If is it can't, why is sequence can't inherit schema's privileges?
    (It is very inconvenient to assign permissions for each user to the sequence.)

  • HibikiHibiki Vertica Employee Employee

Leave a Comment

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