Grants

Grants are the bread and butter of DbRhino. They are what give users access to your database. A grant is a combination of

• A user
• A database where the grant will be applied
• SQL GRANT statements

There is only ever one grant per user / database combination. The DbRhino agent is responsible for collecting all the grants to be applied to your databases and implementing them.

Writing SQL

Writing your GRANT statements is mostly the same as if you were running the SQL in the database yourself. There is one big exception: the SQL you write is templated. This allows you to write generic, re-usable SQL statements across your users. Here is a sample of how powerful this can be:

{% for schema in schemas %}
  {% if schema not in ["foo", "bar", "baz"] %}
    GRANT USAGE ON SCHEMA {{schema}} TO {{username}};
    GRANT SELECT ON ALL TABLES IN SCHEMA {{schema}} TO {{username}};
  {% endif %}
{% endfor %}

Usernames

DbRhino will fill in usernames for you. This means trying to use the following will fail:

grant select, insert on *.* to 'myusername'@'%'

Instead, you must always use:

grant select, insert on *.* to {{username}}

PostgreSQL / Redshift

Grant Documentation

• PostgreSQL
• Redshift

Database-level Grants

When you want to write PostgreSQL or Redshift grants that apply to the current database, you can do so using the {{database}} variable. This allows you to write generic grants that can be applied across databases. For example:

grant connect on database {{database}} to {{username}};

Grants on All Schemas

PostgreSQL and Redshift do not have built-in support for writing grants across all schemas. DbRhino allows you to do this through the use of some templating constructs. The easiest way to demonstrate is with an example:

{% for schema in schemas %}
  {% if schema not in ["foo", "bar", "baz"] %}
    GRANT USAGE ON SCHEMA {{schema}} TO {{username}};
    GRANT SELECT ON ALL TABLES IN SCHEMA {{schema}} TO {{username}};
  {% endif %}
{% endfor %}

The schemas variable is provided for you by the DbRhino Agent. The above example loops through every schema in the database, and if the schema is anything other than foo, bar, or baz, grants usage and select rights to the user.

Unsupported Syntax

Grants on the following are not currently supported in DbRhino:

• foreign data wrapper
• foreign server
• language
• large object
• tablespace

Please let us know if you'd like to see them supported!

MySQL

Grant Documentation

MySQL GRANT Documentation

Unsupported Syntax

DbRhino does not allow MySQL grants to use the IDENTIFIED BY syntax. Users will be created by the DbRhino agent, using the passwords supplied by the user being given the grant.