Skip to main content

Creating read only users in Vertica

  • October 27, 2015
  • 2 replies
  • 11 views
DPhoenix

Hi,

 

I'm creating a read only user in Vertica using the following script. Ideally, I would like to give SELECT permissions to the entire schema but its not possible in vertica, hence I gave SELECT permissions to ALL TABLES in the schema. Now when I create a new table in the same schema, the read only user has to be given SELECT permission again on this new table else he gets a permission denied error. Is it possible to give SELECT permissions to the new and existing objects in vertica at once instead of giving permission to new objects everytime i create one?

 

Create user apiuser identified by '*****';
Grant usage on schema PUBLIC to apiuser;
Grant select on ALL TABLES IN SCHEMA PUBLIC to apiuser;

    2 replies

    Adrian_Oprea_1
    Forum|alt.badge.img+1
    • Adrian_Oprea_1
    • Participating Frequently
    • October 27, 2015

     Hi @DPhoenix,

     I suddgest that you start using user created database roles, this will prevent you form loosing track of your user accesses and less work will be done when doing user maintainance.

     You can follow that SQL Server user role model.

    •  schemaname_dbreader(read access)
    •  schemaname_dbwriter(write access)
    •  schemaname_dbexecute(exec access on UDP,UDF)
    •  schemaname_schemacreate(create table in the schema)

     

     Ohh , there is no automatic grant, that would be nice(to have a role like dbreader in SQL Server). You have ot do it manually after table create. 

     So with roles i just have ot do it once and that roles spreads the 'love' to all users that have acess to that role.

     

     Also in the latest Vertica version 7.2 thy have introduced the concept of Inherited Privileges , but still there is some extra lines of code to be written so this would work. 

     

     

     

     

    DPhoenix
    • DPhoenix
    • Author
    • New Participant
    • October 28, 2015

    If I add a user to pseudosuperuser role, then will that user inherit existing privileges for new objects in the schema or even in this case, we need to grant permissions to the pseudosuperuser role after every new object is created?

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement