REVOKE Public from System views

Question

Hi

After a Audit of databases (using AppDetective) I've been tasked with revoking the Public roles access from our servers. On some server we've had good luck doing this but but I have one server where the Public role has been granted SELECT permissions to system views on the Master DB,  and I cannot not modify the permission (I can't REVOKE the public role access nor can I GRANT the role we are replacing the public role SELECT permission.

When I run

IF  DB_ID(N'master') IS NOT NULL
   EXEC('USE [master]' + ' REVOKE SELECT ON [sys].[dm_pdw_nodes_os_tasks] FROM PUBLIC')

It returns

Cannot find the object 'dm_pdw_nodes_os_tasks', because it does not exist or you do not have permission.

I've given my account I'm running this under DBO to the Master DB.

Does anyone know what I am doing wrong?

Answer 1

Can you please check that DMV name? It does not exist under the system views.

---

SQL Server Database Administrator

Answer 2

Yes it does on this server. When I go to Databases System Databases Master Views System Views I can browse down to dm_pdw_nodes_os_tasks.  This view I choose as just an example, there are a couple hundred other ones, mostly stating with dm or pwd that I am having this problem with.

Answer 3

FYI it is SQL 2008 SP3 running on Windows 2008 R2

Answer 4

You are trying to revoke access on a non-existing view. That view exists only in the Parallel Data Warehouse.

Yes, the view is listed in sys.all_objects, but I guess that this is because PDW uses the same mssqlsystemresource as the mainline product.

I would also discourage you to blindly go and revoke permission from DMVs and catalog views. Many of these have built-in security. For instance, sys.dm_exec_sessions will only list your own session if you don't have VIEW SERVER STATE. If you revoke permissions, you can break code that makes legit access to these views.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

I recomnend that you read the following article http://blogs.technet.com/b/fort_sql/archive/2010/02/04/remove-public-and-guest-permissions.aspx

---

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
MC ID: Microsoft Transcript

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 6

Thank you for the replies, I will try the script from the article.

I not sure I understand how the dm pdw are non-exiting views, since they appear in listing of views, with security applied (the Public role has select for them) to them and the auditing tool we are using (AppDetective) have identified them, and our auditors requested we remove them.

If we cannot remove the Public roles access to the view, is there a way to drop these non-existence views, which we are not using, so they don't appear on our audit?

Thanks again.

 

 

Answer 7

Some is putting the horse before the cart. A non-existing cart.

The PDW views does not exist on your server. And that was exactly what the error message told you.

The views are however listed in sys.all_objects. This is because they are listed in mssqlsystemresource, the resource database. The resource database is a read-only database that you can only access indirectly. It's a database that holds all definitions of system objects. Since mssqlsystemresource is logically a DLL, the PDW has the same resource database as all other editions of SQL Server, and therefore you see them.

Why the permissions show up I don't know, but maybe these are also hardwired into the resource database. Which would mean that you can't even remove the permissions if you have PDW. But I'm out a limb here. I have never seen PDW.

If your auditors ask you why the permissions are still there, you tell them that the permission is useless, because there is nothing to access.

If they are not willing to accept that explanation, please ask them what risk the permission to a non-existing view would mean.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

Thank you for the reply, it does seem to make sense.

Do you know of any MS links that have this info that I might be able to point the auditors to?

Answer 9

I don't know of any current links that mention this, however I can confirm that the view that contain pdw are for the Parallel Data Warehouse (PDW) version of SQL Server. And though they show up in sys.all_objects they cannot be used in versions of the SQL Server that are not PDW. (I notice that they do not appear in sys.objects.)

I have added the following note to the SQL Server 2008 R2 version of Books Online.

Note:
Some objects that support SQL Server PDW can appear in <database xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">sys.all_objects.</database>

I put the note in the SQL Server 2008 R2 version of Books Online because we are unlikely to publish another version of the SQL Server 2008 Books Online. And if this problem doesn't occur in R2 yet, it might show up with the next SP.

<database xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5"></database>

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 10

Thanks for the replies.

There are also a lot of views starting with dm (such as dm_audit_actions) that appear to have the same issue as the pdw_ views (the PUBLIC role appears to have access to them, though the access cannot be removed and the objects cannot be viewed) , can you confirm this is the same issue?

Thanks,

Rich

Answer 11

Your ealier post indicates that you are using SQL 2008 SP3 but you didn't say which edition. In SQL Server 2008, Audit is restricted to the Enterprise edition. So if you are not on the Enterprise, Evaluation, or Developer edition, you will not be able to view anything in any of the audit related DMVs.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 12

Thanks Rick,

We are using Standard edition of SQL, so I guess we cannot view the item, but I'm wondering why are REVOKE script fails on these item  (the only other items it fails on are the pdw views). Are the dm views like the pdw views?