Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- modul:m290:learningunits:lu06:aufgaben:a01 [2024/10/22 13:05] – vdemir
+++ modul:m290:learningunits:lu06:aufgaben:a01 [2024/10/29 11:55] (aktuell) – vdemir
@@ Zeile 1: / Zeile 1: @@
-====== LU09.A01 - SQL- DCL: CREATE User - Under construction ======
+====== LU09.A01 - SQL- DCL:  Preparatory Work ======
+It is high time to mess a litte around with our new SQL powers, don't you think? So, let's try it directly on our Webstorm.
+As the database administrator, we want to create a new user and only grant this user the necessary rights to operate the web application, which includes DML operations such as INSERT, UPDATE, DELETE, but not DDL operations such as CREATE or DROP of tables. After all, we don't want the webapplication take control over our database, are we?
 ===== Requirements  =====
   * Work type: individual
-  * Timeframe: 15 Minutes
+  * Timeframe: 10 Minutes
   * Means of aid:
       * only teaching materials, no websearch, no use of ai.
       * Webstorm with connection to the MySQL-DB
-  * Expected result: Semantically and syntactically correct SQL statements according to the requirements of the case studies.
+  * Expected result: Prepare working enviroment, according to the requirements below.
-It is high time to mess a litte around with our new SQL powers, don't you think. So let's try it directly on our Webstorm.
 ===== Case studies / Assignments =====
+Before we spend elbow grease, we need to prepare our working enviroment. Meaning we need to prepare our computer for the assignments later.
+==== Task 1.1 ====
+**Login as root**: Login in from Webstorm to your database as //root// (sysdba = systemadministrator for the datababase) with your a //root-password//.
-==== Task A ====
+==== Task 1.2 ====
-**Create the User**: Create a user named //restrictedUser// with the password //SafePassword123// using the //mysql_native_password// plugin.
+**New Database**: Create a new database named //myDatabase//.
-==== Task B ====
+  CREATE DATABASE myDatabase;
-**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.
-==== Task C ====
+==== Task 1.3 ====
-**Rovoke Privileges**: To be certain that nothing unintended can happen revoke the CREATE, ALTER, and DROP privileges explicitly.
+**use db**: Use that newly created db.
-==== Tasc D ====
+  USE myDatabase;
-**View the User’s Privileges**: Check the privileges to ensure that the user cannot manage tables.
-==== Task E ====
+==== Task 1.4: ====
-**Test the User’s Access**: Connect as //restrictedUser// and try to perform a CREATE TABLE or DROP TABLE operation. The attempt should result in a permission error.
+** Create a test table**: Create a table //users// with 3 columns of your choice as a test table.
-==== Task F ====
+  CREATE TABLE users (
-**Delete the User (optional)**: After testing, you can delete the user if they are no longer needed.
+    user_id INT AUTO_INCREMENT PRIMARY KEY,
+    username VARCHAR(50) NOT NULL,
+    email VARCHAR(100) NOT NULL
+  );
+==== Task 1.5: ====
+**Content data for testing:** Fill the table user with some test data:
+  INSERT INTO users (username, email)
+  VALUES  ('john_doe', 'john.doe@example.com'),
+        ('jane_smith', 'jane.smith@example.com'),
+        ('michael_brown', 'michael.brown@example.com'),
+        ('sarah_johnson', 'sarah.johnson@example.com'),
+        ('chris_williams', 'chris.williams@example.com'),
+        ('anna_lee', 'anna.lee@example.com'),
+        ('david_kim', 'david.kim@example.com'),
+        ('laura_clark', 'laura.clark@example.com'),
+        ('jake_lewis', 'jake.lewis@example.com'),
+        ('emily_martinez', 'emily.martinez@example.com');
 ===== Solution =====