Authenticating comdb2 session

Password-based Authentication

A comdb2 session can be authenticated by setting username and password using set user and set password, just after opening the connection. The allowed limits for username and password are 15 and 18 characters respectively.

set user 'foo_user'
set password 'foo_password'

Certificate-based Authentication

A comdb2 session can also be authenticated by setting the client certificate/key using set ssl_cert and set ssl_key, just after opening the connection.

set ssl_cert /path/to/certificate
set ssl_key /path/to/key

Adding/deleting users to database

Users can be added and deleted through the put password statement. Once authentication is enabled only users with OP credentials can add or delete users.

To add a new user or update the password of an existing user: put password '<password>' for <user>

put password 'foo_password' for 'foo_user'
put password 'op_password' for 'op_user'

To delete a user: put password off for <user>

put password off for 'foo_user'

Users can be granted OP credentials using grant OP. Only OP users can run this statement after authentication is enabled.

grant OP to 'op_user'

Existing users can be seen by performing a query on the comdb2_users table, the result set will give user names along with their OP status.

select * from comdb2_users

select * from comdb2_users
(username='foo_user', isOP='N')
(username='op_user', isOP='Y')

This information is part of the database's metadata, therefore copying the database will preserve it.

Turning on Authentication

The following SQL statement will turn on authentication

put authentication on

It can be turned off by running

put authentication off

Only users which have OP credentials can turn on/off authentication.

As a convenience, if create_default_user tunable is turned on, turning on authentication would automatically create a new user with name 'default' and empty password if it doesn't already exist. This newly created user doesn't have any privileges to any objects, but can be granted any privilege by an OP user. It will be used for every unauthenticated session.

Granting/Revoking table privileges to users

Comdb2 defines the following privileges on a table:

OP users can grant table privileges using grant

grant read on t1 to 'foo_user'

privileges can be revoked using the revoke statement

revoke read on t1 from 'foo_user'

Existing privileges can be seen by running a query on comdb2_tablepermissions

select * from comdb2_tablepermissions

testdb> set user foo_user
[set user foo_user] rc 0
testdb> set password foo_password
[set password foo_password] rc 0
testdb> select * from comdb2_tablepermissions /* Can only see tables on which user has read access */
(tablename='t1', username='default', READ='N', WRITE='N', DDL='N')
(tablename='t1', username='foo_user', READ='Y', WRITE='N', DDL='N')
(tablename='t1', username='op_user', READ='Y', WRITE='Y', DDL='Y')
[select * from comdb2_tablepermissions] rc 0
testdb> set user op_user
[set user op_user] rc 0
testdb> set password op_password
[set password op_password] rc 0
testdb> select * from comdb2_tablepermissions 
(tablename='sqlite_stat1', username='default', READ='N', WRITE='N', DDL='N')
(tablename='sqlite_stat1', username='foo_user', READ='N', WRITE='N', DDL='N')
(tablename='sqlite_stat1', username='op_user', READ='Y', WRITE='Y', DDL='Y')
(tablename='sqlite_stat4', username='default', READ='N', WRITE='N', DDL='N')
(tablename='sqlite_stat4', username='foo_user', READ='N', WRITE='N', DDL='N')
(tablename='sqlite_stat4', username='op_user', READ='Y', WRITE='Y', DDL='Y')
(tablename='t1', username='default', READ='N', WRITE='N', DDL='N')
(tablename='t1', username='foo_user', READ='Y', WRITE='N', DDL='N')
(tablename='t1', username='op_user', READ='Y', WRITE='Y', DDL='Y')
(tablename='t2', username='default', READ='N', WRITE='N', DDL='N')
(tablename='t2', username='foo_user', READ='N', WRITE='N', DDL='N')
(tablename='t2', username='op_user', READ='Y', WRITE='Y', DDL='Y')
[select * from comdb2_tablepermissions] rc 0

User Schemas

Comdb2 supports tables in user's namespace. This allows multiple users to have tables with same name.

To enable it, this lrl option needs to be enabled

allow_user_schema

The following example will create multiple users and separate table (with same name) for each user. Querying comdb2_tables from op user account will show all the tables.

put password 'user' for 'user'
put password 'user1' for 'user1'
put password 'user2' for 'user2'
grant op to user

set user user
set password user
put authentication on /* Only op can turn on the authentication. */

select * from comdb2_users
(username='user', isOP='Y')
(username='user1', isOP='N')
(username='user2', isOP='N')
[select * from comdb2_users] rc 0

set user user1
set password user1
create table test { schema {int t} keys { "T1" = t}}$$
insert into test values(1)
(rows inserted=1)

set user user2
set password user2
create table test { schema {int t} keys { "T1" = t}}$$
insert into test values(2)
(rows inserted=1)

set user user1
set password user1
select * from test
(t=1)

set user user2
set password user2
select * from test
(t=2)

set user user
set password user
select * from comdb2_tables
(tablename='sqlite_stat1')
(tablename='sqlite_stat2')
(tablename='sqlite_stat4')
(tablename='test@user1')
(tablename='test@user2')