Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

GRANT with database name wildcard in MySQL?

Tags:

syntax

mysql

permissions

grant

I want to create a user 'projectA' that has the same permissions to every database named 'projectA_%'

I know its possible but MySQL doesn't like my syntax:

grant all on 'projectA\_%'.* to 'projectA'@'%';

Reference: http://dev.mysql.com/doc/refman/5.1/en/grant.html

like image 576
JR Lawhorne Avatar asked Apr 19 '10 15:04

JR Lawhorne

People also ask

What is grant usage on * * in MySQL?

GRANT USAGE ON *. * means "No privilege". In other word, the user has been created (with an entry in mysql. users table) with no privilege.

How do I grant privileges to a database in MySQL?

To GRANT ALL privileges to a user , allowing that user full control over a specific database , use the following syntax: mysql> GRANT ALL PRIVILEGES ON database_name. * TO 'username'@'localhost';

Can we use wildcard in MySQL?

MySQL WildcardsA wildcard character is used to substitute one or more characters in a string. Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

2 Answers

If I use back-tics instead of single quotes in the syntax, it appears to work just fine:

grant all on `projectA\_%`.* to `projectA`@`%`;
like image 128
JR Lawhorne Avatar answered Sep 19 '22 08:09

JR Lawhorne

 
GRANT ALL PRIVILEGES ON `projectA\_%`.* TO 'projectA'@'%' IDENTIFIED BY 'your_passwd';

back-tics are needed for the database name

Edited:Underscore is now escaped.

like image 39
johnson Avatar answered Sep 21 '22 08:09

johnson
Sign in to Comment

Related questions

Difference between "int" and "int(2)" data types in my sql
MySQL: difference of two result sets
Select from a table where fields don't match conditions
Best way to store working hours and query it efficiently
MySQL CONVERT_TZ()
SQLite and custom order by
Only show tables with certain patterns in mysql "show tables"
Why the \G in SELECT * FROM table_name\G?
If else on WHERE clause
Python Pandas to_sql, how to create a table with a primary key?
'Access denied for user 'root'@'localhost' (using password: NO)'
MySQL get missing IDs from table
How to configure MySQL 5.6 LONGBLOB for large binary data
How to ORDER BY a SUM() in MySQL?
MySQL NULL or NOT NULL That is The Question?
mysql transaction - roll back on any exception
Dump File MySQL 5.6.10
mysql GROUP_CONCAT DISTINCT multiple columns
Is there a difference in using INT(1) vs TINYINT(1) in MySQL?
MySql: MyISAM vs. Inno DB! [closed]

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!