Part 5, Oracle Network and Security
[Q]
How to limit specific
IP
Access database
[A]
You can use the login trigger,
CMGW
Or
$ Oreacle_home / network / admin
Add a new one
Protocol.ora
File (some
OS
may be
Protocol.ora
),
9i
Can be modified directly
SQLNET.ORA
:
Added as follows:
Tcp.validnode_checking = yes #
Accessible
IPTCP.INITED_NODES = (IP1, IP2, ...) #
Not allowed
iptcp.excluded_nodes = (IP1, IP2, ...) [q]
How to connect through the firewall connection database
[A]
This problem will only be in
Win
The platform appears,
Unix
The platform will be automatically resolved.
Solution:
Server-side
SQLNET.ORA
Similar
SQLNET.AUTHENTICATION_SERVICES = (nts) Names.directory_path = (TNSNAMES, ONAMES, HOSTNAME) Trace_LEVEL_CLIENT = 16
Registry
HOME0
plus
[HKEY_LOCAL_MACHINE] Uses_shared_socket = true [q]
How to use
Hostname
Way connection database
Host Name
Way only support
TCP / IP
Protocol small LAN
modify
Listener.ora
The following information
(SID_DESC = (global_dbname = UR_HOSTNAME) -
Your machine name
(Oracle_Home = E: / Oracle / ORA92) - Oracle Home (SID_NAME = Orcl) - Sid Name)
Then at the client
of
SQLNET.ORA
Must, 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
What safety hazard can be brought
[A]
If a user can execute
DBMS_REPCAT_ADMIN
Package will get great system authority.
The following cases may get the execution permission of the package:
1
,in
SYS
under
Grant Execute on dbms_repcat_admin to public [user_name] 2
Users with
Execute Any Procedure
Privilege
9i
the following,
9i
Authorization must be displayed)
If the user performs the following statement:
EXEC SYS.DBMS_REPCAT_ADMIN.GRANT_ADMIN_ADMIN_SCHEMA ('user_name');
This user will get great system privileges
Can
User_sys_privs
Get detailed information
[Q]
When you don't 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 methods, then jump back, and more useful at some time
need
Alter User
Authority or
DBA
Permissions:
SQL> SELECT Password from dba_users where username = 'scott'; password ---------------------------- f
894844C
34402B67SQL> 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' F894844C
34402B67 '; user altered.sql> connection scott / tigerConnected. [Q]
How to reinforce your database
[A]
Pay attention to the following aspects
1.
modify
Sys, system
Password.
2. Lock
, Modify, delete the default user:
dbsnmp, ctxsys
Wait.
3.
Put
Remote_OS_AUTHENT
Change to
False
Prevent remote machines from logging in directly.
4.
Put
O7_DICTIONARY_ACCESSIBILITILIBILITY
Change to
False
.
5.
Put some permissions from
Public role
Cancel.
6.
Check the security of the database's data files. Don't set it into
666
some type of. Check other
DBA
user.
7.
Put some unwanted services (such as
FTP, NFS
Wait closed)
8.
Restrict the number of users above the database host.
9.
Periodic inspection
Metalink / OTN
Above
Security Alert
. such as:
Http://otn.racle.com/deploy/security/alerts.htm 10.
Put your database with your application in a separate subnet, or you have your user's password is easy to be
Sniffer
go with. Or use
Advance Security
, Log in to the user.
11.
Something
IP
Can access your database.
12. lsnrctl
To add a password, other people can easily get rid of you from the outside
Listener
.
13.
If possible, don't use the default
1521
port
[Q]
How to check if the user uses the default password
[A]
If you use the default password, you are likely to cause a certain security hazard for your database, you can use the following query to get those users to use the default password.
SELECT Username "User (s) with default password!" from dba_users where password in ('E066D214D5421ccc ", - dbsnmp' 24abab8b06281b
4C
', - ctxsys'
72979A
94BAD2AF80 ', - MDSYS' C252E8FA117AF049 ', - ODM' A
7A
32CD03D3CE8D5 ', - ODM_MTR'
88A
2b
2C
183431F
00 ', - Ordplugins' 7efa02ec7ea6b
86F
', - ORDSYS'
4A
3BA55E
08595C
81 ', - OUTLN' F
894844C
34402B67 ', - Scott'
3F
9fbd883d787341 ', - wk_proxy' 79df
7A
1BD138CF11 ', - WK_SYS'
7C
9ba
362F
8314299, - WMSYS '88D8364765FCE6AF', - XDB 'F9DA8977092B7B81', - TracesVR '9300C
0977D7DC75E ', - OAS_PUBLIC' A97282CE3D94E29E ', - Websys' AC9700FD
3F
1410EB ', - LBACSYS' E7B5D
92911c
831E1 ', - RMAN' AC98877DE1297365 ', - Perfstat'
66F
4ef
5650C
20355 ', - EXFSYS' 84B8CBCA4D477FA3 ', - Si_INFORMTN_SCHEMA' D
4C
5016086B2DC
6A
', - SYS' D4DF7931AB130E37 ') - System / [q]
How to modify the default
Xdb
Monitor port
[A] Oracle9i
default
XML DB
Put
HTTP
The default port is set
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
Provide three modifications
DBCA
, Choose your database, then
Standard Database Features-> Customize-> Oracle XML DB Option
You should know how to change this picture.
2.oem console
,in
XML Database
Configuration inside
3.
use
Oracle
Provided package:
-
Put
HTTP / WebDAV
Port from the port
8080
Change to
8081 SQL> Call dbms_xdb.cfg_update (UpdateXML (dbms_xdb.cfg_get (), '/ xdbconfig / sysconfig / protocolconfig / httpconfig / http-port / teix (), 8081)) /
Put
FTP
Port from the port
2100
Change 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 user login information, such as
SID
,
IP
Address, etc.
[A]
You can use login triggers, 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, termin al, 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; pay attention to the following points in the above triggers
1
This user has
V_ $ session
versus
V_ $ mystat
Object query permissions, can
SYS
This support explicitly authorized.
2
,
SP_WRITE_LOG
Originally a process of writing a log, can be replaced with your own needs, such as
NULL
jump over.
3
, You must create one before creating the trigger
LOG $ INFORMATION
The table records login information.
[Q]
How to capture the entire database
DDL
Sogens or object structure changes and modifications
[A]
Can be used
DDL
Trigger, 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; pay attention to a few points when creating above triggers
1
Must create one
DDL $ TRACE
Table, used to record
DDL
record of
2
,
SP_WRITE_LOG
Originally a process of writing a log, can be replaced with your own needs, such as
NULL
jump over.
[Q]
How to capture the table
DML
Statement (excluding
SELECT)
Statement)
[A]
Can be used
DML
Trigger, 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_ADDR ESS'), userenv ( 'SESSIONID'), 'MANAGER', ' Test ', state_sql; eXception when others thensp_write_log (' Capture DML Exception: '|| SQlerRM); END TR_CAPT_SQL;
Pay attention to you when you create more than the above trigger
1
Must create one
CAPT $ SQL
Table, used to record
DDL
record of
2
,
SP_WRITE_LOG
Originally a process of writing a log, can be replaced with your own needs, such as
NULL
jump over.
