Database Security and
Access Control
Introduction to Database Security
• Why is Database Security Important?
• Prevent unauthorized access
• Ensure data integrity and confidentiality
• Manage privileges effectively
• Common Threats:
• Unauthorized access
• SQL injection
• Privilege misuse
Creating Roles
• Definition: A role is a collection of privileges assigned to users.
• Syntax:
CREATE ROLE Role_Name;
• Example: Creating a role for HR managers who need access to
employee records.
CREATE ROLE hr_manager;
Creating Users
• Definition: Users are created to access the database with specific
privileges.
• Syntax:
CREATE USER username
IDENTIFIED BY password
[DEFAULT ROLE role_name]
[WITH resource_option] [password_option] [lock_option];
User Options
• Resource Options:
• MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count;
• Password Options:
• PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY];
• Lock Options:
• ACCOUNT LOCK | ACCOUNT UNLOCK;
Examples of Creating Users
• -- Basic User Creation
CREATE USER user1 IDENTIFIED BY 'user1’;
• -- User with Password Expiry
CREATE USER user2 IDENTIFIED BY 'user2' EXPIRE NEVER;
• -- User with Resource Limits
CREATE USER user3 IDENTIFIED BY 'user3’
WITH MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 2;
• -- User with Account Lock
CREATE USER user4 IDENTIFIED BY 'user4' ACCOUNT LOCK;
• -- User with Default Role (role must be created first)
CREATE USER user5 IDENTIFIED BY 'user5' DEFAULT ROLE IT;
Altering Users
• Definition: Used to modify existing user settings.
• Syntax:
ALTER USER username IDENTIFIED BY password
[DEFAULT ROLE role_name]
[WITH resource_option] [password_option] [lock_option];
• Example:
ALTER USER user4 IDENTIFIED BY 'newpassword’
ACCOUNT UNLOCK;
Assigning Roles to Users
• Assign a Role to a User:
GRANT Role_name TO user_name;
• Example: Assigning the hr_manager role to User 1:
GRANT hr_manager TO user1;
Granting Privileges
• Definition: Users and roles are assigned privileges to perform
actions on database objects.
• Privilege Types: CREATE | CREATE USER | CREATE ROLE | CREATE
VIEW | CREATE TEMPORARY TABLE | ALTER | DROP;
• Syntax:
GRANT priv_type [(column_list)]
[, priv_type [(column_list)]]
ON [object_type] priv_level
TO user_or_role [, user_or_role]
[WITH GRANT OPTION];
Examples:
• -- Granting HR access to modify employee records:
GRANT SELECT, INSERT, UPDATE ON employee TO hr_manager;
• -- Granting specific privileges to multiple roles:
GRANT INSERT, SELECT ON lab7.* TO role2, role3;
Granting DDL Privileges
• Definition: Allows users to create, alter, or drop tables, roles, or
users.
• Syntax:
GRANT priv_type
ON [object_type]
TO user_or_role [, user_or_role]
[WITH GRANT OPTION];
Examples:
• -- Allowing the HR Manager to create new users:
GRANT CREATE USER ON *.* TO hr_manager;
• Explanation:
• The ON keyword specifies the scope of the privilege.
• *.* means all databases and all objects (tables, views, etc.).
• Limiting to a specific database:
GRANT all ON lab6.* TO IT;
• -- Granting INSERT and SELECT privileges to a role:
GRANT INSERT,SELECT on lab6.* to role2, role3;
Revoking Privileges
• Definition: Used to remove privileges from a user or role.
• Syntax:
REVOKE priv_type
ON database_name.object
FROM user_name|role_name;
Examples:
• -- Revoking HR Manager’s update access from the Employee
table:
REVOKE UPDATE ON employee FROM hr_manager;
• Revoking a role from a user:
REVOKE hr_manager FROM user1;
Dropping Users and Roles
• Definition: Removing users and roles from the database.
• Syntax:
DROP USER username;
DROP ROLE role_name;
• Example:
DROP USER user4;
DROP ROLE role2;
Creating Views for Security
• Definition: A view restricts access to specific columns of a table.
• Syntax:
CREATE VIEW view_name AS query_statement;
• Example: Creating a view to hide employee salary data:
CREATE VIEW emp_basic
AS SELECT eid, ename, gender
FROM employee;
• Granting access to the view:
GRANT SELECT ON emp_basic TO hr_manager;
Logging In as a New User
• Steps:
• Connect to MySQL with the new user credentials.
• Click home icon , and next to MySql Connections, click on the plus icon
• Create a new connection with the username and password
• Type
• Set the appropriate role.
SET ROLE hr_manager;
• Use the database:
USE lab7;
Summary and Best Practices
• Use roles to manage user privileges efficiently.
• Grant only necessary privileges to minimize risks.
• Use views to restrict direct table access.
• Regularly review and revoke unnecessary privileges.
