°¢ µ¥ÀÌÅͺ£À̽º¿¡ ´ëÇÑ Á¤º¸´Â ÀÚµ¿À¸·Î ½Ã½ºÅÛ Ä«Å»·Î±×¶ó°í ÇÏ´Â ºä ¼¼Æ®¿¡ º¸Á¸µË´Ï´Ù. ÀÌ ½Ã½ºÅÛ Ä«Å»·Î±×´Â µ¥ÀÌÅͺ£À̽º°¡ ÀÛ¼ºµÉ ¶§ ÀÛ¼ºµË´Ï´Ù. ÀÌ·¯ÇÑ ½Ã½ºÅÛ Ä«Å»·Î±×¿¡¼´Â Å×À̺í, Ä÷³, »öÀÎ, ÇÁ·Î±×·¥, Ư±Ç ¹× ±âŸ ¿ÀºêÁ§Æ®¿¡ ´ëÇØ ¼³¸íÇÕ´Ï´Ù.
ÀÌ Áß 6°³ÀÇ ºä¿¡´Â »ç¿ëÀÚÀÇ Æ¯±Ç°ú °¢ Ư±ÇÀ» ±ÇÇÑ ºÎ¿©ÇÏ´Â »ç¿ëÀÚÀÇ ID°¡ ³ª¿µÇ¾î ÀÖ½À´Ï´Ù.
½Ã½ºÅÛÀÌ »ç¿ëÀÚ¿¡°Ô ±ÇÇÑ ºÎ¿©ÇÑ Æ¯±ÇÀº SYSIBMÀ» ±ÇÇÑ ÁØ »ç¿ëÀÚ·Î °®°Ô µË´Ï´Ù. SYSADM, SYSMAINT ¹× SYSCTRLÀº ½Ã½ºÅÛ Ä«Å»·Î±×¿¡ ³ª¿µÇÁö ¾Ê½À´Ï´Ù.
CREATE ¹× GRANT¹®Àº ½Ã½ºÅÛ Ä«Å»·Î±×¿¡ Ư±ÇÀ» À§Ä¡½Ãŵ´Ï´Ù. SYSADM ¹× DBADM ±ÇÇÑÀ» °¡Áö°í ÀÖ´Â »ç¿ëÀÚ´Â ½Ã½ºÅÛ Ä«Å»·Î±× ºä¿¡ ´ëÇØ SELECT Ư±ÇÀ» ±ÇÇÑ ºÎ¿©ÇÏ°í ±ÇÇÑ Ãë¼ÒÇÒ ¼ö ÀÖ½À´Ï´Ù. ´ÙÀ½ ¿¹¿¡¼´Â ÀÌ·¯ÇÑ SQL Á¶È¸¸¦ »ç¿ëÇÏ¿© Ư±Ç Á¤º¸¸¦ ¹ßÃéÇÏ´Â ¹æ¹ýÀ» º¸¿©ÁÝ´Ï´Ù.
¸ðµç Ư±Ç¿¡ °üÇÑ Á¤º¸°¡ ÇϳªÀÇ ½Ã½ºÅÛ Ä«Å»·Î±× ºä¿¡ µé¾î ÀÖÁö´Â ¾Ê½À´Ï´Ù. ´ÙÀ½ ¸í·É¹®¿¡¼´Â Ư±ÇÀ» °¡Áø ¸ðµç ±ÇÇÑ ºÎ¿© À̸§À» °Ë»öÇÕ´Ï´Ù.
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'DATABASE' FROM SYSCAT.DBAUTH UNION SELECT DISTINCT GRANTEE, GRANTEETYPE, 'TABLE ' FROM SYSCAT.TABAUTH UNION SELECT DISTINCT GRANTEE, GRANTEETYPE, 'PACKAGE ' FROM SYSCAT.PACKAGEAUTH UNION SELECT DISTINCT GRANTEE, GRANTEETYPE, 'INDEX ' FROM SYSCAT.INDEXAUTH UNION SELECT DISTINCT GRANTEE, GRANTEETYPE, 'COLUMN ' FROM SYSCAT.COLAUTH UNION SELECT DISTINCT GRANTEE, GRANTEETYPE, 'SCHEMA ' FROM SYSCAT.SCHEMAAUTH UNION SELECT DISTINCT GRANTEE, GRANTEETYPE, 'SERVER ' FROM SYSCAT.PASSTHRUAUTH ORDER BY GRANTEE, GRANTEETYPE, 3
Á¤±âÀûÀ¸·Î ÀÌ ¸í·É¹®¿¡ ÀÇÇØ °Ë»öµÇ´Â ¸ñ·ÏÀº ½Ã½ºÅÛ º¸¾È ±â´É¿¡ Á¤ÀÇµÈ »ç¿ëÀÚ ¹× ±×·ì À̸§ÀÇ ¸ñ·Ï°ú ºñ±³µÇ¾î¾ß ÇÕ´Ï´Ù. ±×·¯¸é »ç¿ëÀÚ´Â ´õ ÀÌ»ó À¯È¿ÇÏÁö ¾ÊÀº ÇØ´ç ±ÇÇÑ ºÎ¿© À̸§À» ½Äº°ÇÒ ¼ö ÀÖ½À´Ï´Ù.
ÁÖ: | »ç¿ëÀÚ°¡ ¿ø°Ý µ¥ÀÌÅͺ£À̽º Ŭ¶óÀÌ¾ðÆ®¸¦ Áö¿øÇϰí ÀÖ´Â °æ¿ì, ¿ø°Ý Ŭ¶óÀÌ¾ðÆ®¿¡¸¸ ±ÇÇÑ ºÎ¿© À̸§ÀÌ Á¤ÀÇµÇ°í »ç¿ëÀÚÀÇ µ¥ÀÌÅͺ£À̽º ¼¹ö ¸Ó½Å¿¡´Â Á¤ÀǵÇÁö ¾Êµµ·Ï ÇÒ ¼ö ÀÖ½À´Ï´Ù. |
´ÙÀ½ ¸í·É¹®Àº Á÷Á¢ÀûÀ¸·Î DBADM ±ÇÇÑÀÌ ±ÇÇÑ ºÎ¿©µÈ ¸ðµç ±ÇÇÑ ºÎ¿© À̸§À» °Ë»öÇÕ´Ï´Ù.
SELECT DISTINCT GRANTEE FROM SYSCAT.DBAUTH WHERE DBADMAUTH = 'Y'
´ÙÀ½ ¸í·É¹®¿¡¼´Â JAMES ±ÔÁ¤ÀÚ·Î Å×À̺í EMPLOYEE¿¡ ¾×¼¼½ºÇϵµ·Ï Á÷Á¢ ±ÇÇÑÀÌ ºÎ¿©µÈ ¸ðµç ±ÇÇÑ ºÎ¿© À̸§À» °Ë»öÇÕ´Ï´Ù.
SELECT DISTINCT GRANTEETYPE, GRANTEE FROM SYSCAT.TABAUTH WHERE TABNAME = 'EMPLOYEE' AND TABSCHEMA = 'JAMES' UNION SELECT DISTINCT GRANTEETYPE, GRANTEE FROM SYSCAT.COLAUTH WHERE TABNAME = 'EMPLOYEE' AND TABSCHEMA = 'JAMES'
±ÔÁ¤ÀÚ JAMES·Î Å×À̺í EMPLOYEE¸¦ °»½ÅÇÒ ¼ö ÀÖ´Â »ç¿ëÀÚ¸¦ ¾Ë¾Æ³»·Á¸é, ´ÙÀ½ ¸í·É¹®À» ¹ßÇàÇϽʽÿÀ.
SELECT DISTINCT GRANTEETYPE, GRANTEE FROM SYSCAT.TABAUTH WHERE TABNAME = 'EMPLOYEE' AND TABSCHEMA = 'JAMES' AND (CONTROLAUTH = 'Y' OR UPDATEAUTH = 'Y' OR UPDATEAUTH = 'G') UNION SELECT DISTINCT GRANTEETYPE, GRANTEE FROM SYSCAT.DBAUTH WHERE DBADMAUTH = 'Y' UNION SELECT DISTINCT GRANTEETYPE, GRANTEE FROM SYSCAT.COLAUTH WHERE TABNAME = 'EMPLOYEE' AND TABSCHEMA = 'JAMES' AND PRIVTYPE = 'U'
ÀÌ´Â CONTROL ¶Ç´Â UPDATE Ư±ÇÀÌ Á÷Á¢ ±ÇÇÑ ºÎ¿©µÈ À̸§»Ó¸¸ ¾Æ´Ï¶ó, DBADM ±ÇÇÑÀ» °®´Â ¸ðµç ±ÇÇÑ ºÎ¿© À̸§µµ °Ë»öÇÕ´Ï´Ù. ±×·¯³ª, SYSADM ±ÇÇѸ¸À» º¸À¯Çϰí ÀÖ´Â »ç¿ëÀÚÀÇ ±ÇÇÑ ºÎ¿© À̸§Àº ¸®ÅϵÇÁö ¾Ê½À´Ï´Ù.
±ÇÇÑ ºÎ¿© À̸§ Áß ÀϺδ °³º° »ç¿ëÀÚ°¡ ¾Æ´Ñ ±×·ì IDÀÏ ¼öµµ ÀÖÀ½À» ±â¾ïÇϽʽÿÀ.
½Ã½ºÅÛ Ä«Å»·Î±× ºä¿¡ ´ëÇØ Á¶È¸ÇÔÀ¸·Î½á, »ç¿ëÀÚ°¡ º¸À¯Çϰí Àִ Ư±ÇÀÇ ¸ñ·Ï°ú ´Ù¸¥ »ç¿ëÀÚ¿¡°Ô ±ÇÇÑ ºÎ¿©ÇÑ Æ¯±ÇÀÇ ¸ñ·ÏÀ» °Ë»öÇÒ ¼ö ÀÖ½À´Ï´Ù. ¿¹¸¦ µé¾î, ´ÙÀ½ ¸í·É¹®¿¡¼´Â °³º° ±ÇÇÑ ºÎ¿© À̸§¿¡ Á÷Á¢ ±ÇÇÑ ºÎ¿©µÈ µ¥ÀÌÅͺ£À̽º Ư±ÇÀÇ ¸ñ·ÏÀ» °Ë»öÇÕ´Ï´Ù.
SELECT * FROM SYSCAT.DBAUTH WHERE GRANTEE = USER AND GRANTEETYPE = 'U'
´ÙÀ½ ¸í·É¹®¿¡¼´Â ƯÁ¤ »ç¿ëÀÚ¿¡ ÀÇÇØ Á÷Á¢ ±ÇÇÑ ºÎ¿©µÈ Å×ÀÌºí Æ¯±ÇÀÇ ¸ñ·ÏÀ» °Ë»öÇÕ´Ï´Ù.
SELECT * FROM SYSCAT.TABAUTH WHERE GRANTOR = USER
´ÙÀ½ ¸í·É¹®¿¡¼´Â ƯÁ¤ »ç¿ëÀÚ¿¡ ÀÇÇØ Á÷Á¢ ±ÇÇÑ ºÎ¿©µÈ °¢ Ä÷³ Ư±ÇÀÇ ¸ñ·ÏÀ» °Ë»öÇÕ´Ï´Ù.
SELECT * FROM SYSCAT.COLAUTH WHERE GRANTOR = USER
ÀÌ ¸í·É¹®¿¡¼ Ű¿öµå USER´Â »ç¿ëÀÚÀÇ ±ÇÇÑ ºÎ¿© À̸§ °ª°ú Ç×»ó °°½À´Ï´Ù. USER´Â Àбâ Àü¿ë Ư¼ö ·¹Áö½ºÅÍÀÔ´Ï´Ù. Ư¼ö ·¹Áö½ºÅÍ¿¡ ´ëÇØ¼´Â SQL ÂüÁ¶¼¿¡¼ ÀÚ¼¼ÇÑ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
µ¥ÀÌÅͺ£À̽º°¡ ÀÛ¼ºµÇ´Â µ¿¾È, ½Ã½ºÅÛ Ä«Å»·Î±× ºä¿¡ ´ëÇÑ SELECT Ư±ÇÀÌ PUBLIC¿¡ ±ÇÇÑ ºÎ¿©µË´Ï´Ù(PUBLIC¿¡ ÀÚµ¿À¸·Î ±ÇÇÑ ºÎ¿©µÇ´Â ´Ù¸¥ Ư±ÇÀº µ¥ÀÌÅͺ£À̽º Ư±Ç¿¡¼ ÀÚ¼¼ÇÑ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ). ´ëºÎºÐÀÇ °æ¿ì, º¸¾È»ó ¹®Á¦Á¡ÀÌ ³ªÅ¸³ªÁö ¾Ê½À´Ï´Ù. ±×·¯³ª ¸Å¿ì Áß¿äÇÑ µ¥ÀÌÅÍÀÇ °æ¿ì¿¡ ÀÌ Å×À̺íµéÀÌ µ¥ÀÌÅͺ£À̽º¿¡ ÀÖ´Â ¸ðµç ¿ÀºêÁ§Æ®¸¦ ¼¼úÇϱ⠶§¹®¿¡, ÀûÀýÇÏÁö ¾ÊÀ» ¼öµµ ÀÖ½À´Ï´Ù. ÀÌ °æ¿ì, PUBLICÀ¸·ÎºÎÅÍ SELECT Ư±ÇÀ» Ãë¼ÒÇϰí, ƯÁ¤ »ç¿ëÀÚ¿¡°Ô ÇÊ¿äÇÑ SELECT Ư±ÇÀ» ±ÇÇÑ ºÎ¿©ÇÏ´Â ¹æ¹ýÀ» °í·ÁÇØº¸½Ê½Ã¿À. ½Ã½ºÅÛ Ä«Å»·Î±× ºä¿¡ ´ëÇØ SELECT¸¦ ±ÇÇÑ ºÎ¿©Çϰí Ãë¼ÒÇÏ´Â ¹æ½ÄÀº ¸ðµç ºä¿¡¼ µ¿ÀÏÇÏÁö¸¸, »ç¿ëÀÚ°¡ À̸¦ ¼öÇàÇÒ ¼ö ÀÖ´Â SYSADM ¶Ç´Â DBADM ±ÇÇÑ °¡Áö°í ÀÖ¾î¾ß ÇÕ´Ï´Ù.
ÃÖ¼ÒÇÑ ´ÙÀ½ īŻ·Î±× ºä·ÎÀÇ ¾×¼¼½º¸¦ Á¦ÇÑÇÏ´Â ¹æ¹ýÀ» °í·ÁÇØ¾ß ÇÕ´Ï´Ù.
ÀÌ·¸°Ô Çϸé, ºê·¹ÀÌÅ©Àο¡ ´ëÇÑ ±ÇÇÑ ºÎ¿© À̸§À» ´ë»óÀ¸·Î ÁöÁ¤ÇÏ´Â µ¥ »ç¿ëµÉ ¼ö ÀÖ´Â »ç¿ëÀÚ Æ¯±Ç¿¡ ´ëÇÑ Á¤º¸¸¦ µ¥ÀÌÅͺ£À̽º¿¡ ¾×¼¼½ºÇÏ´Â ¸ðµç »ç¿ëÀÚ°¡ »ç¿ëÇÒ ¼ö ¾ø°Ô µË´Ï´Ù.
¶ÇÇÑ, ¼öÁýµÈ Åë°è¿¡ ´ëÇÑ Ä÷³µµ Á¶»çÇØ¾ß ÇÕ´Ï´Ù(½Ã½ºÅÛ Ä«Å»·Î±× Åë°è ÂüÁ¶). ½Ã½ºÅÛ Ä«Å»·Î±×¿¡ ±â·ÏµÈ ÀϺΠÅë°è¿¡´Â »ç¿ëÀÚÀÇ È¯°æ¿¡¼ Áß¿äÇÑ µ¥ÀÌÅͰ¡ µÉ ¼ö ÀÖ´Â µ¥ÀÌÅͰªÀÌ µé¾î ÀÖ½À´Ï´Ù. ÀÌ·¯ÇÑ Åë°è¿¡ Áß¿äÇÑ µ¥ÀÌÅͰ¡ µé¾î ÀÖÀ¸¸é, SYSCAT.COLUMNS ¹× SYSCAT.COLDIST īŻ·Î±× ºä¿¡ ´ëÇØ PUBLICÀ¸·ÎºÎÅÍ SELECT Ư±ÇÀ» ±ÇÇÑ Ãë¼ÒÇÒ ¼öµµ ÀÖ½À´Ï´Ù.
½Ã½ºÅÛ Ä«Å»·Î±× ºä·ÎÀÇ ¾×¼¼½º¸¦ Á¦ÇÑÇÏ·Á´Â °æ¿ì, ºä¸¦ Á¤ÀÇÇÏ¿© °¢ ±ÇÇÑ ºÎ¿© À̸§ÀÌ ÀÚü Ư±Ç Á¤º¸¸¦ °Ë»öÇϵµ·Ï ÇÒ ¼ö ÀÖ½À´Ï´Ù.
¿¹¸¦ µé¾î, ´ÙÀ½ÀÇ ºä MYSELECTS¿¡´Â »ç¿ëÀÚÀÇ ±ÇÇÑ ºÎ¿© À̸§ÀÌ SELECT Ư±Ç¿¡ Á÷Á¢ ±ÇÇÑ ºÎ¿©µÈ ¸ðµç Å×À̺íÀÇ À̸§ ¹× ¼ÒÀ¯ÀÚ°¡ ÀÖ½À´Ï´Ù.
CREATE VIEW MYSELECTS AS SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABAUTH WHERE GRANTEETYPE = 'U' AND GRANTEE = USER AND SELECTAUTH = 'Y'
ÀÌ ¸í·É¹®¿¡¼ Ű¿öµå USER´Â ±ÇÇÑ ºÎ¿© À̸§ °ª°ú Ç×»ó °°½À´Ï´Ù.
´ÙÀ½ ¸í·É¹®¿¡¼´Â ¸ðµç ±ÇÇÑ ºÎ¿© À̸§¿¡ »ç¿ë °¡´ÉÇÑ ºä¸¦ ¸¸µì´Ï´Ù.
GRANT SELECT ON TABLE MYSELECTS TO PUBLIC
±×¸®°í ¸¶Áö¸·À¸·Î, ±âº» Å×ÀÌºí¿¡ ´ëÇÑ SELECT Ư±ÇÀ» ±ÇÇÑ Ãë¼ÒÇØ¾ß ÇÕ´Ï´Ù.
REVOKE SELECT ON TABLE SYSCAT.TABAUTH FROM PUBLIC