Fix permission grammar spec: TYPE #4201
Conversation
For user-defined types, permissions should be scoped to the TYPE:: they operate on.
|
@jzabroski : Thanks for your contribution! The author(s) have been notified to review your proposed change. |
|
Thanks, @jzabroski! Hey @julieMSFT - It looks like John's proposed changes are under the syntax for DW and PDW. Is this something you can help confirm? Thanks! |
|
@jzabroski Hi John, I am verifying your suggestion. |
|
Note that although my suggestion is under DW and PDW, it has more to do with how the document is laid out than applying to just these target types. For my use case, I was filling a hole in the SMO API, where if you have an |
|
My C# code looks something like this: using System;
using Microsoft.SqlServer.Management.Smo;
namespace X
{
public static class ObjectPermissionInfoExtensions
{
public static string ToSqlString(this ObjectPermissionInfo opi)
{
string toTarget;
switch (opi.GranteeType)
{
case PrincipalType.None:
throw new Exception("What the heck happened?");
case PrincipalType.Login:
case PrincipalType.ServerRole:
case PrincipalType.User:
case PrincipalType.DatabaseRole:
case PrincipalType.ApplicationRole:
default:
toTarget = $"/*{opi.GranteeType}::*/[{opi.Grantee}]";
break;
};
string permissionedObjectExpression;
switch (opi.ObjectClass)
{
case ObjectClass.Database:
permissionedObjectExpression = $"ON {opi.ObjectClass}::[{opi.ObjectName}]";
break;
case ObjectClass.ObjectOrColumn:
// ex A: https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql?view=sql-server-ver15#a-granting-select-permission-on-a-table
// ex B: https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql?view=sql-server-ver15#b-granting-execute-permission-on-a-stored-procedure
permissionedObjectExpression = string.IsNullOrWhiteSpace(opi.ColumnName)
? $"ON OBJECT::[{opi.ObjectSchema}].[{opi.ObjectName}]"
: $"([{opi.ColumnName}]) ON OBJECT::[{opi.ObjectSchema}].[{opi.ObjectName}]";
break;
case ObjectClass.Schema:
permissionedObjectExpression = $"ON {opi.ObjectClass}::[{opi.ObjectSchema}].[{opi.ObjectName}]";
break;
case ObjectClass.User:
// https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-database-principal-permissions-transact-sql?view=sql-server-ver15
permissionedObjectExpression = $"ON USER::[{opi.ObjectName}]";
break;
case ObjectClass.DatabaseRole:
// https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-database-principal-permissions-transact-sql?view=sql-server-ver15
permissionedObjectExpression = $"ON ROLE::[{opi.ObjectName}]";
break;
case ObjectClass.ApplicationRole:
// https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-database-principal-permissions-transact-sql?view=sql-server-ver15
permissionedObjectExpression = $"ON APPLICATION ROLE::[{opi.ObjectName}]";
break;
case ObjectClass.SqlAssembly:
permissionedObjectExpression = $"ASSEMBLY [{opi.ObjectName}]";
break;
case ObjectClass.UserDefinedType:
permissionedObjectExpression = $"ON TYPE::[{opi.ObjectSchema}].[{opi.ObjectName}]";
break;
case ObjectClass.SecurityExpression:
case ObjectClass.XmlNamespace:
case ObjectClass.MessageType:
case ObjectClass.ServiceContract:
case ObjectClass.Service:
case ObjectClass.RemoteServiceBinding:
case ObjectClass.ServiceRoute:
throw new Exception($"{opi.ObjectClass} is in set of object classes not supported.");
case ObjectClass.FullTextCatalog:
// https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-full-text-permissions-transact-sql?view=sql-server-ver15
permissionedObjectExpression = $"ON FULLTEXT CATALOG :: {opi.ObjectName}";
break;
case ObjectClass.SearchPropertyList:
// https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-search-property-list-permissions-transact-sql?view=sql-server-ver15
permissionedObjectExpression = $"ON SEARCH PROPERTY LIST :: {opi.ObjectName}";
break;
case ObjectClass.SymmetricKey:
// https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-symmetric-key-permissions-transact-sql?view=sql-server-ver15#examples
permissionedObjectExpression = $"ON SYMMETRIC KEY {opi.ObjectName}";
break;
case ObjectClass.Server:
// This won't work for every SERVER permission, but it at least covers one scenario:
// ex A: https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-server-permissions-transact-sql?view=sql-server-ver15#a-granting-a-permission-to-a-login
permissionedObjectExpression = "SERVER";
break;
case ObjectClass.Login:
// https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-server-principal-permissions-transact-sql?view=sql-server-ver15
permissionedObjectExpression = $"ON LOGIN :: {opi.ObjectName}";
break;
case ObjectClass.ServerPrincipal:
throw new Exception("Not clear from documentation how to handle this. See: https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-server-principal-permissions-transact-sql?view=sql-server-ver15");
case ObjectClass.ServerRole:
// https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-server-principal-permissions-transact-sql?view=sql-server-ver15
permissionedObjectExpression = $"ON SERVER ROLE :: {opi.ObjectName}";
break;
case ObjectClass.Endpoint:
// https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-endpoint-permissions-transact-sql?view=sql-server-ver15
permissionedObjectExpression = $"ON ENDPOINT :: {opi.ObjectName}";
break;
case ObjectClass.Certificate:
// https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-certificate-permissions-transact-sql?view=sql-server-ver15
permissionedObjectExpression = $"ON CERTIFICATE :: {opi.ObjectName}";
break;
case ObjectClass.FullTextStopList:
// https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-full-text-permissions-transact-sql?view=sql-server-ver15
permissionedObjectExpression = $"FULLTEXT STOPLIST :: {opi.ObjectName}";
break;
case ObjectClass.AsymmetricKey:
// https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-asymmetric-key-permissions-transact-sql?view=sql-server-ver15
permissionedObjectExpression = $"ASYMMETRIC KEY :: {opi.ObjectName}";
break;
case ObjectClass.AvailabilityGroup:
case ObjectClass.ExternalDataSource:
case ObjectClass.ExternalFileFormat:
default:
throw new Exception($"{opi.ObjectName} is in object class {opi.ObjectClass}, which is not a supported object class.");
}
return $"{opi.PermissionState.ToString().ToUpper()} {opi.PermissionType} {permissionedObjectExpression} TO {toTarget}";
}
}
} |
|
@nanditavalsan Hi Nandita, please review this PR and let me know if this is a valid change. |
|
@nanditavalsan Can you review this suggested change to verify. Or any help finding the right contact would be great. @jzabroski Sorry for the delay on this suggested change. Thanks for your feedback! |
|
@jzabroski - Hey John! Sorry for the delay. We were looking into this internally, and there might have been some confusion. Your PR changes are to the syntax of Azure Synapse (DW) and PDW. Please let us know if we have misunderstood, or if we can clarify anything. I will go ahead and close this PR, but feel free to submit another one if you find any clarifying data. We really appreciate your help on our documentation! |
|
@VanMSFT I agree that it is sophisticated/nuanced, but it should still have a well-documented grammar. Perhaps I can resubmit the PR. Can you explain to me why SQL Server uses class:: vs. the other products use class_type::? Sql Server uses class:: Warehouse uses class_type:: I suppose one logical explanation is that class:: and class_type:: are two unique sets of items, but the way I read the grammar was the same way I would if looking at Sql Server 2012 docs vs 2019 docs - as a version change and thus I don't see why the need for class:: vs. class_type:: P.S. Thank you for your kind words, too. I would contribute more to more Microsoft projects but I think my average PR has been open for many months and in one case, on another MS project, the PR was open for two years before it was finally accepted. But your kinds words certainly add a lot of value. |
|
@jzabroski - Hey John, I think those are just place holders for the actual class that should be used. You're right, that it probably should be the same. We really do appreciate your contributions (and those of the community). It helps our documentation greatly. We rely on the community to help us with errors or any stale issues that we have missed, as we're all human, and prone to mistakes. I'm also sorry about the delays. We try our best to be as prompt as possible, but with limited resources, we must prioritize certain things. We understand the inconvenience this can cause, but we hope that you continue to help us grow! |
For user-defined types, permissions should be scoped to the TYPE:: they operate on.