The more complex your database gets, the more complicated it is to drop a user.
Generally, it’s ok to lock users out only but sometimes (esp. if you are as fussy as I am 🙂 ) it’s better to drop them just to keep your database clean.
In this case, feel free to explore the following script which might help you identify what dependencies are there for that particular user:
CREATE OR REPLACE PROCEDURE safe_drop(username IN VARCHAR2) IS
-- number of issues occured during the check
v_issues NUMBER;
-- number of checks performed
v_check NUMBER;
-- processing variables
v_username VARCHAR2(30);
v_sql VARCHAR2(4000);
TYPE t_sourcetyp IS REF CURSOR;
v_sourcecursor t_sourcetyp;
v_owner VARCHAR2(30);
v_name VARCHAR2(30);
v_type VARCHAR2(12);
BEGIN
v_username := upper(username);
v_check := 0;
-- check if there are any user object on which application might be dependent
dbms_output.put_line(chr(10) || '*** Check dependencies in DBA_DEPENDENCIES ***********************');
v_issues := 0;
FOR i IN (SELECT TYPE
,owner
,NAME
,referenced_type
,referenced_owner
,referenced_name
FROM dba_dependencies
WHERE owner IN
(SELECT username
FROM dba_users
WHERE profile IN ('APPLICATION', 'INTERFACE'))
AND referenced_owner = v_username)
LOOP
dbms_output.put_line('Application ' || i.type || ' ' || i.owner || '.' ||
i.name || '' || ' has reference to user ' ||
i.referenced_type || ' ' || i.referenced_owner || '.' ||
i.referenced_name || '.' || chr(10));
v_issues := v_issues + 1;
END LOOP;
IF v_issues = 0
THEN
dbms_output.put_line('No dependencies in DBA_DEPENDENCIES.');
ELSE
v_check := v_check + 1;
END IF;
-- check direct grants from USER => APPLICATION
v_issues := 0;
dbms_output.put_line(chr(10) || '*** Check direct grants granted by USER to APPLICATION (GRANTS WILL BE REVOKED IF USER IS DROPPED) ***********');
FOR i IN (SELECT grantee
,owner
,table_name
,privilege
,grantor
FROM dba_tab_privs
WHERE grantee IN
(SELECT username
FROM dba_users
WHERE profile IN ('APPLICATION', 'INTERFACE'))
AND grantor = v_username)
LOOP
dbms_output.put_line(i.grantor || ' granted direct grant to ' || i.grantee || ' on ' || i.owner || '.' || i.table_name || '.');
v_issues := v_issues + 1;
END LOOP;
IF v_issues = 0
THEN
dbms_output.put_line('No direct grants granted by USER to APPLICATION.');
ELSE
v_check := v_check + 1;
END IF;
-- check direct grants from APPLICATION => USER
dbms_output.put_line(chr(10) || '*** Check direct grants on application objects granted to user ***');
SELECT COUNT(*)
INTO v_issues
FROM dba_tab_privs
WHERE grantee = v_username
AND owner IN (SELECT username
FROM dba_users
WHERE profile IN ('APPLICATION', 'INTERFACE')
AND table_name NOT IN ('PLAN_TABLE',
'PLSQL_PROFILER_RUNS',
'PLSQL_PROFILER_DATA',
'PLSQL_PROFILER_UNITS',
'REWRITE_TABLE'));
IF v_issues = 0
THEN
dbms_output.put_line('No direct grants on application objects to user.');
ELSE
dbms_output.put_line('User ' || v_username || ' has direct grants to APPLICATION or INTERFACE schemas. If you intend to drop the user do so in maintenance window only.' ||
chr(10));
END IF;
-- check if application source code is dependant on user objects
dbms_output.put_line(chr(10) || '*** Check dynamic reference to user objects in DBA_SOURCE ********');
v_sql := 'select owner, name, type from dba_source ' ||
'where owner in (select username from dba_users where profile in (''APPLICATION'',''INTERFACE'')) and (' ||
' upper(text) like ''%' || v_username || '.%'' ' ||
') group by owner, name, type order by owner, type, name';
OPEN t_sourcecursor FOR t_sql;
LOOP
FETCH t_sourcecursor
INTO t_owner
,t_name
,t_type;
EXIT WHEN t_sourcecursor%NOTFOUND;
v_issues := t_sourcecursor%ROWCOUNT;
IF v_issues != 0
THEN
dbms_output.put_line('Application ' || v_type || ' ' || v_owner || '.' || v_name || ' calls object in user schema.');
v_check := v_check + 1;
END IF;
END LOOP;
CLOSE t_sourcecursor;
-- check dba_jobs
dbms_output.put_line(chr(10) || '*** Check DBA_JOBS reference *************************************');
v_issues := 0;
FOR i IN (SELECT job
FROM dba_jobs
WHERE log_user = v_username
OR priv_user = v_username
OR schema_user = v_username)
LOOP
dbms_output.put_line('Job ' || i.job || ' has reference to user.');
v_issues := v_issues + 1;
END LOOP;
IF v_issues = 0
THEN
dbms_output.put_line('No DBA_JOBS referencies.');
ELSE
v_check := v_check + 1;
END IF;
-- check dba_scheduler_jobs
dbms_output.put_line(chr(10) || '*** Check DBA_SCHEDULER_JOBS reference ***************************');
v_issues := 0;
FOR i IN (SELECT job_name
FROM dba_scheduler_jobs
WHERE owner = v_username
OR job_creator = v_username)
LOOP
dbms_output.put_line('Scheduler job ' || i.job_name || ' has reference to user.');
v_issues := v_issues + 1;
END LOOP;
IF v_issues = 0
THEN
dbms_output.put_line('No DBA_SCHEDULER_JOBS referencies.');
ELSE
v_check := v_check + 1;
END IF;
-- check indexes
dbms_output.put_line(chr(10) || '*** Check INDEXES ************************************************');
v_issues := 0;
FOR i IN (SELECT index_name
,table_owner || '.' || table_name table_path
FROM dba_indexes
WHERE index_name IN (SELECT object_name
FROM dba_objects
WHERE object_type = 'INDEX'
AND owner = v_username
AND table_owner <> v_username))
LOOP
dbms_output.put_line('Index ' || i.index_name || ' on table ' || i.table_path);
v_issues := v_issues + 1;
END LOOP;
IF v_issues = 0
THEN
dbms_output.put_line('No indexes.');
ELSE
v_check := v_check + 1;
END IF;
-- Final check
dbms_output.put_line(chr(10) || '*** FINAL CHECK **************************************************');
v_issues := 0;
SELECT nvl(COUNT(*), 0) INTO v_issues FROM dba_objects;
IF v_check > 0
THEN
dbms_output.put_line('!!! It is not safe to drop user, see report above. Check all sections. !!!' || chr(10));
ELSE
dbms_output.put_line('It is safe to drop user.' || chr(10));
IF v_issues > 0
THEN
dbms_output.put_line('drop user ' || v_username || ' cascade;');
ELSE
dbms_output.put_line('drop user ' || v_username || ' ;');
END IF;
END IF;
--- exceptions
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Ouch! Error:' || chr(13) || chr(10) || SQLERRM);
END;