All geek questions in one place

When will sys.database_permissions contain a line with state = 'R'?

The MSDN documentation for sys.database_permissions says that the status column can be either "G", "D", "R", or 'W'. The value "R" has a description of "REVOKE", which makes the sound like a line with this value will correspond to the revoked resolution. However, as far as I can tell from the docs for REVOKE , revoking the permission completely removes it, so I would expect that just remove the line from sys.database_permissions . What happened when I tested the provision and then the revocation of permission; after GRANT , permission appears in this view, and after REVOKE it disappears.

My question is: under what circumstances does this view always contain lines with the state set to "R"? I am asking about this because I am not sure whether to process the “R” strings when viewing this view in code.

I can think of several possible scenarios where this might happen, but I have not found confirmation:

  • Lines
  • 'R' may appear if you granted some general permission, and then revoked the more detailed permission that was implied by the blanket permission (the granular permission will display as 'R'). So far I have not found such permissions. Lines
  • 'R' can be displayed very briefly while SQL processes the REVOKE command and then the entire line disappears. I did not notice this, but, apparently, there was only a very small window of time when it appeared.
+4
source share
3 answers

As Jack Richins replied on Twitter (thanks to @Remus Rusanu for sending):

I believe this happens with wi column level permissions that contradict perms tables or views.

I tested this and he is right.

+1
source

For objects that may have column permissions, such as tables or views, having DENY or GRANT object permissions requires REVOKE to maintain column permissions. The following is a working example tested on SQL Server 2008, which demonstrates when a record with state R can exist in sys.database_permissions . If the order of the GRANT and REVOKE canceled, a record with state R not saved.

https://gist.github.com/mches/d2282946fbe7f50a708b

 CREATE USER RevokeTestUser WITHOUT LOGIN; REVOKE CONNECT TO RevokeTestUser AS dbo; CREATE TABLE dbo.RevokeTest ( col int NOT NULL ); GRANT SELECT ON dbo.RevokeTest TO RevokeTestUser AS dbo; REVOKE SELECT ON dbo.RevokeTest (col) TO RevokeTestUser AS dbo; SELECT * FROM sys.database_permissions WHERE grantee_principal_id = DATABASE_PRINCIPAL_ID(N'RevokeTestUser'); DROP USER RevokeTestUser; DROP TABLE dbo.RevokeTest;

These are the results of the SELECT :

 class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc 1 OBJECT_OR_COLUMN 1081939822 0 31 1 SL SELECT G GRANT 1 OBJECT_OR_COLUMN 1081939822 1 31 1 SL SELECT R REVOKE
+3
source

Yes, it is possible that the sys.database_permissions tables contain a row with a status of R. R means cancellation and it will be in the status column of the table.

We can also have D (Deny), G (grant), W (grant with the possibility of a grant) together with R.

The data type of this status column will be char (1)

See the link below for a better understanding.

http://msdn.microsoft.com/en-us/library/ms188367.aspx

0
source

Source: https://habr.com/ru/post/1389372/

More articles:


All Articles