How to grant all objects under a schema to a user.

Martin_2

Hi,
I have a schema A, and a user B. How can I grant all objects under schema A to user B.
I have used the following statement, but it seems it don't make sense, since I can't select table under that schema A by user B.
GRANT USAGE ON SCHEMA A to B;

Anybody know how to grant all objects under a schema to a user. thanks.

 

[Deleted User]

Hi,

What Vertica version are you on.

Starting version 6.1, we can provide grant on all tables in a schema using "ON ALL TABLES"option

vsql=> GRANT SELECT ON ALL TABLES IN SCHEMA <schema  name> TO <user name>;


You can read more about this in Vertica Doc:

SQL Reference Manual --> SQL Statements --> Grant Statements --> GRANT (Table)

https://my.vertica.com/docs/6.1.x/HTML/index.htm#3044.htm

Thanks,
Pravesh

Vivek_V__Kumar

Hi,

Grant USAGE allows the user access to the objects contained within the schema. This allows the user to look up objects within the schema.  But along with this, user must also be granted access to the individual objects.

Example:  grant select on A.<table_name> to B;

Now user B would be able to run select queries on A.<table_name>.

If you want to grant all objects under a schema to a user, you can run a small script for the same.

#!/bin/bash

user=B

schema=A

vsql -ta -w password -c " select 'grant all privileges on table ' || table_schema || '.' || table_name || ' to $user ;' from tables where table_schema = '$schema'; " | vsql

Note: Pleases run GRANT USAGE ON SCHEMA A to B; before running the script.
           Grant all used in script will grant all privileges to user B on Schema A. I fyou want to grant select only, Please use 'Grant select' instead of '        'grant all'.

Thanks,
Vivek 

 

Martin_2

Hi Bhardwaj,

Thank you , it is great, it works!


Martin

Martin_2

Hi Kumar,

It is a good idea of using the vsql to grant the privileges. Thank you.


Martin

Vince

Hi,

Does the GRANT <privileges> ON ALL TABLES IN SCHEMA <schema  name> TO <user name>; apply the privileges (retroactively) to any tables created in the same schema after the GRANT statement was executed?

Thanks... Vince

sreeblr

as i could see tables after the GRANT ON ALL TABLES IN SCHEMA TO ; dont get added to grants hence guess we need to give grants to new tables.

Jim_Knicely

Tables can inherit grants from schemas!

See:
https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Security/DBUsersAndPrivileges/GrantInheritedPrivileges.htm

You can ALTER a current schema so that new grants can be inherited by new tables created in that schema...

dbadmin=> alter schema vertica_stuff default include privileges;
ALTER SCHEMA

You have to re-run the GRANT SELECT ON ALL ... statement again after running ALTER SCHEMA statement,

dbadmin=> grant select on all tables in schema vertica_stuff to jim1;
GRANT PRIVILEGE

eringardner

Thanks Jim! Information that you posted here is really useful