Can I use wildcard for do 'GRANT ... TO user'?

Hi, I just started using Cockroach and I have a question about its user and privilege management.

Question is, can I use a wildcard when I give privileges to a user? In MySQL, we can use the following syntax to give it:

GRANT ALL PRIVILEGES ON  `app\_%` . * TO  'user'@'localhost';

so user can create any database started with app_. It is useful for dynamic database creation and automation.

Or if it is not possible, can I give a user privilege to create database freely?

You can use GRANT ... ON dbname.* TO ... to grant privileges on all the tables in the database named dbname. It is not a glob character on the rest of the dbname.

Unlike mysql, the objects being granted privileges on must exist, so dbname must exist already.

In CockroachDB, you need to be in the admin role to be able to create databases. The root user is the only member of it by default.

Thanks for your answer.
Then can I make a new user with admin role in community version? As I undetstand role management is enterprise feature.

In fact, I need this for both:

  • to use database handling tools for migration, creation and dropping like rails and buffalo.
  • to concept design and implementation of automated DB as a Service.

Any suggestion? Now I just use separated profile for this with user root but it is not good way IMHO.

You are correct, roles are enterprise only.

Without using roles, you would need to do the following as the root user:

  • create the desired database
  • create the desired user
  • GRANT ALL ON DATABASE newdatabase TO newuser

From there, newuser will be able to do whatever they like to the database.
I’m afraid that’s the only way around it.

OK, I got it.

Thanks for kind response.