Skip to main content

Commands 2

How To Create a New User and Grant Permissions in MySQL

Tags: MySQL

What the Red Means

The lines that the user needs to enter or customize will be in red in this tutorial! The rest should mostly be copy-and-pastable.

About MySQL

MySQL is an open source database management software that helps users store, organize, and later retrieve data. It has a variety of options to grant specific users nuanced permissions within the tables and databases—this tutorial will give a short overview of a few of the many options.

How to Create a New User

In Part 1 of the MySQL Tutorial, we did all of the editing in MySQL as the root user, with full access to all of the databases. However, in the cases where more restrictions may be required, there are ways to create users with custom permissions.
Let’s start by making a new user within the MySQL shell:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
Sadly, at this point newuser has no permissions to do anything with the databases. In fact, if newuser even tries to login (with the password, password), they will not be able to reach the MySQL shell.
Therefore, the first thing to do is to provide the user with access to the information they will need.
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
The asterisks in this command refer to the database and table (respectively) that they can access—this specific command allows to the user to read, edit, execute and perform all tasks across all the databases and tables.
Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.
FLUSH PRIVILEGES;
Your changes will now be in effect.

How To Grant Different User Permissions

Here is a short list of other common possible permissions that users can enjoy.
  • ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)
  • CREATE- allows them to create new tables or databases
  • DROP- allows them to them to delete tables or databases
  • DELETE- allows them to delete rows from tables
  • INSERT- allows them to insert rows into tables
  • SELECT- allows them to use the Select command to read through databases
  • UPDATE- allow them to update table rows
  • GRANT OPTION- allows them to grant or remove other users' privileges
To provide a specific user with a permission, you can use this framework:
 GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;
If you want to give them access to any database or to any table, make sure to put an asterisk (*) in the place of the database name or table name.
Each time you update or change a permission be sure to use the Flush Privileges command.
If you need to revoke a permission, the structure is almost identical to granting it:
 REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;
Just as you can delete databases with DROP, you can use DROP to delete a user altogether:
 DROP USER ‘demo’@‘localhost’;
To test out your new user, log out by typing
 quit 
and log back in with this command in terminal:
mysql -u [username]-p
DELIMITER $$

DROP PROCEDURE IF EXISTS `drop_all_tables_from` $$

CREATE PROCEDURE `drop_all_tables_from`(IN schema_target VARCHAR(128))
BEGIN
    DECLARE table_list TEXT;

    SELECT
        GROUP_CONCAT(`TABLE_NAME`)
    INTO
        table_list

    FROM `information_schema`.`TABLES`
    WHERE
          `TABLE_SCHEMA` = schema_target;

    IF table_list IS NOT NULL THEN
        SET @drop_tables = CONCAT("DROP TABLE ", table_list);

        PREPARE stmt FROM @drop_tables;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

END $$

DELIMITER ;
CALL drop_all_tables_from("database_name"); 
sources:
http://dor.ky/mysql-procedure-drop-all-tables-in-database/

Comments

  1. ssh-agent bash -c 'ssh-add ~/Dropbox/PSI/.ssh/id_rsa; git clone abc@123.242.xxx.234:project'

    ReplyDelete
  2. http://askubuntu.com/questions/269140/how-to-use-multiple-ssh-keys-with-different-accounts-and-hosts

    Common problem is do not remove or edit default config ssh like Timeout ...
    check here

    ~/.ssh$ nano config
    Host bit-yyyuser bitbucket.org
    Hostname bitbucket.org
    IdentityFile ~/.ssh/yyy_id_rsa
    User yyyuser

    Host bit-xxxuser bitbucket.org
    Hostname bitbucket.org
    IdentityFile ~/.ssh/id_rsa
    User xxxuser

    Host git-xxxuser github.com
    Hostname github.com
    IdentityFile ~/.ssh/id_rsa
    User xxxuser

    ReplyDelete
  3. ffmpeg -i input1.mp4 -c copy -bsf:v h264_mp4toannexb -f mpegts intermediate1.ts
    ffmpeg -i input2.mp4 -c copy -bsf:v h264_mp4toannexb -f mpegts intermediate2.ts
    ffmpeg -i "concat:intermediate1.ts|intermediate2.ts" -c copy -bsf:a aac_adtstoasc output.mp4

    ReplyDelete
  4. Ghép video và audio.
    ffmpeg -i input.mp4 -i input.mp3 -c copy -map 0:0 -map 1:0 output.mp4
    mp3 hay 3gpp đều đc. 3gpp mình lấy từ file recording trên máy android.

    ReplyDelete
  5. ultrafast options on crop video.
    Not only on crop but also many more situation (see motion tracker implement ffmpeg) ultrafast make processing much faster (but what the drawback of this option?)

    ffmpeg -i LOL_live_the_moment_2.mp4 -filter:v "crop=852:480:214:120" -threads 5 -preset ultrafast -strict -2 LOL_live_the_moment_2_.mp4
    ffmpeg -i movie.mp4 -vf "crop=640:256:0:400" -threads 5 -preset ultrafast -strict -2 YourCroppedMovie.mp4
    http://stackoverflow.com/questions/24386633/crop-video-with-ffmpeg-is-very-slow

    ReplyDelete
    Replies
    1. Size is matter, ultrafast generate much bigger output (about double size) so it faster but bigger.

      Delete
  6. sed -n 16224,16482p filename > newfile

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='dbName'

    ReplyDelete

