Vertica schema privileges

atmc

Hello!

 

In version 7.2 there are new schema-level privileges described here:

 

https://my.vertica.com/docs/7.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/GRANT/GRANTSchema.htm

 

SELECT
Allows the user to perform a SELECT on any column of any table in the schema.

But if I give SELECT to user on schema, it can't select from any table in that schema.

Granting schema-level SELECT doesn't enable user to select from tables in that schema, and revoking it doesn't restrict from selecting from tables in that schema either, if that user has been given table-level SELECT privileges.

 

Furthermore, there is another page in the documentation:

https://my.vertica.com/docs/7.2.x/HTML/Content/Authoring/AdministratorsGuide/Security/DBUsersAndPrivileges/SchemaPrivileges.htm

 

It says

A user can be granted one of the following privileges through the GRANT statement:

Privilege	Description
CREATE
Allows the user to create new objects within the schema. This includes the ability to create a new object, rename existing objects, and move objects into the schema from other schemas.

USAGE
Permission to select, access, alter, and drop objects in the schema. The user must also be granted access to the individual objects in order to alter them. For example, a user would need to be granted USAGE on the schema and SELECT on a table to be able to select data from a table. You receive an error message if you attempt to query a table that you have SELECT privileges on, but do not have USAGE privileges for the schema that contains the table.

And nothing is said about SELECT, INSERT, UPDATE, DELETE privileges from the first page.

 

So there is new schema-level GRANT syntax in 7.2, but it is effectively useless? Or does it work in some other tricky way?

 

 

 

 

 

colin_loghin

The feature works for select and insert , but not so much for UPDATE and DELETE with a where clause. For those, I am waiting for bug VER-50865 to get fixed in the foreseeable future. If anyone who can find out is reading this, please post a response with a release date for the bug fix.

Now for the schema level select , multiple conditions must be met for the feature to work . Use documentation to get more specifics, I am only outlining the bits required:

• data schema level inheritance must be enabled ( might be by default depending on version)
• schema level inheritance must be on ( check the vs_schemata system table for the defaultinheritprivileges = 't' column)
• then you need one of USAGE or CREATE privileges at the schema level to be able to access the schema contents
• only last you need grant select on schema bla to user ;

By now, you should be able to query all tables in schema without direct grants.

Hope this helps.