EX 7
.docx
School
Anna University, Chennai**We aren't endorsed by this school
Course
CS 7202
Subject
Information Systems
Date
Dec 17, 2024
Pages
3
Uploaded by BarristerAlligator4782
Experiment: Implementing Access Control in a Relational DatabaseAimTo implement access control in a relational database by managing user permissions and roles,ensuring secure access to lab records based on the role of the user (Admin, Lab Assistant, Student).ProcedureStep 1 : Set up the Database and Tables1.Create a database named LabManagement.2.Create two tables:oUsers: To store user details and roles.oLab_Records: To store information about lab experiments.Example CREATE DATABASE LabManagement;USE LabManagement;CREATE TABLE Users (UserID INT AUTO_INCREMENT PRIMARY KEY,Username VARCHAR(50) UNIQUE NOT NULL,Password VARCHAR(50) NOT NULL,Role ENUM('Admin', 'Lab_Assistant', 'Student') NOT NULL); CREATE TABLE Lab_Records (RecordID INT AUTO_INCREMENT PRIMARY KEY,ExperimentName VARCHAR(100) NOT NULL,Description TEXT,CreatedBy INT NOT NULL,FOREIGN KEY (CreatedBy) REFERENCES Users(UserID));OUTPUTUsers Table Structure+--------+----------+----------+-----------------+| UserID | Username | Password | Role |+--------+----------+----------+-----------------+| 1 | admin | adminpass| Admin || 2 | assistant1 | assistpass | Lab_Assistant || 3 | student1 | studentpass | Student |+--------+----------+----------+-----------------+Lab_Records Table Structure+----------+----------------+--------------------+-----------+| RecordID | ExperimentName | Description | CreatedBy |
+----------+----------------+--------------------+-----------+Step 2 : Insert Sample User DataInsert sample users with different roles into the Userstable.Example :INSERT INTO Users (Username, Password, Role) VALUES('admin', 'adminpass', 'Admin'),('assistant1', 'assistpass', 'Lab_Assistant'),('student1', 'studentpass', 'Student');OUTPUT+--------+-------------+-------------+-----------------+| UserID | Username | Password | Role |+--------+-------------+-------------+-----------------+| 1 | admin | adminpass | Admin || 2 | assistant1 | assistpass | Lab_Assistant || 3 | student1 | studentpass | Student |+--------+-------------+-------------+-----------------+Step 3 : Create Database Users and Grant PermissionsCreate database-level users and grant appropriate permissions based on their roles:Admin: Full access to the entire database.Lab Assistant: Permissions to view and insert data in the Lab_Recordstable but cannot delete records.Example-- Create database usersCREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'adminpassword';CREATE USER 'assistant_user'@'localhost' IDENTIFIED BY 'assistantpassword';CREATE USER 'student_user'@'localhost' IDENTIFIED BY 'studentpassword';-- Grant permissionsGRANT ALL PRIVILEGES ON LabManagement.* TO 'admin_user'@'localhost';GRANT SELECT, INSERT ON LabManagement.Lab_Records TO 'assistant_user'@'localhost';GRANT SELECT ON LabManagement.Lab_Records TO 'student_user'@'localhost';
OUTPUT+------------------------+---------------------------------------------+| Action | Result |+------------------------+---------------------------------------------+| Create admin_user | Query OK, 0 rows affected (0.01 sec) || Create assistant_user | Query OK, 0 rows affected (0.01 sec) || Create student_user | Query OK, 0 rows affected (0.01 sec) || Grant ALL to admin_user| Query OK, 0 rows affected (0.01 sec) || Grant SELECT, INSERT to|| assistant_user | Query OK, 0 rows affected (0.01 sec) || Grant SELECT to || student_user | Query OK, 0 rows affected (0.01 sec) |+------------------------+---------------------------------------------+Step 4 : Implement Row-Level Access Control (Optional)To restrict Lab Assistants to viewing only the records they created, create a view:Grant permissions on the view instead of the table:CREATE VIEW Assistant_Records ASSELECT * FROM Lab_RecordsWHERE CreatedBy = (SELECT UserID FROM Users WHERE Username = USER());GRANT SELECT ON Assistant_Records TO 'assistant_user'@'localhost';OUTPUT+----------+----------------+--------------------+-----------+| RecordID | ExperimentName | Description | CreatedBy |+----------+----------------+--------------------+-----------+| 2 | Experiment X | Sample Description | 2 |+----------+----------------+--------------------+-----------+ResultAccess control was implemented successfully, with roles and permissions defined for Admin,Lab Assistant, and Student users.