microsoft / microsoft/sqlmanagementobjects

Scripting object permissions independently

Abierto
#98 4 comentarios 0 reacciones 0 asignados Ver en GitHub

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

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. 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

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.