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.
How to grant all objects under a schema to a user.
+1
+2Hi,
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
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
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.
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
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
+1Hi Bhardwaj,
Thank you , it is great, it works!
Martin
Thank you , it is great, it works!
Martin
+1Hi Kumar,
It is a good idea of using the vsql to grant the privileges. Thank you.
Martin
It is a good idea of using the vsql to grant the privileges. Thank you.
Martin
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
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
+2as 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.
+2Tables can inherit grants from schemas!
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
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
