DavidSzczesniak
2/16/2018 - 2:03 PM

Creating New Users

\!h Simple example - create a user called 'allmusic' who has privileges on all tables in the music database, and a password of 'the_password':
GRANT ALL ON music.* TO 'allmusic'@'localhost' IDENTIFIED BY 'the_password';
-- music.* = everything in the music database
-- 'allmusic'@'localhost' = the user has the name allmusic and can connect to the server only from the localhost
-- the single quotes around the username and host name are only needed if either name contains special characters or wildcards

\!h To log in as allmusic:
mysql --user=allmusic password=the_password

\!h User who can only access certain tables in the music database:
GRANT ALL ON music.artist TO 'partmusic'@'localhost' IDENTIFIED BY 'the_password';

-- provide the user with access to more tables, but not all of them:
GRANT ALL ON music.album TO 'partmusic'@'localhost'; -- re-using username and location, so password isn't needed again

\!h Grant a user access to specific columns in a table:
GRANT SELECT (track_id, time) ON music.track TO 'partmusic'@'localhost';
-- only specified SELECT privileges as oppposed to ALL (can't grant all at once at the column level)
-- those privileges apply to the specified columns track_id and time

-- note: DESCRIBE still works on columns even if you don't have access to them. (only columns)