DBA Utility Scripts using PLSQL

1
DBA Utility Scripts using PL/SQL

• Month Day, Year
• Michael Zipse

2
Dropping a dictionary managed table with many
extents

• Table created without storage params inherited
  default values meant for code tables
• Table now 1.3 GB 176242 extents
• If you drop it, there will be massive contention
  for FET and UET. Also SMON will spin, consuming
  100 of a CPU while coalescing extents.
• In an ideal world, we would control when/how fast
  this happens.

3
Dropping a dictionary managed table with many
extents

• Solution1
• TRUNCATE TABLE COMMIT_PRICE_XML_OLD REUSE
  STORAGE
• ALTER TABLE COMMIT_PRICE_XML_OLD DEALLOCATE
  UNUSED KEEP 1120M
• ALTER TABLE COMMIT_PRICE_XML_OLD DEALLOCATE
  UNUSED KEEP 840M
• ALTER TABLE COMMIT_PRICE_XML_OLD DEALLOCATE
  UNUSED KEEP 560M
• ALTER TABLE COMMIT_PRICE_XML_OLD DEALLOCATE
  UNUSED KEEP 280
• DROP TABLE COMMIT_PRICE_XML_OLD
• Outcome
• Database contention became unbearable killed
  process and lost all work.

4
Dropping a dictionary managed table with many
extents Metalink Note 180553.996

• table_size pls_integer
• table_name varchar2(60) 'CMT.COMMIT_PRICE_XML_O
  LD'
• sql_cur pls_integer dbms_sql.open_cursor
• default_sql varchar2(100) 'alter table '
  table_name ' deallocate unused keep '
• sql_stmt varchar2(500)
• begin
• select round(sum(bytes)/(10241024),0) into
  table_size
• from dba_extents where owner'.'segment_name
  table_name
• loop
• table_size table_size - 1
• sql_stmt default_sql table_size 'M'
• dbms_sql.parse(sql_cur, sql_stmt,
  dbms_sql.native)
• exit when table_size lt 2
• end loop
• dbms_sql.close_cursor(sql_cur)

5
Wrap it up to limit credentials

• Application needs permission to truncate work
  tables
• Could Let application connect as table owner, an
  unattractive option at best.

6
Wrap it up to limit credentials

• CREATE OR REPLACE PROCEDURE trunc_table_dw
  (table_name_in varchar2) AS
• cursor_handle integer dbms_sql.open_cursor
  
