MySQL Archives

Feb 2

Check for CREATE VIEW in MySQL 5

I was working on a PHP script the other day which populated a database with some tables, data, and a view. During testing, it was discovered the script would error out for database users that didn’t have the CREATE VIEW privilege. Since the view was a requirement of the project, I had to first check the users database rights. If they couldn’t create a view, the script should error out and inform them their database user needs those rights.

MySQL gives us a way to check a users rights. You can test this by running the following query when you are logged into your server. The following indicates I have full rights when I am logged in as justin.

/* the query */
SHOW GRANTS;

/* the result */
GRANT ALL PRIVILEGES ON *.* TO 'justin'@'localhost' WITH GRANT OPTION

Continue Reading

39
Jan 19

MySQL tables and case sensitivity

I just came across an interesting MySQL issue while working on a project. By default, if you create a table in MySQL it will provide a case insensitive collation to the table. What that means is checking a text field in the database for string and STRING would both equate to true! That might not be the ideal situation if you want to preserve case sensitivity.

For example, say I have a users table that stores login info for a website, each row has a username. If the default case insensitive collation was used, the following SQL statements would both return the user row.

/* returns 1 row */
SELECT *
FROM users
WHERE username = 'justin';

/* returns 1 row */
SELECT *
FROM users
WHERE username = 'JUSTIN';

Continue Reading

Comments Off