1

I'm using a Python script to create tables and insert data into them. However, only I'm able to see the tables created. My team members can't view them. How do I change this?

What Python statements can I include to grant access to everyone? I'm using 'vertica_python' and 'sqlalchemy' to connect to database via Python.

Lax Mandis
  • 131
  • 4
  • 14

2 Answers2

2

The syntax for granting privileges is (see GRANT (Table)):

GRANT SELECT ON TABLE <schema>.<table> TO <user>;

You can grant the following privileges on tables:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • REFERENCES
  • TRUNCATE

Before you grant privileges on the table, you have to at least grant USAGE on the table's schema.

GRANT USAGE ON SCHEMA <schema> to <user>;

If however, these tables are all in the same schema, you can grant privileges to users on the schema level. To do this, first you have to enable schema inheritance. Then grant the privileges on the schema to the users. That way you don't need to keep granting privileges on tables as you create them.

GRANT USAGE, SELECT ON SCHEMA <schema> TO <user>;
A. Saunders
  • 815
  • 1
  • 6
  • 19
1

To grant permissions such as select privileges requires that you first have such permissions.

According to Vertica's site, the ability to GRANT requires:

  • A superuser can grant privileges on all object types to other users.
  • An object owner can grant privileges on the object to other users using the optional WITH GRANT OPTION clause.

A pseudo solution

Depending on who needs access, you can either use username, role, or PUBLIC (all).

Here are some example statements - as I have not worked out of vertica myself, I cannot test this

GRANT SELECT on all tables in schema myschema TO [user/role/PUBLIC] WITH GRANT OPTION; 
GRANT PRIVILEGE

OR

GRANT SELECT on schema.TABLENAME TO [user/role/PUBLIC] WITH GRANT OPTION; 
GRANT PRIVILEGE

it may also require GRANT USAGE on schema as well

Processing Request with Python

And to process it through sqlalchemy in a python script (see this question for caveats):

sSQL = "my sql grant statement(s)"
with oSuperEngine.begin() as conn:
    conn.execute(sSQL)
    conn.execute("COMMIT")
rogersdevop
  • 66
  • 1
  • 3