PostgreSQL 테이블, 컬럼 권한

PostgreSQL DB 권한보기

 

테이블 조회

SELECT TABLE_SCHEMA
           , TABLE_NAME
           , TABLE_TYPE
--SELECT *
FROM INFORMATION_SCHEMA.TABLES
-- WHERE TABLE_SCHEMA IN (SELECT CURRENT_USER)
-- WHERE TABLE_SCHEMA LIKE 'string%' 
AND TABLE_NAME LIKE '%'
ORDER BY TABLE_NAME
;

 

컬럼 조회

SELECT TABLE_SCHEMA
           , TABLE_NAME
           , ORDINAL_POSITION
           , COLUMN_NAME
           , DATA_TYPE
           , CHARACTER_MAXIMUM_LENGTH
           , NUMERIC_PRECISION
--SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
-- WHERE TABLE_SCHEMA IN (SELECT CURRENT_USER)
-- WHERE TABLE_SCHEMA LIKE 'string%' 
AND TABLE_NAME LIKE '%'
ORDER BY TABLE_SCHEMA
, TABLE_NAME
, ORDINAL_POSITION
;

 

테이블에 할당된 권한 조회

SELECT GRANTOR
           , GRANTEE
           , TABLE_CATALOG
           , TABLE_SCHEMA
           , TABLE_NAME
           , PRIVILEGE_TYPE
           , IS_GRANTABLE
--SELECT *
FROM INFORMATION_SCHEMA.ROLE_TABLE_GRANTS
-- WHERE GRANTOR IN (SELECT CURRENT_USER) 
-- WHERE TABLE_NAME LIKE '%'
-- AND TABLE_SCHEMA IN (SELECT CURRENT_USER) 
-- AND TABLE_SCHEMA LIKE 'string%'
ORDER BY TABLE_NAME
, GRANTEE
;