I received a development email last night, saying that the following error was prompted when I used PL/SQL DEV to log on to the database:
I am also depressed when I see this error. I have never encountered this situation. So we created a test user scott and found that there was no problem with remote logon. In this case, some users in the database restrict logon. Let's look at the error number: ORA-20001, the Oracle retained error number range is-20999 to-20000, which is provided to the user for custom exception use, this further confirms that some database users are restricted. What are the restrictions?
There are two methods:
(1) Configure in the sqlnet. ora file, for example:
Restrict access to the database from the IP address 192.168.131.109
Add the following content to the sqlnet. ora file:
Tcp. validnode_checking = yes
Tcp. invited_nodes = (192.168.130.11)
Tcp. excluded_nodes = (192.168.131.109)
The meaning of the first line: Enable the IP address restriction function;
The meaning of the second line: List of IP addresses allowed to access the database. Separate multiple IP addresses with commas (,). In this example, we write the IP addresses of the database server;
Meaning of Row 3: List of IP addresses that are prohibited from accessing the database. Separate multiple IP addresses with commas. Here we will write the IP address 192.168.131.109 to be restricted.
Then restart the listener to take effect.
(2) trigger
Define in trigger which IP addresses and which users can remotely log on to the database.
Note: Using profile cannot restrict IP login.
Obviously, method (1) cannot customize the error code: ORA-20001, while using method (2) trigger can define the error code, it is likely to use the trigger, so View:
Select t. owner, t. trigger_name, t. triggering_event, t. table_owner, t. status, t. trigger_body, t. description from dba_triggers t where t. triggering_event like '% LOGON % ';
Trigger is used to restrict logon IP addresses and users.
Trigger_body:
DECLARE
Ipaddr VARCHAR2 (30 );
BEGIN
SELECT sys_context ('userenv', 'IP _ address') INTO ipaddr FROM dual;
IF ipaddr not in ('2017. 168.131.54 ', '2017. 168.131.55', '2017. 168.131.97 ', '2017. 168.131.60 ', '2017. 168.131.61 ', '2017. 168.131.63 ', '2017. 168.131.64 ', '2017. 168.131.62 ', '2017. 168.131.65 ', '2017. 168.131.95 ', '2017. 168.131.57 ', '2017. 168.131.58 ', '2017. 168.131.59 ', '2017. 168.131.94 ', '2017. 168.131.93 ', '2017. 168.105.94 ', '2017. 168.105.95 ', '2017. 168.105.96 ', '2017. 168.105.98 ', '2017. 168.105.99 ', '2017. 168.105.65 ') THEN
Raise_application_error ('-20001', 'you can not login, Please contact administrator ');
End if;
END disablelogin_userdb1;
The final suggestions are as follows:
You can use mtdb (another user without restrictions) to add a user prefix to the logon access, or remove the trigger (not recommended ).