Oracle – How to Disable All Password Requirements

I recommend you put the code in an AUTHID CURRENT_USER package. You can certainly pass a Boolean, but I think a Varchar2 makes the usage clear. You should unit test this code, add documentation, formalize the error handling, include instrumentation, and probably modify the reporting method.

CREATE OR REPLACE PACKAGE Maint AUTHID CURRENT_USER IS

Procedure ToggleConstraints (iNewStatus In Varchar2);

END;
/


CREATE OR REPLACE PACKAGE BODY Maint IS

Procedure ToggleConstraints (iNewStatus In Varchar2) Is
Begin
   If (UPPER(iNewStatus) NOT IN ('ENABLED','DISABLED')) Then
      Raise_Application_Error(-20001
         , 'Constraints can only be toggled to ENABLED OR DISABLED.');
   End If;

   For vConstraint In 
   (
       SELECT 'alter table ' || table_name      
         || DECODE(UPPER(iNewStatus), 'DISABLED',' disable',' enable')
         || ' constraint ' || constraint_name As Statement
       FROM user_constraints 
       WHERE constraint_type = 'R' 
       AND status = DECODE(UPPER(iNewStatus),'DISABLED','ENABLED','DISABLED')
   ) Loop
      DBMS_Output.Put_Line(vConstraint.Statement);
      execute immediate vConstraint.Statement;
   End loop;
End;


END;
/

Set serveroutput on size 1000000 format wrapped
EXECUTE Maint.ToggleConstraints(iNewStatus=>'ENABLED');

You'll need to decide whether you want to use OS authentication or use a password file. See http://docs.oracle.com/cd/E11882_01/server.112/e25494/dba006.htm#i1006628 to help you decide.

Will users administer the database by first logging in to the database server using eg ssh? Then you can use OS authentication.

Best practice for OS authentication: No-one should be logging in as the oracle OS user. Each DBA should have their own OS account and login as that. Their OS account should be a member of the OS DBA group (remember that only members of the OS DBA group can login using sqlplus / as sysdba).

If you want to allow remote administration, then you'll either need a secure connection to the database (just like SSH provides that to the server when administering locally) or you'll need to use a password file.