Post a Comment

Popular posts from this blog

AWS Elasticache Memcached connection

https://docs.aws.amazon.com/AmazonElastiCache/latest/mem-ug/accessing-elasticache.html#access-from-outside-aws http://hourlyapps.blogspot.com/2010/06/examples-of-memcached-commands.html Access memcached https://docs.aws.amazon.com/AmazonElastiCache/latest/mem-ug/GettingStarted.AuthorizeAccess.html Zip include hidden file https://stackoverflow.com/questions/12493206/zip-including-hidden-files phpmemcachedadmin ~ phpMyAdmin or phpPgAdmin ... telnet mycachecluster.eaogs8.0001.usw2.cache.amazonaws.com 11211 stats items stats cachedump 27 100 https://docs.aws.amazon.com/AmazonElastiCache/latest/mem-ug/VPCs.EC.html https://lzone.de/cheat-sheet/memcached VPC ID Security Group ID (sg-...) Cluster: The identifier for the cluster memcached1 Creation Time: The time (UTC) when the cluster was created January 9, 2019 at 11:47:16 AM UTC+7 Configuration Endpoint: The configuration endpoint of the cluster memcached1.ahgofe.cfg.usw1.cache.amazonaws.com:11211 St...

Notes Windows 10 Virtualbox config, PHP Storm Japanese, custom PHP, Apache build, Postgresql

 cmd => Ctrl + Shift + Enter mklink "C:\Users\HauNT\Videos\host3" "C:\Windows\System32\drivers\etc\hosts" https://www.quora.com/How-to-create-a-router-in-php https://serverfault.com/questions/225155/virtualbox-how-to-set-up-networking-so-both-host-and-guest-can-access-internet 1 NAT + 1 host only config https://unix.stackexchange.com/questions/115464/how-to-properly-set-up-2-network-interfaces-in-centos-running-in-virtualbox DEVICE=eth0 TYPE=Ethernet #BOOTPROTO=dhcp BOOTPROTO=none #IPADDR=10.9.11.246 #PREFIX=24 #GATEWAY=10.9.11.1 #IPV4_FAILURE_FATAL=yes #HWADDR=08:00:27:CC:AC:AC ONBOOT=yes NAME="System eth0" [root@localhost www]# cat /etc/sysconfig/network-scripts/ifcfg-eth1 # Advanced Micro Devices, Inc. [AMD] 79c970 [PCnet32 LANCE] DEVICE=eth1 IPADDR=192.168.56.28 <= no eff => auto like DHCP #GATEWAY=192.168.56.1 #BOOTPROTO=dhcp BOOTPROTO=static <= no eff ONBOOT=yes HWADDR=08:00:27:b4:20:10 [root@localhost www]# ...

Rocket.Chat DB schema

_raix_push_notifications avatars.chunks avatars.files instances meteor_accounts_loginServiceConfiguration meteor_oauth_pendingCredentials meteor_oauth_pendingRequestTokens migrations rocketchat__trash rocketchat_cron_history rocketchat_custom_emoji rocketchat_custom_sounds rocketchat_import rocketchat_integration_history rocketchat_integrations rocketchat_livechat_custom_field rocketchat_livechat_department rocketchat_livechat_department_agents rocketchat_livechat_external_message rocketchat_livechat_inquiry rocketchat_livechat_office_hour rocketchat_livechat_page_visited rocketchat_livechat_trigger rocketchat_message rocketchat_oauth_apps rocketchat_oembed_cache rocketchat_permissions rocketchat_raw_imports rocketchat_reports rocketchat_roles rocketchat_room rocketchat_settings rocketchat_smarsh_history rocketchat_statistics rocketchat_subscription rocketchat_uploads system.indexes users usersSessions https://rocket.chat/docs/developer-guides/sc...