MariaDB Tutorial / Create Users and Grant Only Required Privileges

A MariaDB account is identified by a user name and host combination, and GRANT assigns privileges at a defined scope. Separate administration from application connections and test both permitted and rejected operations using the actual restricted account.

Distinguish account identity, origin and privilege scope

The same name at localhost and 127.0.0.1 can describe different account definitions. Connection method and name resolution affect matching; CURRENT_USER() identifies the account used for privilege checking. Using root for routine application reads makes a least-required privilege policy difficult to enforce. Troubleshoot Local and Remote MariaDB Connections checks whether the connection uses the intended privilege account; Connect Python to MariaDB and Use Parameterized Queries demonstrates a complete program using restricted access.

Replace the sample password with a unique value. Use the password prompt or an access-restricted option file rather than place real credentials directly in command arguments or published records.

Check the MariaDB documentation for the syntax and conditions used here: create user, GRANT.

Prepare data separately from account administration

Connect to the learning server as course_user and select shopcourse. The command-line and UTF-8 SQL-file guide explains how to save the code as a UTF-8 file and execute it with SOURCE C:/mariadb-lab/lesson.sql;, replacing the path with the actual file. Run SQL inside the MariaDB client, rather than at the operating-system prompt.

Check SELECT VERSION(), DATABASE(); and SHOW TABLES; before creating c43_customers, c43_products, c43_orders, c43_items. These names belong only to this exercise. If they exist, inspect their definitions and rows first; remove them only when you have established that they are disposable exercise objects. Do not replace existing application tables.

USE shopcourse;
CREATE TABLE c43_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c43_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c43_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c43_order_customer FOREIGN KEY (customer_id)
  REFERENCES c43_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c43_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c43_item_order FOREIGN KEY (order_id)
  REFERENCES c43_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c43_item_product FOREIGN KEY (product_id)
  REFERENCES c43_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c43_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c43_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c43_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c43_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

The SQL retains Korean sample names and city strings, while the explanations use stable customer and product IDs. Products 1–4 are a notebook, pen, mug and lamp. Customers 1 and 3 have the Seoul city value; customers 2 and 4 have Busan and Daejeon values. Preserve the supplied UTF-8 literals when reproducing the results.

A learning account with the relevant database privileges can prepare tables. CREATE USER and GRANT require a separate appropriately privileged administrator connection. If shop_reader43 already exists, choose another exercise name; do not overwrite or delete an existing account.

Grant SELECT on exactly one product table

Run this block as the administrator after replacing the demonstration password. Treat account-definition output as potentially sensitive and avoid publishing secret values:

CREATE USER 'shop_reader43'@'127.0.0.1' IDENTIFIED BY 'Replace_With_Unique_Lab_Password!';
GRANT SELECT ON shopcourse.c43_products TO 'shop_reader43'@'127.0.0.1';
SHOW GRANTS FOR 'shop_reader43'@'127.0.0.1';

The account can read c43_products but has not been granted customer/order reads, INSERT, UPDATE or DDL. CREATE USER and GRANT do not routinely need a following FLUSH PRIVILEGES. Use supported account statements instead of directly editing privilege tables.

Open a genuinely restricted connection and test both outcomes

Run the client command at the operating-system terminal and enter the new password at its prompt. Do not keep using the course_user session to judge the restricted account:

mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=shop_reader43 --password --default-character-set=utf8mb4 shopcourse

SELECT USER(),CURRENT_USER();
SELECT product_id,product_name FROM c43_products ORDER BY product_id;

The four products should be readable. Execute each following statement separately in that restricted session; both should be rejected:

SELECT * FROM c43_customers;
UPDATE c43_products SET stock=stock WHERE product_id=1;

Do not immediately grant ALL PRIVILEGES because a denial occurred. Establish whether the actual requirement needs reads or writes. Where roles are used, inspect active-role privileges as well as direct grants.

Verify revocation and review account lifetime

As administrator, revoke the table read and verify the denial through a new restricted connection:

REVOKE SELECT ON shopcourse.c43_products FROM 'shop_reader43'@'127.0.0.1';
SHOW GRANTS FOR 'shop_reader43'@'127.0.0.1';

After losing its only database privilege, selecting shopcourse during connection can itself be rejected. In that case connect without a default database and test the fully qualified table name. Existing-session privilege refresh can depend on scope, so do not rely on a single already-open session.

USAGE can indicate an existing account without substantive privileges at the shown scope. DROP USER is not a complete cleanup of stored data or objects depending on that account as DEFINER. Review application connections, views and routines before retiring an account.

Remove only this exercise’s new account and tables

Confirm shop_reader43 was created solely for this exercise, close its connections and remove it as administrator. Other application accounts are outside this cleanup. Remove child tables before referenced parents:

DROP USER 'shop_reader43'@'127.0.0.1';

Before repeating the exercise, inspect the selected database, actual table definitions and stored rows, and back up anything that must be kept. Drop only this article’s disposable practice objects, with child tables before their referenced parents. DROP TABLE removes both definitions and rows and cannot be undone by an ordinary ROLLBACK; keep cleanup separate from transaction experiments.

DROP TABLE shopcourse.c43_items;
DROP TABLE shopcourse.c43_orders;
DROP TABLE shopcourse.c43_products;
DROP TABLE shopcourse.c43_customers;