Grant selected columns access to user and run select * without error in mysql

Question

My requirement is to grant only few columns access to a user and still have the ability to run select * without error. Right now I am getting error -

select command denied to user ''@' ' for column 'id' in table

Reason: I have a table with 100 columns, out of which only two need to be barred from access. In such a scenario, a user has to write select 98 columns from table instead of a simple select * from table.

Is there a way we could make it work?

Answer 1

I don't have experience as a DBA for MySQL, but here is what I would do in MS SQL Server:

1. Put the users in appropriate security groups.
2. Configure each security group to have its own default schema.
3. Create a view for each group including the appropriate columns for that group and put it in the default schema for the group.

Added bonus:
SELECT * is a generally a bad idea. Prohibit anyone from running SELECT * on the table by adding an errant computed column to the table. In MS SQL Server, this would look like this:

CREATE TABLE [dbo].[NoSelectStar](
  [id] [int] IDENTITY(1,1) NOT NULL,
  [a] [varchar](7) NULL,
  [NoSelectStar]  AS ((1)/(0)),  -- This is the one that deliberately causes problems.
 CONSTRAINT [pkNoSelectStar] PRIMARY KEY CLUSTERED 
(
  [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

insert NoSelectStar
values ('a'), ('b'), ('c')

--  This will work
select a
from NoSelectStar

--  This will fail
select *
from NoSelectStar