Search code examples
vertica

User restriction in vertica

I have some public functions in vertica. I want to create a read only user in vertica who can access only the data tables in the defined schema and not from v_catalog(Information_schema) to list all the table names. Is it possible in vertica to create a user who can be restricted to use any system commands but can access the public functions and UDFS and execute only select command in the defined schema.

Solution

  • If you create a user, the only role the user is granted is PUBLIC.

    All that is granted to PUBLIC , but nothing more, will be granted to the new user.

    Once the new user is logged in, if no tables exist that are granted to PUBLIC, a SELECT * FROM v_catalog.tables; will return an empty table.

    If I were you , I would create a user, as dbadmin:

    CREATE USER marvin IDENTIFIED BY 'heart_of_gold';

    Then, I'd log in as that user:

    vsql -U marvin -w heart_of_gold -h 00.000.000.00 -d dbname

    And then, try all sorts of queries to see what I'm allowed to do; then log back in as dbadmin, grant some things to marvin and see what happens.

    And check the Vertica documentation around the GRANT command to find out what can be granted and revoked. I do it exactly like that until I feel confident about what I'm trying to achieve..

    Good luck and happy playing