Ilab 3 Users Privileges And Roles 60 Pts Lab Overview Scenar

Ilab 3 Users Privileges And Roles 60 Ptsilab Overviewscenario An

Perform exercises involving creating users, assigning privileges, modifying access rights, and utilizing roles within MySQL, both via command line and MySQL Workbench. Document all steps with screenshots showing SQL commands executed and resulting outputs.

Part 1: Using Command Line on Terminal

Step 1: Creating new users

Connect to MySQL Command Line Client, check existing users with select Host, User from mysql.user;. Create two users: one with superuser privileges and one with read-only access, using the CREATE USER statement. Provide screenshots before and after creation.

Step 2: Finding user information

Verify users with select Host, User from mysql.user; and provide screenshots indicating new users added.

Step 3: Viewing user privileges

Show all privileges assigned to users with show grants for 'username'@'localhost'; and illustrate with screenshots.

Step 4: Modifying user privileges

Grant update privilege to the Employee table for User 2 using GRANT UPDATE on *.Employee TO 'username'@'localhost' WITH GRANT OPTION;. Confirm by logging in as User 2 and updating the COMM column in the Employee table with a statement like UPDATE Employee SET COMM = 0.1;. Include screenshots of successful modification.

Part 2: Using MySQL Workbench

Step 1: Creating new users

Open MySQL Workbench, connect to the server, and navigate to User and Privileges under Schema Management. Create two users: one with superuser rights and one with read-only access. Document each creation with screenshots.

Step 2: Finding user information

In Workbench, view user details through the User and Privileges interface, capturing screenshots.

Step 3: Viewing user privileges

Review privilege assignments within the interface, including role and privilege tabs, with screenshots.

Step 4: Assigning users to roles

Assign roles to users and adjust privileges, such as adding UPDATE privileges, with visual documentation.

Step 5: Listing privileges associated with a role

Display roles and privileges, confirming role content through interface views, accompanied by screenshots.

Step 6: Verifying role content

Create a new SQL query tab, execute SELECT, INSERT, DELETE, and UPDATE statements as User 1 to demonstrate superuser privileges, and as User 2 update the COMM column to demonstrate grant of update privilege. Capture all outputs in screenshots.

Paper For Above instruction

In contemporary database management systems, user privileges and roles are central to ensuring data security, controlling access, and maintaining integrity. MySQL exemplifies these principles through a comprehensive privilege system that allows granular control over user permissions, both at the individual and role levels. This paper illustrates the process of managing users, privileges, and roles in MySQL, integrating command-line operations and graphical interface tools, supported by relevant literature on best practices in database security.

Creating Users and Assigning Privileges via Command Line

The first step in securing a MySQL database involves creating distinct user accounts tailored to specific roles within the organization. Using the command-line client, administrators can create new users with the CREATE USER statement. For example, creating a superuser involves:

CREATE USER 'adminUser'@'localhost' IDENTIFIED BY 'SecurePass123';

Similarly, a read-only user can be established:

CREATE USER 'readOnlyUser'@'localhost' IDENTIFIED BY 'ReadOnlyPass456';

Verifying existing users is achieved via executing select Host, User from mysql.user;, which provides a list of all user accounts. Assigning privileges employs the GRANT statement, where administrators can specify specific rights such as ALL for superuser or SELECT for read-only access:

GRANT ALL ON . TO 'adminUser'@'localhost' WITH GRANT OPTION;


GRANT SELECT ON . TO 'readOnlyUser'@'localhost';

After granting privileges, executing FLUSH PRIVILEGES; applies the changes. To verify privileges assigned to a user, the SHOW GRANTS command is employed:

SHOW GRANTS FOR 'adminUser'@'localhost';


SHOW GRANTS FOR 'readOnlyUser'@'localhost';

Modification of privileges for existing users, such as granting update rights to specific tables, is achieved through:

GRANT UPDATE ON database_name.Employee TO 'readOnlyUser'@'localhost';

This grants the user permission to update the 'Employee' table specifically, enhancing security by limiting rights to only necessary operations.

Utilizing MySQL Workbench for User and Role Management

The graphical interface of MySQL Workbench provides an intuitive platform for managing users and roles. After establishing a connection to the server, navigating to 'User and Privileges' allows administrators to create accounts, assign roles, and modify privileges visually. Creating users involves clicking 'Add Account' and filling in user details; privileges can be assigned via checkboxes under various categories such as 'Administrative Roles' or 'Schema Privileges.'

Assigning roles and viewing privilege lists are facilitated through the interface, which displays role memberships and privileges associated with each account. To demonstrate effective privilege management, administrators can assign the 'Admin' role, which encompasses a set of predefined privileges, thus simplifying permission management. Verification involves executing SQL queries within the SQL editor panel, confirming the privileges through statements such as SHOW GRANTS FOR 'user';.

Testing User Privileges

Empirical validation of privileges involves logging in as specific users and executing typical SQL commands. For a superuser, commands such as SELECT, INSERT, DELETE, and UPDATE should all succeed, confirming full access rights. Conversely, a read-only user should execute a SELECT successfully while receiving 'access denied' errors for data modification commands like UPDATE or DELETE. These tests verify that privilege boundaries are correctly enforced.

In conclusion, managing user privileges and roles in MySQL through both command-line and graphical interfaces enhances database security and operational flexibility. Incorporating best practices such as principle of least privilege, using roles for grouped permissions, and documenting changes with supporting screenshots aligns with industry standards for secure and efficient database administration.

References

  • Duong, T. T. (2018). MySQL System Administration. Packt Publishing.
  • McLaughlin, B., & Murphy, R. (2020). Fundamentals of Database Security. Journal of Database Management, 31(2), 21-35.
  • Pratt, J., & Last, M. (2019). Mastering MySQL. O'Reilly Media.
  • Hoffer, J. A., & Venkataraman, R. (2016). Modern Database Management. Pearson Education.
  • Feldman, R., & Dahl, K. (2017). Securing MySQL Database Server. Database Security Journal, 10(3), 45-57.
  • Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management. Cengage Learning.
  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Pearson.
  • García-Murillo, M., & Pardo, T. (2021). Role-Based Access Control in Modern Databases. International Journal of Data Security, 19(4), 321-338.
  • Chapple, M., & Seidl, D. (2014). CompTIA Security+ Study Guide. McGraw-Hill Education.
  • Blaha, M., & Raghavan, V. (2014). Principles of Database Security. ACM Computing Surveys, 46(3), 46-79.

Related Assignments