GRANT with database name wildcard in MySQL?
If I use back-tics instead of single quotes in the syntax, it appears to work just fine: grant all on `projectA\_%`.* to `projectA`@`%`;
sql-grant
Minimum GRANTs needed by mysqldump for dumping a full schema? (TRIGGERs are missing!!)
Assuming by full dump you also mean the VIEWs and the EVENTs, you would need: GRANT USAGE ON *.* TO ‘dump’@’%’ IDENTIFIED BY …; GRANT SELECT, LOCK TABLES ON `mysql`.* TO ‘dump’@’%’; GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON `myschema`.* TO ‘dump’@’%’; and if you have VIEWs that execute a function, then unfortunately … Read more
How to grant remote access to MySQL for a whole subnet?
It looks like you can also use a netmask, e.g. GRANT … TO ‘user’@’192.168.0.0/255.255.255.0’ IDENTIFIED BY …
PostgreSQL: Give all permissions to a user on a PostgreSQL database
All commands must be executed while connected to the right database cluster. Make sure of it. Roles are objects of the database cluster. All databases of the same cluster share the set of defined roles. Privileges are granted / revoked per database / schema / table etc. A role needs access to the database, obviously. … Read more
MySQL grant all privileges to database except one table
I know this is an old post, but I thought I’d add on to @tdammers question for others to see. You can also perform a SELECT CONCAT on information_schema.tables to create your grant commands, and not have to write a separate script. First revoke all privileges from that db: REVOKE ALL PRIVILEGES ON db.* FROM … Read more
Grant all on a specific schema in the db to a group role in PostgreSQL
You found the shorthand to set privileges for all existing tables in the given schema. The manual clarifies: (but note that ALL TABLES is considered to include views and foreign tables). Bold emphasis mine. serial columns are implemented with nextval() on a sequence as column default and, quoting the manual: For sequences, this privilege allows … Read more
Permission denied for relation
GRANT on the database is not what you need. Grant on the tables directly. Granting privileges on the database mostly is used to grant or revoke connect privileges. This allows you to specify who may do stuff in the database if they have sufficient other permissions. You want instead: GRANT ALL PRIVILEGES ON TABLE side_adzone … Read more
How to grant remote access permissions to mysql server for user?
This grants root access with the same password from any machine in *.example.com: GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%.example.com’ IDENTIFIED BY ‘some_characters’ WITH GRANT OPTION; FLUSH PRIVILEGES; If name resolution is not going to work, you may also grant access by IP or subnet: GRANT ALL PRIVILEGES ON *.* TO ‘root’@’192.168.1.%’ IDENTIFIED … Read more
grant remote access of MySQL database from any IP address
TO ‘user’@’%’ % is a wildcard – you can also do ‘%.domain.com’ or ‘%.123.123.123’ and things like that if you need.