Home » RDBMS Server » Security » How to restrict schema access to specfic IP address (11.2.0.1.0, windows 7)
How to restrict schema access to specfic IP address [message #611141] Fri, 28 March 2014 06:52 Go to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
hello i want to restrict user access from specific ip address for that i just test it but i got this error


SQL> create or replace trigger system.check_ip_addresses
  2  after logon
  3  on database
  4  declare
  5  i_ip_address varchar(16) := sys_context ('USERENV','IP_ADDRESS');
  6  BEGIN
  7  IF USER IN('SCOTT','HR')THEN
  8  IF i_ip_address = '10.10.2.1' THEN
  9  NULL;
 10  ELSE
 11  RAISE_APPLICATION_ERROR(-20000,'Can not log in from this IP address ( ' ||
i_ip_address ||')
 12  end if;
 13  end if;
 14  end;
 15  /

Warning: Trigger created with compilation errors.

SQL> show errors
Errors for TRIGGER SYSTEM.CHECK_IP_ADDRESSES:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/91     PLS-00103: Encountered the symbol ")
         end if;
         end if;
         end;" when expecting one of the following:
         ( - + case mod new null <an identifier>
         <a double-quoted delimited-identifier> <a bind variable>
         continue avg count current max min prior sql stddev sum
         variance execute forall merge time timestamp interval date
         <a string literal with character set specification>
         <a number> <a single-quoted SQL string> pipe
         <an alternatively-quoted string literal with character set

LINE/COL ERROR
-------- -----------------------------------------------------------------
         specification>
         <an alt

SQL>

Re: How to restrict schema access to specfic IP address [message #611145 is a reply to message #611141] Fri, 28 March 2014 07:19 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
To begin with, your quotes are not balanced.
Re: How to restrict schema access to specfic IP address [message #611150 is a reply to message #611145] Fri, 28 March 2014 07:54 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
And your brackets aren't balanced either.
Re: How to restrict schema access to specfic IP address [message #611151 is a reply to message #611150] Fri, 28 March 2014 08:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
might not work as desired for 3-tier application
Re: How to restrict schema access to specfic IP address [message #611152 is a reply to message #611141] Fri, 28 March 2014 08:08 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
In general, this is rather ill-advised and certainly not very maintainable.

What is the business problem you think you are trying to solve? No, restricting connections by IP is not a business problem, it is a pre-conceived solution to a (as yet un-named) problem.

If we know the business problem, we can advise a better approach than simply trying to fix a trigger.
Re: How to restrict schema access to specfic IP address [message #611180 is a reply to message #611152] Sat, 29 March 2014 01:46 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
hmm but can you guide me where i make error how can i correct this code
Re: How to restrict schema access to specfic IP address [message #611181 is a reply to message #611180] Sat, 29 March 2014 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have been guided on this point, answer Ed's question.

Re: How to restrict schema access to specfic IP address [message #611199 is a reply to message #611180] Sat, 29 March 2014 09:46 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
x-oracle wrote on Sat, 29 March 2014 01:46
hmm but can you guide me where i make error how can i correct this code


Others have already shown you that.
At this point the situation is like you are driving down a road, past a sign (erected by me) that says "Bridge out ahead". Instead of asking for a better route to Aintry, you ask how to fix your car so you can proceed to drive off the bridge into the river.
Previous Topic: ORA-01917: user or role does not exist
Next Topic: how to implement audit purge ?
Goto Forum:
  


Current Time: Thu Mar 28 16:17:39 CDT 2024