New to Vertica 7.2.0, the inherited privileges feature provides a one-step method for enabling privileges at the schema level. With inherited privileges enabled on a schema, Vertica grants that schema?’s privileges to all tables and views in that schema automatically. For example, you may have several permanent tables that remain in your database. Rather than perform individual grants on each permanent table, you can perform one grant to the schema using the inherited privileges feature.
In addition, all NEW tables and views added to a schema inherit the schema’?s privileges. This eliminates the need to grant privileges every time you create a new table or view in the schema. Therefore, you can grant schema privileges to user and roles with the assurance that the users and roles have the same privileges on tables and views in that schema.
Inherited Privileges Workflow
To implement inherited privileges you must be the object owner or a pseudosuperuser. The following example shows a simple workflow you may use when implementing inherited privileges:
- Create a schema and make inherit privileges the default behavior by using the INCLUDE PRIVILEGES keyword:
=> CREATE SCHEMA schema1 DEFAULT INCLUDE PRIVILEGES;
- Create a table:
=> CREATE TABLE schema1.table1 (id int);
This table automatically inherits the privileges from schema1.
- Grant USAGE on schema1 to user1:
=> GRANT USAGE ON SCHEMA schema1 to user1;
- GRANT CREATE on schema1 to user1;
=> GRANT CREATE ON SCHEMA schema1 to user1;
- Grant SELECT on schema1 to user1:
=> GRANT SELECT ON SCHEMA schema1 to user1;
- As user1 perform a SELECT on table1;
user1=> SELECT * from schema1.table1;
This works because when user1 was granted SELECT privileges on schema2, they were granted SELECT privileges on all tables in that schema, including table1.
You can also use ALTER SCHEMA, ALTER TABLE, and ALTER VIEW to modify existing objects to use inherited privileges:
=> ALTER SCHEMA schema1 DEFAULT INCLUDE PRIVILEGES;
=> ALTER TABLE schema1.table1 INCLUDE SCHEMA PRIVILEGES;
=> ALTER VIEW view1 INCLUDE SCHEMA PRIVILEGES;
Do all tables/views in the schema need to inherit privileges?
The default behavior for new tables in a schema with inherited privileges enabled is to include inherited privileges.
At times you may want to restrict access to a specific table in a schema with inherited privileges enabled. For example, a table in a schema for employee information may contain sensitive information such as salary and social security numbers; and the rest of the tables contain information that needs to be accessed by everyone such as contact information and job description.
This example shows how to exclude a new table from inheriting a schema’?s privileges:
=> CREATE TABLE schema1.table1(id int) EXCLUDE SCHEMA PRIVILEGES;
This example shows how to exclude an existing table from inheriting a schema’?s privileges:
=> ALTER TABLE schema1.table1 EXCLUDE SCHEMA PRIVILEGES;
The inherited privileges feature not only provides an easy solution for assigning privileges to objects in a schema, but also offers a quick and secure way to ensure users have access to only the data required to perform their assigned tasks. For more information see Inherited Privileges in the Vertica documentation.