6. Skill Check Managing MySQL Users

　You must disable automatic password expiration on the ‘erika’@’localhost’ account. Which command will do this?

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE NEVER;

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE DEFAULT;

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE INTERVAL 30 DAY;

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE;

　You must grant SELECT privilege on the salaries table in the employees’ database to user jan@localhost? Which command will do this?

GRANT SELECT ON employees.salaries TO jan@localhost;

REVOKE GRANT OPTION ON employees.salaries FROM ‘jan’@’localhost’;

REVOKE GRANT OPTION ON salaries.employees FROM ‘jan’@’localhost’;

GRANT SELECT ON salaries.employees TO jan@localhost;

Which command activates roles at the session level?

SET ROLE ALL;

ALTER USER kari@localhost DEFAULT ROLE ALL;

SET DEFAULT ROLE ALL TO kari@localhost;

SET PERSIST activate_all_roles_on_login = ON;

　You plan to use the test authentication plug-in test_plugin_server. Which statements are true? (Choose two)

It authenticates against operating system users and groups.

It is intended for use during testing and development.

It only allows MySQL users to log in via a UNIX socket.

It sends a plain text password to the server.

It implements native and old password authentication.

Which command removes DDL privileges from a role?

REVOKE DELETE, INSERT, UPDATE ON world.* FROM r_dev;

REVOKE GRANT OPTION ON world.* FROM r_dev

REVOKE CREATE, DROP ON world.* FROM r_dev;

REVOKE r_updater FROM r_dev;

You are using the caching_sha2_password plug-in? Which statements are true (Choose two)

It prevents clients from hashing passwords.

It authenticates MySQL accounts against the operating system.

It sends plain text passwords to a server.

It is the default authentication plug-in in MySQL 8.0.

It implements SHA-256 authentication and uses server side caching for better performance.

===

6. Skill Check Managing MySQL Users

　You must disable automatic password expiration on the ‘erika’@’localhost’ account. Which command will do this?

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE NEVER;

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE DEFAULT;

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE INTERVAL 30 DAY;

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE;

答案：
ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE NEVER;

说明：
Configuring Password Expiration
• The default_password_lifetime global variable specifies the number of days after
which passwords must be changed.
– The default value is 0, which indicates that passwords do not expire.
• Set per-account password lifetime with the PASSWORD EXPIRE clause of CREATE
USER or ALTER USER:
• Apply the default password lifetime to an account:
• Disable automatic password expiration on an account:
CREATE USER ‘consultant’@’laptop3’ IDENTIFIED BY ‘change%me’
PASSWORD EXPIRE INTERVAL 30 DAY;
ALTER USER ‘consultant’@’laptop3’ PASSWORD EXPIRE NEVER;

　You must grant SELECT privilege on the salaries table in the employees’ database to user jan@localhost? Which command will do this?

GRANT SELECT ON employees.salaries TO jan@localhost;

REVOKE GRANT OPTION ON employees.salaries FROM ‘jan’@’localhost’;

REVOKE GRANT OPTION ON salaries.employees FROM ‘jan’@’localhost’;

GRANT SELECT ON salaries.employees TO jan@localhost;

答案：
GRANT SELECT ON employees.salaries TO jan@localhost;

说明：
GRANT Statement
• The GRANT statement assigns privileges or roles to MySQL user accounts and roles.
– Example GRANT syntax:
• Statement clauses:
– Privileges to be granted
— Example: SELECT, UPDATE, DELETE
– Privilege level:
— Global: .
— Database: db_name.*
— Table: db_name.table_name
— Stored routine: db_name.routine_name
– Account or role to which you are granting the privilege

Which command activates roles at the session level?

SET ROLE ALL;

ALTER USER kari@localhost DEFAULT ROLE ALL;

SET DEFAULT ROLE ALL TO kari@localhost;

SET PERSIST activate_all_roles_on_login = ON;

答案：
SET ROLE ALL;

说明:
Activating Roles at Session Level
• Use SET ROLE statement to modify the list of active roles in the current session. It
accepts a list of roles or one of the following role specifiers:
– DEFAULT: Activate the account default roles.
– NONE: Disable all roles.
– ALL: Activate all roles granted to the account.
– ALL EXCEPT: Activate all roles granted to the account except those named.
• Use CURRENT_ROLE() function to determine which roles are active in the current
session.
SET ROLE ALL;
SET ROLE r_viewer, r_updater;
SELECT CURENT_ROLE();

　You plan to use the test authentication plug-in test_plugin_server. Which statements are true? (Choose two)

It authenticates against operating system users and groups.

It is intended for use during testing and development.

It only allows MySQL users to log in via a UNIX socket.

It sends a plain text password to the server.

It implements native and old password authentication.

答案：

It implements native and old password authentication.
It is intended for use during testing and development.

说明：
Loadable Authentication Plugins
• Test Authentication plugin (test_plugin_server): Implements native and old
password authentication
– This plugin uses the auth_test_plugin.so file and is intended for testing and
development purposes.
• Socket Peer-Credential (auth_socket): Allows only MySQL users who are logged in
via a UNIX socket from a UNIX account with the same name
– This plugin uses the auth_socket.so file.

Which command removes DDL privileges from a role?

REVOKE DELETE, INSERT, UPDATE ON world.* FROM r_dev;

REVOKE GRANT OPTION ON world.* FROM r_dev

REVOKE CREATE, DROP ON world.* FROM r_dev;

REVOKE r_updater FROM r_dev;
答案：
REVOKE CREATE, DROP ON world.* FROM r_dev;

说明：

REVOKE: Examples
• Assume that Amon has SELECT, DELETE, INSERT, and UPDATE privileges on the
world database.
– You want to change the account so that he has SELECT access only.
• Revoke Jan’s ability to grant to other users any privileges that he holds for the world
database, by revoking the GRANT OPTION privilege from his account.
• Assume that the r_dev role has the CREATE, DROP, SELECT, DELETE, INSERT, and
UPDATE privileges on the world database.
– You want to remove all DDL privileges from the role.
• Revoke r_updater role from Kari user account.
REVOKE GRANT OPTION ON world. FROM ‘Jan’@’localhost’;
REVOKE DELETE, INSERT, UPDATE ON world. FROM ‘Amon’@’localhost’;
REVOKE CREATE, DROP ON world.* FROM r_dev;
REVOKE r_updater FROM kari@localhost;

You are using the caching_sha2_password plug-in? Which statements are true (Choose two)

It prevents clients from hashing passwords.

It authenticates MySQL accounts against the operating system.

It sends plain text passwords to a server.

It is the default authentication plug-in in MySQL 8.0.

It implements SHA-256 authentication and uses server side caching for better performance.

答案：
It is the default authentication plug-in in MySQL 8.0.
It implements SHA-256 authentication and uses server side caching for better performance.

说明：

Pluggable Authentication
• In MySQL 8.0, caching_sha2_password is the default authentication plugin.
• During connection using the caching_sha2_password plugin, MySQL uses the
following to authenticate an account:
– Username
– Password
– Client host
• When specifying host names, remember the proper perspective:
– Specify the server’s host name when connecting using a client.
– Specify the client’s host name when adding a user to the server