[Q] How to limit 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 be 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 problem will only appear in 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 embodiment hostname Connecting the database host name only supports 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] DBMS_REPCAT_ADMIN can bring any security hazard [ A] If a user can execute the 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.dbms_repcat_admin.grant_admin_Admin.grant_admin_Andmin.d ('user_name'); the user will get great system privileges can get more information from user_sys_privs [Q] How to jump to another user when do not know the user password The user does not affect the user? [A] We can safely use the user safely through the following methods, then jump back, more useful to use ALTER USER privileges or DBA permissions: 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 the password of SYS, System. 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 to cause a certain security risks to your database, then you can use the following query to get those users use 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 change the default listening port XDB [a] Oracle9i default XML DB sets the default port of HTTP to 8080. This is a very common port. Many other webserver will use this port. If we installed it, it is best to modify it, avoid conflicts, if not, It is best not to install three modifications 1.DBCA, choose your database, then Standard Database Features-> Customize-> Oracle XML DB Option, you should know how to change it.
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 if the modification has been successful SQL> SELECT DBMS_XDB.CFG_GET from Dual; [q] How to capture 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_LOGI) n_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; I need to pay attention to the following points in the above triggers, the user has V_ $ session and the V_ $ MyStat object query permissions, which can be explicitly authorized 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 Show Busy Level [Q] Configure SSH key [A] to prevent "intermediary" offensive mode 1, ssh-keygen or ssh-keygen -d (ssh 2.x) Generate a key 2, then copy a table to the server you want to log in, rename 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 the operation such as Host * BJHostName machine name or IPuser username with this configuration file, you can use SSH BJ to access the specified 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 CD target directory Put file Get file # 查询 LS #drop out BYE EOF
