ColumnStore Database User Management

You are viewing an old version of this article. View the current version here.

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.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.