Part 5, Oracle Network and Security [Q] How to define a specific IP access database [A] You can use the login trigger, CMGW, or add a protocol.ora file under $ OREACLE_HOME / Network / Admin (Some OS may be. Protocol.ora), 9i can directly modify SQLNET.ORA: Added: tcp.validnode_checking = YES # iptcp.Inited_nodes = (IP1, IP2, ....) # iptcp.excluded_nodes = (IP1, " IP2, ......)
[Q] How to pass through the firewall connection database [A] This issue will only appear on the WIN platform, and UNIX platforms will be saved. Solution: In SQLNET.ORA server should look similar SQLNET.AUTHENTICATION_SERVICES = (NTS) NAMES.DIRECTORY_PATH = (TNSNAMES, ONAMES, HOSTNAME) TRACE_LEVEL_CLIENT = 16 HOME0 registry plus [HKEY_LOCAL_MACHINE] USE_SHARED_SOCKET = TRUE
[Q] How to use the hostname mode to connect to the database host name only support the TCP / IP protocol Small LAN to modify the following information in Listener.ora (SID_DESC = (Global_DBNAME = UR_HOSTNAME) - Your Machine Name (Oracle_Home = E: / Oracle / ORA92) - ORACLE Home (SID_NAME = Orcl) - Sid Name) Then in the client's SQLNET.ORA, make sure there is Names.Directory_Path = (HostName) You can use the name of the database server to access the database.
[Q] What security hazards can be brought by dbms_repcat_admin [A] If a user can execute a DBMS_REPCAT_ADMIN package, great system permissions will be obtained. The following may get the execution permission of the package: 1. Grant Execute on dbms_repcat_admin to public [| User_name] 2 under SYS, the user has an Execute Any Procedure privilege (below 9i, 9i must display the authorization) If the user performs the following statement : EXEC SYS.DBMS_REPCAT_ADMIN.GRANT_ADMIN_ADMIN.GRANT_ADMIN_ANAY_SCHEMA ('user_name "); the user will get great system privileges to get detailed information from user_sys_privs
[Q] When do not know the user password, how to jump to another user does not affect the user? [A] We can safely use the user safely through the following method, then jump back, Some useful use of ALTER USER privileges or DBA privilege: SQL> SELECT Password from dba_users where username = 'scott'; password ----------------------- ------ F894844C34402B67SQL> alter user scott identified by lion; User altered.SQL> connect scott / lionConnected.REM Do whatever you like ... SQL> connect system / managerConnected.SQL> alter user scott identified by values' F894844C34402B67 '; User altered.sql> Connect Scott / TigerConnected. [Q] How to reinforce your database [a] To pay attention to the following: Modify Sys, System passwords. 2. LOCK, modification, delete default users: dbsnmp, ctxsys, etc. 3. Change Remote_OS_AUTHENT to FALSE to prevent remote machines from logging in directly. 4. Change O7_DICTIONARY_ACCESSIBILITY to False. 5. Cancel some permissions from Public Role. 6. Check the security of the database's data files. Do not set to 666. Check other DBA users. 7. Close some unwanted services (such as FTP, NFS, etc.) 8. Limit the number of users above the database host. 9. Regularly check the security alert above the MetaLink / OTN. For example: http: //otn.racle.com/deploy/security/alerts.htm 10. Place your database with the app in a separate subnet, or your user's password is easily removed by Sniffer. Or use Advance Security to log in to the user. 11. Restrictions only some IP can access your database. 12. lsnrctl To add a password, other people can easily turn off your Listener from the outside. 13. If possible, do not use the default 1521 port
[Q] How to check if the user uses the default password [A] If you use the default password, it is likely that you can create a certain security hazard for your database, then you can use the following query to get those users using the default password Select UserName "User (s ) with Default password "from dba_users where password in ( 'E066D214D5421CCC', -! dbsnmp '24ABAB8B06281B4C', - ctxsys '72979A94BAD2AF80', - mdsys 'C252E8FA117AF049', - odm 'A7A32CD03D3CE8D5', - odm_mtr '88A2B2C183431F00' , - ordplugins '7EFA02EC7EA6B86F', - ordsys '4A3BA55E08595C81', - outln 'F894844C34402B67', - scott '3F9FBD883D787341', - wk_proxy '79DF7A1BD138CF11', - wk_sys '7C9BA362F8314299', - wmsys '88D8364765FCE6AF', - xdb 'F9DA8977092B7B81', - tracesvr '9300C0977D7DC75E', - oas_public 'A97282CE3D94E29E', - websys 'AC9700FD3F1410EB', - lbacsys 'E7B5D92911C831E1', - rman 'AC98877DE1297365', - perfstat '66F4EF5650C20355', - - EXFSYS '84B8CBCA4D477FA3', - Si_INFORMTN_SCHEMA 'D4C5016086B2DC6A', - SYS 'D4DF7931AB130E37') - System /
[Q] How to modify the default xdb listening port [A] Oracle9i default XML DB puts HTTP's default port to 8080, this is a very common port, and many other webserver use this port if we installed it. It is best to modify it, avoid conflicts, if not, do not install three modifications 1.DBCA, choose your database, then Standard Database Features-> Customize-> Oracle XML DB Option, enter this You should know how to change the picture. 2.Oem Console, modify it in XML Database Configuration 3. Use Oracle Provided Packets: - Change the HTTP / WebDAV port from 8080 SQL> Call dbms_xdb.cfg_update (UpdateXML (dbms_xdb.cfg_get (), '/ xdbconfig / sysconfig / protocolconfig / httpconfig / http-port / text () ', 8081)) / - Change the FTP port from 2100 to 2111 SQL> Call DBMS_XDB.CFG_UPDATE (UpdateXML (DBMS_XDB.CFG_GET (),' / xdbconfig / sysconfig / protocolconfig / ftpconfig / ftp-port / text () ', 2111)) / SQL> Commit; SQL> EXEC DBMS_XDB.CFG_REFRESH; - Check the modification has been successful SQL> SELECT DBMS_XDB.CFG_GET from DUAL;
[Q] how to capture user login information, such as SID, IP address, etc. [A] may be utilized login trigger, such as CREATE OR REPLACE TRIGGER tr_login_recordAFTER logon ON DATABASEDECLAREmiUserSid NUMBER; mtSession v $ session% ROWTYPE; CURSOR cSession (iiUserSid IN NUMBER) ISSELECT * FROM v $ sessionWHERE sid = iiUserSid; BEGINSELECT sid INTO miUserSid FROM v $ mystat WHERE rownum <= 1; OPEN cSession (miUserSid); FETCH cSession INTO mtSession; - if user exists then insert dataIF cSession% FOUND THENINSERT INTO log $ information (login_user, login_time, ip_adress, ausid, terminal, osuser, machine, program, sid, serial #) VALUES (ora_login_user, SYSDATE, SYS_CONTEXT ( 'USERENV', 'IP_ADDRESS'), userenv ( 'SESSIONID'), mtSession.Terminal, mtSession.Osuser, mtSession.Machine, mtSession.Program, mtSession.Sid, mtSession.Serial #); ELSE - if user do not exists then return errorsp_write_log ( 'Session Information Error:' || SQLERRM); CLOSE cSession; raise_application_error (-20099, 'login exception', false; end if; close csession; Exceptionwhen Others thensp_write_log ('login trigger error:' || sqlerrm); End TR_Login_Record; need to be in the above flip To pay attention to the following points, the user has a V_ $ session with the object query permissions of V_ $ MyStat, which can be explicitly licensed under SYS. 2, sp_write_log originally a process of writing logs, can be replaced with your own needs, such as NULL skip. 3. You must create a log address login message before creating the trigger.
[Q] DDL statements how capture the entire database or that variations and modifications object structure [A] may be employed DDL triggers, such as CREATE OR REPLACE TRIGGER tr_trace_ddlAFTER DDL ON DATABASE DECLAREsql_text ora_name_list_t; state_sql ddl $ trace.ddl_sql% TYPE; BEGINFOR i IN 1..ora_sql_txt (sql_text) LOOPstate_sql: = state_sql || sql_text (i); END LOOP; INSERT INTO ddl $ trace (login_user, ddl_time, ip_address, audsid, schema_user, schema_object, ddl_sql) VALUES (ora_login_user, SYSDATE, userenv ( 'SESSIONID'), sys_context ( 'USERENV', 'IP_ADDRESS'), ora_dict_obj_owner, ora_dict_obj_name, state_sql); EXCEPTION wHEN OTHERS THEN sp_write_log ( 'Capture DDL Excption:' || SQLERRM); END tr_trace_ddl; to create a more triggers when Note Note, you must create a DDL $ TRACE table, used to record DDL record 2, sp_write_log is originally a write log process, can be replaced with your own needs, such as Null skip. [Q] DML statements on how to capture a table (not select) statement) [A] may be employed dml triggers, such as CREATE OR REPLACE TRIGGER tr_capt_sqlBEFORE DELETE OR INSERT OR UPDATE ON manager.testDECLAREsql_text ora_name_list_t; state_sql capt $ sql.sql_text% TYPE; BEGINFOR i IN 1..ora_sql_txt (sql_text) LOOPstate_sql: = state_sql || sql_text (i); END LOOP; INSERT INTO capt $ sql (login_user, capt_time, ip_address, audsid, owner, table_name, sql_text) VALUES (ora_login_user, sysdate, sys_context ( 'USERENV', 'IP_ADDRESS'), userenv ( 'SESSIONID'), 'MANAGER', 'TEST', state_sql); EXCEPTION WHEN OTHERS THENsp_write_log ( 'Capture DML Exception:' || SQLERRM); END tr_capt_sql; Pay attention to a few points when creating more than the above trigger, you must create a table of CAPT $ SQL, to record DDL record 2, sp_write_log originally a process of writing logs, can be replaced with your own needs, such as NULL skips.
Section 6, OS-related and other [Q] how to generate a date format file [A] On Linux / UNIX, use the `Date % Y% M% D` (` this is the key on the keyboard ~ Or $ (DATE % Y% M% D), such as: Touch Exp_table_name_`date % Y% M% D`.dmpdate = $ (DATE % Y% M% D) or Date = $ (Date % Y % M% D --Date '1 Days Ago') # Get the date of yesterday or more days, using% DATE: ~ 4, 10%, where 4 is the start character, 10 is the extraction length, indicating from DATE During the date, the extraction of the start length is 10 strings of 10. You can change to other numbers you need, such as: Echo% Date: ~ 4, 10% If you want to get more accurate time, Win can also use TIME [q] test disks and array performance [A] Similar methods Test Writing Ability TIME DD IF = / Dev / Zero of = / ORADATA / BIDDB / TESTIND / TESTFILE.DBF BS = 1024000 Count = 1000 System IO Use (UNIX): iostat-xnp 2 Display Busy Level
[Q] How to configure SSH key [A] Can prevent "Intermediary" offensive mode 1, ssh-keygen or ssh-keygen -d (ssh 2.x) generates a key 2, then copy a table to the server you want to log in , Renamed Authorized_Keys, if it is 3.0 or less, you need to change to Authorized_Keys23, you can also use the config file to further simplify operations such as Host * BJHostName machine name or IPuser username with this configuration file, you can use SSH BJ to access the specified The machine, you can use SCP to transfer files with SFTP.
[Q] How to automatically upload / download in the script / download [A] You can write FTP to the shell script, such as ftp -n -i host ip << Eofuser username passcd target directory PUT Fileget File # Query file LS # Exit Byeeof
