December 2, 2002 at 7:16 am
Many of our developers are given full access to our production servers for one reason or another and it is messy to keep tabs on them all ... that is, remembering to revoke any permissions granted when they're finished . We've decided to write a sproc to create a login, add the user to a role and do an insert of a reason for the grant into an audit database/table. We can then simply keep an eye on this table and use it profile the reasons for which permissions are granted.
The sproc (incomplete) is run from the master table but I'm unsure about whether to insert a reason entry into a seperate audit database or simply add a table to the master db. We only have 6-8 developers. Anybody got any opinions or suggestions about what route we should go. The incomplete sproc is below
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER procedure dbo.sp_addauditeduser
@dbasename varchar(50),
@userlogin varchar(50),
@rolename varchar(50),
@issrverrole int
as
if @issrverrole=''
begin
select @issrverrole=0
end
exec sp_grantlogin @userlogin
Set @userlogin = '[' + @userlogin + ']'
if @issrverrole=0
BEGIN
EXEC
('use '+ @dbasename +' '+
'exec sp_grantdbaccess ' + @userlogin + ' , ' + @userlogin)
EXEC
('use '+ @dbasename +' '+
' exec sp_addrolemember ' + @rolename +',' + @userlogin)
END
ELSE
BEGIN
EXEC
('use '+ @dbasename +
'GO
exec sp_grantdbaccess ' + @userlogin + ',' + @userlogin+
'GO
exec sp_addsrvrolemember ' + @rolename+',' + @userlogin )
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
December 2, 2002 at 10:09 am
Use a separate db. Couple reasons.
1. If you need to move this, or alter it, or add a tlog backup of it, you want it in a separate table.
2. If you duplicate this server, it's easy to forget to move things that are in master. Easier to remember to move the db.
Steve Jones
December 3, 2002 at 1:54 am
Thanks Steve,
That's helpful. Have you any opinon on the overall strategy, good, bad or indifferent.
David
December 3, 2002 at 5:10 am
Overall sounds sufficient to keep up with when and why a person was given access. I do agree set this up in a seperate database. Also, if this is an audit are you setting a begin and end date in your table?
How are you verifying afteerwards that a person is not in a DB they are not supposed to be? I would suggest setting up a nightly process to check all DBs and generate a report to a table verify verification auditing in addition to what you have. I wrote this awhile back to check a users permissions which should be helpfull http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=268. And use sp_helpdbfixedrole, sp_helprolemember, and sp_helpsrvrolemember to get additional information on possible permissions that exist.
December 3, 2002 at 6:55 am
"Begin and End date"-- glazed look. I don't understand; there is going to be a lot of that in this thread let me warn you!
We had thought of just keeping an eye on the table but further consideration has shown the flaw in that plan. In relation to keeping tabs of permissions, we've manged to avoid any permissions on individual objects instead we use roles, be they user-defined, fixed database or fixed server roles.
The other aspect of this auditing is the audit of the revoke. We intended simply to sp_revokelogin the individual thus dropping their individual access to a role. However this doesn't happen as I've discovered and I'm not really sure how to write a script that will remove all the related users in each database when we do revoke the login, a la Enterprise Manager.
December 3, 2002 at 7:24 am
Begin and end date, I was referring to the date they would start having access to the objects of a DB in a role and the date it should end, we do this on some of our projects and can automate the removal of users.
sp_revokelogin only works on NT logins.
Step back and give us a run down of what you want to accomplish, this will give us better insight to answer your question.
December 4, 2002 at 1:57 am
Here's the gist of what we're trying to achieve.
We periodically allow developers (devs) to have dbo or even sysadmin access to our servers and that considered okay. What's not okay is that no check is maintained on the frequency or reason for these grants. So now we want to be able to let auditors come in, check who has admin rights and be able to turn around to them (the auditors) and say, we gave x, y & z devs access on this date and for this reason.
As an NT group the devs have limited, say read, access to the production database. The scenario I have proposed is that when a particular dev needs additional access we grant their individual login access, make them a member of the necessary role and put an entry in an audit table. Whenever we look at EM their login will stand out (as it isn't an NT group) and thus we will be continually aware that the have more access than they need. When the have finished "mucking" around with whatever they were doing, we will drop the login and thus the individual acceess.
The sproc above grants the permissions simply enough but the revoke requires more effort, since to avoid orphaned users it will be necessary to scroll through each database to remove the database login first. (I know there is an orphaned user script but I want this arrangement to be self-contained). I presume the simplest way to achieve that -- and let me tell you this it a big leap in my sql knowledge-- is to use a cursor to select the names from sysdatabases and, scroll through the cursor executing sp_droplogin for each database name.
Antares686, at the risk of slipping off the sharply rising learning curve, I'm curious as to how you automate the removal of the logins. Thanks all for you patience.
December 5, 2002 at 8:53 pm
quote:
I'm curious as to how you automate the removal of the logins.
from BOL:
sp_droplogin
Removes a Microsoft® SQL Server™ login, preventing access to SQL Server using that login name.
When these expire, just drop them. Since you already have a procedure to do your dirty work, go ahead and fully automate it. THen when the expiration date is reached, just undo everything automagically added.
December 6, 2002 at 1:42 am
Hi Don,
Sorry, I didn't really make my question clear; is it possible to have them expire automatically and if so how can I do that because that would be extremely useful.
DeltaKilo
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply