Welcome Inheritence

Starting with Vertica version 7.2.x HP Vertica comes with a great feature called Inheritance, this concept is similar to SQL Server schema roles that will dynamic propagate the privileges across all objects in the schema and associate them with the users that have access to this schema roles. Thanks HP for implementing this as it makes my life much more easy when dealing with new objects and access rules. So lets dive in and see what is this all about. To Enable or Disable Inherited Privileges at Database Level use the syntax below: 

-- Enable
ALTER DATABASE database_name SET disableinheritedprivileges = 0;

-- Disable
ALTER DATABASE database_name SET disableinheritedprivileges = 1;
This new feature all privileges granted to the schema are automatically granted to all newly created tables or views in the schema. Note: Existing tables or views remain unchanged when you alter the schema to include or exclude inherited privileges. Not be able to apply inherited privileges you need be the owner of the schema or a superuser. Below is full step by step example of inheritance works: We will create a new schema called no_grants and put one table in it. Next create a new user. 
create schema no_grants;
create table no_grants.mytab(id int);
create user user_nograt identified by 'bla';
Login with the new user. 
dbadmin= c - user_nograt
Password:
You are now connected as user "user_nograt".
Try to query the new table.
  • make sense the error since we havent granted anything to the new user.
dbadmin= select * from no_grants.mytab;
ERROR 3580:  Insufficient privilege: USAGE on SCHEMA 'no_grants' not granted for current user
Now lets alter the schema definition and enable inheritance using the "include privileges" option and add a new table. 
alter schema no_grants default include privileges;
-- grant select to user
grant select on schema no_grants to user_nograt;
-- grant usage on schema to user
grant usage on schema no_grants to user_nograt;
-- and create a new table
create table no_grants.mytab2(id int);
Try to query the secound table, and we can see that even no direct grant to the table was given to the user he is able to query the table.
  • this is because of the inheritance we enabled on schema level in combination with the USAGE + SELECT grant on schema granted to the user.
Run this as user_nograt. 
dbadmin= select * from no_grants.mytab2;
 id
----
(0 rows)
Now try to query the old table logged as user_nograt and see we get an error due to permission fault.
  • the reason for this is that even thou we have applied the inheritance at the schema level the inheritance it will only apply to new objects and will omit the existing objects.
dbadmin= select * from no_grants.mytab;
ERROR 4367:  Permission denied for relation mytab
To fix this we need to alter each object in the database using the following syntax: 
alter table  no_grants.mytab include schema privileges;
Now the user can query the table. 
dbadmin= select * from no_grants.mytab;
 id
----
(0 rows)
Looking at the grants table we can see that we only have entries for the schema granted privileges: 
select grantor,privileges_description,object_name,grantee from grants where grantee='user_nograt';

grantor  privileges_description  object_name  grantee
-------  ----------------------  -----------  -----------
dbadmin  SELECT, USAGE           no_grants    user_nograt
Also there is no reference in the v_catalog.tables system table about the inheritance parameter, if is enabled or disabled. But there is a way for you find and list the tables that have inheritance enabled, the table is vs_tables. 
select name,tb.table_schema,inheritprivileges
 from vs_tables vtb join tables tb
 on tb.table_schema_id =vtb.schema
  where vtb.inheritprivileges='true' ;
name    table_schema  inheritprivileges
------  ------------  -----------------
mytab   no_grants     true
mytab   no_grants     true
mytab2  no_grants     true
mytab2  no_grants     true
Now to make your life a bit easier when trying to implement inheritance use the below script to pickup the present tables and views and alter their definition to apply schema privilege inheritance. Note:
  • replace the $Schema_Name$ with the schema of your choice.
select 'ALTER SCHEMA '||'$Schema_Name$'||' DEFAULT INCLUDE PRIVILEGES;' from dual
union all
select 'ALTER TABLE '||table_schema||'.'||table_name||' INCLUDE SCHEMA PRIVILEGES;' from tables
where table_schema='$Schema_Name$'
union all
select 'ALTER view  '||table_schema||'.'||table_name||' INCLUDE SCHEMA PRIVILEGES;' from views
where table_schema='$Schema_Name$';
Also  we can revoke and inherited privilege for a single object that resides in a schema that has inheritance enabled. 
alter table no_grants.mytab exclude schema privileges;
                      or
alter view no_grants.v_mytab exclude schema privileges;
To see if the privileges were excluded you can use the query below : 
select name,tb.table_schema,inheritprivileges
 from vs_tables vtb join tables tb
 on tb.table_schema_id =vtb.schema
  where vtb.inheritprivileges='true'  and tb.table_schema='no_grants';


grantor privileges_description object_name grantee
------- ---------------------- ----------- -----------
dbadmin USAGE general user_nograt
dbadmin SELECT, USAGE no_grants user_nograt
Or try to query the table: 
badmin= select * from no_grants.mytab;
ERROR 4367:  Permission denied for relation mytab

Uppsss.....
Hope this was helpful !

Copyright 2020. All Rights Reserved