![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
IPアドレスが複数存在すること、OSユーザー、プログラムユーザー、データベースユーザー（DBユーザー）を管理し、さらにパスワードを特定のグループに所属するユーザーにのみ見せるためのデータベース設計を提案します。
機器テーブル (equipment) これは基本的な機器情報を管理するテーブルです。これは変更ありません。
sql
コードをコピーする
CREATE TABLE equipment (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
model VARCHAR(100),
serial_number VARCHAR(100) UNIQUE,
purchase_date DATE,
status VARCHAR(50),
location VARCHAR(100)
);
ネットワーク情報テーブル (network_info) 複数のIPアドレスとMACアドレスを管理するテーブルです。
sql
CREATE TABLE network_info (
id SERIAL PRIMARY KEY,
equipment_id INT REFERENCES equipment(id) ON DELETE CASCADE,
ip_address INET,
mac_address VARCHAR(17),
interface_name VARCHAR(50)
);
OS情報テーブル (os_info) 機器のOS情報を管理するテーブルです。
sql
CREATE TABLE os_info (
id SERIAL PRIMARY KEY,
equipment_id INT REFERENCES equipment(id) ON DELETE CASCADE,
os_name VARCHAR(100) NOT NULL,
os_version VARCHAR(50),
install_date DATE
);
ユーザグループテーブル (user_groups) ユーザグループを管理します。特定のグループに所属するユーザーのみがパスワードにアクセスできるように、グループごとに権限を分けます。
sql
CREATE TABLE user_groups (
id SERIAL PRIMARY KEY,
group_name VARCHAR(50) NOT NULL UNIQUE,
can_view_passwords BOOLEAN DEFAULT FALSE
);
ユーザーテーブル (users)
各種ユーザー（OSユーザー、プログラムユーザー、DBユーザーなど）を管理するテーブルです。ユーザーは複数のグループに属することができ、パスワードの閲覧権限もここで決まります。sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(100) NOT NULL,
user_type VARCHAR(50) NOT NULL, -- 'OS', 'Program', 'DB'など
group_id INT REFERENCES user_groups(id) ON DELETE CASCADE
);
パスワード管理テーブル (passwords) パスワードを管理するテーブルです。ユーザーグループごとの権限に応じて、このテーブルの内容を参照できるようにします。パスワードはセキュリティのために暗号化して保存します。
sql
CREATE TABLE passwords (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id) ON DELETE CASCADE,
password TEXT NOT NULL -- 暗号化されたパスワードを保存する
);
重要: passwordフィールドに保存するパスワードは必ず暗号化（例: pgcrypto拡張を使用してハッシュ化）する必要があります。sql
-- pgcrypto拡張のインストール（必要に応じて）
CREATE EXTENSION pgcrypto;
-- パスワードをハッシュ化して挿入
INSERT INTO passwords (user_id, password)
VALUES
(1, crypt('user_password', gen_salt('bf')));
プログラム情報テーブル (installed_programs) インストールされているプログラムと、そのユーザーを管理するテーブルです。
sql
CREATE TABLE installed_programs (
id SERIAL PRIMARY KEY,
equipment_id INT REFERENCES equipment(id) ON DELETE CASCADE,
program_name VARCHAR(100) NOT NULL,
version VARCHAR(50),
install_date DATE
);
プログラムユーザーテーブル (program_users) プログラムごとのユーザーと、そのアクセスレベルを管理します。
sql
CREATE TABLE program_users (
id SERIAL PRIMARY KEY,
program_id INT REFERENCES installed_programs(id) ON DELETE CASCADE,
user_id INT REFERENCES users(id) ON DELETE CASCADE,
access_level VARCHAR(50) -- 例: 'Administrator', 'User'など
);
ユーザグループの挿入
sql
INSERT INTO user_groups (group_name, can_view_passwords)
VALUES
('Admin', TRUE),
('User', FALSE);
ユーザーの挿入（OS、プログラム、DBユーザー）
sql
INSERT INTO users (username, user_type, group_id)
VALUES
('os_admin', 'OS', 1),
('db_user', 'DB', 2),
('program_user', 'Program', 2);
パスワードの挿入（ハッシュ化）
sql
INSERT INTO passwords (user_id, password)
VALUES
(1, crypt('os_admin_password', gen_salt('bf'))),
(2, crypt('db_user_password', gen_salt('bf'))),
(3, crypt('program_user_password', gen_salt('bf')));
プログラムの挿入
sql INSERT INTO installed_programs (equipment_id, program_name, version, install_date) VALUES (1, 'Microsoft Office', '2019', '2023-02-12'), (2, 'LibreOffice', '7.1', '2022-11-16');
プログラムユーザーの挿入
sql INSERT INTO program_users (program_id, user_id, access_level) VALUES (1, 1, 'Administrator'), (2, 3, 'User');
パスワードを閲覧できるのは、特定のグループ（例えばAdminグループ）に所属しているユーザーに限定します。例えば、can_view_passwordsがTRUEのグループに属するユーザーのみがパスワードを取得できるようにします。
パスワードの表示（管理者グループのみ）
sql SELECT u.username, p.password FROM users u JOIN passwords p ON u.id = p.user_id JOIN user_groups g ON u.group_id = g.id WHERE g.can_view_passwords = TRUE;
特定ユーザーのパスワードを表示（グループに基づく）
sql SELECT u.username, p.password FROM users u JOIN passwords p ON u.id = p.user_id JOIN user_groups g ON u.group_id = g.id WHERE u.username = 'os_admin' AND g.can_view_passwords = TRUE;
