FAQs about Oracle database network and security

[Q] How to restrict specific IP addresses to access the database

[A] You can use the logon trigger, cmgw, or add A protocol under $ OREACLE_HOME/network/admin. ora file (Some OS may be. protocol. ora), 9i can directly modify sqlnet. ora:

Add the following content:

Tcp. validnode_checking = yes # Ip addresses allowed for access Tcp. inited_nodes = (ip1, ip2 ,......) # Ip addresses not allowed for access Tcp. excluded_nodes = (ip1, ip2 ,......)

[Q] how to connect to the database through the firewall

[A] this problem only occurs on the WIN platform, and is automatically solved on the UNIX platform.

Solution:

The SQLNET. ORA on the server side should be similar

SQLNET. AUTHENTICATION_SERVICES = (CNT) NAMES. DIRECTORY_PATH = (TNSNAMES, ONAMES, HOSTNAME) TRACE_LEVEL_CLIENT = 16 Add [HKEY_LOCAL_MACHINE] To HOME0 in the Registry USE_SHARED_SOCKET = TRUE

[Q] How to Use hostname to connect to a database

The host name method only supports the TCP/IP protocol for small LAN

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 sqlnet. ora of the client, make sure there are

NAMES. DIRECTORY_PATH = (HOSTNAME)

You can use the name of the database server to access the database.

[Q] What security risks does dbms_repcat_admin bring?

[A] in the following situations, you may be granted the permission to run the package:

1. In sys

Grant execute on dbms_repcat_admin to public [| user_name]

2. the user has the execute any procedure privilege (limited to 9i and 9i must show the authorization)

If you execute the following statement:

Exec sys. dbms_repcat_admin.grant_admin_any_schema ('user _ name ');

This user will gain great system privileges

You can obtain detailed information from user_sys_privs.

[Q] How does one jump to another user without knowing the user password?

[A] using the following method, we can safely use this user and then jump back. This is useful in some cases.

The Alter user or DBA permission is required:

SQL> select password from dba_users where username = 'Scott '; PASSWORD ----------------------------- F894844C34402B67 SQL> alter user scott identified by lion; User altered. SQL> connect scott/lion Connected. REM Do whatever you like... SQL> connect system/manager Connected. SQL> alter user scott identified by values 'f894844c34402b67 '; User altered. SQL> connect scott/tiger Connected

[Q] How to reinforce your database

[A] pay attention to the following aspects:

1. Modify the password of sys and system.

2. Lock, modify, and delete default users, such as dbsnmp and ctxsys.

3. Change REMOTE_ OS _AUTHENT to False to prevent remote machine login.

4. Change O7_DICTIONARY_ACCESSIBILITY to False.

5. Remove some permissions from the PUBLIC Role.

6. Check the security of database data files. Do not set it to 666 or the like. Check other dba users.

7. disable unnecessary services (such as ftp and nfs)

8. Limit the number of users on the Database Host.

9. Check the security Alert on Metalink/OTN regularly. For example: http://otn.oracle.com/deploy/security/alerts.htm

10. Place your database and application in a separate subnet. Otherwise, your user password will be easily removed by sniffer. Or use advance security to encrypt user logon.

11. Only some ip addresses can access your database.

12. You need to add a password to lsnrctl. Otherwise, it is easy for others to turn off your listener from the outside.

13. If possible, do not use the default 1521 Port

[Q] How to check whether the user has used the default password?

[A] If you use the default password, it may pose A certain security risk to your database. You can use the following query to obtain the default password for those users.

Select username "User (s) with Default Password! " From dba_users Where password in ('E066d214d5421ccc ', -- dbsnmp '24ab8b06281b4c ', -- 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] The default xml db of Oracle9i sets the default HTTP port to 8080, which is A very common port. Many other webservers use this port,

If we have installed it, we 'd better modify it to avoid conflicts. If not, we 'd better not install it.

Three modification methods are provided.

1. dbca, select your Database, and then select Standard Database Features-> Customize-> Oracle xml db option. You should know how to change this screen.

2. Modify the OEM console in the XML Database Configuration

3. Use the packages provided by oracle:

-- Change HTTP/WEBDAV port from 8080 to 8081

SQL> call dbms_xdb.cfg_update (updateXML (dbms_xdb.cfg_get (), '/Xdbconfig/sysconfig/protocolconfig/httpconfig/http-port/text ()', 8081 )) / -- Change 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.mongo_refresh; -- Check whether the modification is successful SQL> select dbms_xdb.get _get from dual;

