How to Set access restrictions for Oracle, IP layer, user layer, oracleip

How to Set access restrictions for Oracle, IP layer, user layer, oracleip

Oracle sets access restrictions, IP layer, and user layer (Access prohibited for specific users)

1. Configure the IP address whitelist and blacklist through the SQLNET. ORA File

Sqlnet. ora

Tcp. validnode_checking = yes # You need to set it to yes before activation takes effect.

Tcp. invited_nodes = (10.240.1.8, 10.240.1.7) # IP addresses allowed for access

# Tcp. excluded_nodes = (10.240.1.8, 10.240.1.7) # IP addresses not allowed for access

2. Use TRIGGER settings to disable logon by IP addresses and users.

TRIGGER

--> TRIGGER restricts the user BADUSER;

CREATE OR REPLACE TRIGGER DISABLELOGIN

After logon on baduser. SCHEMA

BEGIN

IF ORA_CLIENT_IP_ADDRESS LIKE ('192. 60.40.105 ') THEN

RAISE_APPLICATION_ERROR (-20001, 'user' | ORA_LOGIN_USER | 'is not allowed to connect from' | ORA_CLIENT_IP_ADDRESS );

End if;

END;

/

-- Sys. schema cannot be specified, the ORA-30510: system triggers cannot be defined in SYS User Programme

-- Restrict an IP address ORA_CLIENT_IP_ADDRESS IN ('10. 240.1.7 ', '10. 240.1.8 ')

Create or replace trigger DISABLELOGIN2

After logon database

Declare

Ip STRING (30 );

User STRING (30 );

Begin

SELECT SYS_CONTEXT ('userenv', 'session _ user') into USER from dual;

SELECT SYS_CONTEXT ('userenv', 'IP _ address') into IP from dual;

If user = 'baduser123'

THEN

IF ip in ('1970. 60.40.105 ')

THEN

Raise_application_error (-20001, 'user' | User | 'is not allowed to connect from' | ip );

End if;

End if;

End;

/

Drop trigger sys. TRI_NODROPTAB;

Create or replace trigger sys. tri_nodroptab

Before drop on database

WHEN (

USER not in ('sys ', 'system ')

)

DECLARE

User_program v $ session. PROGRAM % TYPE;

Usr_command v $ session. COMMAND % TYPE;

CURSOR ses_program IS

SELECT upper (program), command from sys. v $ session WHERE audsid = sys_context ('userenv', 'sessionid') and sid = sys_context ('userenv', 'sid ');

Begin

OPEN ses_program;

FETCH ses_program INTO user_program, usr_command;

CLOSE ses_program;

-- In 10gr2, v $ session. command = 12 means drop table

IF (user_program like 'sqldev % 'or user_program like 'toad %') and usr_command = 12) THEN

RAISE_APPLICATION_ERROR (-20001, 'Are you really sure to drop this table Pls use sqlplus to do it if you ARE determined .');

End if;

End;

/