반응형
/********************************************************************************************
-- Title : [PGS9.2] 다른 스키마 테이블 조회 권한
-- Key word : create role grant create schema pg_role pg_group pg_namespace
********************************************************************************************/
-- 초기화(Initiation)
drop table sch_ttt.ttt_tbl;
drop table sch_fff.fff_tbl;
drop schema sch_ttt;
drop schema sch_fff;
drop user usr_ttt;
drop user usr_fff;
drop group grp_ggg;
-- 사용자 롤 생성
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');
select * from pg_shadow
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 g.groname "grp_nm", g.grosysid "grp_id"
, u.usename "usr_nm", u.usesysid "user_id"
from pg_auth_members a
inner join pg_group g
on a.roleid = g.grosysid
inner join pg_user u
on a.member = u.usesysid;
-- 스키마 생성
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; /* 성공-지꺼니깐(owner to usr_fff)*/
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_grantablefrom information_schema.role_table_grants
where table_name in ('ttt_tbl', 'fff_tbl');
-- 모든 권한 제거
revoke all on schema sch_ttt from usr_ttt;
revoke select on all tables in schema sch_ttt from usr_ttt;
revoke all on schema sch_fff from usr_ttt;
revoke select on table sch_fff.fff_tbl from usr_ttt;
-- 그룹에 권한 부여
grant all on schema sch_ttt to grp_ggg;
grant all on schema sch_fff to grp_ggg;
grant select on all tables in schema sch_ttt to grp_ggg;
grant select on all tables in schema sch_fff to grp_ggg;
반응형