💡 Ask Tutor

Chapter 21: Database Users and Permissions (GRANT/REVOKE)

In a production environment, you should never connect your web application to the database using the root user (or the default administrative user). This is a major security risk. If your application is compromised, an attacker could gain full control over your entire database system, potentially leading to data theft, data destruction, or even server compromise.

Instead, you create specific database users for your application and grant them only the minimum necessary privileges to perform their tasks. This is known as the principle of least privilege. MySQL/MariaDB (and other database systems) provide robust mechanisms for managing users and their permissions using the CREATE USER, GRANT, and REVOKE statements.

Database Users

  • A database user is an account used to connect to the database server.
  • Each user has a username and is authenticated, usually with a password.
  • Users can be configured to connect from specific hosts or IP addresses (e.g., 'my_app_user'@'localhost' or 'my_app_user'@'192.168.1.100').

Privileges (Permissions)

Privileges define what actions a user can perform on specific database objects. They can be granted at different levels:

  • Global Level: Applies to all databases on the server (e.g., CREATE USER, RELOAD).
  • Database Level: Applies to all tables within a specific database (e.g., SELECT, INSERT, UPDATE, DELETE on scriptbuzz_db.*).
  • Table Level: Applies to all columns within a specific table (e.g., SELECT on scriptbuzz_db.users).
  • Column Level: Applies to specific columns within a specific table (less common for web apps, more for fine-grained reporting).
  • Stored Routine Level: For stored procedures and functions.

Common Privileges for Web Applications:

  • SELECT: Read data from tables.
  • INSERT: Add new rows to tables.
  • UPDATE: Modify existing rows in tables.
  • DELETE: Remove rows from tables.
  • CREATE: Create new tables or databases.
  • ALTER: Modify table structure.
  • DROP: Delete tables or databases.
  • INDEX: Create or drop indexes.
  • REFERENCES: Required to create foreign keys.
  • LOCK TABLES: Ability to explicitly lock tables (often implied by other privileges but sometimes needed for transactions).

SQL Syntax & Examples (MySQL/MariaDB)

To manage users and permissions, you typically need to be connected as a user with administrative privileges (e.g., root).

Let’s use our scriptbuzz_db. Remember to run USE scriptbuzz_db; first.

1. Creating a New User (CREATE USER)

Syntax:

SQL
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

'username': The desired username.

'host': Specifies where the user can connect from.

  • 'localhost': Only from the local machine.
  • '%': From any host (use with caution, restrict if possible).
  • '192.168.1.100': Only from a specific IP address.

'password': The password for the user.

Example 1: Create a user for your web application Let’s create a user webapp_user that can connect from localhost.

SQL
CREATE USER 'webapp_user'@'localhost' IDENTIFIED BY 'MyStrongAppPassword!';

What it does: Creates a new user account. At this point, webapp_user has no privileges on any database object.

2. Granting Privileges (GRANT)

Syntax:

SQL
GRANT privilege_type [, privilege_type] ...
ON database_name.table_name
TO 'username'@'host';

  • privilege_type: One or more privileges (e.g., SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES).
  • database_name.table_name: Specifies the scope.
    • *.*: All databases and all tables (global privilege).
    • scriptbuzz_db.*: All tables in scriptbuzz_db.
    • scriptbuzz_db.users: Only the users table in scriptbuzz_db.

Example 2: Grant common CRUD privileges to webapp_user on scriptbuzz_db

SQL
GRANT SELECT, INSERT, UPDATE, DELETE
ON scriptbuzz_db.*
TO 'webapp_user'@'localhost';

What it does: Grants the SELECT, INSERT, UPDATE, and DELETE privileges on all tables within the scriptbuzz_db database to webapp_user when connecting from localhost.

Example 3: Grant CREATE and ALTER only on new tables (if needed for migrations) If your application or migration tool needs to create/alter tables, you’d grant these:

SQL
GRANT CREATE, ALTER, DROP, INDEX, REFERENCES, CREATE TEMPORARY TABLES
ON scriptbuzz_db.*
TO 'webapp_user'@'localhost';

What it does: Adds more DDL (Data Definition Language) privileges. You would typically grant these more sparingly and perhaps only to a separate “migration user” or during specific deployment phases.

Example 4: Grant ALL PRIVILEGES (Use with caution!): This grants all available privileges on the specified scope. Still better than root, but more privileges than typically needed for a simple web app.

SQL
GRANT ALL PRIVILEGES
ON scriptbuzz_db.*
TO 'webapp_user'@'localhost';

