Manuals - 3Dfindit

4.2. Installation Link database

4.2.4. Create database scheme and users for ERP (exemplified by MSSQL)

4.2.4.1. Installation scripts

4.2.4.2. Explanation on the database tables

4.2.4.2.1. LOCKTABLE

In the following the creation of the database scheme and the user "erpuser" is explained:^[65]

Configuration of the SQL-Server Connection
Create the Database: Right-click ‘Databases’ -> ‘New Database’
Enter the desired database name in the New database dialog box that opens and click on " Add " and then on OK.
Select the database and click on New query.
Now you need a database script for the creation of the database structure (..\tools\databases\MSSQL\erp_install_mssql.sql).
Hereinafter this is shown as an example for MSSQL. Under $CADENAS/tools/databases, you will find Database scripts for other databases.
Copy the content into the empty query field using Copy&Paste and in addition, at the beginning, add the following:
```
USE [datenbankname]
GO
...
```
Click on Execute.

Now open another empty query and create the user "erpuser". You can use the script plinkdb_reuse.sql (at ..\tools\databases\MSSQL) as a template.

	Caution
	Adjust the database name!

USE [master]
GO
CREATE LOGIN [erpuser] WITH PASSWORD=N'erpuser', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
CREATE DATABASE [plinkdb] ON 
( FILENAME = N'<DBFILE>' ),
( FILENAME = N'<DBLOGFILE>' )
 FOR ATTACH
GO
if not exists (select name from master.sys.databases sd where name = N'plinkdb' and 
 SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) 
 EXEC [plinkdb].dbo.sp_changedbowner @loginame=N'sa', @map=false
GO
USE [plinkdb]
GO
DROP USER [erpuser]
GO
CREATE USER [erpuser] FOR LOGIN [erpuser] WITH DEFAULT_SCHEMA=[dbo]
GO

Click on Execute.

-> The database schema and the user "erpuser" should now be available.

Finally you have to allocate the desired rights for the user "erpuser": Right-click on „erpuser“ -> "Properties".
In the dialog box that opens, define the desired memberships for the relevant user and then confirm with OK.

Database creation scripts with new GRANT sections

As of V12.9, there are different scripts with different GRANT sections for "erpuser" and "psadmin". Until then, there was no distinction between standard and admin rights.

CREATE USER IF NOT EXISTS 'erpuser' IDENTIFIED WITH mysql_native_password BY 'erpuser';

GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.ERPTABLE to erpuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.LINKTABLE to erpuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.CLASSFOLDERTABLE to erpuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.CLASSTABLE to erpuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.PRJTABLE to erpuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.PLMTABLE2 to erpuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.PLM_HIERARCHY to erpuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.CNSSEQ to erpuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.TREETABLE to erpuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.LOGTABLE to erpuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.EXPORTTABLE to erpuser;

The admin must also be able to change column definitions in the ERPTABLE.

CREATE USER IF NOT EXISTS 'psadmin' IDENTIFIED WITH mysql_native_password BY 'psadmin';

GRANT ALTER, SELECT, INSERT, UPDATE, DELETE ON partlinkdb.ERPTABLE to psadmin;
GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.LINKTABLE to psadmin;
GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.CLASSFOLDERTABLE to psadmin;
GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.CLASSTABLE to psadmin;
GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.PRJTABLE to psadmin;
GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.PLMTABLE2 to psadmin;
GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.PLM_HIERARCHY to psadmin;
GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.CNSSEQ to psadmin;
GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.TREETABLE to psadmin;
GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.LOGTABLE to psadmin;
GRANT SELECT, INSERT, UPDATE, DELETE ON partlinkdb.EXPORTTABLE to psadmin;

The connection can be checked in PARTadmin under Database connection.

^[65]Here exemplified by MSSQL Use the provided script for your database type (see Section 4.2.4.1, “Installation scripts ”) and adjust it accordingly.

Prev	Up	Next
4.2.3. Configuration of the database server connection (exemplified by MSSQL)	Home	4.2.4.1. Installation scripts