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)));
}
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");
}
}
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);
}
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;
}
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))));
}
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");
}
}
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);
}
}
}
}
}
}
