Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- modul:m290:learningunits:lu06:theorie:01 [2024/10/17 12:05] – [3. Viewing User Privileges] vdemir
+++ modul:m290:learningunits:lu06:theorie:01 [2024/11/16 09:00] (aktuell) – kdemirci
@@ Zeile 1: / Zeile 1: @@
-====== LU09a - USER and Privilege Management ======
+====== LU09a - USER and PRIVILEGE Management ======
 ===== Learning Objectives =====
@@ Zeile 7: / Zeile 7: @@
   - Revoke privileges from user
   - Deleting obsolete user
-===== Introcuction =====
+===== Introduction =====
 In a warehouse such as Globus, Manor or Lafayette there are different user categories with different authorizations and privileges. According to the required responsibilties the general manager e.g. has full privileges, while the apprentice has much fewer. And as we know, a database is basicly a warehouse, not for goods, but for data.
@@ Zeile 24: / Zeile 23: @@
 To create a new user in MySQL, you use the CREATE USER statement:
-  CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
+  CREATE USER 'username'@'localhost' IDENTIFIED WITH 'caching_sha2_password' BY 'password';
 This creates a user username that can only connect from localhost (the MySQL server machine).
+** Note **
+MySQL supports several types of password authentication mechanisms. These include:
+  - **mysql_native_password**: This is the traditional password hashing method in MySQL, using the SHA1 hashing algorithm. It's one of the oldest and widely used methods for authentication.
+  - **caching_sha2_password**: This is the default authentication plugin starting from MySQL 8.0. It uses SHA-256 for hashing passwords and offers better security than mysql_native_password.
+  - **sha256_password**: This plugin provides SHA-256 based password hashing and can be used in conjunction with SSL for encrypted communication.
+  - **auth_socket**: This plugin allows users to authenticate based on the operating system's user credentials, which can be useful for local access without passwords (e.g., root user in some setups).
+  - **auth_pam**: This plugin enables MySQL to use external authentication mechanisms like PAM (Pluggable Authentication Modules), which allows for integration with OS-level authentication systems or LDAP.
+  - **authentication_ldap_sasl**: This plugin provides authentication using LDAP servers via the SASL (Simple Authentication and Security Layer) protocol.
+  - **authentication_ldap_simple**: A simpler LDAP authentication plugin that doesn't use SASL but supports basic LDAP-based authentication.
 ==== 2. Granting Privileges ==
@@ Zeile 47: / Zeile 58: @@
   REVOKE INSERT ON database_name.* FROM 'username'@'localhost';
-==== Dropping a User ====
+==== 5. Dropping a User ====
 If a user is no longer needed, you can remove the account using the DROP USER statement:
@@ Zeile 54: / Zeile 65: @@
 This removes both the user and their associated privileges.
+==== 6. Activating Change of Privileges After Altering Them ====
+To activate changes to privileges in MySQL after modifying them, you can use the following command:
+  FLUSH PRIVILEGES;
+This command forces MySQL to reload the privilege tables, applying any recent changes made to user privileges. Normally, after using GRANT, REVOKE, or ALTER USER, MySQL applies changes automatically, but if you've made manual adjustments directly in the mysql database tables, running FLUSH PRIVILEGES ensures that the changes take effect immediately.
+<color #ed1c24>Please note that the change will only take effect AFTER a new login. This means that you must open a new console window with your new login data in order to recognize the change in permissions.</color>
+===== Video-Tutorials ====
+^How to Create a New User in MySQL |{{:modul:m290:learningunits:lu06:theorie:how_to_create_a_new_user_in_mysql.mp4 }} |
+^Create User, Grant ReadWrite Privileges and Test Privileges Granted in MySQL |{{:modul:m290:learningunits:lu06:theorie:create_user_grant_readwrite_privileges_and_test_privileges_granted_in_mysql.mp4 }} |
 ==== Vocabulary ====