GRANT a privilege to USER who can access all tables in schema FOREVER?

ozi_samur

Hi guys,

I would like to give an access to a USER who can access all tables in schema FOREVER. If I give that USER an access like that the USER cannot access the tables which created after given access :

GRANT SELECT ON ALL TABLES IN SCHEMA DIM TO BI;

Think about I have 10 tables in schema DIM just before issue the above statement. The BI user can access those 10 tables. But If I create a new table in DIM schema after issuing above command the BI user cannot access that table. How can I handle this situation ?

RCasey

The way security works the user must be granted access to the new objects after they are created.
That is how I understand it anyway. I was able to do a work around by granting access to a role then granting the role to the user then having that role be one of the users default roles. That way when the user logs on the newly created objects are available to them. It is not as straight forward as other brands of sql but once you realize the limitations you can work with them.

ozi_samur

It did not work. I created the role and assign it the SELECT ALL TABLES grant. And then assign that role to the newly created user. It works ok firstly but when I create new table the new user could not see that again.

id10t

Hi Ozi!

Its not what asked but you can set pseudosuperuser or dbadmin role.
Run the SHOW AVAILABLE_ROLES command to get a list of the roles available to the user.

For JDBC or ODBC you can define role in ConnSettings property to assign role automatically.

****************************************
ODBC ("+" - space, "%3B" =

ConnSettings                = set+role+dbadmin%3B

****************************************
JDBC

jdbc:vertica://host:port/dbname?ConnSettings=SET ROLE DBADMIN;

Example:http://vertica-forums.com/viewtopic.php?f=50&t=282
****************************************
vsql

db=> set role dbadmin;
SET

****************************************