Question

SQL CODE

– System Variables

System variables can be set via the command line. Please write a sequence of statements to:

• Set safe updates to false for the session.
• Do the following at the global level:
  • Set the maximum number of connections to 45.
  • Set the threshold for long running queries to 7 seconds – do this at the global level.
  • Set logs to expire when they are more than 3 days old

Write the commands to display the value of the four variables you have updated.

2. – Managing Users

Write statements that do the following:

• • Create users as follows:
    • Create user daisy with password imadog
    • Create user elmo with password imacat
    • Create user ap_superuser with password imsuper
    • Create user ap_dev with password apdev
    • Create user ap_guest with password apuser
  • Rename user daisy to have username lazydaisy
  • Change password for daisy to be imalazydog
  • Drop user elmo
• – Granting and Revoking Privileges

Write statements that do the following:

• Grant privileges to ap_superuser as follows:
  • SELECT/UPDATE/INSERT/DELETE privileges to all tables in ap database
• Grant privileges to ap_dev as follows:
  • SELECT/UPDATE/INSERT privileges to all tables in ap database
• Grant privileges to ap_guest as follows:
  • SELECT privileges to all tables
  • UPDATE privileges to VENDORS table only
• Revoke all privileges from ap_guest
• Revoke INSERT privileges from ap_dev

4. – Viewing User and Privilege Information

Write statements to do the following:

• Display a list of user and host for all users, sort by host, then user.
• Show grants for the currently logged on user
• Show privileges for lazydaisy
• Show privileges for ap_superuser
• Show privileges for ap_guest

Answer 1

SQL_SAFE_UPDATES is a system variable.

SET SQL_SAFE_UPDATES=0;

SET GLOBAL long_query_time = 7;

mysql> SET GLOBAL max_connections = 250;

expire_log_days=3

*********************************************************

CREATE USER daisy IDENTIFIED BY imadog

CREATE USER elmo IDENTIFIED BY imacat

CREATE USER ap_superuser IDENTIFIED BY imsuper

CREATE USER ap_dev IDENTIFIED BY apdev

CREATE USER ap_guest IDENTIFIED BY apuser

RENAME USER 'daisy' TO 'lazydaisy';

ALTER USER daisy IDENTIFIED BY imslazydog;

DROP USER 'elmo';

*********************************************************************

GRANT Insert, Select, Update, Delete ON ap database To 'ap-superuser';

GRANT Insert, Select, Update ON ap database To 'ap-dev';

GRANT SELECT ON ap database To 'ap-guest';

GRANT Update ON VENDORS To 'ap-guest';

REVOKE ALL ON ap database FROM  'ap-guest';

REVOKE INSERT ON ap database FROM 'ap-dev';

**********************************************************************************

select User,Host from mysql.user;

SHOW GRANTS FOR CURRENT_USER;

SHOW GRANTS FOR lazydaisy;

SHOW GRANTS FOR ap_superuser ;

SHOW GRANTS FOR 'ap-guest';