User/group association

Поиск
Список
Период
Сортировка
Искать

User/group association

От:
Casey Allen Shobe <cshobe@secureworks.net>
Дата:
I'm working on a users view for my database (what I have so far can be found 
below), and I was wondering how postgres associates users with groups, as 
there is no grosysid column in pg_user.  I would like to display information 
like so:

Username:         Primary Group:       Other Groups:
-----------------------------------------------------------------------
cshobe            Administrators       Security Operations, Development
[etc.]

See the question marks below for an in-context request.

Any help is greatly appreciated,

- Casey

create view	"users" as
	select		ps."usesysid" as "UID",
			ps."usename" as "Username",
			ps."passwd" as "Password",
			coalesce (
				(
					select		cast (p."position_name" as varchar)
					from only	"positions" as "p"
					where		p."position_id" = (
						select		cast (ui."info_value" as integer)
						from only	"user_info" as "ui"
						inner join only	"user_info_types" as "uit"
						on		uit."type_id" = ui."info_type_id"
						where uit."info_type" = 'Position ID'
						and uit."info_type_name" = 'Default'
						and ui."user_id" = ps."usesysid"
					)
				),
				'Undefined'
			) as "Position",
			coalesce (
				(
					select		cast (pg."groname" as varchar)
					from only	"pg_group" as "pg"
					where		pg."grosysid" = (
						????????where do I get this from?????????
					)
				),
				'Undefined'
			) as "Group",
			coalesce (
				(
					select		cast (ui."info_value" as integer)
					from only	"user_info" as "ui"
					inner join only	"user_info_types" as "uit"
					on		uit."type_id" = ui."info_type_id"
					where uit.info_type = 'Creator ID'
					and uit.info_type_name = 'Default'
					and ui."user_id" = ps."usesysid"
				),
				0
			) as "Creator ID",
			coalesce (
				(
					select		cast (ui."info_value" as varchar)
					from only	"user_info" as "ui"
					inner join only	"user_info_types" as "uit"
					on		uit."type_id" = ui."info_type_id"
					where uit.info_type = 'E-Mail'
					and uit.info_type_name = 'Work'
					and ui."user_id" = ps."usesysid"
				),
				'None'
			) as "Work E-Mail Address",
			coalesce (
				(
					select		cast (ui."info_value" as varchar)
					from only	"user_info" as "ui"
					inner join only	"user_info_types" as "uit"
					on		uit."type_id" = ui."info_type_id"
					where uit.info_type = 'Extension'
					and uit.info_type_name = 'Work'
					and ui."user_id" = ps."usesysid"
				),
				'None'
			) as "Extension",
			coalesce (
				(
					select		cast (ui."info_value" as varchar)
					from only	"user_info" as "ui"
					inner join only	"user_info_types" as "uit"
					on		uit."type_id" = ui."info_type_id"
					where uit.info_type = 'Telephone'
					and uit.info_type_name = 'Home'
					and ui."user_id" = ps."usesysid"
				),
				'None'
			) as "Home Telephone",
			coalesce (
				(
					select		cast (ui."info_value" as varchar)
					from only	"user_info" as "ui"
					inner join only	"user_info_types" as "uit"
					on		uit."type_id" = ui."info_type_id"
					where uit.info_type = 'Telephone'
					and uit.info_type_name = 'Mobile'
					and ui."user_id" = ps."usesysid"
				),
				'None'
			) as "Mobile Telephone"
	from only	"pg_shadow" as "ps"
	where		ps."usesysid" > 100
	order by	"UID";

-- 
Casey Allen Shobe / Network Security Analyst & PHP Developer
SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144
cshobe@secureworks.net / http://www.secureworks.net
Content is my own and does not necessarily represent my company.
FAQ