Skip to content

Instantly share code, notes, and snippets.

@shady-robot

shady-robot/SQL syntax

Created July 20, 2015 08:07
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save shady-robot/fe0faf47f17dec845fad to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save shady-robot/fe0faf47f17dec845fad to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. shady-robot created this gist Jul 20, 2015.
    55 changes: 55 additions & 0 deletions SQL syntax
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,55 @@
    ####This is a cheat sheet for mysql syntax, which is mostly about privilege management in mysql.
    ---------------------------------------------------------------------------------------------------------------------
    ### List all the user in mysql(with access to the mysql database)
    mysql> select user, host, password from mysql.user;

    ### Create a user with password
    mysql> create user 'shady'@'localhost' identified by 'password';
    ### Create a user without password
    mysql> create user 'eric'@'localhost';
    -----------------------------------------------------------------------------------------------------------------------

    ### Change the password for currently logged in account
    #### Find out which account you are currently logged in
    mysql> select current_user();
    mysql> set password = password('new_password');

    /*Notice that the password() function in mysql
    The PASSWORD() function that computes password hash values and in
    the structure of the user table where passwords are stored.
    */

    ### Change the password for specific user(with corresponding privileges)
    mysql> set password for 'eric'@'localhost' = password('new_password');
    --------------------------------------------------------------------------------------------------------------------------
    #### Grant privilege for the new created user.

    ### Show the privileges of specific user
    mysql> show grants for 'daniel'@'localhost';
    ### You may see something like:
    +---------------------------------------------------------------------------------------------------------------+
    | Grants for daniel@localhost |
    +---------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'daniel'@'localhost' IDENTIFIED BY PASSWORD '*3C06A471CB6048FCCCF5DB904D8F5BE49F1C7585' |
    +---------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    ### Show privileges for the current account that you logged in
    mysql> show grants;

    ### Grant all privileges on one database to specific user
    mysql> grant all on databaseName.* to 'daniel'@'localhost';
    ### Grant specific privileges(such as select, update, insert, delete) on database to specific user
    mysql> grant select on databaseName.* to 'daniel'@'localhost';
    ### Grant specific privileges on table for specific user
    mysql> grant select(track_id, time) on music.track to 'daniel'@'localhost';

    ### Use the with grant option, to make the user have the privilege to pass the privileges that he/she had granted
    mysql> grant select on databaseName.* to 'daniel'@'localhost' with grant option;
    /* This gives the the user 'daniel'@'localhost' the privilege to grant the select privilege to other user */
    ---------------------------------------------------------------------------------------------------------------------
    ### revoke privileges from specific user
    #### First, to see which privileges the user has
    mysql> show grants for 'daniel'@'localhost'
    #### Rebvoke the select privileges from the user
    mysql> revoke select on databaseName.* from 'daniel'@'localhost';