ColumnStore Database User Management
Contents
Basic user management
MariaDB ColumnStore allows permissions to be set for user accounts. The syntax of these grants follows the standard MariaDB syntax (see GRANT).
For the root user, ColumnStore comes with full privileges. In order to set/restrict user accounts, privileges must be given/restricted. ColumnStore uses a dedicated schema called infinidb_vtable for creation of all temporary tables used for ColumnStore query processing. The root user account has been given permission to this account by default, but full permission MUST be given for all user accounts to this schema:
grant ALL on infinidb_vtable.* to user_account;
where user_account = user login, server and password characteristics
Further permissions/restrictions can now be placed on any existing objects (tables, functions, procedures, views) for any access/limitations wanting to be placed on users: Example to give a user that has a password full access to all tables for a database (after the above grant has been given):
use mysql; grant ALL on my_schema.* to ‘someuser’@’somehost’ identified by ‘somepassword’; flush privileges;
Example to give a user that has a password read-only access to only 1 table (after the above grant has been given):
use mysql; grant SELECT on my_schema.table1 to ‘someuser’@’somehost’ identified by ‘somepassword’; flush privileges;
PAM authentication
Starting with ColumnStore 1.0.8, ColumnStore includes the necessary authentication plugin for PAM support. For general details see pam-authentication-plugin but here we will outline the steps necessary to configure this for os authentication specific to a ColumnStore installation.
First ensure that the mysql user has read access to the /etc/shadow file, in this example a group is used to facilitate this:
$ sudo groupadd shadow $ sudo usermod -a -G shadow mysql $ sudo chown root:shadow /etc/shadow $ sudo chmod g+r /etc/shadow
Create a pam.d entry to configure unix password authentication:
$ vi /etc/pam.d/mysql auth required pam_unix.so account required pam_unix.so
Load the auth_pam.so plugin and create a user:
$ mcsmysql > INSTALL SONAME 'auth_pam'; > GRANT SELECT ON test.* TO david IDENTIFIED VIA pam; > GRANT ALL ON infinidb_vtable.* TO david;
Restart ColumnStore so that the mariadb server process picks up the auth plugin and group changes:
$ sudo su - $ mcsadmin restartSystem
Now attempt to login to verify correct setup, entering the unix password for the account david when prompted:
$ mcsmysql -u david -p
If this still fails, try restartSystem once more and try logging in again as this seems to resolve the issue.