Sshd mysql

Sissejuhatus

Õpetus kuidas panna nii süsteem kui ssh tunnistama kasutajaid kes asuvad mysql baasis.

Kasutamiseks mõeldud linuxil

Install ja sedaistus

Süsteemi on vaja paigaldada kaks paketti

#mysql-pam 
#libnss-mysql (versiooniks 1.5 näites)

Andmebaasi auth struktuur

CREATE TABLE IF NOT EXISTS `grouplist` (
  `rowid` int(11) NOT NULL auto_increment,
  `gid` int(11) NOT NULL default '0',
  `username` char(16) NOT NULL default ,
  PRIMARY KEY  (`rowid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

CREATE TABLE IF NOT EXISTS `groups` (
  `name` varchar(16) NOT NULL default ,
  `password` varchar(34) NOT NULL default 'x',
  `gid` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`gid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5001 ;

CREATE TABLE IF NOT EXISTS `users` (
  `username` varchar(16) NOT NULL default ,
  `uid` int(11) NOT NULL auto_increment,
  `gid` int(11) NOT NULL default '5000',
  `gecos` varchar(128) NOT NULL default ,
  `homedir` varchar(255) NOT NULL default ,
  `shell` varchar(64) NOT NULL default '/bin/bash',
  `password` varchar(34) NOT NULL default 'x',
  `lstchg` bigint(20) NOT NULL default '1',
  `min` bigint(20) NOT NULL default '0',
  `max` bigint(20) NOT NULL default '99999',
  `warn` bigint(20) NOT NULL default '0',
  `inact` bigint(20) NOT NULL default '0',
  `expire` bigint(20) NOT NULL default '-1',
  `flag` bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (`uid`),
  UNIQUE KEY `username` (`username`),
  KEY `uid` (`uid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5001 ;

Lisame ka ühe kasutaja ja grupi

INSERT INTO users (username,gecos,homedir,password)
   VALUES ('kasutaja', 'kasutaja nimi', '/home/kasutaja', ENCRYPT('kasutaja'));

INSERT INTO groups (name)
   VALUES ('kasutaja');

INSERT INTO grouplist (gid,username)
   VALUES (5000,'kasutaja');

/etc/libnss-mysql.cfg

getpwnam    SELECT username,'x',uid,gid,gecos,homedir,shell \
            FROM users \
            WHERE username='%1$s' \
            LIMIT 1

getpwuid    SELECT username,'x',uid,gid,gecos,homedir,shell \
            FROM users \
            WHERE uid='%1$u' \
            LIMIT 1

getspnam    SELECT username,password,lstchg,min,max,warn,inact,expire,flag \
            FROM users \
            WHERE username='%1$s' \
            LIMIT 1

getpwent    SELECT username,'x',uid,gid,gecos,homedir,shell \
            FROM users

getspent    SELECT username,password,lstchg,min,max,warn,inact,expire,flag \
            FROM users

getgrnam    SELECT name,password,gid \
            FROM groups \
            WHERE name='%1$s' \
            LIMIT 1x

getgrgid    SELECT name,password,gid \
            FROM groups \
            WHERE gid='%1$u' \
            LIMIT 1

getgrent    SELECT name,password,gid \
            FROM groups

memsbygid   SELECT username \
            FROM grouplist \
            WHERE gid='%1$u'

gidsbymem   SELECT gid \
            FROM grouplist \
            WHERE username='%1$s'

host        localhost
database    auth
username    kasutaja
password    parool

teine fail mis vaja seadistada /etc/libnss-mysql-root.cfg

sinna piisab kui lisada andmebaasi kasutajanimi ning parool

username    kasutaja
password    parool

ja /etc/pam.d/ssh

auth sufficient pam_mysql.so \
verbose=1 \
user=kasutaja \
passwd=parool \
host=localhost \
db=auth \
table=users \
usercolumn=username \
passwdcolumn=password crypt=4

account sufficient pam_mysql.so \
verbose=1 \
user=kasutaja \
passwd=parool \
host=localhost \
db=auth \
table=users \
usercolumn=username \
passwdcolumn=password crypt=4

password sufficient pam_mysql.so \
verbose=1 \
user=kasutaja \
passwd=parool \
host=localhost \
db=auth \
table=users \
usercolumn=username \
passwdcolumn=password crypt=4

lõpuks ka /etc/nsswitch.conf

passwd: files mysql
shadow: files mysql
group:  files mysql

Ning nii toimib.

JJ