Mysql Account Creation and Permissions Source

May 1, 2007 Markdown source

1---
2title: 'Mysql Account Creation and Permissions'
3date: '2007-05-01'
4published_at: '2007-05-01T14:07:00.000+10:00'
5tags: ['databases', 'mysql', 'programming']
6author: 'Gavin Jackson'
7excerpt: 'I finally had a chance to sit down and learn the syntax of creating mysql users and assigning specific permissions. This allows me to have different users for different web services - eg. cron jobs, w...'
8updated_at: '2007-05-01T14:27:48.977+10:00'
9legacy_url: 'http://www.gavinj.net/2007/04/mysql-account-creation-and-permissions.html'
10---
11
12I finally had a chance to sit down and learn the syntax of creating mysql users and assigning specific permissions. This allows me to have different users for different web services - eg. cron jobs, web servers etc.
13
14`GRANT [SELECT|UPDATE|INSERT|UPDATE|CREATE|DROP] ON DATABASE.[table`|*] TO 'user'@'host' IDENTIFIED BY 'password'
15
16These can be cumulative. One thing that originally confused me was the 'host' - you actually need to have two accounts for a user to connect both locally and remotely - one set to "localhost" and the other set to "%".
17
18The grant command is actually just populating the mysql.user table.
19
20Other useful commands include changing a password:
21
22`SET PASSWORD FOR 'bob'@'loclahost" = PASSWORD('newpassword') SET PASSWORD = PASSWORD('newpassword'); GRANT USAGE ON *.* TO 'user`'@'host' IDENTIFIED BY 'password'
23
24
25

---
title: 'Mysql Account Creation and Permissions'
date: '2007-05-01'
published_at: '2007-05-01T14:07:00.000+10:00'
tags: ['databases', 'mysql', 'programming']
author: 'Gavin Jackson'
excerpt: 'I finally had a chance to sit down and learn the syntax of creating mysql users and assigning specific permissions. This allows me to have different users for different web services - eg. cron jobs, w...'
updated_at: '2007-05-01T14:27:48.977+10:00'
legacy_url: 'http://www.gavinj.net/2007/04/mysql-account-creation-and-permissions.html'
---

I finally had a chance to sit down and learn the syntax of creating mysql users and assigning specific permissions. This allows me to have different users for different web services - eg. cron jobs, web servers etc.

`GRANT [SELECT|UPDATE|INSERT|UPDATE|CREATE|DROP] ON DATABASE.[table`|*] TO 'user'@'host' IDENTIFIED BY 'password'

These can be cumulative. One thing that originally confused me was the 'host' - you actually need to have two accounts for a user to connect both locally and remotely - one set to "localhost" and the other set to "%".

The grant command is actually just populating the mysql.user table.

Other useful commands include changing a password:

`SET PASSWORD FOR 'bob'@'loclahost" = PASSWORD('newpassword') SET PASSWORD = PASSWORD('newpassword'); GRANT USAGE ON *.* TO 'user`'@'host' IDENTIFIED BY 'password'