MySQL Users and Privileges

Download notebook

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5c02fefb-09ac-4837-8883-e9b27d3c2f17",
   "metadata": {},
   "source": [
    "### MySQL Users and Privileges"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d46049be-7597-4335-942e-b49f8fb0b1b8",
   "metadata": {},
   "source": [
    "##### Pre-requisite\n",
    "1. Have the root access to the MySQL Server"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc0968e3-eb8e-46eb-b2e8-b3caa85d0861",
   "metadata": {},
   "source": [
    "###### 1. Connect to the root user  \n",
    "*By default, root user has no password. You are suggested to login the root account in the terminal rather than using Jupyter Notebook*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8c631bc-34c1-486b-bdb6-0f551ebce33c",
   "metadata": {},
   "source": [
    "Login `root` account in a `bash` shell"
   ]
  },
  {
   "cell_type": "raw",
   "id": "f8d64438-80d4-4cd9-b3d0-e42b5b4dfa70",
   "metadata": {},
   "source": [
    "sudo mysql"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "991642e3-60ed-4de4-9ee9-58cb7e5e00f1",
   "metadata": {},
   "source": [
    "###### 2. Create a new user 'example_user' with password 'example_p@sswd!'  \n",
    "*Change example_user, example_p@sswd! with your desired user name and password*  "
   ]
  },
  {
   "cell_type": "raw",
   "id": "bdf3512d-2734-4099-b047-f13889a3d294",
   "metadata": {},
   "source": [
    "CREATE USER 'example_user'@'localhost' IDENTIFIED BY 'example_p@sswd!';"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "654c816b-8fad-4969-b997-9bd8c49148a8",
   "metadata": {},
   "source": [
    "###### 3. Grant privileges on the database `classicmodels` to the desired user `example_user`."
   ]
  },
  {
   "cell_type": "raw",
   "id": "8b3dd779-cf8c-4d9e-9f17-b898501b299f",
   "metadata": {},
   "source": [
    "GRANT ALL PRIVILEGES ON classicmodels.* TO 'example_user'@'localhost';"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93ccf64a-5818-448b-b76b-5f74f7f9aec4",
   "metadata": {},
   "source": [
    "###### 4. Reload the grant tables"
   ]
  },
  {
   "cell_type": "raw",
   "id": "2c7ffec0-42d1-4044-801e-01d00d70f0f0",
   "metadata": {},
   "source": [
    "FLUSH PRIVILEGES;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5dd9e2a-01d1-4a50-8e0e-fd39da952f0f",
   "metadata": {},
   "source": [
    "###### Optional. Display the permissions granted for the user 'example_user'"
   ]
  },
  {
   "cell_type": "raw",
   "id": "c75d3413-375a-4000-8f83-c10a5e8d48ff",
   "metadata": {},
   "source": [
    "SHOW GRANTS FOR 'example_user'@'localhost';"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13d3019c-eade-42ab-b7ab-638ac99756c0",
   "metadata": {},
   "source": [
    "```\n",
    "+-------------------------------------------------------------------------+\n",
    "| Grants for example_user@localhost                                       |\n",
    "+-------------------------------------------------------------------------+\n",
    "| GRANT USAGE ON *.* TO `example_user`@`localhost`                        |\n",
    "| GRANT ALL PRIVILEGES ON `classicmodels`.* TO `example_user`@`localhost` |\n",
    "+-------------------------------------------------------------------------+\n",
    "2 rows in set (0.00 sec)\n",
    "```"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Mysql",
   "language": "sql",
   "name": "mysql"
  },
  "language_info": {
   "file_extension": ".sql",
   "mimetype": "text/x-sh",
   "name": "sql"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}

Loading