• invalid_table EXCEPTION
• BEGIN
• IF (upper(table_name_in) 'DW_AUTO_VALUATION_M
  ODEL') OR
• (upper(table_name_in) 'DW_LOAN_ADJUSTMENT_
  ACTIVITY') OR
• (upper(table_name_in) 'DW_LOAN_AUTO_EVAL')
  OR
• (upper(table_name_in) 'DW_LOAN_DELIVERY_SU
  PPORT') OR
• (upper(table_name_in) 'DW_LOAN_EVAL_HOME_E
  QUITY_QUAL') OR
• (upper(table_name_in) 'DW_LOAN_SUBMISSION'
  ) OR
• (upper(table_name_in) 'DW_MLY_FUNDED_LOAN'
  ) OR
• (upper(table_name_in) 'DW_PEND_LOAN') OR
• (upper(table_name_in) 'DW_PR_SVC_LOAN_STAT
  US_HIST') OR
• (upper(table_name_in) 'DW_TABLE_STATUS')
• THEN
• RAISE invalid_table
• ELSE
• dbms_sql.parse(cursor_handle,
• 'TRUNCATE TABLE ' table_name_in,

7
Wrap it up to limit credentials

• CREATE OR REPLACE PROCEDURE SECURITY_ADMIN
• (p_command IN VARCHAR2 DEFAULT NULL,
• p_parm1 IN VARCHAR2 DEFAULT NULL,
• p_parm2 IN VARCHAR2 DEFAULT NULL) IS
• /
  
• NAME SECURITY_ADMIN(p_command,
  p_parm1, p_parm2)
• PURPOSE This procedure is a wrapper for
  the package SECURITY_P_01 and
• provides a caling interface for the
  functions therein.
• REVISIONS
• Ver Date Author
  Description
• --------- ---------- ---------------
  ------------------------------------
• 1.0 05/23/2000 Justin White
  Created this procedure
• 
  /
• e_no_command_passed EXCEPTION
• e_userid_in_exclude_list EXCEPTION
• v_return_value NUMBER 0
• BEGIN
• IF (p_command IS NULL) THEN
• RAISE e_no_command_passed

8
Identity Management

• Problem As users left their positions cleanup of
  their credentials did not always happen need to
  identify stale personal userids and drop them.
• Solution
• Run a script nightly against databases that scans
  thru audit trail and records who logged on.
• Based in this info, lock accounts that have not
  been used for 90 days.
• Drop accounts that have been locked for 30 days.

9
Cursor Variablesbuild it and run it

• cursor c_databases is select DB_NAME from
  production_databases order by 1
• begin
• for databases_rec in c_databases
• loop
• begin
• dbms_output.put_line(chr(10))
• dbms_output.put_line('database
  'databases_rec.db_name)
• OPEN username_cv FOR
• 'select to_char(timestamp,'chr(39)'YYYY-m
  m-dd'chr(39)'),username from
  dba_audit_session_at_'databases_rec.db_name
• ' where returncode0 group by
  to_char(timestamp,'chr(39)'YYYY-mm-dd'chr(39
  )'),username'

10
Identity Management upsert

• LOOP
• FETCH username_cv INTO username_rec
• EXIT WHEN username_cvNOTFOUND
• SELECT COUNT() INTO v_entry_exists FROM
  username_activity
• WHERE username
  username_rec.username and databasedatabases_rec.d
  b_name
• IF (v_entry_exists 0) THEN
• / Ok, so the
  username-database entry is not already there.
  Create a new entry. /
• INSERT
• INTO username_activity
  (username, database, last_successful_login)
• VALUES (username_rec.username
  , databases_rec.db_name, to_date(username_rec.time
  stamp,'YYYY-mm-dd'))
• COMMIT
• ELSE
• UPDATE username_activity
• SET last_successful_login
  to_date(username_rec.timestamp,'YYYY-mm-dd')
• WHERE username
  username_rec.username and
• 
  databasedatabases_rec.db_name
• COMMIT

11
Identity Management lock stale userids

• for databases_rec in c_databases
• loop
• begin
• OPEN username_cv FOR
• 'select username,last_successful_login from
  username_activity where database '
• chr(39)databases_rec.db_namechr(39)
• ' and LAST_SUCCESSFUL_LOGIN lt SYSDATE -
  90'
• ' and ((DATE_LOCKED is null) or (date_locked
  gt (sysdate - 4)))'
• ' and username not in (select username from
  exclude)'
• ' and username not LIKE 'chr(39)'C\_'c
  hr(39)' ESCAPE 'chr(39)'\'chr(39)
• ' and username not LIKE 'chr(39)'_BATCH'
  chr(39)
• ' and not exists (select distinct owner from
  dba_objects_at_'databases_rec.db_name' where
  ownerusername)'
• LOOP
• FETCH username_cv INTO username_rec
• EXIT WHEN username_cvNOTFOUND
• DBMS_OUTPUT.put_line ('-- Last login for
  ' username_rec.username ' '
  username_rec.last_successful_login)
• DBMS_OUTPUT.put_line ('alter user '
  username_rec.username ' account lock')

12
Identity Management unlock user

• / Unlock a single username.
• PARMS
• 1 username to unlock
• 2 database
• EXAMPLE
• _at_i\common\dba\username_activity\unlock_user
  tim db
• written by Michael Zipse - GMAC-RFC /
• update sma.username_activity
• set date_lockednull
• where usernameupper('username1')
• and databaseupper('database2')
• commit
• select 'connect system_at_' 'database2' from
  dual
• select 'alter user ''username1'' account
  unlock' from dual

13
Identity Management drop stale userids

• for databases_rec in c_databases
• loop
• begin
• dbms_output.put_line('connect
  un/password_at_'databases_rec.db_name' as
  sysdba')
• OPEN username_cv FOR
• 'select username, date_locked from
  username_activity where database '
• chr(39)databases_rec.db_namechr(39)
• ' and DATE_DROPPED is null'
• ' and DATE_LOCKED lt SYSDATE - 32'
• LOOP
• FETCH username_cv INTO username_rec
• EXIT WHEN username_cvNOTFOUND
• DBMS_OUTPUT.put_line ('-- Date locked for
  ' username_rec.username ' '
  username_rec.date_locked)
• DBMS_OUTPUT.put_line ('drop user '
  username_rec.username ' cascade')
• UPDATE
  username_activity

14
Identity Management privileged accounts

• Audit Remediation Privileged accounts (DBA,
  select any table, etc)
• Password complexity requirements
• Password expiration requirements
• Aggressive schedule
• Options
• Lengthly investigation /implementation of
  multiple vendors tools
• Oracle Profiles
• But how to identify users and assign them to
  profile?

15
Identity Management privileged accounts

• for databases_rec in c_databases
• loop
• begin
• dbms_output.put_line('Database
  'databases_rec.db_name)
• dbms_output.put_line('--DBA Role')
• OPEN priv_cv FOR
• 'select grantee from sys.dba_role_privs_at_'da
  tabases_rec.db_name' where grantee not in
  (select from sysexclude minus select from
  dbaexclude) and granted_role'chr(39)'DBA'ch
  r(39)
• LOOP
• FETCH priv_cv INTO priv_rec
• EXIT WHEN priv_cvNOTFOUND
• DBMS_OUTPUT.put_line ('alter user
  'priv_rec.grantee' profile GMAC_STRONG_AUTH')
  
• END LOOP
• CLOSE priv_cv

16
Identity Management privileged accounts

• Privileged Users
• gee, Thanks Michael.
• Now I have 35 userids whose passwords expire all
  the time, I dont even know where they all areIf
  there was a way I could easily change them all at
  the same time

17
Identity Management privileged accounts

• for databases_rec in c_databases
• loop
• begin
• OPEN priv_cv FOR
• 'select username from sys.dba_users_at_'databa
  ses_rec.db_name' where username'chr(39)uppe
  r(p_username)chr(39)
• LOOP
• FETCH priv_cv INTO priv_rec
• EXIT WHEN priv_cvNOTFOUND
• DBMS_OUTPUT.put_line ('conn
  'p_username'_at_'databases_rec.db_name)
• DBMS_OUTPUT.put_line ('alter user
  'priv_rec.grantee' identified by
  "NewPassword_'databases_rec.db_name'" REPLACE
  "OldPassword_'databases_rec.db_name'')

18
Identity Management privileged accounts

• SET SERVEROUTPUT ON SIZE 1000000
• exec un.password_changer_prc('mcrue')
• Database DBX
• conn mcrue_at_DBX
• alter user mcrue identified by "NewPassword_DBX"
  REPLACE "OldPassword_DBX
• --------------------------- END of info for
  DBX-------------------------------

19
Thank You

• I hope you found this useful.
• If you have questions, please email me at
  michael.zipse_at_gmacrfc.com.