modul:m290:learningunits:lu06:aufgaben:a02

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
Nächste Überarbeitung		 Vorhergehende Überarbeitung
modul:m290:learningunits:lu06:aufgaben:a02 [2024/10/28 14:35] – vdemirmodul:m290:learningunits:lu06:aufgaben:a02 [2024/11/20 14:36] (aktuell) – cbolzern
Zeile 1: Zeile 1:
-====== LU09.A02 - SQL- DCL: CREATE User ======+====== LU09.A02 - SQL- DCL: CREATE USER ======
 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.  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. 
  
Zeile 6: Zeile 6:
 ===== Requirements  ===== ===== Requirements  =====
   * Work type: individual   * Work type: individual
-  * Timeframe: 30 Minutes+  * Timeframe: 20 Minutes
   * Means of aid:    * Means of aid: 
       * only teaching materials, no websearch, no use of ai.       * only teaching materials, no websearch, no use of ai.
Zeile 25: Zeile 25:
   - Drop the newly created user finally   - Drop the newly created user finally
  
 +==== Task 2.1 ====
 +**Create the User**: Create a user named //restrictedUser// with the password //SafePassword123// using the //caching_sha2_password// plugin.
  
-==== Task A1: Prepatory Work ==== +  CREATE USER 'restrictedUser'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'SafePassword123';
-**Login as root**: Login in from Webstorm to your database as //root// (sysdba = systemadministrator for the datababase) with your a //root-password//.+
  
-==== Task A2: Preparatory Work ==== +==== Task 2.2 ====
-**New Database**: Create a new database named //myDatabase//.  +
- +
-  CREATE DATABASE myDatabase; +
- +
-==== Task A3: Preparatory Work ==== +
-**use db**: Use that newly created db. +
- +
-  USE myDatabase; +
- +
-==== Task A4: Preparatory Work ==== +
-** Create a test table**: Create a table //user// with 3 columns of your choice as a test table, and fill it with some test data.  +
- +
-  CREATE TABLE user ( +
-    user_id INT AUTO_INCREMENT PRIMARY KEY, +
-    username VARCHAR(50) NOT NULL, +
-    email VARCHAR(100) NOT NULL +
-  ); +
-   +
-  INSERT INTO user (username, email) VALUES ('john_doe', 'john.doe@example.com'); +
-  INSERT INTO user (username, email) VALUES ('jane_smith', 'jane.smith@example.com'); +
-  INSERT INTO user (username, email) VALUES ('michael_brown', 'michael.brown@example.com'); +
-  INSERT INTO user (username, email) VALUES ('sarah_johnson', 'sarah.johnson@example.com'); +
-  INSERT INTO user (username, email) VALUES ('chris_williams', 'chris.williams@example.com'); +
-  INSERT INTO user (username, email) VALUES ('anna_lee', 'anna.lee@example.com'); +
-  INSERT INTO user (username, email) VALUES ('david_kim', 'david.kim@example.com'); +
-  INSERT INTO user (username, email) VALUES ('laura_clark', 'laura.clark@example.com'); +
-  INSERT INTO user (username, email) VALUES ('jake_lewis', 'jake.lewis@example.com'); +
-  INSERT INTO user (username, email) VALUES ('emily_martinez', 'emily.martinez@example.com'); +
- +
-==== Task B1 ==== +
-**Create the User**: Create a user named //restrictedUser// with the password //SafePassword123// using the //mysql_native_password// plugin. +
- +
-  CREATE USER 'restrictedUser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'SafePassword123'; +
-   +
- +
-==== Task B2 ====+
 ** Overview of the current privileges**: Display all users. ** Overview of the current privileges**: Display all users.
  
Zeile 72: Zeile 37:
 The result set should look like: The result set should look like:
  
-{{:modul:m290:learningunits:lu06:aufgaben:showusers.png?600|}} +==== Task 2.3 ====
- +
-==== Task B2 ====+
 **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. **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.
  
-  GRANT SELECT, INSERT, UPDATE, DELETE ON myAppDB.* TO 'restrictedUser'@'localhost';+  GRANT SELECT, INSERT, UPDATE, DELETE ON myDatabase.* TO 'restrictedUser'@'localhost';
  
-==== Task B3 ==== +==== Task 2.4 ==== 
-**Rovoke Privileges**: To be certain that nothing unintended can happen revoke the CREATE, ALTER, and DROP privileges explicitly.+**Revoke Privileges**: To be certain that nothing unintended can happen revoke the CREATE, ALTER, and DROP privileges explicitly.
  
-  REVOKE CREATE, ALTER, DROP ON myAppDB.* FROM 'restrictedUser'@'localhost';+  REVOKE CREATE, ALTER, DROP ON myDatabase.* FROM 'restrictedUser'@'localhost';
  
-==== Tasc B4 ====+==== Tasc 2.5 ====
 **View the User’s Privileges**: Check the privileges to ensure that the user cannot manage tables. **View the User’s Privileges**: Check the privileges to ensure that the user cannot manage tables.
  
   SHOW GRANTS FOR 'restrictedUser'@'localhost';   SHOW GRANTS FOR 'restrictedUser'@'localhost';
 +  
 +==== Task 2.6 ====
 +**Test the User’s Access**: Establish a new console connection to the database by using //restrictedUser + password//.
 +
 +==== Task 2.7 ====
  
-==== Task B5 ==== +Finally, try to perform a CREATE TABLE or DROP TABLE operation. The attempt should result in a permission error as displayed in the image below.
-**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.+
  
-==== Task B6 ==== +==== Task 2.8 ==== 
-**Delete the User (optional)**: After testing, you can delete the user if they are no longer needed.+**Delete the User (optional)**: After testing, you can delete the user if it is not needed anymore.
  
 ===== Solution ===== ===== Solution =====
-[[modul:m290:learningunits:lu06:loesungen:l01|Lösung]]+[[modul:m290:learningunits:lu06:loesungen:l02|Lösung]]
  
  
  • modul/m290/learningunits/lu06/aufgaben/a02.1730122519.txt.gz
  • Zuletzt geändert: 2024/10/28 14:35
  • von vdemir