°ü¸® ¾È³»¼­


½Ã½ºÅÛ Ä«Å»·Î±× »ç¿ë

°¢ µ¥ÀÌÅͺ£À̽º¿¡ ´ëÇÑ Á¤º¸´Â ÀÚµ¿À¸·Î ½Ã½ºÅÛ Ä«Å»·Î±×¶ó°í ÇÏ´Â ºä ¼¼Æ®¿¡ º¸Á¸µË´Ï´Ù. ÀÌ ½Ã½ºÅÛ Ä«Å»·Î±×´Â µ¥ÀÌÅͺ£À̽º°¡ ÀÛ¼ºµÉ ¶§ ÀÛ¼ºµË´Ï´Ù. ÀÌ·¯ÇÑ ½Ã½ºÅÛ Ä«Å»·Î±×¿¡¼­´Â Å×À̺í, Ä÷³, »öÀÎ, ÇÁ·Î±×·¥, Ư±Ç ¹× ±âŸ ¿ÀºêÁ§Æ®¿¡ ´ëÇØ ¼³¸íÇÕ´Ï´Ù.

ÀÌ Áß 6°³ÀÇ ºä¿¡´Â »ç¿ëÀÚÀÇ Æ¯±Ç°ú °¢ Ư±ÇÀ» ±ÇÇÑ ºÎ¿©ÇÏ´Â »ç¿ëÀÚÀÇ ID°¡ ³ª¿­µÇ¾î ÀÖ½À´Ï´Ù.

SYSCAT.DBAUTH
µ¥ÀÌÅͺ£À̽º Ư±Ç ³ª¿­

SYSCAT.TABAUTH
Å×À̺í°ú ºä Ư±Ç ³ª¿­

SYSCAT.COLAUTH
Ä÷³ Ư±Ç ³ª¿­

SYSCAT.PACKAGEAUTH
ÆÐŰÁö Ư±Ç ³ª¿­

SYSCAT.INDEXAUTH
»öÀΠƯ±Ç ³ª¿­

SYSCAT.SCHEMAAUTH
½ºÅ°¸¶ Ư±Ç ³ª¿­

SYSCAT.PASSTHRUAUTH
¼­¹ö Ư±Ç ³ª¿­

½Ã½ºÅÛÀÌ »ç¿ëÀÚ¿¡°Ô ±ÇÇÑ ºÎ¿©ÇÑ Æ¯±ÇÀº 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 ±ÇÇÑÀÌ ÀÖ´Â ¸ðµç À̸§ °Ë»ö

´ÙÀ½ ¸í·É¹®Àº Á÷Á¢ÀûÀ¸·Î 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


[ ÆäÀÌÁöÀÇ ¸Ç À§ | ÀÌÀü ÆäÀÌÁö | ´ÙÀ½ ÆäÀÌÁö | ¸ñÂ÷ | »öÀÎ ]