microsoft / microsoft/sqlmanagementobjects
Scripting object permissions independently
Nadie ha tomado este issue todavía.
- Lenguaje dominante
- C#
- Estrellas
- 143
- Forks
- 28
- Métricas de merge de PR
- Sin PR fusionados en 30 d
Descripción
@shueybubbles I wrote this code awhile ago to integrate with SMO and overcome limitations in the built-in scripting engine (note the LOL). Can I get some initial feedback on how I can convert this into a PR? I am sure it's missing a bunch of edge cases.
public static class ObjectPermissionInfoExtensions
{
public static string ToSqlString(this ObjectPermissionInfo opi)
{
if (opi is null) throw new ArgumentNullException(nameof(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}]"
: $"ON OBJECT::[{opi.ObjectSchema}].[{opi.ObjectName}]([{opi.ColumnName}])";
break;
case ObjectClass.Schema:
permissionedObjectExpression = $"ON {opi.ObjectClass}::[{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.");
}
var result =
$"{opi.PermissionState.ToString().ToUpper()} {opi.PermissionType} {permissionedObjectExpression} TO {toTarget}";
if (string.IsNullOrWhiteSpace(result))
System.Diagnostics.Debugger.Break(); // <-- LOL.
return result;
}
Guía de contribución
No hay ninguna guía de contribución indexada para este repositorio
Primeros pasos
- Lee el issue completo y luego la guía de contribución del proyecto.
- Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
- Haz un fork del repositorio y trabaja en una rama.
- Abre un pull request que haga referencia al número del issue.
Línea de trabajo
No se indica ningún archivo del repositorio, prueba ni punto de entrada; empieza por localizar ObjectPermissionInfo y la API de scripting o permisos existente en el repositorio. Compara el tratamiento propuesto de las clases de objetos de SQL Server con el comportamiento actual y la documentación, y después define los casos compatibles y las pruebas antes de decidir si la conversión está completa.
Escrito por el modelo de indexación a partir del texto del issue.
Evaluación
- Stack tecnológico
- csharp, sql
- Área
- api, database
- Tipo de issue
- Nueva funcionalidad
- Dificultad
- 5/5
- Tiempo estimado
- Más de una semana
- Estado de actividad
- Estancado
- Claridad
- Necesita aclaración
- Aptitud para principiantes
- 25/100