MySQL / MariaDB style commands simple questions
The following questions relate to administrating a database (you have ADMIN rights). You are to assume the tables ‘branch’ and ‘employees’ exist within a database named ‘companydb’ and is populated with data. The users ‘dunn’, ‘taylor’ and ‘davis’ exist but have no current privileges, all activities are undertaken in one ADMIN session. Issue MySQL / MariaDB style commands that:
(i) Give ‘dunn’ (with PASSWORD ‘thunder’) the ability to perform all functions on the ‘branch’ table and ‘taylor’ (with PASSWORD ‘ballpane’) SELECT rights on ‘branch’ table only.
(ii) Give ‘davis’ rights to update and insert data on the ‘employees’ table only.
(iii) Prevent ‘dunn’ from updating or altering the ‘branch’ table only.
(iv) The table ‘employees’ has become fragmented and requires action to remedy this, issue the necessary command to achieve this.
(v) The table ‘branch’ has become damaged and requires action to remedy this, issue the necessary command to achieve this.
(vi) The table ‘employees’ requires a new attribute to record the name of their ‘department’, issue the command to achieve this.
(vii) A frequent query is run against ‘branch’ table to search for branches based on their branch code. Provide commands that enable modification of this attribute to enhance and optimise the search and retrieval of records based on the ‘branch_code’ attribute.
Need help with these questions. I will return the favor some day. thanks!
Re: MySQL / MariaDB style commands simple questions
Quote:
Originally Posted by
#ProGear
(ii) Give ‘davis’ rights to update and insert data on the ‘employees’ table only.
GRANT SELECT, UPDATE, INSERT ON database.employees TO davis@'localhost';
Quote:
Originally Posted by
#ProGear
Prevent ‘dunn’ from updating or altering the ‘branch’ table only.
Grant him priveliges on other tables then revoke his priveliges on the specific table you'd like him not to mess with.
REVOKE UPDATE, ALTER ON database.branch FROM dunn@'localhost';
Quote:
Originally Posted by
#ProGear
(i) Give ‘dunn’ (with PASSWORD ‘thunder’) the ability to perform all functions on the ‘branch’ table and ‘taylor’ (with PASSWORD ‘ballpane’) SELECT rights on ‘branch’ table only.
This creates a new user 'dunn', with the mentioned password and grants him all priviledges to the specified table.
1. GRANT * ON database.branch TO dunn@'localhost' IDENTIFIED BY 'thunder';
2. GRANT SELECT ON database.branch TO taylor@'localhost' IDENTIFIED BY 'ballpane';
If you wish to grant priviledges to users that already exist, solely delete the line 'IDENTIFIED BY 'PASSWORD''.
Once done, you'll need to run the cmd 'FLUSH PRIVILEGES;' within the MySQL cmd.
----
Quote:
Originally Posted by
#ProGear
(v) The table ‘branch’ has become damaged and requires action to remedy this, issue the necessary command to achieve this.
mysql> check table 'branch'; If this cmd returns a status else than 'OK', then you'd repair your table thro the cmd,
mysql> repair table 'branch';
Quote:
Originally Posted by
#ProGear
(vi) The table ‘employees’ requires a new attribute to record the name of their ‘department’, issue the command to achieve this.
ALTER TABLE 'employees' ADD 'department' VARCHAR( 255 ) NOT NULL;
------------
Sorry I couldn't understand the other two Qs. Hope I helped tho.
