User Management

1. Finding all the privileges and roles granted to a user

To find all the privileges and roles granted to a user 3 dictionary views comes very handy:

1. dba_sys_privs
2. dba_tab_privs
3. dba_role_privs

I queried all 3 views and then verified with toad to match the result for a user called "USERNAME".
The result was very accurate.

SQL> select GRANTEE, PRIVILEGE from dba_sys_privs where GRANTEE='USERNAME';

GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------
USERNAME                        CREATE PUBLIC SYNONYM
USERNAME                        CREATE SYNONYM
USERNAME                        UNLIMITED TABLESPACE
USERNAME                        CREATE SEQUENCE
USERNAME                        CREATE ANY SYNONYM
USERNAME                        CREATE MATERIALIZED VIEW
USERNAME                        CREATE TRIGGER
USERNAME                        CREATE TABLE
USERNAME                        CREATE SESSION
USERNAME                        CREATE DATABASE LINK
USERNAME                        CREATE PROCEDURE
USERNAME                        CREATE VIEW

12 rows selected.

SQL> select GRANTEE, PRIVILEGE from dba_tab_privs where GRANTEE='USERNAME';

no rows selected

SQL> select GRANTEE, GRANTED_ROLE from dba_role_privs where GRANTEE='USERNAME';

GRANTEE                        GRANTED_ROLE
------------------------------ ------------------------------
USERNAME                        CONNECT
USERNAME                        RESOURCE

2. How to find out who has changed the user's password and when

In our day to activity we come across this situation when suddenly a password gets changed and no one takes responsibility who did this. So here is the way to find out the genius who is not taking responsibility. Or simply we can say it is the best way to find out who did the password change in oracle database.

Note: We can find out the user who changed the password and when he did, only when we have below 4 steps already in place. 

Step1- Create a table

SQL> create table reyaj (msg varchar2(1000));
Table created.

Step2- Create a prodedure

SQL> create or replace procedure reyaj_p (who in varchar2, what in varchar2)
  is
  pragma autonomous_transaction;
  begin
 insert into reyaj values (who||' modifies '||what||'''s password at '||systimestamp);
 commit;
  end;
   /
  Procedure created.

Step 3 - Create a function

SQL> CREATE OR REPLACE FUNCTION verify_function
  (username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS
  BEGIN
  reyaj_p (user, username);
  RETURN(TRUE);
  END;
 /
 Function created.

Step 4 - Alter default profile

SYS> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_function;
Profile altered.

Step 5 - Change password 

SQL> alter user test identified by test321;
User altered.

Step 6 - Now query the table to find out who changed the password and when

SQL> select * from reyaj;

MSG
---------------------------------------------------------------------------------------------------------------------------
SYS modifies TEST's password at 02-MAY-16 02.37.27.319762 AM -05:00

Step 7 - We can test it again if it is working properly and creating a new user and chaning its password.

SQL> create user passtest identified by passtest123;
User created.

SQL> alter user passtest identified by passtest321;
User altered.

SQL> select * from reyaj;
MSG
---------------------------------------------------------------------------------------------------------------------------
SYS modifies TEST's password at 02-MAY-16 02.37.27.319762 AM -05:00
SYS modifies PASSTEST's password at 02-MAY-16 02.39.11.985540 AM -05:00
SYS modifies PASSTEST's password at 02-MAY-16 02.39.53.454250 AM -05:00

Posted by: Riyaz