> is there a way for limit tables access for a specific ip ..
Is this the correct question to ask? I do not think so. The appropriate question would be "is it sensible, security wise, to limit table access using IP addresses?".
That raises the question about just where is IP address security used at application level? I cannot think offhand of any application I've ever dealt with that deals with security using IP addresses.
Where can IP addresses be used? If you look at the [url http://en.wikipedia.org/wiki/OSI_model]OSI Model, IP address is almost never used higher up in the OSI stack. Why? Because it is trivial to spoof it.
At network level, IP addresses can be used as network hardware (routers, switches, etc) can enforce certain rules and standards.. and for example, not route a specific IP address from a segment where that subnet mask is not valid, or network address is not valid, etc. After all, that and the MAC address are the only unique client network identifiers to use at that level.. which is why it makes sense to use these.
But at application level.. just what does the IP address mean? Nothing much. It is not a unique identification as it can be spoofed. Even the MAC address can be spoofed. And yes, spoofing this is fairly trivial.
IP address, even when not spoofed, does not guarantee that a specific user is using that IP address. Another person can have access to his machine. Or, in a DHCP environment, changes could result in that user now using a different IP address.
IP address is also meaningless when supporting users via different connectivity points of access. Typically many businesses will also have a dial-in option (modem, DSL, ISDN, etc). The IP address allocation via this, is always different than those used for the internal network (especially for network security and management purposes).
So to be honest.. IP address security features at application level is IMO a very stupid thing to do. It creates a lot of warm fuzzies that there is now "better security", when in fact it is opening fairly sizable holes for even scriptkiddies to exploit.
Well, not that excellent as I forgot to mention dealing with NAT firewalls (which I use a lot) and proxies - where the IP address that the server will see is that of the firewall/proxy and not that of the actual client platform. :-)
"I cannot think offhand of any application I've ever dealt with that deals with security using IP addresses."
Perhaps you can't but I can. And I have seen this as a sensible and required fact-of-life in many situations where people may have a valid username and password and may be refused access based on the fact that they are trying to get in using their laptop rather than a specific desktop or based on being on a VPN connection, or even being on the wrong floor of a building.
Don't be so quick to criticize the OP. The requirement, in some cases I can think of, is a matter of written orders. And in the military, where I've seen it as a requirement, disobeying a written order can land you in a very unpleasant place.
I don't think Billy was criticising the OP as much as pointing out that IP addresses can be spoofed so even if you are ordered on pain of death to implement IP security, you could at least point out the uselessness of doing so to your superiors before going ahead and doing so.
It's certainly an interesting question: my dealings with the military are fairly trivial, but the place I'm thinking of has blue lights in the ceiling that flash whenever an unauthorised person walks on the floor (so authorised personnel know to switch off monitors). One of their more sensitive databases is only ever accessed on the console directly: there's no network at all. And there is one standalone PC on the floor that is connected to a dialup modem, and that is that entire floor's Internet access. That sort of lockdown I can understand and is difficult to circumvent because most of it is physical (no network connection makes it impossible to penetrate from the outside, for example)... but I would be interested to know how anyone would propose using IP addresses to, for example, truly prevent access to a database by someone on the wrong floor of the building.
Obviously one could assign particular MAC addresses particular IP address reservations... but since I can easily spoof my MAC address, that wouldn't actually achieve very much by way of reliable security, would it?
Again, I'm no expert, but I could imagine that whilst physical network cabling could perhaps be used to achieve that sort of security, I doubt it would be **reliably** done via IP address.
So, I think the point Billy was making in regards to the OP was simply: what you are asking to do doesn't sound like a very meaningful way of securing anything very much. If it could be done at all in Oracle, it would at best be security through obscurity, which is no security at all.
If I misunderstood Billy's comment then my comments should be adjusted accordingly.
Of course IP addresses can be spoofed. But for those organizations where I've seen this required it has been part of a multi-tiered access control where the last piece of the puzzle is to say ... ok who are you, what operating system are you on, what time of day is it, are you at the correct IP address, Ok we will let you SELECT but not DELETE or some such fine grained capabilities. That is why I referred the OP to Oracle Advanced Security.
The issue of spoofing comes up often but the situation where I am aware of such things as IP and MAC addresses being used is one where the layers of protection are not made public. Multiple layers of traps exist and stepping on any one will get you tossed into the brig so fast you won't have time to contemplate how you were caught.
You can easily defeat SELECT program FROM gv$session if you know that is what I am using. But you'll never defeat it if it is one of 20+ checks, you don't know how many things are being checked, or what any of them are for sure, or how they are being checked, or which checks are multi-value checks where you must get everything correct and synchronized. The best minefield does not rely on a single type of mine. Even if you are prepared to spoof one of them, or five of them, you'll never simultaneously spoof them all.
> Of course IP addresses can be spoofed. But for those organizations where I've seen this required
it has been part of a multi-tiered access control
Interesting... I do not think my issue is an issue if IP address is used as part of an authentication schema - to determine the client is indeed who it says it is. But I've never came across such an authentication schema in the corporate world thus far. Mainly because things like an IP address is not really a robust thing as far as user authentication/identification goes (especially in large and geographically distributed corporates). Granted, I've never really worked with exotic customers (like the military), so I accept there are cases where this may indeed be a "common" thing. And the multilayer approach you described Daniel, is how security models should look.
But within the "standard" corporate context (based on my experience), launching a DoS attack to down an IP and then assume that IP on another platform are fairly easy things to do. On the NA side, this will very likely not even be detected as most lack the monitoring tools to effectively deal with this. Of course.. if you have the network monitoring tools we have developed, that would not be the case. ;-)
If it works within your environment, then great. But the basic security concerns still are that IP address does not necessarily uniquely identified a specific platform - And it cannot really be used to identify the actual user.
Assuming that I have two machines on your network. Using the 1st machine I launch a DoS attack against the IT Manager's PC (he's likely running Windows, and it is fairly easy to keep a Windows PC tied up). Heck, you may have unpatched IOS on your routers that may make a DoS attack even easier.
On the 2nd machine, I bring up an interface with the manager's IP address. I spoof the MAC address (not needed for bypassing the simplistic SQL*Net check on your side). Using some sophisticated software, I can even attempt to hijack all existing network sessions on that IT Manager's PC.
How do you, on the database server side, now know that I'm not the manager's PC accessing your database?
That is why what Daniel raised is so absolutely critical. IP checking needs to be ONE of MANY security layers used.
It is very dangerous to make it the only lock that needs to be unlocked in order to gain access.
You also need to keep in mind the social engineering scenario. Something like an accomplice luring manager from office (in such a quick and urgent fashion that manager does not lock PC or office). I walk in and within 60 seconds install a backdoor on the PC. The PC is now compromised and all access it has, I now have.
Yes, one should manage the actual risk and not go overboard trying to lock security down to such an extent as to foil James Bond himself. But my experiences in this regard say that all too often, companies implement a "security access method" without fully understanding the actual security benefits it provide - and instead use it in ignorance with warm fuzzies that they now have "better" security.