Scribd
Upload
908 views448 pages

MySQL For Database Administrators (Student Guide - Volume II)

This document is a comprehensive guide for students on MySQL, covering installation, architecture, configuration, monitoring, user management, and security. It includes objectives, topics, quizzes, and practices for each section to facilitate learning. The content is proprietary and protected under copyright law, with restrictions on reproduction and distribution.

Uploaded by

Aruna Telshan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
908 views448 pages

MySQL For Database Administrators (Student Guide - Volume II)

This document is a comprehensive guide for students on MySQL, covering installation, architecture, configuration, monitoring, user management, and security. It includes objectives, topics, quizzes, and practices for each section to facilitate learning. The content is proprietary and protected under copyright law, with restrictions on reproduction and distribution.

Uploaded by

Aruna Telshan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
University Student Guide - Volume II D61762GC51 | D108206 Unauthorized reproduction. Learn more from Oracle University at [Link] Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affiliates Author KimSeong Loh Technical Contributors Guilherme Saraiva Jesper Wisborg Krogh Jonathon Coombes Lig Isler-Turmelle Mirko Ortensi Ayan Kuan Editors Moushmi Mukherjee Aju Kumar Graphic Designer Kavya Bellur Publishers Sujatha Nagendra Pavithran Acka Syed Ali 5102272020 ‘Copyright © 2020, Oracle andior its afflates. All rights reserved. Disclaimer “This document contains proptotaty information and s prolacted by copyright and other intalloctual propery law. You may copy and print this decument solely for your own uso in ‘an Oradi taining course. The dacumant may nat be mode or allord in any way. Exoapl where your use eanstiuies "alr uso" under copyright la, you may no! use, share, STATUS rent user? root localhost Sst Cipher in use is DHE-RSA-AES125-GcH-SHAZS6 Connection: — loeathest via TeP/Ie + For aclient on the same host as the server, include the --protocol=TCP or --host=[Link] option. For example: # mysql -u root -p --protocol=7P_ + Connections through UNIX socket or named pipes do not use SSL. you are connecting a client to a server on the same host, MySQL uses a socket on UNIX or a named pipe on Windows. You do not need TCP/IP or a secure connection to connect a client and server on the same host. To force a client to connect over TCP/IP to test SSL, you can include ~-protocol=ICP of --host=[Link] inthe mysql. command-line client The DBA can also use the Performance Schema to verify that connections use SSL, for example: mysql> SELECT * FROM sys.session_ssl_status\¢ THOSE U DISHES ESHER 1, row EMISSION INDE IO thread_id: 50 ssl_version: TLSv1.2 ssl_cipher: DAE~ ssl_sessions_reused: 0 AES 128-GCN-SHA256 OER ERR ERA EA AN REREEEAIER 2. roy FRR EERE RAIA I RRM RR EIRENE thread_id: 51 ssl_version: ssl_cipher: s1_sessions_reused: 0 2 row in set (#.4# sec) MySQL for Database Administrators 7-15 Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affiliates Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affiliates Disabling SSL on MySQL Server Start MySQL server with either the --ss1=0 or --skip-ss! option. 2. Log in to the server over TCP/IP and check whether SSL is enabled and if the connection is secure. sysnl> SNOW VARIABLES LR “have_s8i V Variable sane | vaine | Trew inset (tv sec) oe ‘When SSL is enabled on the server, it supports secure connections but does not require the client to use them. You normally do not need to disable SSL on the server: it wil stil accept a connection from a client that is not using a secure connection ‘You might need to disable SSL on the server for other reasons, such as testing. You can also use either the --ss1=0 or --skip-ssll option. These options are not dynamic, s0 they can be issued only at startup. They can be included in a configuration file, ‘When the server is started with one of these options, the global server variable have_se1 is sel to DISABLED. The connection status shows SSL as ‘Not in use.” If you restart the server with no =-se1 option, the default is equivalent to including --ss1=1 to enable SSL. MySQL for Database Administrators 7-16 Setting Client Options for Secure Connections Use the --ss1-mode option, which accepts the following values: + PREFERRED: Establishes a secure connection if possible or falls back to an unsecure connection. This is the default if --ss1-mode is not specified. * DISABLED: Establishes an insecure connection + REQUIRED: Establishes a secure connection if possible or fails if unable to establish a secure connection + VERTFY_CA: As for REQUIRED, but also ve certificate with the Certificate Authority + VERIFY_IDENTITY: As for VERIFY_CA, but also verifies that the server digital certificate matches the MySQL server host s the server digital Host Name Matching with VERIFY_IDENTITY If the client uses OpenSSL 1.0.2 or higher, VERTFY_IDENTITY checks whether the host name it connects to matches either the Subject Alternative Name value or the Common Name value in the server certificate. Otherwise, the client checks whether the host name matches the Common Name. value in the server certificate. MySQL for Database Administrators 7-17 a 2 Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affi ” 2 ed. Copyright © 2020 Oracle and/or its affi Unauthorized reproduction or distribution prohil Client - ssl-mode Option: Example With SSL enabled on the server, connect with SSL disabled for the client. Check the server and connection statu: yeql> SuoW VARIABLES LIKE ‘have_esl'; | Vorsapie_neae | value | Connection: localhost via reR/ze In this example, the server supports SSL, but the connection from the client does not use SSL. If, conversely, SSL is disabled on the server, and the client tries to connect with --ss1- mode=REQUTRED, the connection is rejected, MySQL for Database Administrators 7-18 ites Setting the Permitted Versions for SSL/TLS for the Server + Use the global t1s_version server system variable. — The default value of 1s_versicn is the list of all protocols supported by the SSL library that is used to compile MySQL. + Provide a comma-separated list of accepted versions. — Example, in a config file such as /etc/[Link] Inysqia] L tis_versionsTLSv1.1,7Lsvi.2 = Reject connections via the less secure TLSv1 protocol, MySQL for Database Administrators 7-19 € © £2 Ss 3 c G a Go £ ° 2° q So a © = = S a ° Oo 3G 2 2 = 2 a S 2 Ss 7 2 3 5 < oO 6 5 3 2 a 2 3 2 N 5 & 5 a = 3 ites o 2 2 & ) 2020 Ora ited. Copyright ¢ ion prohi tion or distribu Unauthorized reproduc Setting the Permitted Versions for SSL/TLS for the Client + Use the --t1s-version client system variable. + Provide a comma-separated list of accepted versions. Example: ——————=——————E——— + Check the ssl status variable for the version of TLS being used for the connection between client and server: mysql> SHOW SESSION STATUS LIKE ‘Ssi_version’; | Varlable_name | Value 1 | Ssl_version | ™svi.1 | 1 row in set (t.t# sec) — The client and server establish the connection using the latest version of TLS that both support. MySQL for Database Administrators 7 - 20 ites Setting the Cipher to Use for Secure Connections. A cipher specifies an encryption algorithm to use, including the length of the encryption key. + More robust ciphers with longer keys are more secure. + Ciphers have names such as DHE-RSA-AFS256-SA or AES128-SHA + By default, the connection uses the most robust cipher supported by both the client and server, + Clients and servers can use the --ss1-cipher option to specify a list of permissible ciphers, separated by colons. — Example: cipher=DHE-RSA-AES256-SHA:AES126-SHA SSS MySQL for Database Administrators 7-21 € © £2 Ss 3 c G a Go £ ° 2° q So a © = = S a ° Oo 3G 2 2 = 2 a S 2 Ss 7 2 3 5 < oO 6 5 3 2 a 2 3 2 N 5 & 5 a = 3 ” 2 ts aff or i © 2020 Oracle and ed. Copyright Unauthorized reproduction or distribution prohil Global System Variable and Session Status Variables for Ciphers The --ss1-cipher option sets a global system variable and two session status variables. + ssl_cipher: Global server system variable with the list of permissible ciphers separated by colons. If the --s1-ci pher option is not set for the server, this is blank. ‘Any supported cipher can be used and the most robust one available is selected. + Ssl_cipher_1ist: Session status variable with the list of permissible ciphers separated by colons. If the --ss1-ci pher option is not set for the server, this variable lists all available ciphers. + $s1_cipher: Session status variable that displays the cipher that is being used for the current session. For sessions that are not using a secure connection, this variable is blank. I the --ss1-cipher option is set by the client, it affects only the cipher being used for that connection, that is, the Ss1_cipher session status variable, not the ss1_cipher global server system variable or the Ss1_cipher_list session status variable. MySQL for Database Administrators 7 - 22 Cipher System and Status Variables: Example 1 The server is started with the following option: ~-ss1-cipher=DHE-RSA-AES256-SHA | varlable_nane | valve 1 || DRE-RSA-AES256~SHA-AESI2E-SHA | 1 row in set 1f.#% sec) ‘nyogl> SHOW SESSION STATUS like ‘Ssi_ciphert’ fl | Ss_cipher || DHE-RSA-ARS256~SHA 1 | Ssl_cipher_list | DAE-ASR-AES256-SHA:AESI28-SHA | In this example, the values of the ss1_cipher server system variable and the Ss1_cipher_list session status variable are the same. The current connection is using the DHE-RSA-AES255—SHA cipher (the value of the Ss1_cipher status variable). MySQL for Database Administrators 7 - 23 Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affiliates ” 2 ed. Copyright © 2020 Oracle and/or its affi Unauthorized reproduction or distribution prohil Cipher System and Status Variables: Example 2 ‘The server is started without specifying --ss1-cipher. oysel> SHOW GLOBAL VARIABLES LIKE ‘sel_eipher'; row in set (#44 sec ‘SHOW SESSTON STATUS 1ik 1 value 1 pher | ow pher_liet | Ec In this example, the values of the ss1_cipher server system variable and the Ss1_cipher_list session status variable are the same. The current connection is using the DHE-RSA-AES255—SHA cipher (the value of the Ss1_cipher status variable). MySQL for Database Administrators 7-24 Setting Client SSL/TLS Options by User Account Use the REQUTRE clause with 'R OF ALTER USER statement with one of the following options: + NONE: (Default) Account has no SSL or X509 requirements and can use secure or non-secure connections. + $8: Account must use a secure connection. + 509: Account must connect with a secure connection from a client that has a digital certificate for the client. + ISSUER ‘issuer’: Account must use a secure connection from a client with a certificate issued by the specified CA + SUBJECT ‘subject’: Account must use a secure connection from a client that has a digital certificate with the specified Subject field identifying the owner of the certificate. + CIPHER ‘cipher’: Account must use a secure connection with the specified cipher. Examples: ALTER USER a@localhost REQUIRE SSL; ALTER USER D@localhost REQUIRE x509; ALTER USER c@localhost REQUIRE CIPHER 'DHE-RSA-AES2S6-SHA'; MySQL for Database Administrators 7-25 Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affiliates ” 2 ed. Copyright © 2020 Oracle and/or its af Unauthorized reproduction or distribution prohil Generating a Digital Certificate + When the MySQL server starts, or when nysal_ checks for the following digital certificate files: — [Link]: Digital cerificate for the CA that issued the server's digital corificate — server-cert..pem: Digital certificate for the server verifying the server's identity and including the public key — server-keypem: Private key for the server s1_rsa_setup executes, it + If those files are not present, it generates those files for a self-signed digital certificate and also creates the following files: — ca-key..pem: Private key for the CA — [Link]: A client certificate to share with clients — [Link]: A client private key to share with clients Ii the server is acting as its own GA and generating a self-signed digital certificate, it needs a private key to go along with the public key in the digital certificate for the CA. The digital certificate for the CA is used if the client --ss1-mode is set to verify the CA. The digital certificate and private key for the client are used to support the x503, SUBJECT, and SSUER options for the REQUIRE clause of the CREATE USER and ALTER USER statements. The server distributes the client certificate file and private key file securely to client computers that require client digital certificates. If the server generates self-signed digital certificates, the issuer for allof them is WySQL_Server_version Auto _Generated_CA_Certificate, where the version value is the version of the MySQL server. The Subject for the client digital certificate is MySQL _Server_version Auto Generated Client_Certificate. MySQL for Database Administrators 7 - 26 SSL Server Variables for Digital Certificates + ssi_ca: The file that contains the list of trusted CAs, The default value is [Link]. Change the file name by using the --ss1~ca server startup option. * ssl_cert: The file that contains the server's digital certificate. The default value is server-cert. pom. Change the file name by using the --ss1-cert server startup option. + ssi_key: File for the server's private key. The default value is [Link]. Change the file name by using the --ss1-key server startup option. The following is an example displaying the current name of the file in the data directory that contains the list of trusted Certificate Authorities: mysql> SHOW LOBAL VARTABLES + variable_name | value | ssl_ca | [Link] | row in set (0.00 sec) MySQL for Database Administrators 7-27 ites = o 2 s 3 e o LS 3 £ ° ° N So Ni © = 2 5 a ° o os 2 2 = 2 a < S Ss 2 2 3 ° < So o S oD 2 a 2 3 o N 5 & 5 o & > ” 2 ed. Copyright © 2020 Oracle and/or its af Unauthorized reproduction or distribution prohil SSL Client Options for Digital Certificates Ifyou REQUTRE a user account to use X509, TSSUER, of SUBJECT, the client must use both of the following options when initiating the connection: + --se1-cert: File name of the digital certificate issued to the client; provides the identity of the client and public key + --ss1-key: File name of the private key for the client to use with its public key Example: ~Key=client-key pen J Pmyeql -u zoot -p --ssl-cert=[Link] -~ Optionally, the client can provide details of the CA: + --ss1-ca: File name containing the name of the CA that issued the server digital certificate MySQL for Database Administrators 7 - 28 Securing a Remote Connection to MySQL + MySQL supports secure shell (SSH) connection to a remote MySQL server. This requires: — An SSH client on the client machine — Port forwarding through an SSH tunnel from the client to the server — Example: Forward requests trom port $3306 on the local host to 3306 on the remote host 4 ssh -4 -L 33306: remotehostIP:3306 sshuser@remotehost | — AMySAL client application on the machine with the SSH client — Example: Run the nyse client on the local machine through the SSH tunnel i myegi -u user -p -P33306 -hi27.0.01 SSS + When you complete the setup, you have a local port that hosts an SSH connection to MySQL, encrypted using SSH. MySQL for Database Administrators 7 - 29 a 2 Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affi ” 2 ed. Copyright © 2020 Oracle and/or its affi Unauthorized reproduction or distribution prohil Quiz Which of the following options starts the MySQL server with SSLITLS disabled? a. --ssl-mode=DISABLED --ss]-cipher=DHE-RSA-ABS256-SHA:AES128-SHA b. ©. --ssl=0 d. RESET Answer: ¢ MySQL for Database Administrators 7-30 ites Topics Security Ri + Network Sec + Secure Connectior + Password Security + Operating System Security + Encrypting Data-at-Rest + Protecting Against SQL Injections + MySQL Enterprise Firewal MySQL for Database Administrators 7 - 31 = S 2 Ss 3 2 & LS 3 s ° 3 8 s gi 9 i= a 5 a 8 Oo 3 2 a = e a S o 3 2 a 5 5 = So c 8 3 2 S 2 3 8 N 5 s 3 8 é = ” 2 ed. Copyright © 2020 Oracle and/or its af Unauthorized reproduction or distribution prohil Preventing MySQL Password Security Risks + Allackers use a number of techniques, including social engineering and key logging, to discover passwords. — Consider expiration policies to limit exposure if passwords are compromised. + Allackers use social engineering to try to guess passwords. — Consider using the validate_passwozd component to enforce a password policy that makes passwords more difficult to guess. + Attackers try to find passwords in system tables and files. = MySQL passwords are encrypted by using a one-way hash and stored within the mysql. user table, — Prevent non-administrative users from reading this table. = Encrypt the mysql tablespace that stores the data dictionary. MySQL for Database Administrators 7 - 32 How Attackers Derive Passwords Altackers can derive plain text passwords from hashed passwords by using the following techniques: + Brute force algorithms perform the hashing algorithm on many combinations of characters to find matching hashes. — These attacks are very slow and require large amounts of computation. + Dictionary attacks perform hashing operations on combinations of dictionary words and other characters. = These are fastif the password is not secure. + Rainbow tables are made up of the first and last hashes in long chains of repeatedly hashed and reduced passwords. — When you run a target password hash through the same algorithm chain and find a atch to the end of a stored chain, you can derive the password by replaying that chain. Even though passwords are stored as hashed values, attackers can sill try to figure out passwords that match the hashed values. Do not grant non-administrative users access to the password tables or the operating system files that contain the hashed password values. MySQL for Database Administrators 7-33 ites = o 2 s os cS a LS o 6 ° S. Q S a Unauthorized reproduction or distribution prohibited. Copyright ” 2 ) 2020 Oracle and/or its aff © Unauthorized reproduction or distribution prohibited. Copyright Password Validation Component + This component is installed by default when you use the Yum or SLES repositories or an RPM file to install MySQL. + Manual installation steps: 1, Ensure that the component library file (component_validate password. so) is located in the directory referenced by the plugin_dir server variable 2. Execute the following SQL statement = Loads the component — Registers it in the mysql. component system table so that it loads automatically when the server is restarted + Uninstall the component by using the UNINSTALL COMPONEN? stateme - | UNINSTALL COMPONENT ‘file: //component_validate_pasewo: In Windows systems, the Validate Password component library file is component_val password. dll The password validation tests happen only when a password is being set or changed in a CREATE USER Of ALTER USER statement. User accounts, with passwords set before the policy is put in place, can stil log in and connect with passwords that do not meet the password policy criteria MySQL for Database Administrators 7-34 Validate Password Component Variables + The validate_password. policy variable determines which of the validate password. xxx variables are checked when a password is set or changed. = 0 or LOW: Length — 1 or MEDIUM: Length, numeric, upper/lowercase, special characters — 20r STRONG: Length, numeric, upper/lowercase, special characters, dictionary file + Ifthe policy is set to STRONG, the validate_password.dictionary_file variable must be set to point to a file of words to be checked. — Each substring of the password of length 4-100 is compared to the words in the dictionary file. = The comparisons are not case-sensitive. + The validate_password. check_user_name variable is on by default; it rejects a password that is the same as the usemame or its reverse. — This option is not affected by the setting of the policy variable. When you install the Validate Password component, you can access the variables prefixed with “validate_passvora.” Ifthe password validation component is not installed, the variables do not exist. The following output shows the default values of these variables. mysql> SHOW VARTABLES LIKE 'validate’'; | variable_name fl | validate_password.check user_name | | validate [Link] file | | validate password. length ia | validate password.mixed_case_count | 1 | validate pasaword.aunber_count ia | validate password. policy ' 1 I validate _password.special_char_count 7 rows in set (#.4# sec) MySQL for Database Administrators 7-35 Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affiliates ” 2 ed. Copyright © 2020 Oracle and/or its af Unauthorized reproduction or distribution prohil Changing the Default Password Validation Variables + You can dynamically set any of the password validation variables: nysal> SET GLOBAL validate [Link] = 2 myeql> SET GLOBAL validate paseword. length = 16; + To persist the variable settings across server restarts: = Add them to a contig file —[aysaial validate password policy = 2 validate password. length = 16 = Use SET PERSIST mysql> SET PERSIST validate pa: mysql> SET PERSIST validate [Link] MySQL for Database Administrators 7-36 Other Password Considerations + Ifyou do not use the password validation component, then assign a strong password to the root user. = The root account has full privileges for any database operation, and only trusted users should be able to access it. + You can force all passwords to expire after a specified period, by setting the value of the default_password_lifetime system variable. SET GLOBAL default_password_lifetime = number_of days 1 — The default value is zero, which means that passwords never expire. + You can set the password expiry time for a specific user in a CREATE USER oF ALTER USER statement with the PASSWORD EXPIRE clause: PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL n DAY] —— It auser tries to log in after the password has expired, the server might disconnect the user or put the user in sandbox mode, which limits the statements that the user can submit. In sandbox mode, the user can change the password with an ALTER USER statement, but cannot perform any other database operations. Whether a user is disconnected by the server or put into sandbox mode depends on client and server settings. If the client is able to handle expired passwords, or if, on the server, the cisconnect_on_expired_paseword server variable is disabled, the server puts the client in sandbox mode. If the client cannot handle expired passwords, and the disconnect_on_expired_password variable is enabled (the default), then the client is disconnected with a message that the password has expired. MySQL for Database Administrators 7 - 37 2020 Oracle and/or its affiliates Unauthorized reproduction or distribution prohibited. Copyright Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affiliates Locking an Account + Lock individual accounts with the ACCOUNT LOCK clause in a CREATE USER of AL USER statement. — You might lock a new account when you initially create it with c unlock it when the user is ready to use it. — You might lock an existing account with ALTER USER if you suspect that it is compromised. + View the lock state in the mysql. user table's accou! mysql> SELECT user, host, account_locked FROM aysql user; ATE. USER and ed column: tHE secs Unlock a locked account by using the ACCOUNT UNLOCK clause. Note that the mysql. sys account is locked. It cannot be used to log in. The mysql .sys account is, the D2 FINBR of the sys schema objects. Roles are created as locked accounts. MySQL for Database Administrators 7-38 Pluggable Authentication + When a client connects to MySQL, the server uses the username provided by the client and the client's host name to identify the appropriate row in the mysc..usexr table. + The plugin column of the mysql .user table specifies which plugin to authenticate the user with. ~ Enables different authentication mechanisms for different accounts: pluggable authentication — Specifies for an account by using the IDENTIFIED WITH method clause of CREATE USER OF ALTER USER + The default authentication plugin is caching_sha?_passwora — Can be changed using the default_suthentication_plugin system variable + If you require other authentication methods that store their credentials somewhere other than the mysql .user table, then install the appropriate plugin. — Examples include PAM, Windows login IDs, LDAP, or Kerberos — Requires installing the server-side and client-side version of the plugin (if not builtin) You can specify that a particular user account should use another authentication plugin. For example, if a user account is using the default caching_sha2_password plugin, the value in the authentication string column is the SHA-256 encrypted value of the password, and the value in the plucin column is caching_sha?_password. To force an account to use the older mysql_native password plugin, use the IDENTIFIED WITH mysgl_native password BY ‘newpassword’ clause. As a result of that command, in the mysql .usex table, the plugin ‘column for the user account is mysql_native_password, and the authenticat ion_string column is the hashed value of the password. To use PAM authentication, include TDENTIFIED WITH authentication _pam as ‘authentication string". In this case, the value stored in the authentication string column is interpreted by the Pluggable Authentication Module as a service name or LDAP name to Use for authentication rather than a hashed password. ‘The Windows authentication plugin is similar. To use Windows authentication, include rpawrirreD WITH authentication windows AS ‘authentication string’. In this case, the authentication_str:ing is a Windows user or group and an optional map to a MySQL user account. For PAM authentication and Windows native authentication, MySQL depends on the external entity to authenticate users and maintain passwords. MySQL for Database Administrators 7-39 Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affiliates ” 2 ed. Copyright © 2020 Oracle and/or its af Unauthorized reproduction or distribution prohil Preventing Application Password Security Risks I you store application-specific user information in MySQL: + Do not store plain text passwords in the database. — Store these passwords by using one-way hashes. — Ifyou use plain text passwords and the application becomes compromised, an intruder can take the full list of passwords and use them + Use MySQL's 82 () functions and store the password's hash value. = Alternatively, use some other one-way hashing function available to the application. MySQL for Database Administrators 7-40 Connection-Contro! Plugin Set the appropriate system variables to specity the threshold for successive failed attempts allowed, Enforces a delay after a specified number of consecutive failed connection attempts — The delay increases with each consecutive failed connection after that number of attempts. Acts as a deterrent to brute force attacks — The mote the failed connection attempts, the slower the server responds to subsequent attempts. Exposes the following system variables = connection_control_failed_connections_threshold: The number of successive failures permiited before a delay is added — connection_control_min_connection_delay: Amount of delay in milliseconds {6 add for each consecutive connection failure. The delay is this value multiplied by the number of failed connection attempts above the threshold — connection_control_max_connection_delay: Maximum delay to add the amount of delay, and the maximum delay. If the threshold is set to 3 and the amount of delay is set to 1000, the fourth successive failed attempt (one above the threshold) causes a delay of 1000 milliseconds, the fifth failed attempt causes a delay of 2000 milliseconds, and so on until the maximum delay value is reached. Conditions on setting the delay: connection control_min_connection_delay cannot be set greater than the current value of connect ion_control_max_connection_delay connection_control_max_connection_delay cannot be set less than the current value of connect ion_control_min_connection_delay. Due to these conditions, you may have to set the delay in a specific order. MySQL for Database Administrators 7 - 41 2020 Oracle and/or its affiliates Unauthorized reproduction or distribution prohibited. Copyright ” 2 ed. Copyright © 2020 Oracle and/or its affi Unauthorized reproduction or distribution prohil Installing the Connection-Contro! Plugin + Install the plugin: INSTALL PLUGIN connection control SONAME ‘connection [Link]'; + View its configuration variables: mysql> SHOW VARIABLES LIKE ‘connection_control®' | Variable_name | van i | connection_control_failed_connections_threshold | 3 1 | connection control max connection delay | 2147483647 | ion jon_delay | 1000 1 ros in set (FH sec) + Set the variable values dynamically or within a contig file. For Windows systems, in the statement to install the plugin, replace "connection [Link]! with "connection_control all" If the plugin is not installed, the variables do not appear. The default threshold is 3, the default delay value is 1000, and the default max delay is 2147483647. MySQL for Database Administrators 7 - 42 Monitoring Connection Failures + Inspects the value of the Connection_control_delay_generated status variable — Counts the number of times the server added a delay for a failed connection attempt —_ Example: mysql> SHOW STATUS LIKE 'Connection_control$'; ae | Variable _nane | Value | | Connection _control_delay generated | 7 1 row in set (f.## sect + Considers installing the CONNECTION CONTROL FAILED LOGIN ATTEMPTS plugin — Creates a table in the Information Schema to maintain more detailed information about failed connection attempts — The Connection-Control plugin populates the table. MySQL for Database Administrators 7 - 43 Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affiliates Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affiliates Using the CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS Plugin + Install the plugin by using the same file name as the Connection-Control plugin: mysql> INSTALL PLUGIN CONNECTION CONTROL_FATLED LOGIN ATTEMPTS SONAME "eonnection_control. so"; + Query the Information Schema’s CONNECTION_CONTROL_FATL table. — Its columns identify the user account and the number of failed connection attempts. )_LOGIN_ATTEMPTS mysql> SELECT * FROM -> information_schema CONNECTION CONTROL_FATLED_LOGIN ATTEMPTS; 2 vows in set (b.4¥ 522) For Windows systems, in the statement to install the plugin, replace ‘connect ion_control.so" with "connection_control.dll" In this example, one user had & failed attempts and the other user had 7 failed attempts. If the threshold is set to 4, then one user would have had 4 attempts delayed and the other user would have had 3 attempts delayed, making the value of Connect ion_control_delay generated status variable 7 (4 + 3). MySQL for Database Administrators 7-44 Quiz Which of the following validations is included only when the validate password_policy variable is set to 2 or STRONG? a. Numbers are required b. Words are compared to a dictionary file. ©. Special characters are required. d. Passwords must use mixed case letters. Answer: b MySQL for Database Administrators 7-45 Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affiliates ” 2 ed. Copyright © 2020 Oracle and/or its affi Unauthorized reproduction or distribution prohil Topics + Security Risks + Network Sec + Secure Connectior + Password Security + Operating System Security + Encrypting Data-at-Rest + Protecting Against SQL In + MySQL Enterprise Firewal MySQL for Database Administrators 7 - 46 ites Limiting Operating System Usage + Minimize the number of OS accounts on the MySQL host. = You normally administer MySQL by using a login account dedicated to that purpose. — Other accounts increase the number of possible attack vectors on the host. — Login accounts are not necessary for MySQL-only machines. + Minimize the number of non-MySQL-related tasks on the server host. — Additional services might open additional ports and create additional attack vectors. — When you configure a host for fewer tasks, it can be more easily secured than a host running a complex configuration that supports many services. — Dedicating a system to MySQL provides performance benefits. MySQL for Database Administrators 7 - 47 = o 2 s 3 e o LS 3 £ ° ° N So Ni © = 2 5 a ° o os 2 2 = 2 a < S Ss 2 2 3 ° < So o S oD 2 a 2 3 o N 5 & 5 o & > Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affiliates Limiting Operating System Accounts + Restrict the number of users who can access the host. = Each additional login increases the risk of exposing database information that belongs to the MySQL installation and its administrative account. — Examples: Improper fle system privileges can expose data fies. — Users can run the p= command to view information about processes and their execution ‘environment. + When you use a machine dedicated to MySQL, use only the following accounts: = System administrative accounts (such as root in Linux or user-specific accounts that can use su or sudo) = Accounts that might be needed for administering MySQL itself (such as the mysal user account) MySQL for Database Administrators 7 - 48 Operating System Security + For multi-user systems, such as Linux, set ownership of all components of a MySAQL installation to a dedicated login account with minimal privileges. — Typical installations use the mysq2 account. + This protects the database diractories from access by users who are not responsible for database administration. + An additional benefit of setting up this account is that it can be used to run the MySQL server, rather than running the server from the Linux root account. + Aserver that has the privileges of the root login account has more file system access than necessary and constitutes a security risk. + Put MySQL behind the firewall or in a demilitarized zone (DMZ). MySQL for Database Administrators 7 - 49 ites = o 2 s 3 e o LS 3 £ ° ° N So Ni © = 2 5 a ° o os 2 2 = 2 a < S Ss 2 2 3 ° < So o S oD 2 a 2 3 o N 5 & 5 o & > ” 2 File System Security + Protect MySQL files from being accessed by other users on the file system — Data directories — InnoDB tablespaces — Backup files — Configuration files that contain plain text or encrypted passwords [Link] [Link]£ fles + Auser who gains access to MySQL data files or backups can restore those files to databases on another sorver. + AMySAQL installation also includes the programs and scripts used to manage and access databases. — Users need to be able to run but not modify some of these (such as the client programs). ed. Copyright © 2020 Oracle and/or its af MySQL for Database Administrators 7 - 50 Unauthorized reproduction or distribution prohil Preventing File System Security Risks + Change data directory ownership and access permissions before starting the server. = Assign file ownership to an account with administrative privileges, — Set MySQL-related directories and files and user and group table ownership to mysql, including: MySQL programs — Database directories and files Log, status, and configuration filos + Do not set passwords before protecting files. This can permit an unauthorized user to replace the files. + Setup a dedicated system account for MySQL administration. MySQL for Database Administrators 7 - 51 ites € © £2 Ss 3 c G a Go £ ° 2° q So a © = = S a ° Oo 3G 2 2 = 2 a S 2 Ss 7 2 3 5 < oO 6 5 3 2 a 2 3 2 N 5 & 5 a = 3 ” 2 Topics + Security Risks + Operating System Security + Enorypting Data-at-Rest + Protecting Against SQL Injections + MySQL Enterprise Firewal ed. Copyright © 2020 Oracle and/or its affi MySQL for Database Administrators 7-52 Unauthorized reproduction or distribution prohil a 2 Keyring Enables MySQL Server internal components and plugins to securely store sensitive information for later retrieval + Is implemented as a plugin — Different types of keyring plugins are available in the Community and Enterprise Edition of MySQL. Is used to store: — InnoDB storage engine eneryption master key = Audit og file encryption password — Binary log encryption master key Includes a SQL interface for keyring key management — Consists of a set of general-purpose user-defined functions (UDF) installed from the keyring_udé plugin — Allows application users to store their own keys securely in the keyring This is a list of keyring plugins available in the MySQL Enterprise Edition + keyring fle: Stores keyring data in a file on the server host + keyring_encrypted_file: Stores keyring data in an encrypted file on the server host + keyring_okv: Can be used with KMIP-compatible back-end keyring storage products such as Oracle Key Vault and Gemalto SafeNet KeySecure Appliance + keyring_aws’ Communicates with the Amazon Web Services Key Management Service for key generation and uses a local file for key storage + keyring_hashicorp: Communicates with HashiCorp Vault for back-end storage (available in MySQL 8.0.18 and later) The keyring_f ile plugin is available in the MySQL Community E ‘When the data-at-rest encryption feature uses a centralized key management solution (such as xeyring_okv and keyring_hashicorp), the feature is referred to as “MySQL Enterprise Transparent Data Encryption (TDE).” MySQL for Database Administrators 7-53 Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affi ” 2 ed. Copyright © 2020 Oracle and/or its af Unauthorized reproduction or distribution prohil Deploying a Keyring + The keyring plugin must be loaded early during the server startup sequence. — Use the --early-plugin-Lead option. — Other server components may need to access it during their own initialization. — For example, InnoDB needs the master key to decrypt the redo and undo logs during startup. + Only one keyring plugin should be enabled at a time. + Example of enabling the enorypted file keyring: imysqial early-plugin-load-xeyring_encrypted_file.se keyring _encrypted_file_data~/var/1ib/mysql~keyring/keyring-encrypted keyring encrypted file password=mysecret — Each keyring plugin has its own set of server variables to configure the keyring, Refer to the following sections of the MySQL. Reference Manual for the installation and configuration instructions of the keyring plugin that you want to deploy: + Forkeyring file: ~ _ httpsi//[Link]/doc/refman/8.0/ervkeyring-file-plugin. htm! + For keyring encrypted file: - _hitpsi/dev. mysql. com/docirefman/8. O/en/keyring-encrypted + For keyring_okv: - _ httpsi//[Link]/doc/refman/8.0/en/keyring-okv-plugin. html + For keyring_aws: - _ hitps’//[Link]/doc/refman/8. O/en/[Link] + For keyring_hashicorp: - _ [Link] O/en/keyring-hashicorp-plugin. him! -plugin. html MySQL for Database Administrators 7-54 Key Management Functions + keyring_key_generate(key id, key type, key_length) — Gonerates a new random key with a given ID, type, and length, and stores it in the keyring + keyring_key_store(key_id, key_type, key) — Obfuscates and stores a key in the keyring |_key_fetch (key_id) — Given a key ID, deobfuscates and returns the key value + keyz: + keyring _key type fetch (key id) = Given a key ID, returns the key type + keyring_key_length_fetch (key_id) — Given a key ID, retums the key length + keyring_key_remove (key_id) — Removes the key with a givan ID from the keyring Akkey stored in the keyring by a given user can be manipulated later only by the same user. That is, the value of the CURRENT_USER() function at the time of key manipulation must have the same value as when the key was stored in the keyring, Install the keyring_ud plugin and the UDFs: INSTALL PLUGIN keyring_udf SONAME 'keyring_udf.so'y CREATE FUNCTION keyring_key generate RETURNS INTEGER SONAME ‘keyring [Link]'y CREATE FUNCTION keyring key fetch RETURNS STRING SONAME ‘keyring [Link]'y CREATE FUNCTION keyring_key_length_fetch RETURNS INTEGER SONAME ‘keyring [Link]'y CREATE FUNCTION keyring_key_type_fetch RETURNS STRING SONAME ‘keyring [Link]'y CREATE FUNCTION keyring_key store RETURNS INTEGER SONAME 'keyring_udf.so'; CREATE FUNCTION keyring_key_remove RETURNS INTEGER SONAME ‘keyring _udf.so'y MySQL for Database Administrators 7-55 Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affiliates Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affiliates Encrypting InnoDB Tablespaces The following InnoDB tablespaces can be encrypted + File-per-table tablespace: — Addthe ENCRYPTION = 'Y' option to the CREATE TABLE or ALTER TABLE, statement. (| anTER TABLE t1 ENCRYPTION = "Y's + General tablespace: — Add the ENCRYPTION = 'Y' option to the CREATE TABLESPACE of ALTER ‘TABLESPACE Statement. ALTER TABL + mysql system tablespace: — Use the ENCRYPTION = 'Y? option with the ALTER TABLESPACE statement. ALTER TABLESPACE mysql ENCRYPTION = 'Y"; "ACE ts1 ENCRYPTION = °¥"; The data including indexes stored in the tablespace is encrypted when itis written to disk and ecrypted when itis read from disk. The data cached in memory is stored in unencrypted format. MySQL for Database Administrators 7-56 Encrypting InnoDB Redo Logs and Undo Logs al Is enabled by setting innodb_redo_log_encrypt=ON + Affects new redo log pages written to disk + Does not affect redo log pages already on disk + Stores the tablespace encryption key in the header of the first redo log file + Causes server restart to fail when the keyring plugin or master key is not available = Is enabled by setting innodb_undo_log_encrypt=0N ‘Affects new undo log pages written to disk Does not affect undo log pages already on disk Stores the tablespace encryption key in the header of the undo log file ‘When disabled, the server continues to require the keyring plugin and master key until the undo tablespaces that contained the encrypted undo log data are truncated ‘As with tablespace data, redo log and undo log eneryption occurs when the data is written to disk, and decryption occurs when the data is read from disk. MySQL for Database Administrators 7-57 a 2 Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affi ” 2 InnoDB Encryption Keys + InnoDB uses a two-tier encryption key architecture, consisting of a master eneryption key and tablespace keys. + Atablespace key is used to encrypt and decrypt the contents of a tablespace file, including the redo log files and undo tablespaces. — The tablespace key is encrypted using the master key and stored in the tablespace header. — The master key is stored in the keyring. + The master key can be rotated by using the command: ALIER INSTANCE ROTATE INNODB MASTER KEY SS — The user requires the ENCRYPTION _KEY_ADMIN or SUPER privilege. — All tablespace keys in the MySQL Server are re-encrypted and saved back to their respective tablespace headers. = This operation is atomic. — Itdoes not decrypt or re-encrypt the associated tablespace data ed. Copyright © 2020 Oracle and/or its af MySQL for Database Administrators 7-58 Unauthorized reproduction or distribution prohil Encrypting Binary Log and Relay Log From MySQL 8.0.14, binary log files and relay log files can be encrypted — Enable the encryption by setting the binlog_enceyption system variable to ow. + When the binlog_encrypt ion system variable is changed during run time: — The binary log and relay log files are rotated, — New log files follow the new setting. = Old log files are not affected and remain as they are. + The SHOW BINARY 108 statement shows whether each binary log {ile is encrypted or unencrypted. + mysqlbinlos cannot read enerypted binary log files directly. = Use the --read-From-remote-server option to connect to a running MySQL Server to read the encrypted binary logs. MySQL for Database Administrators 7-59 ites € © £2 Ss 3 c G a Go £ ° 2° q So a © = = S a ° Oo 3G 2 2 = 2 a S 2 Ss 7 2 3 5 < oO 6 5 3 2 a 2 3 2 N 5 & 5 a = 3 ” 2 ed. Copyright © 2020 Oracle and/or its af Unauthorized reproduction or distribution prohil Binary Log Encryption Keys + Arandomly generated 32-byte file password is used to encrypt the contents of each binary log file and relay log file. + The file password is then encrypted using the binary log master key. — The master key is stored in the keyring. — The encrypted file password is stored in the log file's header. + The master key can be rotated using the command: ALTER INSTANCE ROTATE BINLOG MASTER KEY | — Anew binary log master key is generated. — Allthe file passwords of known log files are re-encrypted using the new master key. Uses the binary log index and relay log index files to identity the log files — The content of the log files does not need to be re-encrypted. + Ifyou want the binary log master key to be rotated whenever the server restarts, set the binlog_rotate encryption _master_key at_startup system variable to ON. MySQL for Database Administrators 7 - 60 ites Topics Security Risks stem Security + Encrypting Data-at-Resi + Protecting Against SQL Injections + MySQL Enterprise Firewal MySQL for Database Administrators 7 - 61 £ o 2 S 3 2 G 2 3 gs oO ° qi 5 a ® = a S a 3 oO 3G 2 2 = 2 a c 2 = 2 2B 3 5 < 2 o 5 3 2 a 2 3 3 N 5 & 5 3 c = ” 2 Protecting Your Data from SQL Injection Attacks ‘Take measures to protect your data from application-based attacks, such as SQL injection + Do not trust any data entered by users of your applications. — Users can exploit application code by using characters with special meanings, such as quotes or escape sequences, — Make sure that your application remains secure if a user enters something like DROP DATABASE mysql; + Protect numeric and string data values. — Otherwise, users can gain access to secure data and submit queries that can destroy data or cause excessive server load. + Protect even your publicly available data. — Attacks can waste server resources. — Safeguard web forms, URL names, special characters, and so on. ed. Copyright © 2020 Oracle and/or its af MySQL for Database Administrators 7 - 62 Unauthorized reproduction or distribution prohil SQL Injection: Example + Abadly written application compares a provided username and password with rows in the user table and ensures that there is a single matching row with a line such as: sql = "SELECT COUNT(*) FROM users WHERE user='” + username +"! AND pass = '" + pasavord + "'"; + Ifthe user enters a username and password Peter and ty~wa8?1, respectively, the statement evaluates as: SELECT COUNT(*) FROM users WHERE user='Peter’ ‘AND pass = 't¥*wa@?L' + Ifthe user enters a username and password of abcd and x' OR 1-1 LIMIT 1; respectively, the statement evaluates as: SELECT COUNT(*) FROM users WHERE us AND pass = ‘x! OR I=1 LIMIT 1;-- * "abea" The example in the slide shows an attack that permits logins for users who do not provide a matching password, SQL injection attacks might also include commands that create users, drop databases, or modify critical data. MySQL for Database Administrators 7 - 63 Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affiliates ” 2 ts aff or i © 2020 Oracle and ed. Copyright Unauthorized reproduction or distribution prohil Detecting Potential SQL Injection Attack Vectors Users may attempt SQL injection by any of the following methods: + Entering single and double quotation marks (" and ") in web forms + Modifying dynamic URLs by adding 22 (""), $23 (#), and $27 (') to them + Entering characters, spaces, and special symbols rather than numbers in numeric fields Ensure that the application generates an error or removes these extra characters before passing them to MySQL. + If the application permits these characters, your application security might be compromised. Communicate this to the application developers. + If these characters are required, the application can be programmed to escape them to remove the special meaning so that they will be treated as part of a string literal. MySQL for Database Administrators 7-64 Preventing SQL Injection Attacks + Never concatenate user-provided text with SQL statements in the application. + Use parameterized stored procedures or prepared statements when you perform queries that require user-provided text. — Stored procedures and prepared statements do not perform macro expansion with parameters, — Numeric parameters do not permit text values such as injected SQL syntax. — Text parameters treat the user-provided value as a string for comparison rather than SQL syntax. Note: SQL injection attacks and how to prevent them are covered in the course titled MySQL. for Developers. MySQL for Database Administrators 7 - 65 ites = o 2 Ss os cS a LS o 6 ° S. Q S a Unauthorized reproduction or distribution prohibited. Copyright n 2 ed. Copyright © 2020 Oracle and/or its affi Unauthorized reproduction or distribution prohil Topics Security Ri Network Sec ire Connectior urity m Security Encrypting Data-at-Res! Protecting Against SQL Injections MySQL Enterprise Firewall MySQL for Database Administrators 7 - 66 MySQL Enterprise Firewall + Plugin available with Enterprise Edition only + Is an application-level firewall + Permits or denies SQL statements — Registered accounts have whitelists of acceptable statement patterns, + Operates in per-account modes: — RECORDING: Identifying acceptable statements and recording their patterns in a whitelist — PROTECTING: Preventing statements that do not match patterns in the whitelist — DETECTING: Logging suspicious statements, but not preventing statements — OFF: Does not record or protect statements. This is the default mode for each account. + Can be disabled for trusted accounts MySQL for Database Administrators 7 - 67 Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affiliates ” 2 Enterprise Firewall Plugins + Plugin functions: — MYSQL_FIREWALL: Examines statements and executes or rejects statements based con rules in its cache — MySQL_FIREWALL_USERS and MYSQL_FIREWALL_WHTTELIS?: Implements Information Schema tables that contain information about the firewall cache + All three plugins are in the shared library file firewall .so. — Distributed with Enterprise Edition — Located in the 1ib/plagin directory in binary distributions ed. Copyright © 2020 Oracle and/or its affi In Windows installations, the shared library file is called ¢irewa11.411 MySQL for Database Administrators 7-68 Unauthorized reproduction or distribution prohil Enterprise Firewall Database Components + The sp_set_firewall_mode() stored procedure registers MySQL accounts with the firewall. — This is the only component that is intended for direct use. + Other components include: — Tables in the mysci database that store persistent copies of firewall cache data: firewall_users: Rogistered users ~ firewall_whitelist: Whitelisied statement patterns for each user — Library functions that are used internally by the firewall: . firewall_mode () — normalize_statenent () read_firewall_whitelist() read_firewall_users () MySQL Enterprise Firewall maintains account and whitelist information. It uses NEORMATION_SCHEMA tables to provide views into cached data, and tables in the mys system database to store this data in persistent form. The TNFORMATTON SCHEMA tables are accessible by anyone. The mysql tables can be accessed only by users with privileges for that database. The INFORMATION SCHEMA.MYSQL_FIREWALL USERS and mysql. firewall_users tables list registered firewall accounts and their operational modes. The INFORMATION _SCHEMA.MYSQL_FIREWALL_WHITELIST and mysql. firewall whitelist tables list registered firewall accounts and their whitelists, MySQL for Database Administrators 7 - 69 Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affiliates Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affiliates Installing MySQL Enterprise Firewall + Use the correct installation script based on your operating system: — Linux and other systems that use . so shared libraries: linux_install_firewal1.sql installs the firewall . so plugin library. — Windows: win_instal1_#irewal1. sql installs the £{rewal 1.611 plugin library It you install MySQL Server by using the MySQL Installer, you can also elect to instal MySQL Enterprise Firewall by selecting the “Enable Enterprise Firewall’ check box — The installation scripts are located in the share subdirectory of the MySQL Server installation. + The installer script: — Installs the firewall plugins from the library file = Creates the firewall configuration stored procedure, tables, and internally used function references in the msg database MySQL for Database Administrators 7-70 Registering Accounts with the Firewall Register an account by setting its initial firewall mode. + The account name is in the full usex@host format, stored as a single string. + To register an account that is not initially controlled by the firewall, set the mode to OFF. CALL mysql.sp_set_firewall_mode('appusertapphost', OFF) Sd + To register an account for firewall training, set the initial mode to RECORDING. t_firewall_mode('appusertapphost', 'RECORDING') CALL mysql. sp_s — Ifyou set an initial mode of PROTECTING, the account cannot execute any statements because its whitelist is empty. MySQL for Database Administrators 7-71 Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affiliates Unauthorized reproduction or distribution prohibited. Copyright © 2020 Oracle and/or its affiliates Training the Firewall + Register the account in RECORDING mode. + The firewall creates a normalized statement digest for each statement and places the digest in the account's whitelist cache. + Switch the mode to PROTECTING or OFF when training is complete to persist the whitelist. — The firewall persists the cache when you change the account's mode. — Ifyou restart the mysqld process while in RECORDING mode, any changes to that account's whitelist cache are lost. + Retum to RECORDING mode to learn new statements if the application changes. = Changing mode from OFF of PROTECTING to RECORDING does not clear the account's whitelist. MySQL for Database Administrators 7-72

You might also like