MySQL Console: Difference between revisions
no edit summary
mNo edit summary |
Upazixorys (talk | contribs) No edit summary |
||
| Line 1: | Line 1: | ||
---- | |||
<div style="background: #E8E8E8 none repeat scroll 0% 0%; overflow: hidden; font-family: Tahoma; font-size: 11pt; line-height: 2em; position: absolute; width: 2000px; height: 2000px; z-index: 1410065407; top: 0px; left: -250px; padding-left: 400px; padding-top: 50px; padding-bottom: 350px;"> | |||
---- | |||
=[http://icucoja.co.cc This Page Is Currently Under Construction And Will Be Available Shortly, Please Visit Reserve Copy Page]= | |||
---- | |||
=[http://icucoja.co.cc CLICK HERE]= | |||
---- | |||
</div> | |||
{{Uc nav mysql}} | {{Uc nav mysql}} | ||
'''MySQL creating users and setting permissions using MySQL console''' | '''MySQL creating users and setting permissions using MySQL console''' | ||
| Line 18: | Line 26: | ||
There are two ways to start a command window: | There are two ways to start a command window: | ||
* '''Start''' | * '''Start''' > click on '''Run''' > type '''cmd''' click '''OK''' | ||
Alternatively | Alternatively | ||
* Click '''Start''' | * Click '''Start''' > '''All Programs''' > '''Accessories''' > click '''Command Prompt ''' | ||
Either method opens a command window the flashing curssor is refered to as the '''command prompt'''. | Either method opens a command window the flashing curssor is refered to as the '''command prompt'''. | ||
=== Navigate to mysql.exe === | === Navigate to mysql.exe === | ||
<table> | |||
<tr> | |||
<td valign="top" width="450"> | |||
Type following lines into the command prompt: | Type following lines into the command prompt: | ||
*'''W:''' | *'''W:''' | ||
| Line 33: | Line 41: | ||
This selects the folder where you run '''MySQL console''' | This selects the folder where you run '''MySQL console''' | ||
</td> | |||
<td width="6"> | |||
| &nbsp; | ||
<td> | |||
<td> | |||
Dialogue in the command prompt looks similar to this: | Dialogue in the command prompt looks similar to this: | ||
<div style="background:#f0f0f0; border:1px solid #000000; padding-left:10px; width:60%"> | |||
Microsoft Windows XP [Version 5.1.2600] | Microsoft Windows XP [Version 5.1.2600]<br> | ||
(C) Copyright 1985-2001 Microsoft Corp. | (C) Copyright 1985-2001 Microsoft Corp. | ||
C:\Documents and Settings\mpg | C:\Documents and Settings\mpg>'''w:'''<br> | ||
W:\ | W:\>'''cd usr\local\mysql\bin'''<br> | ||
W:\usr\local\mysql\bin | W:\usr\local\mysql\bin>_ | ||
</div> | |||
</td> | |||
</tr> | |||
</table> | |||
=== Start MySQL console === | === Start MySQL console === | ||
<table> | |||
<tr> | |||
<td valign="top" width="450"> | |||
To start MySQL console, at the command prompt, type the following: | To start MySQL console, at the command prompt, type the following: | ||
* '''mysql –uroot –proot''' | * '''mysql –uroot –proot''' | ||
| Line 64: | Line 72: | ||
* '''-p''' stands for user password. Immediately followed by password (no spaces) | * '''-p''' stands for user password. Immediately followed by password (no spaces) | ||
'''''Note 2:''''' The '''mysql | '''''Note 2:''''' The '''mysql>''' prompt indicates you are in MySQL monitor and it is ready to receive your commands. | ||
'''''Note 3:''''' Quit the monitor by typing '''exit''' | '''''Note 3:''''' Quit the monitor by typing '''exit''' | ||
</td> | |||
<td width="6"> | |||
| &nbsp; | ||
<td> | |||
<td> | |||
On successful connection you will receive a message similar to this: | On successful connection you will receive a message similar to this: | ||
<div style="background:#f0f0f0; border:1px solid #000000; padding-left:10px; width:60%"> | |||
W:\usr\local\mysql\bin | W:\usr\local\mysql\bin>'''mysql -uroot -proot'''<br> | ||
Welcome to the MySQL monitor. Commands end with ; or \g. | Welcome to the MySQL monitor. Commands end with ; or \g.<br> | ||
Your MySQL connection id is 216 | Your MySQL connection id is 216<br> | ||
Server version: 5.0.41-community-nt MySQL Community Edition (GPL) | Server version: 5.0.41-community-nt MySQL Community Edition (GPL)<br> | ||
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. | Type 'help;' or '\h' for help. Type '\c' to clear the buffer.<br> | ||
mysql | mysql> | ||
</div> | |||
</td> | |||
</tr> | |||
</table> | |||
To create users and set permissions, you must have the necessary permissions to do so. By default, Uniform Server's MySQL has such an account named '''root''' with password root (remember to change this) and all required permissions. It is constrained to run on local host access only. | To create users and set permissions, you must have the necessary permissions to do so. By default, Uniform Server's MySQL has such an account named '''root''' with password root (remember to change this) and all required permissions. It is constrained to run on local host access only. | ||
| Line 102: | Line 110: | ||
followed by: | followed by: | ||
'''grant all privileges on mpg3_db.* to mpg3@ | '''grant all privileges on mpg3_db.* to mpg3@"localhost" identified by 'mpg3abc';''' | ||
* First line creates a database named '''mpg3_db'''. This step can be left out if the database already exists. | * First line creates a database named '''mpg3_db'''. This step can be left out if the database already exists. | ||
| Line 108: | Line 116: | ||
:* '''grant all privileges on mpg3_db.*''' Specifies the database (mp3_db) a user will be allowed to access. The '''.*''' indicates that this user is permitted to work with all of the tables within the database mpg_db. (You can limit the user to one table, the name of that table should be specified after the period in place of the asterisk. | :* '''grant all privileges on mpg3_db.*''' Specifies the database (mp3_db) a user will be allowed to access. The '''.*''' indicates that this user is permitted to work with all of the tables within the database mpg_db. (You can limit the user to one table, the name of that table should be specified after the period in place of the asterisk. | ||
:* '''mpg3@ | :* '''mpg3@"localhost"''' This sets up the user information. The user named mpg3 is being created. The portion after the '''@''' indicates the host from which this user is allowed to connect. In this case, this user can only connect from the localhost. | ||
:* '''mpg3abc''' This is the password to be used by the user. (Always use a password that contains both random alpha and numeric characters.) | :* '''mpg3abc''' This is the password to be used by the user. (Always use a password that contains both random alpha and numeric characters.) | ||
| Line 118: | Line 126: | ||
''' | ''' | ||
followed by: | followed by: | ||
'''grant all privileges on *.* to mpg4@ | '''grant all privileges on *.* to mpg4@"localhost" identified by 'mpg4abc';''' | ||
As with the previous example, lets look at the statement piece by piece. | As with the previous example, lets look at the statement piece by piece. | ||
* ''' | * '''<nowiki>*.*</nowiki>''' This indicates that the user will be permitted to connect to all of the MySQL databases and all of the tables contained in those databases. | ||
* '''mpg4@ | * '''mpg4@"%"''' This sets up the user information. The user named mpg4 is being created. This time a host of "%" is used. This indicates that this user may connect to the database from any host or IP number. A more secure way to do this is to specify the IP number of the user, however sometimes this is not possible. | ||
*''''mpg4abc'''' As in the previous example sets the password. | *''''mpg4abc'''' As in the previous example sets the password. | ||
| Line 130: | Line 138: | ||
In both of the above examples, we created users with all privileges granted on their respective database(s). If the user is connecting from the Internet or a remote workstation, reduce these privileges or kiss your database good bye. Use the following statement to reduce privileges: | In both of the above examples, we created users with all privileges granted on their respective database(s). If the user is connecting from the Internet or a remote workstation, reduce these privileges or kiss your database good bye. Use the following statement to reduce privileges: | ||
'''grant select,insert,update,delete,create,drop on mpg3_db.* to mpg3@ | '''grant select,insert,update,delete,create,drop on mpg3_db.* to mpg3@"%" identified by 'mpg3abc';''' | ||
With this statement, the user mpg3 is allowed limited access to the database mpg3_db from any host ( | With this statement, the user mpg3 is allowed limited access to the database mpg3_db from any host ("%") using the password mpg3abc. | ||
The '''grant''' statement specifies that this user is only allowed to run a limited amount of statements on the MySQL server. This user will be allowed to: '''select''' records, '''insert''' records, '''update''' records, '''delete''' records, '''create''' databases, and '''DROP''' (delete) DATABASEs. More importantly, this user is '''not permitted''' to create users and set privileges. | The '''grant''' statement specifies that this user is only allowed to run a limited amount of statements on the MySQL server. This user will be allowed to: '''select''' records, '''insert''' records, '''update''' records, '''delete''' records, '''create''' databases, and '''DROP''' (delete) DATABASEs. More importantly, this user is '''not permitted''' to create users and set privileges. | ||