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/22 14:45] – 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. 
  
-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.+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  ===== ===== 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.1729601106.txt.gz
  • Zuletzt geändert: 2024/10/22 14:45
  • von vdemir