December 10, 2021 at 12:36 am
Hi friends,
We need to grant permissions to a user to view security logins at server level. Granting view any definition would give the ability to view any metadata and objects on the server.. which is not what we would like to grant..
Any other permissions to just grant access to view server logins?
Thank you so much
December 10, 2021 at 1:06 pm
Not sure what this, is what you're looking for, but it's from the documentation for sys.server_principals:
Permissions
Any login can see their own login name, the system logins, and the fixed server roles. To see other logins, requires ALTER ANY LOGIN, or a permission on the login. To see user-defined server roles, requires ALTER ANY SERVER ROLE, or membership in the role.
John
December 10, 2021 at 2:11 pm
User wants to be able to view the other logins at server login not her own login name. As I understand Alter any login has the ability to create & drop other logins...
looks like view definition below is the safe bet to grant read only access to view other server logins?
Grant view definition on login::[otherlogin] to user
Thanks
December 10, 2021 at 2:23 pm
why do other uses need to see who can login on a server? that is on its own a security risk and should not be exposed by default.
If it is for auditors then I suggest that they either ask the DBA's for that info when they need it or you create a small dba database where you maintain a table with all active logins on that server and have a sql job refresh that table daily/weekly, and give the auditors access to that table only.
there are other ways of doing this using signed stored procs without giving the users any specific permisisons - see https://www.sqlservercentral.com/forums/topic/view-server-state-privilege-for-a-stored-procedure and https://sqlquantumleap.com/2018/03/05/safely-and-easily-use-high-level-permissions-without-granting-them-to-anyone-database-level/
December 10, 2021 at 5:16 pm
why do other uses need to see who can login on a server? that is on its own a security risk and should not be exposed by default.
If it is for auditors then I suggest that they either ask the DBA's for that info when they need it or you create a small dba database where you maintain a table with all active logins on that server and have a sql job refresh that table daily/weekly, and give the auditors access to that table only.
there are other ways of doing this using signed stored procs without giving the users any specific permisisons - see https://www.sqlservercentral.com/forums/topic/view-server-state-privilege-for-a-stored-procedure and https://sqlquantumleap.com/2018/03/05/safely-and-easily-use-high-level-permissions-without-granting-them-to-anyone-database-level/
+1000 Auditors should need no direct privs on the server except, possibly, to run a stored procedure.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2021 at 5:45 pm
frederico_fonseca wrote:why do other uses need to see who can login on a server? that is on its own a security risk and should not be exposed by default.
If it is for auditors then I suggest that they either ask the DBA's for that info when they need it or you create a small dba database where you maintain a table with all active logins on that server and have a sql job refresh that table daily/weekly, and give the auditors access to that table only.
there are other ways of doing this using signed stored procs without giving the users any specific permisisons - see https://www.sqlservercentral.com/forums/topic/view-server-state-privilege-for-a-stored-procedure and https://sqlquantumleap.com/2018/03/05/safely-and-easily-use-high-level-permissions-without-granting-them-to-anyone-database-level/
+1000 Auditors should need no direct privs on the server except, possibly, to run a stored procedure.
How, then, would the auditors ever be able to catch problems if the DBA was causing them? So of course genuine auditors must have access to retrieve system metadata on their own. When I was at International Paper, they did rigorous audits (when you're a mega-$billion, you have to, otherwise it's not due diligence for a company of that size).
Auditors, however, must not be able to change anything on an instance. I did push back big-time when some auditors demanded that they be given "sysadmin" permissions. That was back in SQL 2000 days, when permissions within SQL Server were not nearly as granular and many queries were almost impossible to do without sysadmin. So the request made more sense then. But I still pushed hard and prevented it.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 10, 2021 at 6:22 pm
Jeff Moden wrote:frederico_fonseca wrote:why do other uses need to see who can login on a server? that is on its own a security risk and should not be exposed by default.
If it is for auditors then I suggest that they either ask the DBA's for that info when they need it or you create a small dba database where you maintain a table with all active logins on that server and have a sql job refresh that table daily/weekly, and give the auditors access to that table only.
there are other ways of doing this using signed stored procs without giving the users any specific permisisons - see https://www.sqlservercentral.com/forums/topic/view-server-state-privilege-for-a-stored-procedure and https://sqlquantumleap.com/2018/03/05/safely-and-easily-use-high-level-permissions-without-granting-them-to-anyone-database-level/
+1000 Auditors should need no direct privs on the server except, possibly, to run a stored procedure.
How, then, would the auditors ever be able to catch problems if the DBA was causing them? So of course genuine auditors must have access to retrieve system metadata on their own. When I was at International Paper, they did rigorous audits (when you're a mega-$billion, you have to, otherwise it's not due diligence for a company of that size).
Auditors, however, must not be able to change anything on an instance. I did push back big-time when some auditors demanded that they be given "sysadmin" permissions. That was back in SQL 2000 days, when permissions within SQL Server were not nearly as granular and many queries were almost impossible to do without sysadmin. So the request made more sense then. But I still pushed hard and prevented it.
The same way I do it... the auditor emails me some code to run and I do a safety review on it. Then the auditor comes to my desk or we fire up a Teams meeting and witnesses me running it.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2021 at 10:02 pm
ScottPletcher wrote:Jeff Moden wrote:frederico_fonseca wrote:why do other uses need to see who can login on a server? that is on its own a security risk and should not be exposed by default.
If it is for auditors then I suggest that they either ask the DBA's for that info when they need it or you create a small dba database where you maintain a table with all active logins on that server and have a sql job refresh that table daily/weekly, and give the auditors access to that table only.
there are other ways of doing this using signed stored procs without giving the users any specific permisisons - see https://www.sqlservercentral.com/forums/topic/view-server-state-privilege-for-a-stored-procedure and https://sqlquantumleap.com/2018/03/05/safely-and-easily-use-high-level-permissions-without-granting-them-to-anyone-database-level/
+1000 Auditors should need no direct privs on the server except, possibly, to run a stored procedure.
How, then, would the auditors ever be able to catch problems if the DBA was causing them? So of course genuine auditors must have access to retrieve system metadata on their own. When I was at International Paper, they did rigorous audits (when you're a mega-$billion, you have to, otherwise it's not due diligence for a company of that size).
Auditors, however, must not be able to change anything on an instance. I did push back big-time when some auditors demanded that they be given "sysadmin" permissions. That was back in SQL 2000 days, when permissions within SQL Server were not nearly as granular and many queries were almost impossible to do without sysadmin. So the request made more sense then. But I still pushed hard and prevented it.
The same way I do it... the auditor emails me some code to run and I do a safety review on it. Then the auditor comes to my desk or we fire up a Teams meeting and witnesses me running it.
No, the DBA can't have direct control over any audit results, else it's not a genuine audit. That is, the DBA(s) themselves must also be audited.
Why does the DBA need to prevent the auditor from directly viewing metadata on the instance? Allowing the DBA alone to run code to get results allow too big an opening for a clever DBA to taint the results.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 10, 2021 at 10:06 pm
Jeff Moden wrote:ScottPletcher wrote:Jeff Moden wrote:frederico_fonseca wrote:why do other uses need to see who can login on a server? that is on its own a security risk and should not be exposed by default.
If it is for auditors then I suggest that they either ask the DBA's for that info when they need it or you create a small dba database where you maintain a table with all active logins on that server and have a sql job refresh that table daily/weekly, and give the auditors access to that table only.
there are other ways of doing this using signed stored procs without giving the users any specific permisisons - see https://www.sqlservercentral.com/forums/topic/view-server-state-privilege-for-a-stored-procedure and https://sqlquantumleap.com/2018/03/05/safely-and-easily-use-high-level-permissions-without-granting-them-to-anyone-database-level/
+1000 Auditors should need no direct privs on the server except, possibly, to run a stored procedure.
How, then, would the auditors ever be able to catch problems if the DBA was causing them? So of course genuine auditors must have access to retrieve system metadata on their own. When I was at International Paper, they did rigorous audits (when you're a mega-$billion, you have to, otherwise it's not due diligence for a company of that size).
Auditors, however, must not be able to change anything on an instance. I did push back big-time when some auditors demanded that they be given "sysadmin" permissions. That was back in SQL 2000 days, when permissions within SQL Server were not nearly as granular and many queries were almost impossible to do without sysadmin. So the request made more sense then. But I still pushed hard and prevented it.
The same way I do it... the auditor emails me some code to run and I do a safety review on it. Then the auditor comes to my desk or we fire up a Teams meeting and witnesses me running it.
No, the DBA can't have direct control over any audit results, else it's not a genuine audit. That is, the DBA(s) themselves must also be audited.
Why does the DBA need to prevent the auditor from directly viewing metadata on the instance? Allowing the DBA alone to run code to get results allow too big an opening for a clever DBA to taint the results.
Like I said, the auditor watches me. What results am I going to taint there?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2021 at 6:21 pm
ScottPletcher wrote:Jeff Moden wrote:ScottPletcher wrote:Jeff Moden wrote:frederico_fonseca wrote:why do other uses need to see who can login on a server? that is on its own a security risk and should not be exposed by default.
If it is for auditors then I suggest that they either ask the DBA's for that info when they need it or you create a small dba database where you maintain a table with all active logins on that server and have a sql job refresh that table daily/weekly, and give the auditors access to that table only.
there are other ways of doing this using signed stored procs without giving the users any specific permisisons - see https://www.sqlservercentral.com/forums/topic/view-server-state-privilege-for-a-stored-procedure and https://sqlquantumleap.com/2018/03/05/safely-and-easily-use-high-level-permissions-without-granting-them-to-anyone-database-level/
+1000 Auditors should need no direct privs on the server except, possibly, to run a stored procedure.
How, then, would the auditors ever be able to catch problems if the DBA was causing them? So of course genuine auditors must have access to retrieve system metadata on their own. When I was at International Paper, they did rigorous audits (when you're a mega-$billion, you have to, otherwise it's not due diligence for a company of that size).
Auditors, however, must not be able to change anything on an instance. I did push back big-time when some auditors demanded that they be given "sysadmin" permissions. That was back in SQL 2000 days, when permissions within SQL Server were not nearly as granular and many queries were almost impossible to do without sysadmin. So the request made more sense then. But I still pushed hard and prevented it.
The same way I do it... the auditor emails me some code to run and I do a safety review on it. Then the auditor comes to my desk or we fire up a Teams meeting and witnesses me running it.
No, the DBA can't have direct control over any audit results, else it's not a genuine audit. That is, the DBA(s) themselves must also be audited.
Why does the DBA need to prevent the auditor from directly viewing metadata on the instance? Allowing the DBA alone to run code to get results allow too big an opening for a clever DBA to taint the results.
Like I said, the auditor watches me. What results am I going to taint there?
If this is actually related to an audit, here are two situations that may apply to this discussion.
In one position, the DBA's access and activities were literally marked as being "non compliant" in the audit. I'm not sure where or who said that this is OK, but we passed the audits year after year. Those decisions were above my pay grade.
The second scenario, at a different company, was that two auditors gathered the same data (queries) completely separate of each other. One auditor was "assigned" to me, the second was assigned to randomly chosen members of the group that could perform the same level of activity as I could. They may have person A do something, and person B do something else, and so forth. The separate results/answers to questions were compared for consistency.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 13, 2021 at 6:58 pm
Jeff Moden wrote:ScottPletcher wrote:Jeff Moden wrote:ScottPletcher wrote:Jeff Moden wrote:frederico_fonseca wrote:why do other uses need to see who can login on a server? that is on its own a security risk and should not be exposed by default.
If it is for auditors then I suggest that they either ask the DBA's for that info when they need it or you create a small dba database where you maintain a table with all active logins on that server and have a sql job refresh that table daily/weekly, and give the auditors access to that table only.
there are other ways of doing this using signed stored procs without giving the users any specific permisisons - see https://www.sqlservercentral.com/forums/topic/view-server-state-privilege-for-a-stored-procedure and https://sqlquantumleap.com/2018/03/05/safely-and-easily-use-high-level-permissions-without-granting-them-to-anyone-database-level/
+1000 Auditors should need no direct privs on the server except, possibly, to run a stored procedure.
How, then, would the auditors ever be able to catch problems if the DBA was causing them? So of course genuine auditors must have access to retrieve system metadata on their own. When I was at International Paper, they did rigorous audits (when you're a mega-$billion, you have to, otherwise it's not due diligence for a company of that size).
Auditors, however, must not be able to change anything on an instance. I did push back big-time when some auditors demanded that they be given "sysadmin" permissions. That was back in SQL 2000 days, when permissions within SQL Server were not nearly as granular and many queries were almost impossible to do without sysadmin. So the request made more sense then. But I still pushed hard and prevented it.
The same way I do it... the auditor emails me some code to run and I do a safety review on it. Then the auditor comes to my desk or we fire up a Teams meeting and witnesses me running it.
No, the DBA can't have direct control over any audit results, else it's not a genuine audit. That is, the DBA(s) themselves must also be audited.
Why does the DBA need to prevent the auditor from directly viewing metadata on the instance? Allowing the DBA alone to run code to get results allow too big an opening for a clever DBA to taint the results.
Like I said, the auditor watches me. What results am I going to taint there?
If this is actually related to an audit, here are two situations that may apply to this discussion.
In one position, the DBA's access and activities were literally marked as being "non compliant" in the audit. I'm not sure where or who said that this is OK, but we passed the audits year after year. Those decisions were above my pay grade.
The second scenario, at a different company, was that two auditors gathered the same data (queries) completely separate of each other. One auditor was "assigned" to me, the second was assigned to randomly chosen members of the group that could perform the same level of activity as I could. They may have person A do something, and person B do something else, and so forth. The separate results/answers to questions were compared for consistency.
Sounds similar to my experiences. And, in fact, DBA activities would go over and above expected activities. It's only once they see it's a a DBA that the auditors can say it's not an issue (as long as the activities are normal for a DBA at that shop).
And in neither case did all inquiries go thru and be approved by the DBA, correct?
An "audit" that only allows what the DBA wants to run is not a true audit.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 13, 2021 at 7:22 pm
An "audit" that only allows what the DBA wants to run is not a true audit.
Correct. That's why I have them witness the runs. And, as a DBA, I have not only the right but the responsibility to review their code.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2021 at 7:49 pm
And in neither case did all inquiries go thru and be approved by the DBA, correct?
An "audit" that only allows what the DBA wants to run is not a true audit.
The point is not running what the DBA wants - the point is that the DBA MUST review what they intend in running so that the auditors do not run something that could break the systems - and then ensure that what is run is indeed what was reviewed.
And only in very specific and rare cases (outside a forensic audit) should a auditor be granted sysadmin to the servers and be given free reign to the servers without tight and strict supervision/monitoring.
in my shop if required we can allow a auditor to run the scripts themselves - but they send us the script to review, they are placed on the server they wish to run the script on and then they are given monitored access to cyberark so they can run that script themselves - but no other script will be allowed and particular keywords are blocked from being executed - e.g. if they try and do a "drop database" it will be blocked.
and the dba monitoring can terminate their session if they see them attempting to query user data.
(and for info - all DBA activity is fully monitored/recorded and audited randomly (with sensitive servers being always reviewed))
December 13, 2021 at 8:28 pm
ScottPletcher wrote:And in neither case did all inquiries go thru and be approved by the DBA, correct?
An "audit" that only allows what the DBA wants to run is not a true audit.
The point is not running what the DBA wants - the point is that the DBA MUST review what they intend in running so that the auditors do not run something that could break the systems - and then ensure that what is run is indeed what was reviewed.
And only in very specific and rare cases (outside a forensic audit) should a auditor be granted sysadmin to the servers and be given free reign to the servers without tight and strict supervision/monitoring.
in my shop if required we can allow a auditor to run the scripts themselves - but they send us the script to review, they are placed on the server they wish to run the script on and then they are given monitored access to cyberark so they can run that script themselves - but no other script will be allowed and particular keywords are blocked from being executed - e.g. if they try and do a "drop database" it will be blocked.
and the dba monitoring can terminate their session if they see them attempting to query user data.
(and for info - all DBA activity is fully monitored/recorded and audited randomly (with sensitive servers being always reviewed))
Hold on. I explicitly stated that auditors should not be able to change anything on the system. Talking about giving auditors "sysadmin" is a straw man.
Scripts that capture full permissions across hundreds of dbs (in our instances) can be very complex. I don't have time -- nor the inclination, frankly -- to review all their code. I do review their conclusions to make sure they are accurate.
We just completed an audit. I answered some qs but I did not run any of the code. Then again, they didn't have authority to change anything on the system, just to query it. I don't understand why anyone would feel they need to review code that just reads metadata during an audit. Is that just me?! I don't think so.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 13, 2021 at 8:32 pm
ScottPletcher wrote:An "audit" that only allows what the DBA wants to run is not a true audit.
Correct. That's why I have them witness the runs. And, as a DBA, I have not only the right but the responsibility to review their code.
We disagree on that for sure. The scripts that gather auditing data can be quite lengthy. I'm fairly sure it's not even realistic to expect to review all auditing code during the time frame allowed.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply