This post is also available in:
Português
Some users in Oracle may have very dangerous privileges without your consent, they can cause great damage to the database. Sometimes this privilege is hidden via a chain of roles, which makes it difficult to perception.
Eg:
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;
In this example, the GRANT DBA to SYSADM was hidden. As the DBA role, IMP_FULL_DATABASE is another dangerous role, granting virtually everything a user.
Other form is also manually grant the "system grants" to a new role and hide this role through a chain of roles, as in the example above.
Eg:
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;
To track these hidden privileges, I developed the query below. After running it, the next step is to revoke unnecessary privileges and then run the query again to check if the user is clean.
Query:
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;
(You can modify it by putting other privileges you want to find)
If you are still using Oracle 10g and receive the error "ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [], []", try the adapted version below (less complex):
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;
Have you enjoyed? Please leave a comment or give a 👍!
8 comments
Skip to comment form
Hi Rodrigo, great post.
I have a question. Is there any way of identifying which user granted the DBA role i.e. which user executed the 'grant dba to user;' command? Notably, user who may have granted themselves dba (understandably they would be connected as sys or system, unless you know of other means!).
Guessing it would require auditing to be switched on for starters.
Even better if this can be gleaned through OEM.
Thanks again
Dee
Author
Hi Dee Lal,
Exactly, the only way to know who granted some user DBA is to enable the database auditing. This can be achieved by running "audit grant any role".
Regards,
RJ
Great, thanks for the confirmation Rodrigo.
This is the query I use to find users with DBA privileges:
select distinct g.grantee username
from dba_users u
, ( select grantee,granted_role from dba_role_privs
connect by prior grantee = granted_role start with granted_role='DBA'
) g
where u.username = g.grantee
order by 1;
Author
Hi Robert,
Good query. However, as you are searching privileges based on Role name, not the system privileges granted, an attacker could clone DBA role with another name and you wouldn't notice.
Regards,
RJ
There are many situations where ADMIN wanted to revoke DBA roles granted to the user. This is the best way to filter out users having dba privileges in Oracle
for the first time i visit to your page ..
i really appreciated your effort..
thanks
Author
Hi Muhammad, glad you enjoyed =]