Logon failures for specific oracle users

Source: Internet
Author: User

A developer email was sent last night when a specific oracle user failed to log on to the database, saying that the following error occurred when using PL/SQL dev to log on to the database. Please handle it: 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 usually two methods: (1) In sqlnet. configure in the ora file, for example, restrict the IP address to access the database in sqlnet. add the following content to the ora file: tcp. validnode_checking = yestcp. invited_nodes = ( tcp. excluded_nodes = ( Meaning of the first line: Enable the IP restriction function; meaning of the second line: List of IP addresses allowed to access the database, multiple IP addresses are separated by commas, in this example, we write the IP address of the database server; the meaning of the third line: the list of IP addresses that are prohibited from accessing the database. Multiple IP addresses are separated by commas. Here we write the IP address to be restricted. Then restart the listener to take effect. (2) Use trigger to 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.9 4', '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; finally, the following suggestions are provided: you can use mtdb (another user without restrictions) to add a user prefix to the logon access, or remove the trigger (not recommended ).

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.