Implementing Listener Security
# Listener Security #
Category: SQL scripts
Free conference number from http://www.freeconference.com
dedicated dial-in number 1 (218) 339-2699 access code 328166
pvm_pre_change.sql
column currdate new_value pvmdt noprint select ‘pvm_comp_’ || to_char(SYSDATE, ‘DDMONRRRR_HH24MI’) || ‘.sql’ currdate from v$instance; spool &pvmdt set lines 120 pages 2000 set head off set feedback off prompt set pages 5000 lines 120 prompt select ‘prompt ‘||a.owner||’.’||a.object_name||’ – ‘||a.object_type|| chr(10)|| prompt ‘alter ‘|| prompt decode(a.object_type,’PACKAGE BODY’,’PACKAGE’,object_type) || ‘ “‘|| prompt a.owner||’”.’||a.object_name||’ compile ‘|| prompt…
Database logon trigger issue
Problem Description ——————- You create a database event trigger that fires each time a user connects to the database. For any reason, the login trigger becomes invalid. (The trigger is retrieving information from a table that may be inadvertently dropped.) Who can connect? Solution Description ——————– Connect under a user who owns the following system…
Restoring a user’s original password 1051962.101
WARNING ======= The method described in this article is not officially supported by development, therefore when you get errors using this procedure support cannot file a bug for it. Please make no mistakes when setting the HEX value with the VALUES keyword as bad values may cause internal errors. This undocumented feature exists to support…
Read More “Restoring a user’s original password 1051962.101” »
ORA-1841 Error Connecting to Upgraded Database After Set PASSWORD_LIFE_TIME
Problem: ~~~~~~~~ When you upgrade the database from Oracle V7 to V8, and then create a profile which limits password_life_time, existing users cannot login to the database. New users can login without a problem. To reproduce this problem: 1. Create a test user (test1) in a V7 database: grant connect, resource to test1 identified by…
Read More “ORA-1841 Error Connecting to Upgraded Database After Set PASSWORD_LIFE_TIME” »
find_string_in_database.sql
set lines 120 pages 2000 accept pvm prompt “Enter The String You Want to Search -> ” select distinct owner, name, type from dba_source where instr(upper(text), upper(‘&pvm’)) != 0 ; set serveroutput on DECLARE CURSOR cur_triggers IS SELECT owner, trigger_name, table_name, trigger_body FROM dba_triggers; v_position INTEGER; BEGIN DBMS_OUTPUT.ENABLE(NULL); DBMS_OUTPUT.PUT_LINE(‘OWNER TABLE NAME TRIGGER NAME ‘); DBMS_OUTPUT.PUT_LINE(‘===================================================================================================’)…
How to Make Trace Files Created by Oracle Readable by All Users ?
Problem Description: ~~~~~~~~~~~~~~~~~~~~ How do you make all trace files that Oracle creates readable to owner, group, and world or public? Any “*.trc” files created by Oracle have permissions of 640, with owner of Oracle and group DBA. For example: -rw-r—– oracle dba Setting umask in the users login script (.cshrc or .profile) does not…
Read More “How to Make Trace Files Created by Oracle Readable by All Users ?” »
How To Limit The Access To The Database So That Only One User Per Schema Are Connected (One Concurrent User Per Schema)
1. Set in INIT.ORA the parameter resource_limit = true and restart Database. Since Oracle 9iR2 (9.2.0.1) you can perform this without restarting Database by issuing as SYS: alter system set resource_limit=true; 2. Connect to Database as SYSTEM or SYS and create the following Profile: create profile single_user limit sessions_per_user 1; The other parameters should be…
Default User Profile
Problem Description ——————- The DBA needs to know which values are assigned to limits set to DEFAULT for a given profile. Querying DBA_PROFILES does not display the actual values but rather lists DEFAULT for those LIMITs. Solution Description ——————– From the SQL*Plus prompt, type: SQL> select * from dba_profiles where profile = ‘DEFAULT’; PROFILE RESOURCE_NAME…