{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "890e5b24-ecb5-4a08-9d09-438270a7b358",
   "metadata": {},
   "source": [
    "\n",
    "## Lecture 17: Access Control and Procedural SQL\n",
    "\n",
    "### Announcements\n",
    "\n",
    "- Homework #5 is out, great for studying for the exam.\n",
    "\n",
    "- Lecture Exercises 16, 17, 18 are due on monday at 2pm.\n",
    "\n",
    "- We will drop the lowest 4 lecture exercises, use them to learn and\n",
    "  practice.\n",
    "\n",
    "- Exam #2 next week thursday. It will be solely on SQL. It will\n",
    "  incorporate everything we did in class, including access control\n",
    "  but not procedural SQL.\n",
    "\n",
    "  - I will post the database schema for the exam the day before the\n",
    "    exam.\n",
    "\n",
    "\n",
    "---\n",
    "\n",
    "### Access Control in SQL \n",
    "\n",
    "PostgreSQL checks privileges in layers.\n",
    "\n",
    "- To get at a table, a role has to pass the check at each level:\n",
    "   database -> schema -> table -> (and optionally column).\n",
    "\n",
    "- A grant at one level does not carry over to another level.\n",
    "\n",
    "1. Database privileges: login/connect\n",
    "\n",
    "2. Schema privileges: access to the namespace\n",
    "\n",
    "A schema has only two privileges:\n",
    "\n",
    "-  USAGE\tLook up objects inside the schema.\n",
    "                Without it you can’t reach anything in the schema at all,\n",
    "\t\teven tables you have full rights on.\n",
    "- CREATE\tCreate new objects in the schema.\n",
    "\n",
    "3. Table privileges: access to the data\n",
    "\n",
    "- Privilege\tAllows\n",
    "- SELECT\tRead rows (also needed for UPDATE/DELETE with a WHERE clause that reads columns)\n",
    "- INSERT\tAdd rows\n",
    "- UPDATE\tChange rows\n",
    "- DELETE\tRemove rows\n",
    "- TRUNCATE\tEmpty the table\n",
    "- REFERENCES\tCreate foreign keys that point to it\n",
    "- TRIGGER\tCreate triggers on it\n",
    "- MAINTAIN (PG 17+)\tVACUUM, ANALYZE, REINDEX, REFRESH MATERIALIZED VIEW, etc.\n",
    "\n",
    "\n",
    "**Who has privileges?**\n",
    "\n",
    "- Users and roles\n",
    "\n",
    "- A role is a database identity that can own objects, receive\n",
    "  privileges, and grant permissions to other roles.\n",
    "\n",
    "- A user is simply a role that has the LOGIN attribute, allowing it to\n",
    "  log in to PostgreSQL.\n",
    "\n",
    "----\n",
    "\n",
    "```\n",
    "-- Create two schemas\n",
    "CREATE SCHEMA sales;\n",
    "CREATE SCHEMA hr;\n",
    "\n",
    "-- Create tables in the sales schema\n",
    "CREATE TABLE sales.customers (\n",
    "    customer_id SERIAL PRIMARY KEY,\n",
    "    customer_name VARCHAR(100),\n",
    "    city VARCHAR(100)\n",
    ");\n",
    "\n",
    "CREATE TABLE sales.orders (\n",
    "    order_id SERIAL PRIMARY KEY,\n",
    "    customer_id INT REFERENCES sales.customers(customer_id),\n",
    "    amount NUMERIC(10, 2)\n",
    ");\n",
    "\n",
    "-- Create tables in the hr schema\n",
    "CREATE TABLE hr.employees (\n",
    "    employee_id SERIAL PRIMARY KEY,\n",
    "    employee_name VARCHAR(100),\n",
    "    department_id INT,\n",
    "    salary NUMERIC(10, 2)\n",
    ");\n",
    "\n",
    "CREATE TABLE hr.departments (\n",
    "    department_id SERIAL PRIMARY KEY,\n",
    "    department_name VARCHAR(100)\n",
    ");\n",
    "\n",
    "\n",
    "CREATE ROLE sales_readonly INHERIT;\n",
    "CREATE ROLE hr_readonly NOINHERIT;\n",
    "\n",
    "CREATE USER john WITH PASSWORD 'john' CREATEDB;\n",
    "CREATE USER mary WITH PASSWORD 'mary' NOINHERIT;\n",
    "\n",
    "GRANT USAGE ON SCHEMA sales TO sales_readonly;\n",
    "\n",
    "GRANT SELECT\n",
    "ON ALL TABLES IN SCHEMA sales\n",
    "TO sales_readonly;\n",
    "\n",
    "GRANT USAGE ON SCHEMA hr TO hr_readonly;\n",
    "\n",
    "GRANT SELECT\n",
    "ON ALL TABLES IN SCHEMA hr\n",
    "TO hr_readonly;\n",
    "\n",
    "GRANT sales_readonly TO john;\n",
    "\n",
    "GRANT hr_readonly TO mary;\n",
    "```\n",
    "\n",
    "- Users have all (owner) rights to any object they create\n",
    "- They can grant these rights to others\n",
    "- Check access control for a role:\n",
    "\n",
    "```\n",
    "SELECT\n",
    "    rolname,\n",
    "    rolcanlogin,\n",
    "    rolsuper,\n",
    "    rolcreatedb,\n",
    "    rolcreaterole,\n",
    "    rolinherit,\n",
    "    rolbypassrls -- bypasses row-level security\n",
    "FROM pg_roles\n",
    "WHERE rolname = 'john';\n",
    "```\n",
    "\n",
    "-----\n",
    "\n",
    "- You can assign a role to many users and then assign access rights\n",
    "to a role.\n",
    "\n",
    "```\n",
    "create role dbstudent ;\n",
    "\n",
    "grant role dbstudent to john ;\n",
    "grant role dbstudent to mary ;\n",
    "\n",
    "\n",
    "grant select on bakers to dbstudent ;\n",
    "grant select on bakers to john ;\n",
    "\n",
    "grant insert on episodes to dbstudent ;\n",
    "grant insert, update, delete on bakercnt to dbstudent ;\n",
    "\n",
    "grant select on showstoppers to dbstudent with grant option ;\n",
    "\n",
    "\n",
    "revoke select on bakers from dbstudent ;\n",
    "revoke grant option on showstoppers from dbstudent ;\n",
    "```\n",
    "\n",
    "### Extended Group by\n",
    "\n",
    "```\n",
    "select day, time, avg(price)\n",
    "from events\n",
    "group by rollup(day, time);\n",
    "\n",
    "\n",
    "group by day, time\n",
    "group by day\n",
    "group by <nothing>\n",
    "\n",
    "\n",
    "select day, time, avg(price)\n",
    "from events\n",
    "group by cube(day, time);\n",
    "\n",
    "\n",
    "day time\n",
    "-   time\n",
    "day -\n",
    "-   - \n",
    "```\n",
    "\n",
    "### Procedural SQL\n",
    "\n",
    "- Client side programming example with Python:\n",
    "\n",
    "```\n",
    "\n",
    "import psycopg2 as dbapi2\n",
    "\n",
    "db = dbapi2.connect (database=\"baking\", user=\"sibeladali\", password=\"sibeladali\")\n",
    "\n",
    "cur = db.cursor()\n",
    "\n",
    "cur.execute (\"select baker,fullname, age from bakers;\")\n",
    "\n",
    "rows = cur.fetchall()\n",
    "i =0\n",
    "for row in rows:\n",
    "    print (\"Baker: {}\\t Fullname: {}\\t Age:{}\".format(row[0],row[1],row[2]))\n",
    "    i += 1\n",
    "\n",
    "try:\n",
    "    cur.execute(\"drop table if exists bakercnt;\") \n",
    "    cur.execute(\"create table bakercnt (id int) ;\") \n",
    "except  dbapi2.Error as e:\n",
    "    print(f\"Database error: {e}\")\n",
    "    raise\n",
    "             \n",
    "\n",
    "try:\n",
    "    cur.execute(\"insert into bakercnt values(%s);\",(i,))\n",
    "except  dbapi2.Error as e:\n",
    "    print(f\"Database error: {e}\")\n",
    "    raise\n",
    "    \n",
    "cur.close()\n",
    "db.commit()\n",
    "db.close()\n",
    "\n",
    "```\n",
    "\n",
    "\n",
    "### PL/PGSQL\n",
    "\n",
    "```\n",
    "CREATE OR REPLACE FUNCTION format_name(f1 varchar, a1 int) RETURNS varchar AS $$\n",
    "DECLARE\n",
    "   outvar VARCHAR ;\n",
    "BEGIN\n",
    "   outvar = f1 || ' (' || a1::varchar || ')';\n",
    "   RETURN outvar ;\n",
    "END ;\n",
    "$$ LANGUAGE plpgsql ;\n",
    "\n",
    "select format_name(fullname, age) as formattedname, hometown from bakers ;\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e5f95b0-4fe8-4980-8ffa-9c9c06fbd1e0",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "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.13.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
