path: root/11-privilege-management/privilege-management.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "899bb06e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import agh_db_lectures\n",
    "agh_db_lectures.prepare_notebook_for_sql()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33596512",
   "metadata": {},
   "source": [
    "# Privilege Management"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf1306b9",
   "metadata": {},
   "source": [
    "Consider the following scenarios.\n",
    "\n",
    "> Multiple applications, each with its own database (i.e., catalog) are served by the same DBMS.  Every application manages its own database by, e.g., creating tables and migrating the schema to new versions on updates.\n",
    "\n",
    "> A database for an enterprise is designed first and then multiple applications are developed to use it.  Certain employees (e.g., administrators, managers, analysts) are also given direct (SQL) access to the database."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7530a98",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "In the first scenario, each application can have its own user account with the DBMS.  Privileges can be configured so that the application is only able to access its own catalog.\n",
    "\n",
    "In the second scenario the privileges can be used on a more granular level to grant access to particular tables or particular (kinds of) operations.  The concept of user roles becomes useful."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c12176f",
   "metadata": {},
   "source": [
    "## Managing Users in MariaDB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bdeab499",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql mysql:///mysql?unix_socket=/var/run/mysql/mysql.sock"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c022728",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "We are connecting to database `mysql` (which exists by default)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d140d6d2",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT Host, User, Password FROM user;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5dc4212",
   "metadata": {},
   "source": [
    "### User Creation and Privilege Assignment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "50756371",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE USER theodore@127.0.0.1 IDENTIFIED BY 'PingVim'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "309c18ab",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "```sql\n",
    "DROP USER theodore@127.0.0.1\n",
    "```\n",
    "\n",
    "```sql\n",
    "SELECT PASSWORD('PingVim')\n",
    "```\n",
    "\n",
    "```sql\n",
    "CREATE OR REPLACE USER theodore@127.0.0.1\n",
    "IDENTIFIED BY PASSWORD '*749882C224A9E49BADB6492AAAE881F371BB7C4E'\n",
    "```\n",
    "\n",
    "```sql\n",
    "CREATE USER theodore@\"192.168.134.0/255.255.255.0\"\n",
    "IDENTIFIED BY 'S3cur3 Pa$$word3'\n",
    "```\n",
    "\n",
    "Whenever in doubt, we can quote the host or user part."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbb4986f",
   "metadata": {},
   "source": [
    "```shell\n",
    "mariadb --port=23306 --host=127.0.0.1 \\\n",
    "    -p'S3cur3 Pa$$word3' --user=theodore mysql\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e442dbe9",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "```shell\n",
    "mariadb --port=23306 --host=127.0.0.1 \\\n",
    "    -p'PingVim' --user=theodore mysql\n",
    "```\n",
    "\n",
    "```sql\n",
    "GRANT SELECT ON agh_it_wordpress TO theodore@127.0.0.1\n",
    "```\n",
    "\n",
    "Theodore can now connect to the catalog."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93135616",
   "metadata": {},
   "source": [
    "```sql\n",
    "SELECT comment_ID,\n",
    "       SUBSTRING(comment_content, 1, 20),\n",
    "       comment_approved\n",
    "FROM wp_comments;\n",
    "```\n",
    "\n",
    "But these fail.\n",
    "\n",
    "```sql\n",
    "SELECT * FROM wp_posts;\n",
    "```\n",
    "\n",
    "```sql\n",
    "UPDATE wp_comments SET comment_approved = 1;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "edb09e41",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "REVOKE SELECT ON agh_it_wordpress.* FROM theodore@127.0.0.1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "028f00cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE OR REPLACE USER pancratius@127.0.0.1 IDENTIFIED VIA pam"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "869894c2",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Hostname can also be matched with a `LIKE`-like pattern, e.g., `10.0.%`.\n",
    "\n",
    "Plugins can be used to authenticate users differently.  For example, authentication through external mechanisms is possible.  The `pam` authentication plugin would use similar mechanisms as used in the (UNIX-like) host operating system.\n",
    "\n",
    "Plugins — as one would expect — are pluggable.  Hence they need to be loaded before they can be used.  This can be done with a command or with a configuration option.\n",
    "\n",
    "```sql\n",
    "INSTALL SONAME 'auth_pam'\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6b193c7",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT Host, User, Password, plugin, authentication_string\n",
    "FROM user;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12598183",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Use of `pam` authentication would be difficult to demonstrate given our server is running in an unprivileged container.  Let's assign `pancratius@\"127.0.0.1\"` a casual password.\n",
    "\n",
    "```sql\n",
    "ALTER USER pancratius@\"127.0.0.1\"\n",
    "IDENTIFIED BY 'MUCH passw0rd, VERY secur!ty'\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d23e0eb3",
   "metadata": {},
   "source": [
    "```sql\n",
    "GRANT SELECT ON agh_it_wordpress.wp_comments TO pancratius@\"127.0.0.1\";\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e71ea97e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "GRANT GRANT OPTION ON agh_it_wordpress.wp_comments\n",
    "TO theodore@127.0.0.1;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45a7bf00",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Perform the above as `theodore@127.0.0.1`.\n",
    "\n",
    "```sql\n",
    "GRANT GRANT OPTION ON agh_it_wordpress.wp_comments\n",
    "TO theodore@127.0.0.1;\n",
    "SHOW GRANTS FOR theodore@127.0.0.1\n",
    "```\n",
    "\n",
    "After this `GRANT`, `theodore@127.0.0.1` can now pass forward the `SELECT` privilege on `wp_comments`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92b87a7b",
   "metadata": {},
   "source": [
    "```shell\n",
    "mariadb --port=23306 --host=127.0.0.1 \\\n",
    "    -p'MUCH passw0rd, VERY secur!ty' --user=pancratius \\\n",
    "    agh_it_wordpress\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd2c5b84",
   "metadata": {},
   "source": [
    "### Privilege Types and Examples"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6df9f5b8",
   "metadata": {},
   "source": [
    "https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant\n",
    "\n",
    "#### Global Level\n",
    "\n",
    "- `ALL PRIVILEGES ON *.*`\n",
    "- `CREATE USER ON *.*`\n",
    "\n",
    "#### Database Level\n",
    "\n",
    "- `DROP ON agh_it_wordpress.*` (user can drop `agh_it_wordpress`) \n",
    "- `CREATE ON theodore_db.*` (user can create `theodore_db`)\n",
    "\n",
    "#### Table Level\n",
    "\n",
    "- `DELETE ON agh_it_wordpress.wp_comments`\n",
    "- `DELETE ON wp_comments` (when already connected to the right db)\n",
    "\n",
    "#### Column Level\n",
    "\n",
    "- `UPDATE (comment_approved) ON agh_it_wordpress.wp_comments`\n",
    "\n",
    "#### Others\n",
    "\n",
    "Function, procedure and proxy privileges…"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "615343d4",
   "metadata": {},
   "source": [
    "## User Roles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8081dcee",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE ROLE user_viewer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "46e4343f",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT Host, User, Password, is_role\n",
    "FROM user"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f6d5898",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "```sql\n",
    "SELECT User AS role FROM user WHERE is_role = 'Y'\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3bcb163b",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "```sql\n",
    "CREATE ROLE IF NOT EXISTS user_viewer;\n",
    "\n",
    "GRANT SELECT (ID, user_login, user_email, user_registered)\n",
    "ON agh_it_wordpress.wp_users\n",
    "TO user_viewer\n",
    "```\n",
    "\n",
    "```sql\n",
    "GRANT user_viewer TO theodore@127.0.0.1\n",
    "```\n",
    "\n",
    "Then as, `theodore@127.0.0.1`, execute the following.\n",
    "\n",
    "```sql\n",
    "SELECT user_login FROM wp_users;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "125995f8",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Still denied.\n",
    "\n",
    "No role is activated by default.\n",
    "\n",
    "```sql\n",
    "SELECT CURRENT_ROLE()\n",
    "```\n",
    "\n",
    "```sql\n",
    "SET ROLE user_viewer;\n",
    "SELECT CURRENT_ROLE();\n",
    "SET ROLE NONE;\n",
    "SELECT CURRENT_ROLE();\n",
    "SET ROLE user_viewer;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f01add7",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE ROLE site_reporter;\n",
    "\n",
    "GRANT SELECT ON agh_it_wordpress.wp_posts TO site_reporter;\n",
    "\n",
    "GRANT user_viewer TO site_reporter;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a2120211",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "REVOKE user_viewer FROM theodore@127.0.0.1;\n",
    "GRANT site_reporter TO theodore@127.0.0.1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4907dc1d",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Theodore now cannot set current role to `user_viewer`, but can set to `site_reporter` and, as such, still use all the privileges of `user_viewer`, `PUBLIC`, and `reporter`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51aec64d",
   "metadata": {},
   "source": [
    "## Major Differences in Postgres"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4db6288e",
   "metadata": {},
   "source": [
    "In Postgres, when a privilege is revoked from a user, it gets revoked in a cascade fashion from those who got it from that user.\n",
    "\n",
    "```sql\n",
    "REVOKE SELECT ON products FROM eugenia CASCADE\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79b9028f",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "`CASCADE` is necessary, otherwise, if dependent privileges exist, the `REVOKE` command shall fail.\n",
    "\n",
    "MariaDB currently does not have the notion of dependent privileges."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "/gnu/store/q35bxk1i5blx0rcgxphhrq9xqmgha9bz-python-3.11.11/bin/python3",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}