Skip to main content
Question

Granting admin-like permissions without granting dbadmin/pseudosuperuser

  • September 10, 2019
  • 2 replies
  • 12 views
Bryan_H
Forum|alt.badge.img+2

Got a request from a customer as follows. I initially suggesting "GRANT CREATE ON database TO user", but what they want is more elaborate:

Here is the scenario :
There are multiple schemas in a database. Each schema has multiple tables.
JohnDo is DBA who gets requests to alter existing tables, create new tables etc in all schemas in the database. JohnDO id is NOT owner on objects of these schemas.
What Roles we can assign to JohnDo ID so that he can perform such requests(alter existing tables, create new tables etc…) .
We do not want to assign Pseudosuperuser to JohnDo. Also we do not want JohnDo to have shutdown/restart like privileges.

    2 replies

    DaveT
    Forum|alt.badge.img
    • DaveT
    • Participating Frequently
    • September 10, 2019

    I would like at SCHEMA-level grants. Create an appropriate ROLE and then grant appropriate schema-level privileges to it. JohnDo can then get access to the ROLE. Vertica has added ALTER and DROP capabilities and ability to ALTER OBJECT OWNER in recent releases.

    mflower
    Forum|alt.badge.img
    • mflower
    • Participating Frequently
    • September 13, 2019

    9.2.1 introduced the ability to GRANT ALTER ON TABLE.... and GRANT DROP ON TABLE....
    This is table-level, not schema-level, so not a perfect match for your customer, but perhaps this would provide some of the capabilities they're looking for?

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement