VisiSec.psm1
|
function Grant-ProjetsTL { [CmdletBinding()] <# .SYNOPSIS Grant SQL privileges to SSI-ProjetsTL group Privileges described here: http://visiportail/ssi/DevBI/Procdures/SQL%20Knowledge%20base/Securite%20SQL%20Server%20Visilab.docx?web=1 .Description .PARAMETER SQLInstance - server .PARAMETER DropExisting - drops existing users and logins - WARNING use with caution, custom privs may be lost when using this option .EXAMPLE #> Param ( [parameter(Mandatory=$false,position=0,ValueFromPipeline=$true)] [ValidateNotNullOrEmpty()] [string]$SQLInstance , [parameter(Mandatory=$false,position=1)] [switch]$DropExisting ) Begin { if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process { $principal = 'GPPH\SSI-ProjetsTL' $ErrorActionPreference = "Stop"; ## optional DROP user and Login if ($DropExisting.IsPresent) { Remove-DbaDbUser -SqlInstance $SQLInstance -User $principal -Force Remove-DbaLogin -SqlInstance $SQLInstance -Login $principal -Force -CONFIRM:$false } ## create login $qry = "exec sp_visi_create_login '$principal'" Write-Debug $qry Invoke-DbaQuery -SqlInstance $sqlinstance -Query $qry ## create db users ## master $qry = "EXEC sp_visi_create_user @user = '$principal', @dbRoleList = NULL, @debug =1; GRANT EXEC ON dbo.xp_readErrorLog TO [$principal] " Write-Debug $qry Invoke-DbaQuery -SqlInstance $sqlInstance -Query $qry -Database Master ## msdb $qry = "EXEC sp_visi_create_user @user = '$principal' , @dbRoleList = 'db_dataReader,SQLAgentOperatorRole,databaseMailUserRole', @debug =1 " #Invoke-Sqlcmd -ServerInstance $srv -Query $qry -Database MSDB Write-Debug $qry Invoke-DbaQuery -SqlInstance $sqlInstance -Query $qry -Database MSDB ## RW DBs @('Arizona','ArizonaCASH','Elvira','ElviraPV','OneCustomer','OneImport','OneShare','OneTab','VisiPeriph ','VisiTemp','Webshop','VisiSystem') | ForEach-Object { $qry = " IF EXISTS ( SELECT * FROM sys.databases AS d WHERE d.name = '$_' ) EXEC sys.sp_executeSQL N' USE [$_]; EXEC dbo.sp_Visi_create_user @user = ''$principal'',@dbRoleList = ''db_datareader,db_dataWriter''; GRANT EXECUTE, VIEW DEFINITION ON database::[$_] TO [$principal] ; " if ($_ -eq 'Arizona') { $qry += " DENY INSERT, UPDATE, DELETE ON dbo.Address TO [$principal] ; DENY INSERT, UPDATE, DELETE ON dbo.Telecom TO [$principal] ; DENY INSERT, UPDATE, DELETE ON dbo.Address_key TO [$principal] ; DENY INSERT, UPDATE, DELETE ON dbo.Address_criteria TO [$principal] ; DENY INSERT, UPDATE, DELETE ON dbo.Customer TO [$principal] ; DENY INSERT, UPDATE, DELETE ON dbo.Supplier TO [$principal] ; " } $qry += "'" Write-Debug $qry Invoke-DbaQuery -SqlInstance $sqlInstance -Query $qry } ## RO dbs @('Archive','DBA','OneShareArchive','VisiAudit' ) | ForEach-Object { $qry = " IF EXISTS ( SELECT * FROM sys.databases AS d WHERE d.name = '$_' ) EXEC sys.sp_executeSQL N' USE [$_]; EXEC dbo.sp_Visi_create_user @user = ''$principal'',@dbRoleList = ''db_datareader''; GRANT EXECUTE ON database::[$_] TO [$principal] ; " ## create table ok in DBA if ($_ -eq 'DBA') { $qry += " GRANT CREATE TABLE ON DATABASE::DBA TO [$principal] AS dbo; GRANT ALTER ON DATABASE::DBA TO [$principal] AS dbo;" } ## insert in VisiAudit if ($_ -eq 'VisiAudit') { $qry += " GRANT INSERT on database::[VisiAudit] TO [$principal] " } $qry += "'" Write-Debug $qry Invoke-DbaQuery -SqlInstance $sqlInstance -Query $qry } ## open bar DBs @('ArizonaLD') | ForEach-Object { $qry = " IF EXISTS ( SELECT * FROM sys.databases AS d WHERE d.name = '$_' ) EXEC sys.sp_executeSQL N' USE [$_]; EXEC dbo.sp_Visi_create_user @user = ''$principal'',@dbRoleList = ''db_datareader,db_dataWriter,db_DDLAdmin''; GRANT EXECUTE ON database::[$_] TO [$principal] ; ' " Write-Debug $qry Invoke-DbaQuery -SqlInstance $sqlInstance -Query $qry } } } Set-Alias -Name Grant-PM -Value Grant-ProjetsTL Set-Alias -Name Grant-SSIPM -Value Grant-ProjetsTL function Grant-DevSQL { <# .SYNOPSIS Grants predefined SQL privileges to SSI-DevSQL .Description .PARAMETER SQLInstance Target SQL instance Value From Pipeline .EXAMPLE # Grant-DevSQL VI511-S1 .EXAMPLE # (Get-VisiServers REC ).server_name | Grant-DevSQL #> [CmdletBinding(SupportsShouldProcess)] Param ( [parameter(ValueFromPipeline=$true)] [string] $SQLInstance ) Begin { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process{ Write-Debug "Server name = $SQLInstance" $principal = 'GPPH\SSI-DevSQL' ## remove from all server roles Get-DbaServerRoleMember -SqlInstance $SQLInstance -Login $principal -debug:$false | ForEach-Object { $qry = " ALTER SERVER ROLE [$($_.Role)] DROP MEMBER [$principal]" Write-Debug $qry Invoke-Sqlcmd -ServerInstance $SQLInstance -Query $qry } ## create login $qry = "EXEC dbo.sp_Visi_create_login @login = '$principal'; ALTER SERVER ROLE bulkadmin ADD member [$principal]; GRANT VIEW SERVER STATE TO [$principal];" Write-Debug $qry Invoke-Sqlcmd -ServerInstance $SQLInstance -Query $qry -ea stop ## remove principal from all db roles Get-DbaDbRoleMember -SqlInstance $SQLInstance -debug:$false | Where-Object Login -eq $principal | ForEach-Object { Remove-DbaDbRoleMember -SqlInstance $SQLInstance -Database $($_.Database) -Role $($_.Role) -User $principal -Confirm:$false -debug:$false } ## create db users ## master $qry = "EXEC sp_visi_create_user @user = '$principal', @dbRoleList = NULL; GRANT EXEC ON dbo.xp_readErrorLog TO [$principal] " Write-Debug $qry Invoke-Sqlcmd -ServerInstance $SQLInstance -Query $qry -Database master ## msdb $qry = "EXEC sp_visi_create_user @user = '$principal' , @dbRoleList = 'db_dataReader,SQLAgentOperatorRole,databaseMailUserRole'" Write-Debug $qry Invoke-Sqlcmd -ServerInstance $SQLInstance -Query $qry -Database MSDB ## RW DBs @('Archive','Arizona','ArizonaCASH','Elvira','ElviraPV','OneCustomer','OneImport','OneShare','OneTab','VisiPeriph ','VisiTemp','Webshop','VisiSystem') | ForEach-Object { Write-debug $($SQLInstance +'.' + $_ ) $qry = " IF EXISTS ( SELECT * FROM sys.databases AS d WHERE d.name = '$_' ) EXEC sys.sp_executeSQL N' USE [$_]; EXEC dbo.sp_Visi_create_user @user = ''$principal'',@dbRoleList = ''db_datareader,db_dataWriter,db_DDLAdmin''; GRANT EXECUTE ON database::[$_] TO [$principal] ; " if ($_ -eq 'Arizona') { $qry += " DENY INSERT, UPDATE, DELETE ON dbo.Address TO [$principal] ; DENY INSERT, UPDATE, DELETE ON dbo.Telecom TO [$principal] ; DENY INSERT, UPDATE, DELETE ON dbo.Address_key TO [$principal] ; DENY INSERT, UPDATE, DELETE ON dbo.Address_criteria TO [$principal] ; DENY INSERT, UPDATE, DELETE ON dbo.Customer TO [$principal] ; DENY INSERT, UPDATE, DELETE ON dbo.Supplier TO [$principal] ; " } $qry += "'" Write-Debug $qry Invoke-Sqlcmd -ServerInstance $SQLInstance -Query $qry } ## RO dbs @('DBA','OneShareArchive','VisiAudit' ) | ForEach-Object{ Write-Verbose $($SQLInstance +'.' + $_ ) $qry = " IF EXISTS ( SELECT * FROM sys.databases AS d WHERE d.name = '$_' ) EXEC sys.sp_executeSQL N' USE [$_]; EXEC dbo.sp_Visi_create_user @user = ''$principal'',@dbRoleList = ''db_datareader''; GRANT EXECUTE ON database::[$_] TO [$principal] ; " ## create table ok in DBA if ($_ -eq 'DBA') { $qry += " GRANT CREATE TABLE ON DATABASE::DBA TO [$principal] AS dbo; GRANT ALTER ON DATABASE::DBA TO [$principal] AS dbo;" } ## insert in VisiAudit if ($_ -eq 'VisiAudit') { $qry += " GRANT INSERT ON DATABASE::[VisiAudit] TO [$principal] " } $qry += "'" Write-Debug $qry Invoke-Sqlcmd -ServerInstance $SQLInstance -Query $qry } ## open bar DBs @('ArizonaLD') | ForEach-Object{ Write-Debug $($SQLInstance +'.' + $_ ) $qry = " IF EXISTS ( SELECT * FROM sys.databases AS d WHERE d.name = '$_' ) EXEC sys.sp_executeSQL N' USE [$_]; EXEC dbo.sp_Visi_create_user @user = ''$principal'',@dbRoleList = ''db_datareader,db_dataWriter,db_DDLAdmin''; GRANT EXECUTE ON database::[$_] TO [$principal] ; ' " Write-Debug $qry Invoke-Sqlcmd -ServerInstance $SQLInstance -Query $qry } } } Set-Alias -Name Grant-SQLDev -Value Grant-DevSQL |