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
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
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.