modul:m290:learningunits:lu06:aufgaben:a01

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:a01 [2024/10/28 14:34] – vdemirmodul:m290:learningunits:lu06:aufgaben:a01 [2024/10/29 11:55] (aktuell) – vdemir
Zeile 1: Zeile 1:
-====== LU09.A01 - SQL- DCL: CREATE User ======+====== 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.  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: 10 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.
       * Webstorm with connection to the MySQL-DB       * 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.
  
 ===== Case studies / Assignments ===== ===== Case studies / Assignments =====
-As a database administrators we are assigned to create a AppUser, which has for security reasons only the right for DML operations, but must not be allowed for DDL operation. We don't want a hacker to delete our entire webshop, do we? +Before we spend elbow grease, we need to prepare our working enviroment. Meaning we need to prepare our computer for the assignments later.
  
-To get the job done, follow the instructions below: +==== Task 1.1 ====
- +
-  - Create as the sysdba (systemadministrator of the databaswe) a new user +
-  - Grant this role only the necessary rights +
-  - Create as a sysdba a test table and fill it with some testdata +
-  - Establish a new connection within the webstorm by using the credentials of this new user +
-  - Try out the DML operations, wich should be possible (insert, update, delete) +
-  - Try out to DDL operations, which should result in errors due to missing permissions for that particular user +
-  - Drop the newly created user finally +
- +
- +
-==== Task A1: Prepatory Work ====+
 **Login as root**: Login in from Webstorm to your database as //root// (sysdba = systemadministrator for the datababase) with your a //root-password//. **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 1.2 ====
 **New Database**: Create a new database named //myDatabase//.  **New Database**: Create a new database named //myDatabase//. 
  
   CREATE DATABASE myDatabase;   CREATE DATABASE myDatabase;
  
-==== Task A3: Preparatory Work ====+==== Task 1.3 ====
 **use db**: Use that newly created db. **use db**: Use that newly created db.
  
   USE myDatabase;   USE myDatabase;
  
-==== Task A4: Preparatory Work ==== +==== Task 1.4: ==== 
-** 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 a test table**: Create a table //users// with 3 columns of your choice as a test table.
  
-  CREATE TABLE user (+  CREATE TABLE users (
     user_id INT AUTO_INCREMENT PRIMARY KEY,     user_id INT AUTO_INCREMENT PRIMARY KEY,
     username VARCHAR(50) NOT NULL,     username VARCHAR(50) NOT NULL,
Zeile 48: Zeile 37:
   );   );
      
-  INSERT INTO user (username, email) VALUES ('john_doe', 'john.doe@example.com'); +==== Task 1.5: ==== 
-  INSERT INTO user (username, email) VALUES ('jane_smith', 'jane.smith@example.com'); +**Content data for testing:** Fill the table user with some test data: 
-  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';+
      
- +  INSERT INTO users (username, email)  
-==== Task B2 ==== +  VALUES  ('john_doe', 'john.doe@example.com'), 
-** Overview of the current privileges**: Display all users. +        ('jane_smith', 'jane.smith@example.com'), 
- +        ('michael_brown', 'michael.brown@example.com'), 
-  SELECT user, host, plugin FROM mysql.user; +        ('sarah_johnson', 'sarah.johnson@example.com'), 
- +        ('chris_williams', 'chris.williams@example.com'), 
-The result set should look like: +        ('anna_lee', 'anna.lee@example.com'), 
- +        ('david_kim', 'david.kim@example.com'),  
-{{:modul:m290:learningunits:lu06:aufgaben:showusers.png?600|}} +        ('laura_clark', 'laura.clark@example.com'),  
- +        ('jake_lewis', 'jake.lewis@example.com'), 
-==== Task B2 ==== +        ('emily_martinez', 'emily.martinez@example.com');
-**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'; +
- +
-==== Task B3 ==== +
-**Rovoke 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'; +
- +
-==== Tasc B4 ==== +
-**View the User’s Privileges**: Check the privileges to ensure that the user cannot manage tables. +
- +
-  SHOW GRANTS FOR 'restrictedUser'@'localhost'; +
- +
-==== Task B5 ==== +
-**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 ==== +
-**Delete the User (optional)**: After testing, you can delete the user if they are no longer needed.+
  
 ===== Solution ===== ===== Solution =====
  • modul/m290/learningunits/lu06/aufgaben/a01.1730122494.txt.gz
  • Zuletzt geändert: 2024/10/28 14:34
  • von vdemir