Difference between revisions of "MySQL"
(→Setting Permissions) |
(→Setting Permissions) |
||
| Line 62: | Line 62: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
|} | |} | ||
| + | |||
| + | You can now log on as a user other than root. | ||
Revision as of 00:51, 20 October 2011
MySQL Setup
In order to run MySQL you will need a MySQL server to work with - you can establish a server on one of your own computers, or use a webserver with MySQL installed.
Setup on a personal computer
Once MySQL is installed on your computer and the MySQL daemon is running (in Arch, /etc/rc.d/mysqld start), the next step is to establish users. If you defined a root MySQL password on setup, you can use this to establish a new user; otherwise, just hit enter at any password prompts you encounter.
In order to set up your MySQL databases, you'll first need to log into MySQL - at this point the only MySQl user will be your root user, so log in with:
|
<syntaxhighlight lang="bash"> mysql -u root -p <root password> </syntaxhighlight> |
This will log you int MySQl as root. At this point, you can establish your other users with the SQL CREATE USER query. For example, if you wanted to create a user, "K_Mitnick":
|
<syntaxhighlight lang="SQL"> CREATE USER K_Mitnick; </syntaxhighlight> |
Note the terminating semicolon - this indicates to MySQl that you wish to send your input as a query. Another method of doing this is by terminating your queries with '\g' - there is no difference between the two, it is simply a matter of personal preference.
This user will be created with absolutely no privileges: they can log into your server but do little else.
Setting Permissions
In order to allow your users to execute queries and interact with your databases, you must indicate to the server just what they are allowed to do. The most simple form of this is
|
<syntaxhighlight lang="SQL"> GRANT ALL ON <database name> TO <username> IDENTIFIED BY '<password>'; </syntaxhighlight> |
|
This kind of heavy handed allowance gives the user total freedom to execute any SQL query that they wish. IT SHOULD NEVER be given to the normal user, as this will create a serious gaping vulnerability in your database. |
A more reasonable form of this would be
|
<syntaxhighlight lang="SQL"> GRANT SELECT ON <database name> TO <username> IDENTIFIED BY '<password>'; </syntaxhighlight> |
This only gives access to the SELECT query for the user, which essentially makes their access read-only. The GRANT query can be used with any SQL query as a parameter in order to grant a user the ability to use that query.
After you have changed permissions, it is a good idea to ensure that MySQL is up-to-date with user permissions with
|
<syntaxhighlight lang="SQL"> FLUSH PRIVILEGES; </syntaxhighlight> |
You can now log on as a user other than root.
