Skip to main content

Database Security and User Management

Marketplaces host sensitive information: customer details, order histories, and payment records. Protecting that data requires thoughtful user management and permission design. In this lesson we map security concepts directly to the marketplace schema using MySQL.

Learning Goals

  • Create database accounts aligned with business roles
  • Grant the minimum privileges necessary
  • Build roles (groups of privileges) for support, sellers, finance, and analysts
  • Understand options for row-level access in MySQL

Users and Roles​

MySQL supports roles that bundle privileges. Start by creating the application users:

Create application accounts
CREATE USER 'support_app'@'%'   IDENTIFIED BY 'change_me_support';
CREATE USER 'seller_app'@'%'    IDENTIFIED BY 'change_me_seller';
CREATE USER 'finance_app'@'%'   IDENTIFIED BY 'change_me_finance';
CREATE USER 'analytics_app'@'%' IDENTIFIED BY 'change_me_analytics';

Next, define roles and grant only the permissions each role needs:

Define least-privilege roles
CREATE ROLE role_support;
CREATE ROLE role_seller_ops;
CREATE ROLE role_finance;
CREATE ROLE role_analytics;

-- Support can view customers and orders
GRANT SELECT ON ecommerce_store.customer TO role_support;
GRANT SELECT ON ecommerce_store.orders TO role_support;
GRANT SELECT ON ecommerce_store.order_item TO role_support;
GRANT SELECT ON ecommerce_store.product TO role_support;

-- Seller ops can manage products and view order items
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce_store.product TO role_seller_ops;
GRANT SELECT ON ecommerce_store.order_item TO role_seller_ops;
GRANT SELECT ON ecommerce_store.orders TO role_seller_ops;

-- Finance handles transactions
GRANT SELECT, INSERT, UPDATE ON ecommerce_store.transactions TO role_finance;
GRANT SELECT ON ecommerce_store.orders TO role_finance;
GRANT SELECT ON ecommerce_store.customer TO role_finance;

-- Analysts receive read-only access
GRANT SELECT ON ecommerce_store.category TO role_analytics;
GRANT SELECT ON ecommerce_store.seller TO role_analytics;
GRANT SELECT ON ecommerce_store.customer TO role_analytics;
GRANT SELECT ON ecommerce_store.product TO role_analytics;
GRANT SELECT ON ecommerce_store.orders TO role_analytics;
GRANT SELECT ON ecommerce_store.order_item TO role_analytics;
GRANT SELECT ON ecommerce_store.transactions TO role_analytics;

-- Attach users to roles and set defaults
GRANT role_support TO 'support_app'@'%';
GRANT role_seller_ops TO 'seller_app'@'%';
GRANT role_finance TO 'finance_app'@'%';
GRANT role_analytics TO 'analytics_app'@'%';

SET DEFAULT ROLE role_support TO 'support_app'@'%';
SET DEFAULT ROLE role_seller_ops TO 'seller_app'@'%';
SET DEFAULT ROLE role_finance TO 'finance_app'@'%';
SET DEFAULT ROLE role_analytics TO 'analytics_app'@'%';

Schema and Object Ownership​

In MySQL, the database (ecommerce_store) is the schema boundary. Grant privileges at the database level when appropriate:

GRANT SELECT ON ecommerce_store.* TO role_analytics;

MySQL does not have default privileges for future tables, so remember to grant access when new tables are created.

Row-Level Access Strategies in MySQL​

MySQL does not provide native row-level security (RLS). Common alternatives include:

  • Separate database users per seller and grant access only to their rows via filtered views.
  • Stored procedures that accept a seller ID and enforce checks inside the routine.
  • Application-level filtering, where the app ensures every query includes the seller/customer scope.

Here is a view-based pattern that ties a database user to a seller record:

Map database users to sellers
CREATE TABLE seller_accounts (
  db_user   VARCHAR(128) PRIMARY KEY,
  seller_id INT NOT NULL
);

CREATE VIEW vw_seller_products
SQL SECURITY DEFINER
AS
SELECT p.*
FROM product AS p
JOIN seller_accounts AS sa ON sa.seller_id = p.seller_id
WHERE sa.db_user = CURRENT_USER();

Grant SELECT on the view instead of the base table for seller users. This keeps access scoped to each seller's data.

Auditing and Password Hygiene​

  • Force periodic password changes:

    ALTER USER 'seller_app'@'%' PASSWORD EXPIRE INTERVAL 180 DAY;

  • Require SSL/TLS connections: configure your MySQL server to enforce secure channels for remote clients.

  • Log privileged operations with the MySQL audit plugin or a custom audit_log table.

  • Remove unused accounts quickly (DROP USER ...) and lock accounts during investigations:

    ALTER USER 'seller_app'@'%' ACCOUNT LOCK;

Summary​

  • Create separate users for each application or integration point.
  • Grant roles aligned to job duties: support (read orders), seller ops (manage products), finance (manage transactions), analytics (read-only).
  • MySQL lacks native RLS; use views, stored procedures, or application-level filters to scope data.
  • Review privileges regularly and enforce password policies and secure connections.

Quiz​

Show quiz

  1. Which privilege lets finance update only the transaction status without editing other columns?
    A) GRANT UPDATE ON ecommerce_store.transactions TO role_finance;
    B) GRANT UPDATE (status) ON ecommerce_store.transactions TO role_finance;
    C) GRANT ALL ON ecommerce_store.transactions TO role_finance;
    D) GRANT SELECT ON ecommerce_store.transactions TO role_finance;

  2. Why create roles such as role_support instead of granting privileges directly to users?
    A) Roles are faster
    B) Roles allow you to manage permissions once and assign them to many users
    C) Users cannot hold direct privileges
    D) Roles automatically enable RLS

  3. Which statement is true about row-level security in MySQL?
    A) MySQL has built-in RLS policies like PostgreSQL
    B) MySQL uses ALTER TABLE ... ENABLE ROW LEVEL SECURITY
    C) MySQL requires views, procedures, or application filtering for row-level access
    D) MySQL blocks row-level filters by design

  4. True or False: MySQL automatically applies privileges to future tables in a database.
    A) True
    B) False

Answers

  1. B
  2. B
  3. C
  4. B