CREATE USER jamesb IDENTIFIED BY 'mi6';

DROP USER jamesb;

ALTER USER jamesb IDENTIFIED BY 'cia';

 SELECT USER FROM mysql.user;

+-----------+
| user      |
+-----------+
| csit115   |
| mysql.sys |
| root      |
+-----------+

	      
CREATE DATABASE university;

	      
DROP DATABASE university;

use university;
SELECT * FROM COURSE;

SELECT * FROM university.COURSE;

 
SHOW DATABASES, SHUTDOWN, PROCESS, CREATE USER, …

 
show privileges;

 
GRANT privilege-type ON privilege-level TO  user[WITH GRANT OPTION];

 
REVOKE privilege-type ON privilege-level FROM user;

REVOKE ALL PRIVILEGES FROM user;

REVOKE GRANT OPTION FROM user;

ALL,ALTER,ALTER ROUTINE,CREATE,CREATE ROUTINE,CREATE TABLESPACE,
CREATE TEMPORARY TABLES,CREATE USER,CREATE VIEW,DELETE,DROP,EVENT,EXECUTE,
FILE,GRANT OPTION,INDEX,INSERT,LOCK TABLES,PROCESS,PROXY,REFERENCES,RELOAD,
REPLICATION CLIENT,REPLICATION SLAVE,SELECT,SHOW DATABASES,SHOW VIEW,
SHUTDOWN,SUPER,TRIGGER,UPDATE,USAGE

 
GRANT SELECT ON *.* TO James;

 
GRANT ALL ON *.* TO Harry;

 
GRANT USAGE ON *.* TO Robin;

 
GRANT CREATE USER ON *.* TO James;

 
GRANT SHOW DATABASES ON *.* TO James;

CREATE TABLESPACE,CREATE USER,FILE,PROCESS,RELOAD,
REPLICATION CLIENT,REPLICATION SLAVE,SHOW DATABASES,SHUTDOWN,SUPER

 
GRANT SELECT ON csit115.* TO James;

 
GRANT ALL ON university.* TO Harry;

 
GRANT INSERT, UPDATE, DELETE on csit115.* TO Robin;

 
GRANT GRANT OPTION on csit115.* TO Robin;

 
CREATE,DROP,EVENT,GRANT OPTION,LOCK TABLES,REFERENCES

 
GRANT SELECT ON csit115.DRIVER TO James;

 
GRANT ALL ON university.COURSE TO Harry;

 
GRANT INSERT, UPDATE, DELETE on csit115.ORDERS TO Robin;

 
GRANT GRANT OPTION on csit115.DRIVER TO Robin;

 
ALTER,CREATE VIEW,CREATE,DELETE,DROP,GRANT OPTION,INDEX,INSERT,
REFERENCES,SELECT,SHOW VIEW,TRIGGER,UPDATE

 
GRANT SELECT (LNUM) ON csit115.DRIVER TO James;

 

GRANT INSERT (sname, level) ON university.SKILL TO Harry;

 

GRANT UPDATE(ordernum), REFERENCES (ordernum) on csit115.ORDERS TO Robin;

INSERT,REFERENCES,SELECT,UPDATE

mysql -u root

ALTER USER root IDENTIFIED BY 'root';

CREATE USER jamesb IDENTIFIED BY 'jamesb';

SELECT user, select_priv, insert_priv, update_priv, delete_priv 
FROM mysql.user 
WHERE user='jamesb';

+--------+-------------+-------------+-------------+-------------+
| user   | select_priv | insert_priv | update_priv | delete_priv |
+--------+-------------+-------------+-------------+-------------+
| jamesb | N           | N           | N           | N           |
+--------+-------------+-------------+-------------+-------------+

GRANT ALL ON *.* to jamesb;

SELECT select_priv, insert_priv, update_priv, delete_priv, grant_priv 
FROM mysql.user 
WHERE user='jamesb';

+--------+-------------+-------------+-------------+-------------+------------+
| user   | select_priv | insert_priv | update_priv | delete_priv | grant_priv |
+--------+-------------+-------------+-------------+-------------+------------+
| jamesb | Y           | Y           | Y           | Y           | N          |
+--------+-------------+-------------+-------------+-------------+------------+

CREATE DATABASE mi6;

mysql -u jamesb -p -v -c

use mi6;

SELECT * FROM mi6.DEPARTMENT;

DELETE FROM mi6.COURSE;

CREATE USER harryp IDENTIFIED BY 'harryp';

GRANT SELECT ON mi6.* TO harryp;

SELECT user, db, select_priv, insert_priv, delete_priv, update_priv 
FROM mysql.db
WHERE user='harryp';

+--------+-----+-------------+-------------+-------------+-------------+
| user   | db  | select_priv | insert_priv | update_priv | delete_priv |
+--------+-----+-------------+-------------+-------------+-------------+
| harryp | mi6 | Y           | N           | N           | N           |
+--------+-----+-------------+-------------+-------------+-------------+

GRANT INSERT, UPDATE, DELETE ON csit115.* TO harryp;

SELECT user, db, select_priv, insert_priv, delete_priv, update_priv 
FROM mysql.db 
WHERE user='harryp';

+--------+---------+-------------+-------------+-------------+-------------+
| user   | db      | select_priv | insert_priv | update_priv | delete_priv |
+--------+---------+-------------+-------------+-------------+-------------+
| harryp | mi6     | Y           | N           | N           | N           |
| harryp | csit115 | N           | Y           | Y           | Y           |
+--------+---------+-------------+-------------+-------------+-------------+

GRANT UPDATE, DELETE ON csit115.DEPARTMENT TO jamesb;

SELECT user, db, table_name, table_priv 
FROM mysql.tables_priv 
WHERE user='jamesb';

+--------+---------+------------+---------------+
| user   | db      | table_name | table_priv    |
+--------+---------+------------+---------------+
| jamesb | csit115 | DEPARTMENT | Update,Delete |
+--------+---------+------------+---------------+

GRANT REFERENCES (DNAME) ON csit115.DEPARTMENT TO jamesb;

SELECT user, db, table_name, column_name, column_priv 
FROM mysql.columns_priv 
WHERE user='jamesb';

+--------+---------+------------+-------------+-------------+
| user   | db      | table_name | column_name | column_priv |
+--------+---------+------------+-------------+-------------+
| jamesb | csit115 | DEPARTMENT | DNAME       | References  |
+--------+---------+------------+-------------+-------------+

CREATE VIEW ITDEPT(DNAME, BUDGET, CHAIRMAN) 
AS (SELECT *
    FROM DEPARTMENT
    WHERE DNAME='IT');

GRANT INSERT ON csit115.ITDEPT TO jamesb;

SELECT user, db, table_name, table_priv 
FROM mysql.tables_priv 
WHERE user='jamesb';

+--------+---------+------------+---------------+
| user   | db      | table_name | table_priv    |
+--------+---------+------------+---------------+
| jamesb | csit115 | DEPARTMENT | Update,Delete |
| jamesb | csit115 | ITDEPT     | Insert        |
+--------+---------+------------+---------------+