How to restrict a database user to specific tables

Granting SELECT on all tables in a database is the convenient option, but it gives external tools access to everything — including tables that contain sensitive data you may not want to expose.

This guide covers how to grant access to specific tables only, for both PostgreSQL and MySQL.

When to use this

You have a database with tables like:

orders
order_items
products
customers
payment_methods     ← contains card data
user_credentials    ← contains hashed passwords
admin_sessions      ← internal use only

You want your analytics tool to access orders, order_items, products, and customers — but not payment_methods, user_credentials, or admin_sessions.

The solution: grant SELECT per table, not on the entire schema.

PostgreSQL: table-level grants

Grant access to specific tables

-- Create the user first
CREATE USER analytics_reader WITH PASSWORD 'strong-password-here';
GRANT CONNECT ON DATABASE your_db TO analytics_reader;
GRANT USAGE ON SCHEMA public TO analytics_reader;

-- Grant SELECT only on the tables you want to expose
GRANT SELECT ON TABLE public.orders TO analytics_reader;
GRANT SELECT ON TABLE public.order_items TO analytics_reader;
GRANT SELECT ON TABLE public.products TO analytics_reader;
GRANT SELECT ON TABLE public.customers TO analytics_reader;

The user can now run SELECT on those four tables. Any attempt to query payment_methods will return:

ERROR: permission denied for table payment_methods

Do NOT set default privileges

When using table-level grants, skip the ALTER DEFAULT PRIVILEGES command. Default privileges apply to all future tables — if you set them, new tables created later will automatically become visible to the user.

Only set default privileges if you intend the user to see all tables, including ones added in the future.

Verify what the user can see

SELECT table_name
FROM information_schema.role_table_grants
WHERE grantee = 'analytics_reader'
  AND privilege_type = 'SELECT';

Add a table later

When you create a new table and want to expose it:

GRANT SELECT ON TABLE public.new_table TO analytics_reader;

For tables you do not grant, the user gets no access automatically.

MySQL: table-level grants

Grant access to specific tables

-- Create the user
CREATE USER 'analytics_reader'@'%' IDENTIFIED BY 'strong-password-here';

-- Grant SELECT on specific tables only
GRANT SELECT ON your_db.orders TO 'analytics_reader'@'%';
GRANT SELECT ON your_db.order_items TO 'analytics_reader'@'%';
GRANT SELECT ON your_db.products TO 'analytics_reader'@'%';
GRANT SELECT ON your_db.customers TO 'analytics_reader'@'%';

FLUSH PRIVILEGES;

Verify

SHOW GRANTS FOR 'analytics_reader'@'%';

Output will list only the tables you granted:

GRANT SELECT ON `your_db`.`orders` TO `analytics_reader`@`%`
GRANT SELECT ON `your_db`.`order_items` TO `analytics_reader`@`%`
...

Any attempt to query payment_methods returns:

ERROR 1142 (42000): SELECT command denied to user 'analytics_reader'@'...' for table 'payment_methods'

What counts as sensitive data worth hiding

The answer varies by context, but common candidates:

Always restrict:

• Payment card data, bank account numbers
• Hashed or plaintext passwords / tokens / API keys
• Social security numbers, national ID numbers
• Health records

Consider restricting:

• Email addresses and phone numbers (depends on your use case and local regulations)
• Internal cost and margin data
• HR and payroll tables
• Admin and session tables used only by your application

Usually fine to expose for analytics:

• Order and transaction records (without payment details)
• Product and inventory data
• Aggregated usage or event data
• Support ticket summaries (without private message bodies)

Schema-level isolation (advanced)

An alternative to table-level grants is to put the tables you want to expose in a separate schema, and grant USAGE + SELECT only on that schema.

-- Create a schema for analytics-safe tables
CREATE SCHEMA analytics_views;

-- Create a view over the safe parts of a table
CREATE VIEW analytics_views.orders AS
  SELECT id, created_at, status, total_amount, region
  FROM public.orders;
  -- Note: excludes customer_email, shipping_address, etc.

-- Grant access to only the analytics schema
GRANT USAGE ON SCHEMA analytics_views TO analytics_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics_views TO analytics_reader;

This approach lets you also strip sensitive columns from tables that are otherwise safe to expose — for example, showing an orders view without customer_email or billing_address.

Summary

Start with per-table grants if you need to hide a few specific tables. Move to schema + views if you also need to hide specific columns within a table.