Achando usuários Oracle com privilégio DBA oculto por roles

This post is also available in: English

Alguns usuários do Oracle podem possuir privilégios muito perigosos sem o seu consentimento, que podem causar um grande estrago no banco de dados. As vezes esse privilégio está oculto via uma cadeia de roles, o que torna difícil a percepção.

Ex:

SQL> CREATE USER SYSADM identified by "sysadm1";

SQL> CREATE ROLE A;
SQL> CREATE ROLE B;
SQL> CREATE ROLE SYSROLE;

SQL> GRANT A TO SYSADM;
SQL> GRANT B TO A;
SQL> GRANT SYSROLE TO B;

SQL> GRANT DBA TO SYSROLE;
SQL> GRANT CREATE SESSION TO SYSADM;

Neste exemplo, o GRANT DBA para o SYSADM foi oculto. Assim como a role DBA, outra role perigosa é o IMP_FULL_DATABASE, que concede praticamente tudo a um usuário.

Outra forma também é conceder manualmente os grants de sistemas para uma nova role e ocultar esta role através de uma cadeia de roles, como no exemplo acima.

Ex:

SQL> CREATE USER SYSADM identified by "sysadm1";

SQL> CREATE ROLE A;
SQL> CREATE ROLE B;
SQL> CREATE ROLE SYSROLE;

SQL> GRANT A TO SYSADM;
SQL> GRANT B TO A;
SQL> GRANT SYSROLE TO B;

SQL> GRANT GRANT ANY PRIVILEGE TO SYSROLE;
SQL> GRANT CREATE SESSION TO SYSADM;

Para rastrear esses tipos de privilégios, desenvolvi a query abaixo. Após executá-la, o próximo passo é revogar os privilégios desnecessário e em seguida executar a query novamente para verificar se o usuário já está limpo.

Segue:

SELECT GRANTEE,
       PRIVILEGE,
       ACCOUNT_STATUS
FROM   (SELECT GRANTEE,
               LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIVILEGE, ', ')), ', ') PRIVILEGE
        FROM   (SELECT GRANTEE,
                       PRIVILEGE,
                       ROW_NUMBER() OVER(PARTITION BY GRANTEE ORDER BY PRIVILEGE) RN
                FROM   (SELECT DISTINCT NVL(A.GRANTEE, B.GRANTEE) GRANTEE,
                                        NVL(A.PRIVILEGE, B.PRIVILEGE) PRIVILEGE
                        FROM   (SELECT A.GRANTEE,
                                       A.PATH PRIVILEGE
                                FROM   (SELECT A.GRANTEE,
                                               A.GRANTED_ROLE_ROOT PRIVILEGE,
                                               A.PATH,
                                               A.NIVEL,
                                               RANK() OVER(PARTITION BY A.GRANTEE, A.FIRST_ROLE ORDER BY NIVEL ASC) RANK
                                        FROM   (SELECT A.GRANTEE,
                                                       GRANTED_ROLE FIRST_ROLE,
                                                       CONNECT_BY_ROOT GRANTED_ROLE GRANTED_ROLE_ROOT,
                                                       '(' || LTRIM(SYS_CONNECT_BY_PATH(GRANTED_ROLE, '->'), '->') || ')' PATH,
                                                       LEVEL NIVEL
                                                FROM   DBA_ROLE_PRIVS A
                                                CONNECT BY PRIOR GRANTEE = GRANTED_ROLE) A,
                                               DBA_SYS_PRIVS B
                                        WHERE  A.GRANTEE NOT IN (SELECT ROLE
                                                                 FROM   DBA_ROLES)
                                        AND    A.GRANTED_ROLE_ROOT = B.GRANTEE
                                        AND    (B.PRIVILEGE LIKE 'DROP ANY%' OR B.PRIVILEGE LIKE 'GRANT%' OR B.PRIVILEGE IN ('ADMINISTER DATABASE TRIGGER'))) A
                                WHERE  A.RANK = 1) A
                        FULL   OUTER JOIN (SELECT GRANTEE,
                                                 PRIVILEGE
                                          FROM   DBA_SYS_PRIVS
                                          WHERE  (PRIVILEGE LIKE 'DROP ANY%' OR PRIVILEGE LIKE 'GRANT%' OR PRIVILEGE IN ('ADMINISTER DATABASE TRIGGER'))
                                          AND    GRANTEE NOT IN (SELECT ROLE
                                                                 FROM   DBA_ROLES)) B ON B.GRANTEE = A.GRANTEE))
        START  WITH RN = 1
        CONNECT BY PRIOR RN = RN - 1
            AND    PRIOR GRANTEE = GRANTEE
        GROUP  BY GRANTEE) A,
       DBA_USERS B
WHERE  A.GRANTEE = B.USERNAME
ORDER  BY 1;

(Você pode modificá-la colocando outros privilégios que deseja buscar)

Se você estiver no Oracle 10g e receber o erro "ORA-00600: código de erro interno, argumentos: [qctcte1], [0], [], [], [], [], [], []", use a versão adaptada que segue (menos complexa mas menos completa):

SELECT GRANTEE,
       PRIVILEGE,
       ACCOUNT_STATUS
FROM   (SELECT GRANTEE,
               LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIVILEGE, ', ')), ', ') PRIVILEGE
        FROM   (SELECT GRANTEE,
                       PRIVILEGE,
                       ROW_NUMBER() OVER(PARTITION BY GRANTEE ORDER BY PRIVILEGE) RN
                FROM   (SELECT DISTINCT NVL(A.GRANTEE, B.GRANTEE) GRANTEE,
                                        NVL(A.PRIVILEGE, B.PRIVILEGE) PRIVILEGE
                        FROM   (SELECT A.GRANTEE,
                                       A.GRANTED_ROLE PRIVILEGE
                                FROM   (SELECT A.*
                                        FROM   DBA_ROLE_PRIVS A
                                        CONNECT BY PRIOR GRANTEE = GRANTED_ROLE) A,
                                       DBA_SYS_PRIVS B
                                WHERE  A.GRANTEE NOT IN (SELECT ROLE
                                                         FROM   DBA_ROLES)
                                AND    A.GRANTED_ROLE = B.GRANTEE
                                AND    (B.PRIVILEGE LIKE 'DROP ANY%' OR B.PRIVILEGE LIKE 'GRANT%' OR B.PRIVILEGE IN ('ADMINISTER DATABASE TRIGGER'))) A
                        FULL   OUTER JOIN (SELECT GRANTEE,
                                                 PRIVILEGE
                                          FROM   DBA_SYS_PRIVS
                                          WHERE  (PRIVILEGE LIKE 'DROP ANY%' OR PRIVILEGE LIKE 'GRANT%' OR PRIVILEGE IN ('ADMINISTER DATABASE TRIGGER'))
                                          AND    GRANTEE NOT IN (SELECT ROLE
                                                                 FROM   DBA_ROLES)) B ON B.GRANTEE = A.GRANTEE))
        START  WITH RN = 1
        CONNECT BY PRIOR RN = RN - 1
            AND    PRIOR GRANTEE = GRANTEE
        GROUP  BY GRANTEE) A,
       DBA_USERS B
WHERE  A.GRANTEE = B.USERNAME
ORDER  BY 1;

+10