IP Restriction Feature

Question

Post reply

IP Restriction Feature

OnlyOneRJ

Hall of Fame

Points: 3894
More actions
August 26, 2013 at 12:01 am

#395366

There should be a feature in database to allow only certain IPs to delete the data. Can we keep a log of the ip address when a record is deleted will help. Is their any solution for this.. please revert your expert advice:-)
************************************
Every Dog has a Tail !!!!! :-D

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 1

48 viewed and 0 replies :w00t:

************************************
Every Dog has a Tail !!!!! :-D

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 2

So if I log into a coworkers computer using my own domain account, I can suddenly have more privileges because I'm using another IP? Awesome, this makes hacking so much easier 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

It's called triggers and audits, along with properly configured security (minimal permissions)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 4

If I remember right MySQL has such features. One is able to specify different grants by IP addresses.

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 5

Anything for SQL 2008????

************************************
Every Dog has a Tail !!!!! :-D

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Yes

GilaMonster (8/26/2013)
It's called triggers and audits, along with properly configured security (minimal permissions)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RandomEvent SSCrazy Points: 2963 More actions · Answer 7

Why would you be interested in the IP address if your using DNS and DHCP. Have a play with

SELECT

conn.session_ID as SPID,

conn.client_net_address as IPAddress,

sess.host_name as MachineName,

sess.program_name as ApplicationName,

login_name as LoginName

FROM sys.dm_exec_connections conn

inner join sys.dm_exec_sessions sess

on conn.session_ID=sess.session_ID

and triggers depending on the version of SQL you are using.

Lowell SSC Guru Points: 323504 More actions · Answer 8

I suspect the core issue is that everyone logged in has sysadmin or dbo priviledges and instead of fixing that as gail implied with proper security, you are looking for a bandaid approach to limit by ip address.

If you fix your security model by creating roles with the expected permissions, you gain twofold : a better security model and directly addressi g the overpriviledges problem while still fixing the problem itself

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 9

Jason.Reeves (8/27/2013)
Why would you be interested in the IP address if your using DNS and DHCP. Have a play with
SELECT
conn.session_ID as SPID,
conn.client_net_address as IPAddress,
sess.host_name as MachineName,
sess.program_name as ApplicationName,
login_name as LoginName
FROM sys.dm_exec_connections conn
inner join sys.dm_exec_sessions sess
on conn.session_ID=sess.session_ID
and triggers depending on the version of SQL you are using.

What will this query result??? please brief.. i ran it & got some records but what will i have to do next & read in it???:hehe:

************************************
Every Dog has a Tail !!!!! :-D

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 10

Lowell (8/27/2013)
I suspect the core issue is that everyone logged in has sysadmin or dbo priviledges and instead of fixing that as gail implied with proper security, you are looking for a bandaid approach to limit by ip address.
If you fix your security model by creating roles with the expected permissions, you gain twofold : a better security model and directly addressi g the overpriviledges problem while still fixing the problem itself

Actually 10 users are using a common User name & password to access the Application.. means all ten 10 users do DML operations on one application & we came across a scenario where some records were deleted from tables but god know who did that.. obviously that was the common Login ID used..

Its a client need..

Client wants 10 users to use same Login ID but wants to know who did what incase of issue, like who deletes who makes updates & also wants that their should be no impact on server if we go ahaead & do some additional implementation on restrictions :w00t: :w00t::w00t:

I am puzzled, my boss is puzzled :w00t: & team members are also puzzled :w00t:

************************************
Every Dog has a Tail !!!!! :-D

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Once more with feeling.

Triggers.

Audits.

The functionality exists, and I've mentioned this repeatedly over a few weeks now, so I don't know why you're still puzzled.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 12

Gail, actually i have never worked on Triggers & audits & so looking out details in google..

But didnt get any good documents for this... so me scared to implement

************************************
Every Dog has a Tail !!!!! :-D

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Try Books Online (the SQL help file). It has pages and pages on both.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 14

Sure Gail, Incase of any query i will reply here.. thanks for Update 🙂

************************************
Every Dog has a Tail !!!!! :-D