Automation easy list management database 1C

Perhaps you, like me, often have to deal with it systems companies, which to you is not clear by whom and how served. Before setting out to put the company on the service it is necessary to make it Audit and allow all systems in order.

Often one of the systems where the order is missing, is a system of 1S. For example, you can find a database with the name of the folder "new_copybase1_old" or "База1КомпанияЗП", or even nested in other folders.

Who and what has to data bases access to identify at times extremely difficult. The database lists the client computers in the application of 1S, too, can be called unclear. Overall, not a very happy picture.

If you have the time to bring all databases to a single standard and to automate the connection users, please a cat.

To bring all databases to a common standard, to restore order — it's all good! But how to do it most quickly and efficiently?

Find ready-made solutions

The searches showed that the best articles on this subject is on "habré":

1. ;
2. "How to cook hundreds of databases 1C and not go crazy";
3. "Managing database list 1C 8.2 with Active Directory";
4. "Easy list management database 1C".

"Easy list management database 1C" user Sergey-S-Kovalev seemed the most useful, it starts with the famous quotes:

"Better day to lose, and then five minutes to fly" (with) m/f Wings, legs and tails.

But not so fast and simple.

So, adjusting for the article the user Sergey-S-Kovalev is to perform the steps, which are divided into 6 stages.

Phase 1 — Inventory;
Stage 2 — Group AD-base 1S;
Step 3 — File of 1C: enterprise configurations;
Step 4 — a File or a DFS share.
Stage 5 — Group policy;
Step 6 — The User.

The steps in stages 1, 5 are implemented once for each of the companies, they have no routine, you can easily make hands.

Configuration scheme, which offers Sergey-S-Kovalev gorgeous! No scripts, all is simple and clear. But you have to lose not a day, and a lot more if you need to configure this scheme of work, for example, 10 companies, which together may be about 200 1C bases.

That is the initial setup in a large company can be a very long and tedious.

I propose to automate some of the steps and to reduce the number of steps.

to better understand what I mean, I recommend you first see "Easy list management database 1C".

Phase 1 — Inventory

1. Create a shared accounting online Excel file, as shown in the figure.

Make sure in this file the names of the old databases and folders, to make it clear where to copy the database. OldBaseName,OldFolderName. The file can take here.

2. Together with the most chief accountant of the company fill in the fields:

BaseName, FolderName, GroupName, AccessUser(1-7)

the

the Column BaseName is filled in manually in the Russian language according to the standard, for example, SP-Ivanovo.
the Column FolderName is filled manually into the Latin alphabet according to the standard, in our case IP IVANOV_BUH.
the name of the AD group already generated on the base of the column FolderName, in our case GRRS_IP-IVANOV_BUH, I think, you know, like in Excel, you can Supplement the contents of the cells on the basis of information from other cells.
to Assign accesses to databases in the fields AccessUser you can ask, the chief accountant, but it is also possible to facilitate the work. It is necessary to take the list of accounting uchetok from AD, for example, with the command
Get-Aduser -searchbase "OU=Accountants, DC=domain, DC=ru" -filter * -Properties SamAccountname | FT SamAccountname
and place this list on the second sheet of our file.
Next to the fields AccessUser need to reference this list. If you do not know how, ask Google something like this "How to add a drop-down list to an Excel cell", or just use my file.

In the end, the most important accountant just put down accesses for each database, selecting users from the list. The list is pre-arrange by means of Excel, for example, the most frequent account be placed at the beginning.

3. After filling this file, you must save and copy to the directory in which to run the script.

From file Sergey-S-Kovalev, it differs in that it is immediately affixed accesses for users, there is no description, name servers, and clusters. In my environment this information superfluous and I did not include in the file.

Step 2 — Run the script

Perhaps you already understand that the file that was the result of the first phase, we feed the script. The script will perform all of the actions that Sergey-S-Kovalev describe the stages 2,3,4,6.

What makes a script

