0

Is it possible to determine what user created another user's account in Oracle database?

I have two types of users in database:

  • Users having system and object privileges
  • Users having only system privileges

I can find the owner of all users having system and object privileges.

select distinct d.username,d.account_status,t.privilege,t.owner 
from dba_users d,dba_tab_privs t where d.username=t.grantee
and d.account_status='OPEN';

But this query doesn't fetch the users having only system Privileges. Please help how to find the owner of all those users having only system privileges?

  • Hello! I wants to find my DB user owner? – Rahman Gurbaz Jan 11 '18 at 6:07
  • Question: How to find the owner of a db user? DB user have only system privileges don't have object privileges. – Rahman Gurbaz Jan 11 '18 at 6:35
0

DBA_USERS (Table) will give you access to WHEN your user was created, but AFAIK the only way to get WHO created your user is if auditing is turned on.

This also requires you to have access to the DBA_AUDIT_TRAIL table.

SELECT OS_USERNAME,
   USERNAME AS USER_WHO_CREATED_YOU,
   USERHOST,
   TERMINAL,
   TIMESTAMP,
   OBJ_NAME AS USER_CREATED,
   ACTION_NAME
 FROM DBA_AUDIT_TRAIL
 WHERE 
TIMESTAMP > SYSDATE-90
AND ACTION_NAME ='CREATE USER'
--AND OBJ_NAME = 'YOURUSERNAMEGOESHERE'

Further Reading: https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_3056.htm#i1619732

  • Dear, I am not interested to find the os-user, I wants to find who have created my database user ? – Rahman Gurbaz Jan 11 '18 at 5:32
  • I tried the bellow Query and it works: SELECT DISTINCT table_name, PRIVILEGE,owner FROM dba_tab_privs WHERE grantee = 'MKHISHKI'; because this user have object privillage and my user don't have object privillages. Please help – Rahman Gurbaz Jan 11 '18 at 6:12
  • @RahmanGurbaz my query is not limited to OS_USERNAME. That's just one of the columns that it returns. The USERNAME column will return who created your user. – MguerraTorres Jan 11 '18 at 15:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.