/* Current user must be SYS or have the following privileges: CREATE ANY PROCEDURE CREATE ANY VIEW CREATE PUBLIC SYNONYM SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE (or DBA) Target user must have the following directly (not via a role): SELECT ON DBA_USERS WITH GRANT OPTION ALTER USER If target user has not the first privilege then current user must be SYS If target user has not the second privilege then current user must have the following privilege (to grant this privilege to target): GRANT ANY PRIVILEGE This script creates 1 view, 1 package and 2 public synonyms on them. It also grants 2 privileges to the target user (see above) Target cannot be SYS */ Whenever sqlerror exit 1 set serveroutput on Accept dba format a30 default SYSTEM - prompt 'Target schema (default SYSTEM)? ' Declare cnt1 pls_integer; cnt2a pls_integer; cnt2b pls_integer; cnt3b pls_integer; dba varchar2(30); Begin -- Check target schema dba := sys.dbms_assert.schema_name(upper('&dba')); If dba = 'SYS' then raise_application_error (-20000, 'Target schema can''t be SYS'); End if; dbms_output.put_line ('Target schema ('||dba||') exists'); -- Mandatory privileges to create the target objects Select count(*) into cnt1 from session_privs where privilege in ('CREATE ANY PROCEDURE', 'CREATE ANY VIEW', 'CREATE PUBLIC SYNONYM'); -- Mandatory privilege to grant target schema Select count(*) into cnt2a from session_privs where privilege = 'GRANT ANY PRIVILEGE'; -- Check target schema privilege Select count(*) into cnt2b from dba_sys_privs where grantee = dba and privilege = 'ALTER USER'; Select count(*) into cnt3b from dba_tab_privs where grantee = dba and owner = 'SYS' and table_name = 'DBA_USERS' and grantable = 'YES'; -- Check current user privileges are sufficient If user != 'SYS' and ( cnt1 != 3 or cnt2a+cnt2b = 0 or cnt3b = 0 ) then raise_application_error (-20000, 'Current user has not enough privileges to complete the task'); End if; dbms_output.put_line ('Current user has enough privileges to complete the task'); -- Grant privileges to target schema If cnt2b = 0 then execute immediate 'Grant ALTER USER to '||dba; End if; If cnt3b = 0 then execute immediate 'Grant SELECT on SYS.DBA_USERS to '||dba||' with grant option'; End if; dbms_output.put_line ('Target schema has the mandatory privileges'); End; / -- Create the view Prompt creation of view &dba..mca_users... Create or replace view &dba..mca_users as select username, account_status, lock_date, expiry_date from dba_users where username not in ( 'ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'MDSYS', 'MGMT_VIEW', 'OLAPSYS', 'ORACLE_OCM', 'ORASYBMAINT', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'AURORA$JIS$UTILITY$', 'AURORA$ORB$UNAUTHENTICATED', 'TSMSYS', 'WMSYS', 'XDB', 'PERFSTAT','APPQOSSYS' ) / -- Create the package Prompt creation of package &dba..mca_pkg_users... Create or replace package &dba..mca_pkg_users as procedure lock_user (p_user in varchar2); procedure unlock_user (p_user in varchar2); procedure change_psw ( p_user in varchar2, p_newpsw in varchar2, p_oldpsw in varchar2 default null); End mca_pkg_users; / Show errors Create or replace package body &dba..mca_pkg_users as procedure lock_user (p_user in varchar2) is begin execute immediate 'Alter user '||sys.dbms_assert.schema_name(p_user)||' account lock'; end lock_user; procedure unlock_user (p_user in varchar2) is begin execute immediate 'Alter user '||sys.dbms_assert.schema_name(p_user)||' account unlock'; end unlock_user; procedure change_psw ( p_user in varchar2, p_newpsw in varchar2, p_oldpsw in varchar2 default null) is begin execute immediate 'Alter user '||sys.dbms_assert.schema_name(p_user)|| ' identified by '||sys.dbms_assert.enquote_name(p_newpsw, false); end change_psw; End mca_pkg_users; / Show errors -- Create the synonyms Create or replace public synonym mca_users for &dba..mca_users; Create or replace public synonym mca_pkg_users for &dba..mca_pkg_users;