FrontPage/IT/MW/PostgreSQL/機器管理DB
をテンプレートにして作成
[
トップ
] [
新規
|
一覧
|
検索
|
最終更新
|
ヘルプ
|
ログイン
]
開始行:
#navi(../)
#Contents
------------------------
IPアドレスが複数存在すること、OSユーザー、プログラムユー...
**要件の整理 [#qf687f97]
+複数のIPアドレス: 各機器が複数のIPアドレスとMACアドレス...
+OSユーザー、プログラムユーザー、DBユーザー: 各機器やプロ...
+パスワード管理: パスワードは特定のグループ（例：管理者グ...
+セキュリティ管理: パスワードの保護（暗号化など）と、適切...
***1. テーブル設計 [#y784ffac]
機器テーブル (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 ...
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 ...
os_name VARCHAR(100) NOT NULL,
os_version VARCHAR(50),
install_date DATE
);
ユーザグループテーブル (user_groups)
ユーザグループを管理します。特定のグループに所属するユー...
-id: 主キー
-group_name: グループ名（例：Admin、Userなど）
-can_view_passwords: このグループに所属するユーザーがパス...
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',...
group_id INT REFERENCES user_groups(id) ON DELETE CA...
);
パスワード管理テーブル (passwords)
パスワードを管理するテーブルです。ユーザーグループごとの...
-id: 主キー
-user_id: パスワードが関連するユーザーのID（usersテーブル...
-password: 暗号化されたパスワード
sql
CREATE TABLE passwords (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id) ON DELETE CASCADE,
password TEXT NOT NULL -- 暗号化されたパスワードを保...
);
重要: passwordフィールドに保存するパスワードは必ず暗号化...
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 ...
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 ...
user_id INT REFERENCES users(id) ON DELETE CASCADE,
access_level VARCHAR(50) -- 例: 'Administrator', 'Us...
);
***2. データの挿入例 [#kf2299cc]
ユーザグループの挿入
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_na...
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_l...
VALUES
(1, 1, 'Administrator'),
(2, 3, 'User');
***3. パスワード閲覧のクエリ例 [#s43d718e]
パスワードを閲覧できるのは、特定のグループ（例えばAdminグ...
パスワードの表示（管理者グループのみ）
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 =...
***4. セキュリティ上の考慮 [#j555d9ea]
-パスワードのハッシュ化:~
パスワードは平文では保存せず、常にハッシュ化して保存しま...
-パスワードのアクセス制御:~
パスワードにアクセスできるかどうかは、user_groupsテーブル...
-データベースユーザー権限:~
PostgreSQL自体のユーザー権限（データベースユーザー）を適...
これで、複数のIPアドレスやMACアドレス、OSユーザー、プログ...
終了行:
#navi(../)
#Contents
------------------------
IPアドレスが複数存在すること、OSユーザー、プログラムユー...
**要件の整理 [#qf687f97]
+複数のIPアドレス: 各機器が複数のIPアドレスとMACアドレス...
+OSユーザー、プログラムユーザー、DBユーザー: 各機器やプロ...
+パスワード管理: パスワードは特定のグループ（例：管理者グ...
+セキュリティ管理: パスワードの保護（暗号化など）と、適切...
***1. テーブル設計 [#y784ffac]
機器テーブル (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 ...
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 ...
os_name VARCHAR(100) NOT NULL,
os_version VARCHAR(50),
install_date DATE
);
ユーザグループテーブル (user_groups)
ユーザグループを管理します。特定のグループに所属するユー...
-id: 主キー
-group_name: グループ名（例：Admin、Userなど）
-can_view_passwords: このグループに所属するユーザーがパス...
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',...
group_id INT REFERENCES user_groups(id) ON DELETE CA...
);
パスワード管理テーブル (passwords)
パスワードを管理するテーブルです。ユーザーグループごとの...
-id: 主キー
-user_id: パスワードが関連するユーザーのID（usersテーブル...
-password: 暗号化されたパスワード
sql
CREATE TABLE passwords (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id) ON DELETE CASCADE,
password TEXT NOT NULL -- 暗号化されたパスワードを保...
);
重要: passwordフィールドに保存するパスワードは必ず暗号化...
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 ...
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 ...
user_id INT REFERENCES users(id) ON DELETE CASCADE,
access_level VARCHAR(50) -- 例: 'Administrator', 'Us...
);
***2. データの挿入例 [#kf2299cc]
ユーザグループの挿入
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_na...
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_l...
VALUES
(1, 1, 'Administrator'),
(2, 3, 'User');
***3. パスワード閲覧のクエリ例 [#s43d718e]
パスワードを閲覧できるのは、特定のグループ（例えばAdminグ...
パスワードの表示（管理者グループのみ）
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 =...
***4. セキュリティ上の考慮 [#j555d9ea]
-パスワードのハッシュ化:~
パスワードは平文では保存せず、常にハッシュ化して保存しま...
-パスワードのアクセス制御:~
パスワードにアクセスできるかどうかは、user_groupsテーブル...
-データベースユーザー権限:~
PostgreSQL自体のユーザー権限（データベースユーザー）を適...
これで、複数のIPアドレスやMACアドレス、OSユーザー、プログ...
ページ名:
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
