[PGS9.2] USER, GROUP and ROLE, PRIVILEGE Management - ver.dBRang.com

/****************************************************************************************************************
-- Title : [PGS9.2] USER, GROUP and ROLE, PRIVILEGE Management - ver.dBRang.com

-- Reference : dbrang.tistory.com
-- Key word : user group role 사용자 그룹 롤
****************************************************************************************************************/

-- 사용자 롤 생성

create role usr_ttt login password 'fff';  /* drop role usr_ttt */

create role usr_fff login password 'fff';  /* drop role usr_fff */

-- 사용자 롤 확인

select * from pg_user

where usename in ('usr_ttt', 'usr_fff');

-- 그룹 롤 생성

create role grp_ggg;                       /* drop role grp_ggg */

-- 그룹 롤 확인

select * from pg_roles

where rolname in ('usr_ttt', 'usr_fff', 'grp_ggg');

select * from pg_group;

-- 그룹 롤에 사용자 롤 추가

grant grp_ggg to usr_ttt;                  /* revoke grp_ggg from usr_ttt */

grant grp_ggg to usr_fff;                  /* revoke grp_ggg from usr_fff */

-- 사용자 추가된 그룹 롤 확인

select a.usename "role_nm", a.usesysid, b.roleid, b.member, b.grantor

     , c.rolname "group_nm", c.oid

from pg_user a

inner join pg_auth_members b

on a.usesysid = b.member

inner join pg_roles c

on b.roleid = c.oid;

select * from pg_group;

-- 스키마 생성

create schema sch_ttt authorization usr_ttt;     /* drop schema sch_ttt */

create schema sch_fff authorization usr_fff;     /* drop schema sch_fff */

-- 스키마 생성 확인

select * from pg_namespace;

-- 테이블 생성(owner는 sa)

create table sch_ttt.ttt_tbl (a int, b int);     /* drop table sch_ttt.ttt_tbl */

create table sch_fff.fff_tbl (a int, b int);     /* drop table sch_fff.fff_tbl */

alter table sch_fff.fff_tbl owner to usr_fff;    /* owner를 usr_fff로 변*/

-- 테이블 확인

select * from pg_tables where tablename in ('ttt_tbl', 'fff_tbl');

-- 데이터 입력

insert into sch_ttt.ttt_tbl values (100, 100);

insert into sch_fff.fff_tbl values (300, 300);

-- 데이터 확인(user:sa)

select * from sch_ttt.ttt_tbl;  /* 성공 */

select * from sch_fff.fff_tbl;  /* 성공 */

-- 데이터 확인(user:usr_ttt);

select * from sch_ttt.ttt_tbl;  /* 실패-테이블에 권한이 없다?*/

    error:  permission denied for relation ttt_tbl

select * from sch_fff.fff_tbl;  /* 실패-스키마에 권한이 없다?*/

    error:  permission denied for schema sch_fff

    line 1: select * from sch_fff.fff_tbl;

-- 데이터 확인(user:usr_fff);

select * from sch_fff.fff_tbl;  /* 성공-지꺼니깐*/

select * from sch_ttt.ttt_tbl;  /* 실패-스키마에 권한이 없다?*/

    error:  permission denied for schema sch_fff

    line 1: select * from sch_fff.fff_tbl;

-- 스키마 권한 할당

grant all on schema sch_ttt to usr_ttt; 

-- 데이터 확인(user:usr_ttt)

select * from sch_ttt.ttt_tbl;  /* 실패-테이블에 권한이 없다?*/

    error:  permission denied for relation ttt_tbl

-- 스키마 권한 철회

revoke all on schema sch_ttt from usr_ttt; 

-- 테이블 권한 할당

grant select on all tables in schema sch_ttt to usr_ttt;

-- 데이터 확인(user:usr_ttt)

select * from sch_ttt.ttt_tbl;  /* 실패-스키마에 권한이 없다?*/

    error:  permission denied for schema sch_ttt

    line 1: select * from sch_ttt.ttt_tbl;

-- 테이블 권한 철회

revoke select on all tables in schema sch_ttt from usr_ttt;

-- 스키마와 테이블 권한 할당

grant all on schema sch_ttt to usr_ttt; 

grant select on all tables in schema sch_ttt to usr_ttt;

grant all on schema sch_fff to usr_ttt;

grant select on table sch_fff.fff_tbl to usr_ttt;

-- 데이터 확인(user.usr_ttt)

select * from sch_ttt.ttt_tbl;  /* 성공 */

select * from sch_fff.fff_tbl;  /* 성공 */

-- 테이블에 할당된 권한 보기

select grantor, grantee, table_catalog, table_schema, table_name, privilege_type, is_grantable

from information_schema.role_table_grants 

where table_name in ('ttt_tbl', 'fff_tbl');