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

12 years ago
Dec 16, 2013 6:32 PM UTC
8 replies
30 views

Martin_2
Participating Frequently

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.

Anonymous
Participating Frequently
12 years ago
Dec 16, 2013 7:06 PM UTC

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
Participating Frequently
12 years ago
Dec 16, 2013 7:17 PM UTC

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
Author
Participating Frequently
12 years ago
Dec 16, 2013 10:23 PM UTC

Hi Bhardwaj,

Thank you , it is great, it works!

Martin

Martin_2
Author
Participating Frequently
12 years ago
Dec 16, 2013 10:25 PM UTC

Hi Kumar,

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

Martin

Vince
New Participant
11 years ago
Jan 27, 2015 9:42 AM UTC

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
Participating Frequently
9 years ago
Jul 19, 2017 7:12 PM UTC

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
Participating Frequently
9 years ago
Jul 20, 2017 5:28 PM UTC

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
New Participant
9 years ago
Jul 22, 2017 12:01 PM UTC

Thanks Jim! Information that you posted here is really useful

Sign up

Please log in or register:

Welcome to the Rocket Forum!

Please log in or register: