Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- modul:m290:learningunits:lu06:loesungen:l02 [2024/09/27 13:44] – vdemir
+++ modul:m290:learningunits:lu06:loesungen:l02 [2024/11/13 16:43] (aktuell) – cbolzern
@@ Zeile 1: / Zeile 1: @@
-====== LU06.S02 - SQL-DDL: Table Management ======
+====== LU09.S02 - SQL- DCL: CREATE USER ======
-===== Case studies / Assignments =====
+===== Task 2.1 =====
+**Create the User**: Create a user named //restrictedUser// with the password //SafePassword123// using the //mysql_native_password// plugin.
-==== Task A: CREATE TABLE ====
+  CREATE USER 'restrictedUser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'SafePassword123';
-Create a table called employees with the following columns:
-  * employee_id (INT) as the primary key
+===== Task 2.2 =====
-  * first_name (VARCHAR 50)
+** Overview of the current privileges**: Display all users.
-  * last_name (VARCHAR 50)
-  * hire_date (DATE)
-  * salary (DECIMAL)
-  CREATE TABLE employees (
+  SELECT user, host, plugin FROM mysql.user;
-    employee_id INT PRIMARY KEY,
-    first_name VARCHAR(50),
-    last_name VARCHAR(50),
-    hire_date DATE,
-    salary DECIMAL(10,2)
-  );
+The result set should look like:
-==== Task B: CREATE TABLE ====
+{{:modul:m290:learningunits:lu06:aufgaben:a2.png?800|}}
-Create a table called products to store inventory information. The table should have:
-  * product_id (INT) as an auto-incrementing primary key
+===== Task 2.3 =====
-  * product_name (VARCHAR 100)
+**Grant Privileges Without Table Management**: Grant the user //restrictedUser// the ability to read and write data but not to create, alter, or drop tables. Use the following commands to give only the required privileges.
-  * category (VARCHAR 50)
-  * price (DECIMAL)
-  * stock_quantity (INT)
-  CREATE TABLE products (
+  GRANT SELECT, INSERT, UPDATE, DELETE ON myDatabase.* TO 'restrictedUser'@'localhost';
-    product_id INT AUTO_INCREMENT PRIMARY KEY,
-    product_name VARCHAR(100),
-    category VARCHAR(50),
-    price DECIMAL(10,2),
-    stock_quantity INT
-  );
+===== Task 2.4 =====
+**Revoke Privileges**: To be certain that nothing unintended can happen revoke the CREATE, ALTER, and DROP privileges explicitly.
-==== Task C: ALTER TABLE ====
+  REVOKE CREATE, ALTER, DROP ON myDatabase.* FROM 'restrictedUser'@'localhost';
-Add a new column email (VARCHAR 100) to the employees table.
-  ALTER TABLE employees
+===== Tasc 2.5 =====
-  ADD COLUMN email VARCHAR(100);
+**View the User’s Privileges**: Check the privileges to ensure that the user cannot manage tables.
-==== TASK D: MODIFY COLUMN ====
+  SHOW GRANTS FOR 'restrictedUser'@'localhost';
-Change the salary column's data type to FLOAT in the employees table.
+{{:modul:m290:learningunits:lu06:aufgaben:a3.png?800|}}
-  ALTER TABLE employees
+===== Task 2.6 =====
-  MODIFY COLUMN salary FLOAT;
+**Test the User’s Access**: Establish a new console connection to the database by using //restrictedUser + password//.
-==== TASK E: DROP COLUMN ====
+{{:modul:m290:learningunits:lu06:aufgaben:a4.png?600|}}
-Remove the stock_quantity column from the products table.
-  ALTER TABLE products
+===== Task 2.7 ====
-  DROP COLUMN stock_quantity;
+Finally, try to perform a CREATE TABLE or DROP TABLE operation. The attempt should result in a permission error als displayed in the image below.
+{{:modul:m290:learningunits:lu06:aufgaben:a5.png?800|}}
+===== Task 2.8 =====
+**Delete the User (optional)**: After testing, you can delete the user if they are no longer needed.
+{{:modul:m290:learningunits:lu06:aufgaben:a6.png?800|}}
-==== TASK F: DROP TABLE ====
-Completely remove the products table from the database.
-  DROP TABLE products;
 ===== Vocabulary =====
 ^ English ^ German ^
-| ... | ... |
+| .. | .. |
-| ... | ... |
 ----
 [[https://creativecommons.org/licenses/by-nc-sa/4.0/|{{https://i.creativecommons.org/l/by-nc-sa/4.0/88x31.png}}]] Volkan Demir