Oracle user created information

data dictionaryoracle

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?

Best Answer

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

Related Solutions

Are there any tables that I can get all Oracle supported system privileges and object privileges

Perhaps you are looking for SYSTEM_PRIVILEGE_MAP?

Sql-server – How to connect multiple users/schemas in Oracle 11g

You need to grant the REFERENCES privilege on the reference table to user2. (See GRANT, Table Privileges section. Note that this cannot be granted to a role, must be granted to the user directly.)

Here's a demo:

SQL> create user user1 identified by user1;
User created.
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.

SQL> create user user2 identified by user2;
User created.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.

SQL> create table user1.ref_table (id number primary key);
Table created.
SQL> insert into user1.ref_table values (1);
1 row created.
SQL> insert into user1.ref_table values (2);
1 row created.
SQL> commit;
Commit complete.

SQL> grant references on user1.ref_table to user2;
Grant succeeded.

SQL> connect user2/user2;
Connected.
SQL> create table oth_table (thing number, fk number);
Table created.
SQL> alter table oth_table add(constraint fk1 foreign key (fk)
  2      references user1.ref_table on delete set null);
Table altered.

SQL> insert into oth_table values (42, 1);
1 row created.
SQL> insert into oth_table values (256, 2);
1 row created.
SQL> commit;
Commit complete.

SQL> connect user1/user1;
Connected.
SQL> delete from ref_table where id = 1;
1 row deleted.
SQL> commit;
Commit complete.

SQL> connect user2/user2;
Connected.
SQL> select * from oth_table;

     THING     FK
---------- ----------
    42
       256      2

If you want admin to do all the work, it'll need the CREATE ANY INDEX privilege, and you still need the REFERENCES grant to user2.

Here's how that could work:

SQL> create user user1 identified by user1;
User created.
SQL> create user user2 identified by user2;
User created.
SQL> create user admin identified by admin;
User created.

--- Grants
----------------
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.
SQL> grant create session, create any table,
  2        create any index, alter any table to admin;
Grant succeeded.

--- Create reference table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user1.ref_table (id number primary key);
Table created.

--- Do the "references" grant
----------------
SQL> connect / as sysdba
Connected.
SQL> grant references on user1.ref_table to user2;
Grant succeeded.

--- Create the second table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user2.oth_table (foo number, bar number);
Table created.

--- Add the constraint
----------------
SQL> alter table user2.oth_table add(constraint fk
  2      foreign key (bar) references user1.ref_table(id)
  3      on delete set null);
Table altered.

Best Answer

Related Solutions

Are there any tables that I can get all Oracle supported system privileges and object privileges

Sql-server – How to connect multiple users/schemas in Oracle 11g

Related Question