About GRANT statement for user creation I am new to databases, learning MySQL through a textbook.

My doubts are regarding grant statement.

I am learning on a localhost environment (both server and clieat are my own system).

FIRST DOUBT

If I create a new user account by

 GRANT ALL on mydb.* TO 'violetkiwi' IDENTIFIED BY '12345';


Command executed successfully.  When I tried to log into this account by

mysql -u violetkiwi -p

Access denied for user 'violetkiwi'@'localhost' (using password: YES)


If I specify @'localhost' in GRANT ALL issue is solved:

 GRANT ALL on mydb.* TO 'violetkiwi'@'localhost' IDENTIFIED BY '12345';


Is @'localhost' or @'IP ADDRESS' compulsory in GRANT ALL user account creation?

SECOND DOUBT is IPADDRESS specification in GRANT ALL for user account

Consider I have to connect to MySQL server over the internet.

How can I achieve connection to MySQL server through ADSL modem which doesnt have static IP to specify during user account creation with GRANT ALL?

THIRD DOUBT

Sometimes I can access MySQL without specifying -p:

mysql -u root;

mysql -u violetkiwi;


but I can't perform operations on DB. Why this access is provided without specifying password? Is it a bug in my system? Does it have a purpose?
Let's look at the way you created the user.

EXAMPLE : I have a database called zipcodes. I will create a user called rolando

mysql&gt; grant all on zipcodes.* to rolando identified by 'blahblah';
Query OK, 0 rows affected (0.04 sec)

mysql&gt; show grants for rolando;
+--------------------------------------------------------------------------------------------------------+
| Grants for rolando@%                                                                                   |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rolando'@'%' IDENTIFIED BY PASSWORD '*446525BB82B5E22BD9E525261D37C494F623C52B' |
| GRANT ALL PRIVILEGES ON `zipcodes`.* TO 'rolando'@'%'                                                  |
+--------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


I specified rolando without an IP or netblock. By defualt, '%' is used. That should allow remote IP address to login.

You said you tried to login to your account with

 mysql -u violetkiwi -p


Why did this not work? Simply put, you were not connecting remotely. You created the user violetkiwi@'%'. You should create the user violetkiwi@localhost. Then, it can cleanly connect.

You need to make sure your mysql authentication scheme has been cleaned up 

MySQL 5.0 Certification Study Guide says on Page 498 Paragraph 6 in its bulletpoints brings out how to cleanup the authentication process:


  On Unix, MySQL comes with a mysql_secure_installation script that can
  perform several helpful security-related operations on your
  installation. The script has the following capabilities:
  
  
  Set a password for the root accounts
  Remove any remotely accessible root accounts.
  Remove the anonymous user accounts. This improves security because
  it prevents the possibility of anyone connecting to the MySQL server
  as root from a remote host. The results is that anyone who wants to
  connect as root must first be able to log in on the server host, which
  provides an additional barrier against attack.
  Remove the test database (If you remove the anonymous accounts, you
  might also want to remove the test database to which they have
  access).
  


A remotely accessible account is an account whose host is % which allows anyone and his grandmother. To find those accounts, run this query:

SELECT user,host FROM mysql.user WHERE host='%';


You should also be cognizant of how MySQL user authentication occurs.

According to MySQL 5.0 Certification Study Guide pages 486,487, the following describes mysql's authentication algorithm:


  There are two stages of client access control:
  
  In the first stage, a client attempts to connect and the server either
  accepts or rejects the connection. For the attempt to succeed, some
  entry in the user table must match the host from which the client
  connects, the username, and the password.
  
  In the second stage (which occurs only if a client has already
  connected sucessfully), the server checks every query it receives from
  the client to see whether the client has sufficient privileges to
  execute it.
  
  The server matches a client against entries in the grant tables based
  on the host from which the client connects and the user the client
  provides. However, it's possible for more than one record to match:
  
  Host values in grant tables amy be specified as patterns contains
  wildcard values. If a grant table contains entris from
  myhost.example.com, %.example.com, %.com, and %, all of them
  match a client who connects from myhost.example.com.
  
  Patterns are not allowed for the User values in grant table entries,
  but a username may be given as an empty string to specify an anonymous
  user. The empty string matches any username and thus effectively acts
  as a wildcard.
  
  When the Host and the User values in more than one user table record
  match a client, the server must decide which one to use. It does this
  by sorting records with the most specific Host and User column values
  first, and choosing the matching record that occurs first in the
  sorted list, Sorting take place as follows:
  
  In the Host Column, literal values such as localhost, 127.0.0.1,
  and myhost.example.com sort ahead of values such as %.example.com
  that have pattern characters in them. Pattern values are sorted
  according to how specific they are. For example, %.example.com is
  more specific than %.com, which is more specific than %.
  
  In the User column, non-blank usernames sort ahead of blank usernames.
  That is, non-anonymous users sort ahead of anonymous users.
  
  The server performs this sorting when it starts. It reads the grant
  tables into memory, sorts them, and uses the  in-memory copies for
  access control.


SUMMARY

If you have any users that must have remote access, change the host of each user that has % into the public IP you will be connecting from. 

For example, if the user myuser must connect from 208.21.42.184, replace the host like this

UPDATE mysql.user SET host='208.21.42.184' WHERE user='myuser' AND host='%';
FLUSH PRIVILEGES;


You should also be checking for the presence of any test databases. The presence of test databases. Any database named test or whose first five(5) characters are test_ need to be renamed immediately !!! I wrote an earlier post on how and why.