[Q] how to capture user logon information, such as SID and IP address

[A] logon triggers can be used, as shown in figure

Create or replace trigger tr_login_record AFTER logon DATABASE DECLARE MiUserSid NUMBER; MtSession v $ session % ROWTYPE; CURSOR cSession (iiUserSid in number) IS SELECT * FROM v $ session WHERE sid = iiUserSid; BEGIN SELECT sid INTO miUserSid FROM v $ mystat WHERE rownum <= 1; OPEN cSession (miUserSid ); FETCH cSession INTO mtSession; -- If user exists then insert data IF cSession % FOUND THEN Insert 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 don't exists then return error Sp_write_log ('session Information Error: '| SQLERRM ); CLOSE cSession; Raise_application_error (-20099, 'login exception', FALSE ); End if; CLOSE cSession; EXCEPTION WHEN OTHERS THEN Sp_write_log ('login Trigger Error: '| SQLERRM ); END tr_login_record;

Pay attention to the following points in the above triggers:

1. This user has the object query permissions of v _ $ session and v _ $ mystat. You can explicitly authorize this user under sys.

2. sp_write_log is originally a log writing process, which can be replaced by your own needs, such as skipping null.

3. A log $ information table must be created before the trigger is created to record the logon information.

[Q] how to capture DDL statements of the entire database or change and modify the object structure?

[A] DDL triggers can be used, as shown in figure

Create or replace trigger tr_trace_ddl AFTER DDL ON DATABASE DECLARE SQL _text ora_name_list_t; State_ SQL ddl $ trace. ddl_ SQL % TYPE; BEGIN FOR I IN 1 .. ora_ SQL _txt (SQL _text) LOOP State_ 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;

Pay attention to the following points when creating the above triggers

1. You must create a ddl $ trace table to record ddl records.

2. sp_write_log is a log writing process, which can be replaced by your own needs, such as skipping null

[Q] how to capture DML statements on a table (excluding select statements)

[A] dml triggers can be used, as shown in figure

Create or replace trigger tr_capt_ SQL BEFORE DELETE OR INSERT OR UPDATE ON manager. test DECLARE SQL _text ora_name_list_t; State_ SQL capt $ SQL. SQL _text % TYPE; BEGIN FOR I IN 1 .. ora_ SQL _txt (SQL _text) LOOP State_ 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 THEN Sp_write_log ('capture DML Exception: '| SQLERRM ); END tr_capt_ SQL;

Pay attention to the following points when creating the above triggers

1. You must create a capt $ SQL table to record ddl records.

2. sp_write_log is originally a log writing process, which can be replaced by your own needs, such as skipping null.

[Q] how to generate a date-Format File

[A] on LINUX/UNIX, use 'date + % y % m % d' ('This is the keyboard ~ The key) or $ (date + % y % m % d), such:

Touch exp_table_name _ 'date + % y % m % D'. dmp DATE = $ (date + % y % m % d) Or DATE = $ (date + % Y % m % d -- date '1 days ago ') # Get the DATE from yesterday or multiple days ago

On Windows, use % date :~ 4,10%, where 4 is the start character and 10 is the extract length, which indicates that the string with the length of 10 from 4 is extracted from the date generated. You can change it to another number you need, for example:

Echo % date :~ 4,10%

If you want to get a more accurate time, you can also use time

[Q] Testing disk and Array Performance

[A] test the write capability using A method similar to the following:

Time dd if =/dev/zero of =/oradata/biddb/testind/testfile. dbf bs = 1024000 count = 1000

During this period, the system I/O can be used (unix ):

Iostat-xnp 2 shows Busy degree

[Q] how to configure an SSH key

[A] preventing man-in-the-middle attacks

1. ssh-keygen or ssh-keygen-d (ssh 2.x) generate the key

2. Copy the public key to the server you want to log on to and change it to authorized_keys. If it is a version earlier than 3.0, change it to authorized_keys2.

3. You can also use the config file to further simplify operations.

For example

Host * bj Host Name or IP address User Username

With this configuration file, you can use ssh bj to access the specified Machine and use scp and sftp to transfer files.

[Q] How does FTP automatically upload/download in the script?

[A] You can write FTP to A shell script, as shown in figure

Ftp-n-I host IP Address Cd target directory Put file Get file # Querying files Ls # Exit Bye