1.Create a group in AD for each database.
2.Will add in the description field of each group the path to the folder with the database.
3.In the notes field of each group, add the database name and the path to the folder with the database.
4.Add to the access group users according to the list.
5.Will generate a file 1CEStart.cfg with a list of all databases and place this file in a network folder with configuration files 1S.
6.Generates v8i files for each database and place the data files in the network folder with configuration files 1S.
7.Prescribe for each v8i-file access lists the corresponding group.
8.Create a folder for the database and will register in the list access to the folder corresponding group.

In General the script will do almost everything that Sergey-S-Kovalev in his article was done by hand.

Before running the script you need the following.

(automation of these actions is only in the plans)

Create AD groups

1. To create an OU in the domain storage groups, access to databases 1C. In my example this would be $OU = “OU=1C,OU=Resources,DC=domain,DC=ru”
2. To create this OU group GRRS_1C_ConfigBasesRO. To add to the group GRRS_1C_ConfigBasesRO group “Domain computers”
3. To create this OU group GRRS_1CBases.
4. To create this OU group GRUS_1Cadmins and add the admins 1C (optional)

Create folder

1. To create on the server 1C folder for storing databases. In our case it will be 1cshare
2. To the folder 1cshare need to give access to the group GRRS_1CBases write permissions on the Share tab.
3. On the tab Security remove group Users and set read permissions for the group GRRS_1CBases.
4. To create a domain DFS shared folder or just a folder, e.g. \\domain.EN\DfsShare\1cconfig\
5. In this folder will be a configuration file 1C.
6. To the folder \\domain.EN\DfsShare\1cconfig\ give access to the group GRRS_1C_ConfigBasesRO read-only.

you want the user to run the script, had the right to write to the folder with the database and to the folder \\domain.EN\DfsShare\1cconfig\.

Requirements to run the script

The script is implemented reading an Excel file using the Microsoft Access Database Engine 2010 Redistributable
It is better to install this product on the server that will run the script.

Engine download here. It is important that the bitness of the operating system coincides with the bit width of the engine.

Attention! If you run the script from your desktop client computer, the bitness of Microsoft Office must also match the bitness of the engine.

Recommend to install the engine on the server and not have to suffer with reinstallation of Microsoft Office on your computer. The computer to run the script should be installed powershell version 4.0 and snap the AD PowerShell modules.
you must Also register the script the following variables.

#1C Name of the server where the database
 
$1CServer = "nn-1cserver"
 
#The name of the shared folder, where it will lie 1C database.
 
$ShareName = "1cshare"
 
#OU in Active Directory, where will be access group 1c
 
$OU = “OU=1C, OU=Resources, DC=domain, DC=ru”
 
#The name of the DFS shared folder, where will be config files 1C
 
$1CConfigFolder = "\\domain.EN\DfsShare\1cconfig\"
 
#File with list of databases and users should be in the directory of the script
 
$datafile = "BasesBuh.xlsx"
 
#The name of the sheet in the file with the list of databases and users
 
$strSheetName = 'Sheet1$'

note: Why I implemented reading an Excel and not csv? Don't like to work with CSV, as there need to respect the encoding for Cyrillic characters, also the CSV is not convenient to edit. If you have the opportunity to read the Excel file, it can be right to shove any script without preparation of its format.

Step 3 — Create policies for users

Sergey-S-Kovalev describe very well the creation of policies for the operation of this circuit.

1.Realize it article copy the configuration file 1CEStart.cfg from a network share to a folder on the computer, %ProgramData%\1C\1CEStart\ Create this policy for computers.

2.Further, in politics there should be a clean file %Appdata%\1C\1CEStart\ibases.v8i for each user. New content will be generated when you first start 1C from file 1CEStart.cfg.

Step 4 — Copy the old database in a new folder

After you run the script, and he created all the group folders and files you need to copy the old database to the new location, that is, to distribute them in new folders. It will help columns OldFolderName,FolderName.

Script here

########################################################### 
 
# AUTHOR : K. Rinat Nugaev - http://www.nugaev.net - rinat@nugaev.net
 
# DATE : 07-02-2016 
 
# EDIT : 07-03-2016 
 
# COMMENT : This script creates folders, groups, and other 
 
# stuff for 1C envinronment.
 
# Use it for your own risk! 
 
# VERSION : 1.2 
 
########################################################### 
 

 
# CHANGELOG 
 
# Version 1.2: 07-03-2016 - Changed the code 
 
# - Added checking DataFilePath
 
# - Added AD modules installing
 
# - Changed users s creating array
 
# - Added Russian comments
 

 
#Check for PowerShell AD modules
 
Try 
 
{ 
 
Import-Module ActiveDirectory -ErrorAction Stop 
 
} 
 
Catch 
 
{ 
 
Write-Host "[ERROR]`t ActiveDirectory Module for Powershell is not installed! Please install the modules!" 
 
Write-Host "[ERROR]`t For Windows 2008/2008R2 run Import-Module ServerManager"
 
Write-Host "[ERROR]`t Add-WindowsFeature RSAT-AD-PowerShell"
 
Write-Host "[ERROR]`t For Windows 2012/2012R2 run the Add-WindowsFeature RSAT-AD-PowerShell"
 
Exit 1
 
}
 

 
#---------------------------------------------------------------------------------------
 
# CONSTANT VARIABLES - change under your environment
 
#---------------------------------------------------------------------------------------
 
#The file name database
 
$dataFile = "BasesBuh.xlsx"
 
#1s the name of the server where the database
 
$1Cserver = "nn-1cserver"
 
#The name of the folder on 1C server with the General access which will lie 1C database.
 
$ShareName = "1cshare"
 
#OU in Active Directory, where will be access group 1c
 
$OU = “OU=1C,OU=Resources,DC=domain,DC=ru”
 
#The name of the DFS shared folder, where it will lie configs 1C
 
#You want the user to run the script have write permissions in this folder.
 
$1CConfigFolder = "\\domain.EN\DfsShare\1cconfig\"
 
#File with list of databases and users
 
#The name of the sheet in the file with the list of databases and users
 
$strSheetName = 'Sheet1$' # or Sheet1$
 

 
#---------------------------------------------------------- 
 
#CONSTANT VARIABLES
 
#----------------------------------------------------------
 
$dataFilePath = $localPath + "\$dataFile" 
 
$localPath = $PSScriptRoot 
 
#The name of the configuration file 1C
 
$1CBasesCFG = $1CConfigFolder + “1CEStart.cfg”
 
#The group name with the access to the configuration
 
#This create a group and specify it in the ACL to the folder with the configs.
 
$GRRS_1C_ConfigBasesRO = "GRRS_1C_ConfigBasesRO"
 
#The name of the group to ensure common access to the root public folder database
 
#This create a group and specify it in the ACL to the folder with the database.
 
$GRRS_1CBases = "GRRS_1CBases"
 
#The name of the administrators group 1C
 
$GR_1CAdmins = "GRUS_1Cadmins"
 

 
#Check sure you have the database file in the directory of the script
 
If (!(Test-Path -Path $dataFilePath -PathType Any))
 
{
 
Write-Host "[ERROR]`t a database File $dataFile does not exist! Please copy $dataFile in the directory of the script!" -ForegroundColor Red
 

}
 

 
# Read Excel file and save it as a temporary table SQLDB
 
# To do this, the computer should be installed Microsoft Access Database Engine 2010 Redistributable
 
# You can download it here www.microsoft.com/en-us/download/details.aspx?id=13255
 
# It is necessary that the bit width of the system coincides with the bit width of the engine. If you installed the Office
 
# The bitness of office should also be the same. It is better to put on a server where there is no office.
 
$strProvider = "Provider=microsoft.ace.oledb.12.0"
 
$strDataSource = "Data Source = $dataFilePath"
 
$strExtend = "Extended Properties=Excel 8.0"
 
$strQuery = "Select * from [$strSheetName]"
 
$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")
 
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)
 
$sqlCommand.Connection = $objConn
 
$objConn.open()
 
$DataReader = $sqlCommand.ExecuteReader()
 

 
#Create an array to store the users
 
$AccessArr = New-Object System.Collections.ArrayList
 

 
#Read our table and store the cell values into variables
 
While($DataReader.Read()) 
 
{ 
 
#Data immediately removed the extra spaces (.replace(' ',")), if someone is introduced when filling the table.
 
$BaseName = $DataReader[2].Tostring().replace(' ',")
 
$FolderName = $DataReader[3].Tostring().replace(' ',")
 
$GroupName = $DataReader[4].Tostring().replace(' ',")
 
#Immediately add the user to the array $AccessArr
 
[void] $AccessArr.Add($DataReader[5].Tostring().replace(' ',"))
 
[void] $AccessArr.Add($DataReader[6].Tostring().replace(' ',"))
 
[void] $AccessArr.Add($DataReader[7].Tostring().replace(' ',"))
 
[void] $AccessArr.Add($DataReader[8].Tostring().replace(' ',"))
 
[void] $AccessArr.Add($DataReader[8].Tostring().replace(' ',"))
 
[void] $AccessArr.Add($DataReader[10].Tostring().replace(' ',"))
 
[void] $AccessArr.Add($DataReader[11].Tostring().replace(' ',"))
 

 
#Dynamic variables. Variables do not touch, everything works automatically.
 
#Full path to the database. Needed to create folders for the database.
 
$FullPathBase = "\" + "\" + $1Cserver + "\" + $Sharename + "\" + $FolderName 
 

 
#Create comments to add to the description of the AD groups
 
$CommentBase = “Base” + “ ” + $BaseName + “ ”
 
$CommentPath = $FullPathBase
 
$Comment = $CommentBase + $CommentPath
 

 
#Create the name of the config file, for each database
 
$ConfigBaseFile = $1cConfigFolder + $FolderName + “.v8i”
 

 
#Create the content for each file v8i
 
#We have a file of 1s, so Connect=File
 
$ConfigBaseFileContent ="[$BaseName]
 
Connect=File=$FullPathBase
 
ClientConnectionSpeed=Normal
 
App=Auto
 
WA=1
 
Version=8.3
 
"
 
#Create v8i-file and file 1CEStart.cfg
 
#Create himself v8i-file for each database
 
$ConfigBaseFileContent | Set-Content $ConfigBaseFile -Encoding UTF8
 
#Create a string with the path to v8i-file for file 1CEStart.cfg
 
$1CBasesCFGContent = "CommonInfoBases=$ConfigBaseFile"
 
#Add the path of each v8i file in the file 1CEStart.cfg
 
$1CBasesCFGContent | Add-Content $1CBasesCFG -Encoding UTF8
 

 
#Create a group for each database that you add to each group the description and the path to the database.
 
New-ADGroup -GroupScope DomainLocal -GroupCategory Security `
 
-name $GroupName -Path $OU -Description $CommentPath -OtherAttributes @{info="$Comment"}
 
#Add this group to the group access to the folder with the configs
 
Add-ADGroupMember -Identity $GRRS_1C_ConfigBasesRO $GroupName
 
#Add this group to the group access to the folder with the database
 
Add-ADGroupMember -Identity $GRRS_1CBases $GroupName 
 

 
#Looping through the users array and add each to the group access to the database.
 
foreach ($i in $AccessArr)
 
{
 
if ($i)
 
{
 
Add-ADGroupMember -Identity $GroupName $i
 
}
 
}
 

 
#Create a folder with the database
 
New-item-Path $FullPathBase -ItemType directory
 
#Create and apply the ACLs to the folder with the database
 
$acl = Get-Acl $FullPathBase
 
$GroupOwner = New-Object System.Security.Principal.NTAccount("Builtin", "Administrators")
 
$acl.SetOwner($GroupOwner)
 
$rule = New-Object System.Security.AccessControl.FileSystemAccessRule(“Administrators”,”Modify,FullControl, Synchronize”, “ContainerInherit, ObjectInherit”, “None”, “Allow”)
 
$acl.AddAccessRule($rule)
 
$rule = New-Object System.Security.AccessControl.FileSystemAccessRule(“Domain admins”,”Modify,FullControl, Synchronize”, “ContainerInherit, ObjectInherit”, “None”, “Allow”)
 
$acl.AddAccessRule($rule)
 
$rule = New-Object System.Security.AccessControl.FileSystemAccessRule(“$GroupName”,”Modify, Synchronize”, “ContainerInherit, ObjectInherit”, “None”, “Allow”)
 
$acl.AddAccessRule($rule)
 
$rule = New-Object System.Security.AccessControl.FileSystemAccessRule(“$GR_1CAdmins”,”Modify, Synchronize”, “ContainerInherit, ObjectInherit”, “None”, “Allow”)
 
$acl.AddAccessRule($rule)
 
$acl.SetAccessRuleProtection($True, $False)
 
Set-Acl $FullPathBase $acl
 

 
#Create and apply access lists to v8i-file
 
$aclfl = Get-Acl $ConfigBaseFile
 
$GroupOwner = New-Object System.Security.Principal.NTAccount("Builtin", "Administrators")
 
$aclfl.SetOwner($GroupOwner)
 
$rulefl = New-Object System.Security.AccessControl.FileSystemAccessRule(“Administrators”,”Modify,FullControl, Synchronize”, “Allow”)
 
$aclfl.AddAccessRule($rulefl)
 
$rulefl = New-Object System.Security.AccessControl.FileSystemAccessRule(“Domain admins”,”Modify,FullControl, Synchronize”, “Allow”)
 
$aclfl.AddAccessRule($rulefl)
 

$aclfl.AddAccessRule($rulefl)
 
$rulefl = New-Object System.Security.AccessControl.FileSystemAccessRule(“$GR_1CAdmins”,”ReadAndExecute, Synchronize”, “Allow”)
 
$aclfl.AddAccessRule($rulefl)
 
Set-Acl $ConfigBaseFile $aclfl
 

 
#Create and apply access lists to 1CEStart.cfg
 
$aclcf = Get-Acl $1CBasesCFG
 
$GroupOwner = New-Object System.Security.Principal.NTAccount("Builtin", "Administrators")
 
$aclcf.SetOwner($GroupOwner)
 
$rulecf = New-Object System.Security.AccessControl.FileSystemAccessRule(“Administrators”,”Modify,FullControl, Synchronize”, “Allow”)
 
$aclcf.AddAccessRule($rulecf)
 
$rulecf = New-Object System.Security.AccessControl.FileSystemAccessRule(“Domain admins”,”Modify,FullControl, Synchronize”, “Allow”)
 
$aclcf.AddAccessRule($rulecf)
 
$rulecf = New-Object System.Security.AccessControl.FileSystemAccessRule(“$GRRS_1C_ConfigBasesRO”,”ReadAndExecute, Synchronize”, “Allow”)
 
$aclcf.AddAccessRule($rulecf)
 
$rulecf = New-Object System.Security.AccessControl.FileSystemAccessRule(“$GR_1CAdmins”,”ReadAndExecute, Synchronize”, “Allow”)
 
$aclcf.AddAccessRule($rulecf)
 
Set-Acl $1CBasesCFG $aclcf
 
}
 
#Close the file with the list of databases and users
 
$dataReader.close()
 
$objConn.close()

Possible problems

Do not understand the reasons why, but I somehow, some folders with the bases not including the option "replace all child object permissions with inheritable permissions from this object", although in the script all written. And so after you copy the databases to a new location, check to see whether the follow rights on the copied files from the parent folder.

Opinion

Colleagues, the script is pretty well documented, but written on the knee, about an hour. All responsibility for its use rests only on the UFO.

I (think) know how to write functions in PowerShell, query, and validation options, implement logging, writing help for a script, and so on, but I think for this script all of this is unnecessary. Although some checks, for example, directories with configs or shared folders can be done. But I think not this time, not a level problem.

#ChangeLog

Corrected the inaccuracies pointed out by Sergey-S-Kovalev;
Changed the way users are added to the array, n1nj4p0w3r;
Added unit testing Active Directory;
Added check for data file in the directory.

Thank you!
All the best!

Article based on information from habrahabr.ru

Поиск по этому блогу

computer express