Настройка Диспетчера Службы Имен для Работы с MySQL

Статья предназначена для настройки диспетчера службы имен для работы с MySQL с целью централизованного хранения учетных записей пользователей и групп пользователей в базе данных.

Установка

Установите следующие пакеты:

  • libnss-mysql

Создание БД

Создание таблиц

CREATE DATABASE `nss_mysql` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
USE `nss_mysql`;
CREATE TABLE `user` (
 `uid` int(11) NOT NULL,
 `gid` int(11) NOT NULL,
 `username` varchar(16) NOT NULL,
 `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,
 `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` USING BTREE (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

CREATE TABLE `group` (
 `gid` int(11) NOT NULL,
 `name` varchar(16) NOT NULL,
 PRIMARY KEY (`gid`),
 UNIQUE KEY `uidx_group_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

CREATE TABLE `grouplist` (
 `gid` int(11) NOT NULL,
 `uid` int(11) NOT NULL,
 PRIMARY KEY (`uid`,`gid`),
 KEY `fk_gid` (`gid`),
 CONSTRAINT `fk_gid` FOREIGN KEY (`gid`) REFERENCES `group` (`gid`),
 CONSTRAINT `fk_uid` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Настройка прав доступа

GRANT USAGE ON *.* TO 'nss_mysql_root'@'%' IDENTIFIED BY "NSS_MYSQL_ROOT_PASSWORD";
GRANT SELECT ON `nss_mysql`.* TO 'nss_mysql_root'@'%'
GRANT USAGE ON *.* TO 'nss_mysql'@'%' IDENTIFIED BY "NSS_MYSQL_PASSWORD";
GRANT SELECT (shell, username, homedir, uid, gid, gecos) ON `nss_mysql`.`user` TO 'nss_mysql'@'%'
GRANT SELECT (gid, name) ON `nss_mysql`.`group` TO 'nss_mysql'@'%'
GRANT SELECT (uid, gid) ON `nss_mysql`.`grouplist` TO 'nss_mysql'@'%'

Добавление записей

INSERT INTO user (uid, gid, username, gecos, homedir, shell, password) VALUES (500, 500, 'u0', 'User 0', '/home/u0', '/bin/bash', ENCRYPT('password 0'));
INSERT INTO user (uid, gid, username, gecos, homedir, shell, password) VALUES (501, 501, 'u1', 'User 1', '/home/u1', '/bin/bash', ENCRYPT('password 1'));
INSERT INTO user (uid, gid, username, gecos, homedir, shell, password) VALUES (502, 502, 'u2', 'User 2', '/home/u2', '/bin/bash', ENCRYPT('password 2'));
INSERT INTO group VALUES (500, 'u0');
INSERT INTO group VALUES (501, 'u1');
INSERT INTO group VALUES (502, 'u2');
INSERT INTO group VALUES (1000, 'gr1');
INSERT INTO group VALUES (2000, 'gr2');
INSERT INTO grouplist VALUES (1000,500);
INSERT INTO grouplist VALUES (2000,501);
INSERT INTO grouplist VALUES (2000,502);

Настройка libnss-mysql

/etc/libnss-mysql.cfg

getpwnam    SELECT username, "x", uid, gid, gecos, homedir, shell \
            FROM user \
            WHERE username = '%1$s' \
            LIMIT 1
getpwuid    SELECT username, "x", uid, gid, gecos, homedir, shell \
            FROM user \
            WHERE uid = '%1$u' \
            LIMIT 1
getspnam    SELECT username, password, lstchg, min, max,warn, inact, expire, flag \
            FROM user \
            WHERE username = '%1$s' \
            LIMIT 1
getpwent    SELECT username, "x", uid, gid, gecos, homedir, shell \
            FROM user
getspent    SELECT username, password, lstchg, min, max, warn, inact, expire, flag \
            FROM user
getgrnam    SELECT name, "x", gid \
            FROM `group` \
            WHERE name = '%1$s' \
            LIMIT 1
getgrgid    SELECT name, "x", gid \
            FROM `group` \
            WHERE gid = '%1$u' \
            LIMIT 1
getgrent    SELECT name, "x", gid \
            FROM `group`

memsbygid   SELECT user.username FROM grouplist \
            INNER JOIN user \
            ON grouplist.uid = user.uid \
            WHERE grouplist.gid = '%1$u'
gidsbymem   SELECT grouplist.gid FROM grouplist \
            INNER JOIN user \
            ON grouplist.uid = user.uid \
            WHERE user.username = '%1$s'
host        192.168.0.254
port        3306
database    nss_mysql
username    nss_mysql
password    NSS_MYSQL_PASSWORD

/etc/libnss-mysql-root.cfg

username nss_mysql_root
password NSS_MYSQL_ROOT_PASSWORD

/etc/nsswitch.conf

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

Проверка

# getent passwd
...
u0:x:500:500:User 0:/home/u0:/bin/bash
u1:x:501:501:User 1:/home/u1:/bin/bash
u2:x:502:502:User 2:/home/u2:/bin/bash
# getent group
...
u0:x:500:
u1:x:501:
u2:x:502:
gr1:x:1000:u0
gr2:x:2000:u1,u2
# getent shadow
u0:XL0pOJWay9kOk:1:0:99999:0:0::0
u1:2MJ6ZviUO5vCo:1:0:99999:0:0::0
u2:7MUPfzqdMsR/o:1:0:99999:0:0::0

Статьи по Теме

Категории
Поиск