SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /oracle/admin/mydb01/scripts/lockAccount.log append BEGIN FOR item IN ( SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN ( 'SYS','SYSTEM') ) LOOP IF item.AUTHENTICATION_TYPE='PASSWORD' THEN dbms_output.put_line('Locking and Expiring: ' || item.USERNAME); execute immediate 'alter user ' || sys.dbms_assert.enquote_name( sys.dbms_assert.schema_name( item.USERNAME),false) || ' password expire account lock' ; ELSE dbms_output.put_line('Locking: ' || item.USERNAME); execute immediate 'alter user ' || sys.dbms_assert.enquote_name( sys.dbms_assert.schema_name( item.USERNAME),false) || ' account lock' ; END IF; END LOOP; END; / spool off
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
Tuesday, October 29, 2024
Lock and expire users
An anonymous PL/SQL block to lock and expire open accounts:
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment