Privileges and Roles in SQL: Permissions with Examples

6
0

Introduction

An online store’s support team may need to check order status, while only authorized staff should change or delete orders. Giving every account unrestricted access makes these boundaries difficult to maintain.

Privileges define the actions an account can perform on database objects. Roles collect privileges under a reusable name, helping administrators manage access consistently across a team.

What Are Database Privileges?

A privilege is permission to perform a particular operation on a database object, such as reading a table or inserting rows.

Privilege

What it permits

SELECT

Read data.

INSERT

Add rows.

UPDATE

Change existing values.

DELETE

Remove rows.

Creating, changing, and removing database structures can require additional administrative permissions or ownership rights.

Privileges Have a Scope

An account can have permission to read one table without having access to every table in the database. A permission’s scope determines where it applies.

Scope

What it covers

A specific object

Access to a particular table or other database object.

A database or schema

Permissions associated with a larger collection of objects.

Server administration

Broader operations such as managing accounts or server settings.

Grant only the actions and scope required for the task. This is the principle of least privilege. A support account that only checks orders needs permission to read the relevant table.

Users and Connection Access

A database account identifies a person or application. Authentication verifies that identity; privileges determine what the account can do after connecting.

Creating an account and configuring its permissions are separate tasks. The administrator must decide both who can connect and what access that account should receive.

Connections may also be restricted according to where a client connects from. A permitted connection origin does not bypass authentication or network restrictions.

Managing Permissions with Roles

A role is a named collection of privileges that can be assigned to accounts.

Suppose Raj and Asha both work in customer support. Each needs to read orders. An administrator can grant SELECT to a role named support_reader, then assign that role to both accounts.

If the team later grows to 50 people, the shared permission is still maintained on one role. Changing the role’s privileges avoids editing the same grant separately for every member.

Example: Give Support Staff Read Access

Assume a database named shop contains an orders table with order_id and status columns. Raj and Asha should be able to read these records without adding, changing, or deleting them.

Use new ordinary accounts, an administrator-owned table, and no additional grants that provide access. Run the setup as an administrator with the necessary permissions, and replace the example passwords before use.

-- Accounts include a username and a permitted client host.
-- localhost is used here for local connections.
-- For remote or container clients, use the host seen by the server.
CREATE USER 'raj'@'localhost'
IDENTIFIED BY 'Replace_With_Raj_Strong_Password!7';
CREATE USER 'asha'@'localhost'
IDENTIFIED BY 'Replace_With_Asha_Strong_Password!8';
CREATE ROLE 'support_reader';
GRANT SELECT ON shop.orders
TO 'support_reader';
GRANT 'support_reader'
TO 'raj'@'localhost', 'asha'@'localhost';
-- Assigning a role does not necessarily activate it.
-- Activate this role automatically at future logins.
SET DEFAULT ROLE 'support_reader'
TO 'raj'@'localhost', 'asha'@'localhost';

The setup creates the accounts, adds SELECT to support_reader, and assigns the role to Raj and Asha. Once the role’s permissions apply in their sessions, both accounts can read the orders.

INSERT, UPDATE, and DELETE are not included in the role, so it does not provide permission to modify the records.

The diagram shows how one role supplies the same permission to both accounts.

Privileges Through Roles

Privileges Through Roles

Check the Result

Connect as Raj or Asha and run the read query.

-- Run in a session connected as Raj or Asha.
-- New logins use the default role.
-- This also activates the role in an already open session.
SET ROLE 'support_reader';
-- Optional: inspect the active roles.
-- SELECT CURRENT_ROLE();
SELECT order_id, status
FROM shop.orders
ORDER BY order_id;

With the sample records supplied in the setup files, the result is:

order_id

status

101

PLACED

102

SHIPPED

103

DELIVERED

Both accounts can retrieve the same three orders. Under the stated assumptions, attempts to insert, update, or delete records are denied because no write privileges were granted.

Inspecting and Removing Access

Review Accounts and Permissions

Administrative queries help inspect account properties and permission assignments. Use meaningful role names and review the relevant grants. Password presence alone is not a reliable way to identify an account’s intended purpose.

-- Run as an administrator with access to mysql.user.
-- This table includes both user and role account records.
-- account_locked does not reliably distinguish users from roles.
SELECT User, Host, account_locked
FROM mysql.user
WHERE User IN ('asha', 'raj', 'support_reader')
ORDER BY User, Host;
-- Inspect the privileges assigned to the shared role.
SHOW GRANTS FOR 'support_reader';
-- Include the assigned role's privileges when inspecting Raj.
-- SHOW GRANTS does not activate the role.
SHOW GRANTS FOR 'raj'@'localhost'
USING 'support_reader';

These checks show the account configuration and the permission assigned to the shared role. In this example, support_reader has SELECT on orders.

Remove Membership or Change the Shared Role

REVOKE can remove a role from one account or remove a privilege from the role itself. These are different administrative changes.

-- Remove Raj's role assignment.
REVOKE 'support_reader'
FROM 'raj'@'localhost';
-- Remove the shared role's permission to read orders.
REVOKE SELECT ON shop.orders
FROM 'support_reader';

Removing Raj’s membership leaves Asha’s membership in place. Removing SELECT from support_reader changes the permission supplied by that role to its members.

A user may still have the same permission through a direct grant or another role. Review those other grants before concluding that all access has been removed.

Summary

Privileges define which actions an account may perform and on which database objects. Roles group those permissions so administrators can manage shared access consistently. Give accounts only the access they need, distinguish account creation from permission assignment, and verify the permissions available in a user’s session. When access changes, review both role membership and any other grants that may still provide the same permission.

CS Core

Read Similar Blogs

Comments0