Mariadb – How to use MariaDB’s new “Invisible” column type

mariadb-10.3

I have been playing around with this new column type. The use case being that I want to hide this column from a client issuing a "Select *" query.

But the column seems to behave like any normal column and is not hidden. I have enabled the Invisible column type by using the following command:

ALTER TABLE MyTable ADD COLUMN `TestA` INT INVISIBLE;

Reading up in the MariaDB documentation, the examples provided are only with Create statements. Which makes me wonder if one can only enable this feature when creating a table and NOT with an Alter statement. Can anybody shed some light?

Best Answer

The INVISIBLE is an attribute of the column it can be set CREATE TABLE and ALTER TABLE statements.

I've updated the reference page document to show this.

 CREATE TABLE t (x INT, y INT INVISIBLE, z INT INVISIBLE NOT NULL DEFAULT 4);

 DESC t;

Field | Type    | Null | Key | Default | Extra    
:---- | :------ | :--- | :-- | :------ | :--------
x     | int(11) | YES  |     | null    |          
y     | int(11) | YES  |     | null    | INVISIBLE
z     | int(11) | NO   |     | 4       | INVISIBLE

ALTER TABLE t MODIFY x INT INVISIBLE, MODIFY y INT, MODIFY z INT NOT NULL DEFAULT 4

DESC t

Field | Type    | Null | Key | Default | Extra    
:---- | :------ | :--- | :-- | :------ | :--------
x     | int(11) | YES  |     | null    | INVISIBLE
y     | int(11) | YES  |     | null    |          
z     | int(11) | NO   |     | 4       |

db<>fiddle here

MariaDB's `INVISIBLE` Columns

It seems that the intention is to permit MariaDB users to continue to use SELECT * which is almost always against a DBA's best practices. So now you can use SELECT *, and then add to the table without mutating the result returned by SELECT *.

CREATE TABLE users ( id INT, username varchar(255) );

And you have an export that dumps users with SELECT *. Later if you wish to add a column, you can mark it as INVISIBLE and maintain the result

ALERT TABLE users
  ADD COLUMN password varchar(255) INVISIBLE;

Post- ADD COLUMN you're still hilariously safe without learning that no one in their right mind would use SELECT * for in that case.

Though only one type of invisibility is exposed to the user, MariaDB internally has four different types of invisibility. For more information see, MDEV-10177 Invisible columns. Invisibility also effects INSERT, find more information on the ticket

Other Databases

As an aside, Oracle calls this feature INVISIBLE, and DB2 calls it IMPLICITLY HIDDEN. In other dialects of SQL where this isn't supported you would just create a VIEW,

CREATE user_view AS SELECT id, username FROM users;

MySQL Plugin Utility – How to Use for Authentication and Authorization

The .ini needs to contain the plugin followed by the components.

The document Authentication Plugin - Unix Socket hints that the component name is unix_socket.

So the correct contents of the file should be:

echo -e 'auth_socket\nunix_socket' >> /usr/lib/mariadb/plugin/unix_socket.ini

As a workaround use plugin-load=auth_socket in /etc/my.cnf.d/mariadb-server.cnf seems also sufficient.

Best Answer

Related Solutions

What Are ‘Invisible Columns’ in MariaDB and How to Use Them?

MariaDB's INVISIBLE Columns

Other Databases

MySQL Plugin Utility – How to Use for Authentication and Authorization

MariaDB's `INVISIBLE` Columns