Creating an Oracle Account Expiration Report

Dev

Creating an Oracle Account Expiration Report

by Mitch Bond
//

To be more proactive in prevention of account expirations, create an automated report identifying those nearing expiration. Using the scripts outlined below, a report can be set up to run on startup. This provides awareness with ease. The following were excecuted using the SYS account.

Create an account responsible for the report process. Be sure to grant connect privilege to this account.

CREATE USER Expire_Report_User IDENTIFIED BY “EXAMPLE”;
GRANT CONNECT TO Expire_Report_User;

Next, create a directory object specifying the destination for the report. Assign write privilege to account created.

CREATE DIRECTORY DIR_EXPIRE_REPORT AS ‘C:UsersmbondDesktop’;
GRANT WRITE ON DIRECTORY DIR_EXPIRE_REPORT TO Expire_Report_User;

Create a view using fields from the DBA_USERS table, including USERNAME, ACCOUNT_STATUS, and EXPIRY_DATE. Grant select privilege to the execution account and create a private synonym the account.

CREATE OR REPLACE VIEW V_ACCOUNT_EXPIRATION AS (
SELECT
USERNAME,
ACCOUNT_STATUS,
EXPIRY_DATE
FROM
DBA_USERS) WITH READ ONLY;
GRANT SELECT ON V_ACCOUNT_EXPIRATION TO Expire_Report_User;
CREATE SYNONYM Expire_Report_User.V_ACCOUNT_EXPIRATION FOR SYS.V_ACCOUNT_EXPIRATION;

Write a stored procedure that generates the report using the previously created view.

CREATE OR REPLACE PROCEDURE SP_EXPIRE_REPORT
AS
O_FILE UTL_FILE.FILE_TYPE;
l_fmt1 CONSTANT VARCHAR2(34) := ‘

‘;<br /> l_fmt2 CONSTANT VARCHAR2(25) := ‘‘;
l_fmt3 CONSTANT VARCHAR2(19) := ‘

More About the Author

Mitch Bond

Analytics Consultant
Pitchfork’s Top 50 Albums of 2015 Pitchfork, one of the most well-known online music review sites, just released their “Top 50 Albums of the Year” list for ...
Creating an Oracle Account Expiration Report To be more proactive in prevention of account expirations, create an automated report identifying those nearing expiration. Using the ...

See more from this author →

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072

×

Love our blog? You should see our emails. Sign up for our newsletter!