mysql user

mysql-user

When connect to the server, it checks not only the username that you specify, but also what host you're connecting from.

One implicationof this concept of an account is that it is possible to set up separate accounts for different users who have the same usernamebut connect from different hosts.

MySql first considder hostnames and then usernames when a client attemps to connectiion.

Select current_user();


create user 'root'  identified by 'root'
create user 'root'@'192.168.10.112' identified by 'root';
create user 'root'@'192.169.%' identified by 'root';



GRANT  mysql-grant
http://dev.mysql.com/doc/refman/5.1/en/grant.html

GRANT 
(all | usage | create | drop | index | select | usage  ...)
on (databases | tables | indexes | function | procdure | views)
        ( * | db_name.* | db_name.table_name | table_name |  db_name.routine_name )
to someOne@333.444.555.666 


show grants for root;
show grants for userName;

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90
GRANT ALL ON *.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';
GRANT ALL ON mydb.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';
GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';
GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';

revoke create on tables .* from root;


Grant privilege1 (column_name1,column_name2), privilege2 (column_name1, column_name3) on databasename.tablename to 'username' @ 'hostname';



GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option ...]

object_type:
    TABLE
  | FUNCTION
  | PROCEDURE

priv_level:
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name

user_specification:
    user [IDENTIFIED BY [PASSWORD] 'password']

ssl_option:
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'

with_option:
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count