コード例 #1
0
        private async Task<IEnumerable<SqlUserGranting>> FetchDatabaseUserInfo(SqlConnectionInfo connInfo, string database)
        {
            IEnumerable<SqlPermission> perms;
            IEnumerable<SqlRoleMembership> roles;
            using(var connection = await connInfo.Connect(database))
            {
                // Fetch Permissions and role memberships
                perms = (await connection.QueryAsync<SqlPermission>(@"
                    SELECT
	                    u.principal_id,
	                    u.name,
	                    p.class_desc,
                        p.state_desc,
                        p.permission_name,
	                    u.[sid],
	                    (CASE WHEN p.class_desc = 'SCHEMA' THEN s.name 
		                      WHEN p.class_desc = 'DATABASE' THEN NULL END) AS object_name
                    FROM sys.database_permissions p
                    INNER JOIN sys.database_principals u ON p.grantee_principal_id = u.principal_id
                    LEFT OUTER JOIN sys.schemas s ON p.class_desc = 'SCHEMA' AND p.major_id = s.schema_id
                    WHERE u.[type] = 'S'
                ")).ToList();

                roles = (await connection.QueryAsync<SqlRoleMembership>(@"
                    SELECT mem_prin.name AS member, role_prin.name AS role, mem_prin.[sid] as [sid]
                    FROM sys.database_role_members mem
                    INNER JOIN sys.database_principals mem_prin ON mem.member_principal_id = mem_prin.principal_id
                    INNER JOIN sys.database_principals role_prin ON mem.role_principal_id = role_prin.principal_id
                ")).ToList();
            }

            return Enumerable.Concat(
                perms.Select(p => SqlUserGranting.Create(p, database)),
                roles.Select(r => SqlUserGranting.Create(r, database)));
        }
コード例 #2
0
        private async Task CleanSecrets(SqlConnectionInfo connInfo)
        {
            if (Session.CurrentEnvironment == null)
            {
                return;
            }

            var secrets = await GetEnvironmentSecretStore(Session.CurrentEnvironment);

            if (secrets == null)
            {
                return;
            }

            var loginSecretName = new SecretName("sqldb." + connInfo.GetServerName() + ":logins." + User, datacenter: null);
            var secret          = await secrets.Read(loginSecretName, "nucmd db deleteuser");

            if (secret != null)
            {
                await Console.WriteInfoLine(Strings.Db_DeleteUserCommand_DeletingSecret, loginSecretName.Name);

                await secrets.Delete(loginSecretName, "nucmd db deleteuser");
            }

            // Check if there is a link that points at this user
            var match = BaseNameExtractor.Match(User);

            if (!match.Success)
            {
                return;
            }

            var userSecretName = new SecretName("sqldb." + connInfo.GetServerName() + "users." + match.Groups["base"].Value);

            secret = await secrets.Read(userSecretName, "nucmd db deleteuser");

            if (String.Equals(secret.Value, loginSecretName.ToString(), StringComparison.OrdinalIgnoreCase))
            {
                await Console.WriteInfoLine(Strings.Db_DeleteUserCommand_DeletingSecret, userSecretName.Name);

                await secrets.Delete(userSecretName, "nucmd db deleteuser");
            }
        }
コード例 #3
0
        protected DacServices ConnectDac(SqlConnectionInfo connInfo)
        {
            // We have to use an insecure string :(
            var connStr = new SqlConnectionStringBuilder(connInfo.ConnectionString.ConnectionString)
            {
                InitialCatalog = "master"
            };

            connStr.UserID = connInfo.Credential.UserId;

            var passwordPtr = Marshal.SecureStringToBSTR(connInfo.Credential.Password);

            connStr.Password = Marshal.PtrToStringBSTR(passwordPtr);
            Marshal.FreeBSTR(passwordPtr);

            var services = new DacServices(connStr.ConnectionString);

            services.Message += (s, a) =>
            {
                Task t = null;
                switch (a.Message.MessageType)
                {
                case DacMessageType.Error:
                    t = Console.WriteErrorLine(a.Message.Message);
                    break;

                case DacMessageType.Message:
                    t = Console.WriteInfoLine(a.Message.Message);
                    break;

                case DacMessageType.Warning:
                    t = Console.WriteWarningLine(a.Message.Message);
                    break;
                }
                if (t != null)
                {
                    t.Wait();
                }
            };
            return(services);
        }
コード例 #4
0
        protected DacServices ConnectDac(SqlConnectionInfo connInfo)
        {
            // We have to use an insecure string :(
            var connStr = new SqlConnectionStringBuilder(connInfo.ConnectionString.ConnectionString)
            {
                InitialCatalog = "master"
            };
            connStr.UserID = connInfo.Credential.UserId;

            var passwordPtr = Marshal.SecureStringToBSTR(connInfo.Credential.Password);
            connStr.Password = Marshal.PtrToStringBSTR(passwordPtr);
            Marshal.FreeBSTR(passwordPtr);

            var services = new DacServices(connStr.ConnectionString);

            services.Message += (s, a) =>
            {
                Task t = null;
                switch (a.Message.MessageType)
                {
                    case DacMessageType.Error:
                        t = Console.WriteErrorLine(a.Message.Message);
                        break;
                    case DacMessageType.Message:
                        t = Console.WriteInfoLine(a.Message.Message);
                        break;
                    case DacMessageType.Warning:
                        t = Console.WriteWarningLine(a.Message.Message);
                        break;
                }
                if (t != null)
                {
                    t.Wait();
                }
            };
            return services;
        }
コード例 #5
0
        private async Task <IEnumerable <SqlUserGranting> > FetchDatabaseUserInfo(SqlConnectionInfo connInfo, string database)
        {
            IEnumerable <SqlPermission>     perms;
            IEnumerable <SqlRoleMembership> roles;

            using (var connection = await connInfo.Connect(database))
            {
                // Fetch Permissions and role memberships
                perms = (await connection.QueryAsync <SqlPermission>(@"
                    SELECT
	                    u.principal_id,
	                    u.name,
	                    p.class_desc,
                        p.state_desc,
                        p.permission_name,
	                    u.[sid],
	                    (CASE WHEN p.class_desc = 'SCHEMA' THEN s.name 
		                      WHEN p.class_desc = 'DATABASE' THEN NULL END) AS object_name
                    FROM sys.database_permissions p
                    INNER JOIN sys.database_principals u ON p.grantee_principal_id = u.principal_id
                    LEFT OUTER JOIN sys.schemas s ON p.class_desc = 'SCHEMA' AND p.major_id = s.schema_id
                    WHERE u.[type] = 'S'
                ")).ToList();

                roles = (await connection.QueryAsync <SqlRoleMembership>(@"
                    SELECT mem_prin.name AS member, role_prin.name AS role, mem_prin.[sid] as [sid]
                    FROM sys.database_role_members mem
                    INNER JOIN sys.database_principals mem_prin ON mem.member_principal_id = mem_prin.principal_id
                    INNER JOIN sys.database_principals role_prin ON mem.role_principal_id = role_prin.principal_id
                ")).ToList();
            }

            return(Enumerable.Concat(
                       perms.Select(p => SqlUserGranting.Create(p, database)),
                       roles.Select(r => SqlUserGranting.Create(r, database))));
        }
コード例 #6
0
        private async Task CleanSecrets(SqlConnectionInfo connInfo)
        {
            if (Session.CurrentEnvironment == null)
            {
                return;
            }

            var secrets = await GetEnvironmentSecretStore(Session.CurrentEnvironment);
            if (secrets == null)
            {
                return;
            }

            var loginSecretName = new SecretName("sqldb." + connInfo.GetServerName() + ":logins." + User, datacenter: null);
            var secret = await secrets.Read(loginSecretName, "nucmd db deleteuser");
            if (secret != null)
            {
                await Console.WriteInfoLine(Strings.Db_DeleteUserCommand_DeletingSecret, loginSecretName.Name);
                await secrets.Delete(loginSecretName, "nucmd db deleteuser");
            }

            // Check if there is a link that points at this user
            var match = BaseNameExtractor.Match(User);
            if (!match.Success)
            {
                return;
            }

            var userSecretName = new SecretName("sqldb." + connInfo.GetServerName() + "users." + match.Groups["base"].Value);
            secret = await secrets.Read(userSecretName, "nucmd db deleteuser");
            if (String.Equals(secret.Value, loginSecretName.ToString(), StringComparison.OrdinalIgnoreCase))
            {
                await Console.WriteInfoLine(Strings.Db_DeleteUserCommand_DeletingSecret, userSecretName.Name);
                await secrets.Delete(userSecretName, "nucmd db deleteuser");
            }
        }
コード例 #7
0
        protected override async Task OnExecute()
        {
            // Gather default data
            SqlConnectionInfo connInfo;
            if (String.IsNullOrEmpty(DatabaseConnectionString))
            {
                connInfo = await GetSqlConnectionInfo(
                    0,
                    KnownSqlConnection.Legacy.ToString(),
                    specifiedAdminUser: null,
                    specifiedAdminPassword: null,
                    promptForPassword: false);
            }
            else
            {
                connInfo = new SqlConnectionInfo(
                    new SqlConnectionStringBuilder(DatabaseConnectionString), 
                    credential: null);
            }

            await Console.WriteInfoLine(Strings.Package_ReserveCommand_Reserving,
                String.Join(", ", Ids),
                String.Join(", ", Owners),
                connInfo.ConnectionString.DataSource);
            using (var connection = await connInfo.Connect())
            {
                foreach (var id in Ids)
                {
                    if (!Quiet)
                    {
                        await Console.WriteInfoLine(Strings.Package_ReserveCommand_ReservingId, id);
                    }

                    if (!WhatIf)
                    {
                        var result = (await connection.QueryAsync<int>(@"
                            IF EXISTS (SELECT 0 FROM PackageRegistrations WHERE [Id] = @id)
	                            SELECT 0
                            ELSE BEGIN
                                INSERT INTO PackageRegistrations([Id], [DownloadCount]) VALUES(@id, 0)
	                            SELECT 1
                            END",
                            new { id })).Single();
                        if (result == 0)
                        {

                            var existingOwners = await connection.QueryAsync<string>(@"
                                SELECT      u.Username
                                FROM        PackageRegistrations pr
                                INNER JOIN  PackageRegistrationOwners pro ON pro.PackageRegistrationKey = pr.[Key]
                                INNER JOIN  [Users] u ON u.[Key] = pro.UserKey
                                WHERE       pr.[Id] = @id
                                ORDER BY    u.Username",
                            new { id });

                            // If we're in quiet mode, only show errors for packages owned by users not in the target
                            if (!Quiet || existingOwners.Any(o => !Owners.Contains(o)))
                            {
                                await Console.WriteErrorLine(
                                    Strings.Package_ReserveCommand_IdAlreadyExists,
                                    id);

                                foreach (string owner in existingOwners)
                                {
                                    await Console.WriteErrorLine(Strings.Package_ReserveCommand_ExistingOwner, owner);
                                }
                            }

                            continue; // Don't change owners of existing packages.
                        }
                    }
                    foreach (var owner in Owners)
                    {
                        if (!Quiet)
                        {
                            await Console.WriteInfoLine(Strings.Package_ReserveCommand_GrantingOwnership, owner, id);
                        }

                        if (!WhatIf)
                        {
                            var result = (await connection.QueryAsync<int>(@"
                                DECLARE @prKey int = (SELECT [Key] FROM PackageRegistrations WHERE Id = @id)
                                DECLARE @uKey int = (SELECT [Key] FROM Users WHERE Username = @owner)

                                IF EXISTS (SELECT 0 FROM PackageRegistrationOwners WHERE PackageRegistrationKey = @prKey AND UserKey = @uKey)
	                                SELECT 0
                                ELSE BEGIN
                                    INSERT INTO PackageRegistrationOwners([PackageRegistrationKey], [UserKey])
                                    VALUES(@prKey, @uKey)
	                                SELECT 1
                                END",
                                new { id, owner })).Single();

                            if (result == 0 && !Quiet)
                            {
                                await Console.WriteInfoLine(Strings.Package_ReserveCommand_OwnerAlreadyExists, owner, id);
                            }
                        }
                    }
                }   
            }
        }