1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
|
--
UPDATE users SET unix_uid=user_id + 20000 WHERE unix_uid=0;
UPDATE users SET unix_gid=unix_uid WHERE unix_gid=0;
--
-- Passwd view
--
DROP VIEW nss_passwd;
CREATE VIEW nss_passwd AS
SELECT
unix_uid AS uid,
unix_gid AS gid,
user_name AS login,
unix_pw AS passwd,
realname AS gecos,
shell,
user_name AS homedir,
status
FROM users
WHERE unix_status='A';
--
-- Shadow view (for future use)
--
DROP VIEW nss_shadow;
CREATE VIEW nss_shadow AS
SELECT
user_name AS login,
unix_pw AS passwd,
CHAR(1) 'n' AS expired,
CHAR(1) 'n' AS pwchange
FROM users
WHERE unix_status='A';
--
-- Group Table
-- Extracted from group information
--
DROP TABLE nss_groups;
CREATE TABLE nss_groups AS
SELECT 0 AS user_id, group_id,unix_group_name AS name, group_id + 10000 AS gid
FROM groups;
--
-- Insert users group ids in nss_group table
--
INSERT INTO nss_groups (user_id,group_id,name, gid)
SELECT user_id,0,user_name, unix_gid
FROM users
WHERE unix_status='A'
AND status = 'A';
--
-- Insert scm group ids in nss_group table
--
INSERT INTO nss_groups (user_id,group_id,name, gid)
SELECT 0,group_id,'scm_' || unix_group_name, group_id + 50000
FROM groups
WHERE status = 'A';
--
-- User_Group Table
--
DROP TABLE nss_usergroups ;
CREATE TABLE nss_usergroups AS (
SELECT
users.unix_uid AS uid,
groups.group_id + 10000 AS gid,
users.user_id AS user_id,
groups.group_id AS group_id,
users.user_name AS user_name,
groups.unix_group_name AS unix_group_name
FROM users,groups,user_group
WHERE
users.user_id=user_group.user_id
AND
groups.group_id=user_group.group_id
AND
groups.status = 'A'
AND
users.unix_status='A'
AND
users.status = 'A'
UNION
SELECT
users.unix_uid AS uid,
groups.group_id + 50000 AS gid,
users.user_id AS user_id,
groups.group_id AS group_id,
users.user_name AS user_name,
'scm_' || groups.unix_group_name AS unix_group_name
FROM users,groups,user_group
WHERE
users.user_id=user_group.user_id
AND
groups.group_id=user_group.group_id
AND
groups.status = 'A'
AND
users.unix_status='A'
AND
users.status = 'A'
AND
user_group.cvs_flags > 0);
--
-- Grants
--
GRANT SELECT ON nss_passwd TO gforge_nss;
GRANT SELECT ON nss_groups TO gforge_nss;
GRANT SELECT ON nss_usergroups TO gforge_nss;
--
|