3. Refreshing Privileges (FLUSH PRIVILEGES)

After making changes to user privileges (especially if not using WITH GRANT OPTION), it’s often a good idea to refresh the server’s in-memory privilege cache.

Syntax:

SQL
FLUSH PRIVILEGES;

What it does: Reloads the grant tables, ensuring that the new privileges are active immediately without restarting the MySQL server.

4. Checking User Privileges (SHOW GRANTS)

Syntax:

SQL
SHOW GRANTS FOR 'username'@'host';

Example 5: Check privileges for webapp_user

SQL
SHOW GRANTS FOR 'webapp_user'@'localhost';

Expected Output (example):

Plaintext
+------------------------------------------------------------------------------------------------------------------------+
| Grants for webapp_user@localhost                                                                                       |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `webapp_user`@`localhost`                                                                        |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `scriptbuzz_db`.* TO `webapp_user`@`localhost`                                 |
+------------------------------------------------------------------------------------------------------------------------+

USAGE privilege essentially means no privileges, only the ability to connect. The second line shows your specific grants.

5. Revoking Privileges (REVOKE)

Syntax:

SQL
REVOKE privilege_type [, privilege_type] ...
ON database_name.table_name
FROM 'username'@'host';

REVOKE undoes GRANT statements. The syntax is very similar.

Example 6: Revoke DELETE privilege from webapp_user

SQL
REVOKE DELETE
ON scriptbuzz_db.*
FROM 'webapp_user'@'localhost';

FLUSH PRIVILEGES; -- Always flush after changing privileges

What it does: Removes the DELETE permission. webapp_user can no longer delete rows from any table in scriptbuzz_db.

6. Deleting a User (DROP USER)

Syntax:

SQL
DROP USER 'username'@'host';

Example 7: Delete webapp_user

SQL
DROP USER 'webapp_user'@'localhost';

FLUSH PRIVILEGES; -- Always flush after changing users

What it does: Permanently removes the webapp_user account and all its associated privileges.

Best Practices for Web Application Users

  • Principle of Least Privilege: Grant only the specific privileges absolutely necessary for the application to function. If it only reads data, grant SELECT. If it writes, INSERT, UPDATE, DELETE. Avoid ALL PRIVILEGES.
  • Specific Host: Whenever possible, specify localhost or a specific internal IP address for webapp_user connections. Avoid '%' unless absolutely necessary and with strong network security.
  • Strong Passwords: Use long, complex, and unique passwords for database users.
  • Separate Users: Create different users for different purposes (e.g., webapp_user for daily operations, migration_user for schema changes, reporting_user with only SELECT access).
  • Regular Audits: Periodically review user accounts and their granted privileges.
  • Never use root in application code! This cannot be stressed enough.

Notes:

  • Database user accounts control access to your database.
  • Privileges define what actions a user can perform (e.g., SELECT, INSERT, UPDATE).
  • CREATE USER: To create a new database user.
  • GRANT: To assign privileges to a user on specific database objects.
  • REVOKE: To remove privileges from a user.
  • FLUSH PRIVILEGES: To reload the grant tables after changes.
  • SHOW GRANTS: To inspect a user’s privileges.
  • DROP USER: To delete a user account.
  • Crucial Security Best Practice: Implement the principle of least privilege for your web application database users.

Small Exercise

Using your scriptbuzz_db:

  1. Create a new user called reporting_analyst who can connect from localhost with a password of your choice.
  2. Grant this user SELECT privilege only on the products and orders tables within scriptbuzz_db.
  3. Flush privileges.
  4. Log in as reporting_analyst (you might need to open a new terminal or connection, e.g., mysql -u reporting_analyst -p).
    • Try to SELECT * FROM scriptbuzz_db.products; (Should work).
    • Try to INSERT INTO scriptbuzz_db.products (...) VALUES (...); (Should fail with an access denied error).
    • Try to SELECT * FROM scriptbuzz_db.users; (Should fail with an access denied error).
  5. Log back in as root (or your administrative user).
  6. Revoke SELECT privilege from reporting_analyst on the orders table.
  7. Drop the reporting_analyst user.
  8. Flush privileges.

You’ve now taken a crucial step into database security and administration! This is vital for deploying real-world applications safely. Next, we’ll finish up with some final useful SQL functions and tools.

📘 Continue to the Next Chapter

Advanced Functions & Control Flow

Dive deeper into SQL with advanced functions, CASE statements, and control flow techniques for writing more powerful queries.

➡️

🚀 Explore Popular Learning Tracks