Initialize the Oracle Database with Minimum Access Privilege - Intergraph Smart 3D - Installation

Intergraph Smart 3D Installation

PPMProduct
Intergraph Smart 3D
PPMCategory_custom
Installation & Upgrade
SPFVersion_custom
2019 (10.0)
Version_S3D
12.1(2019)

After the Oracle server software is installed and configured (that is, you can connect from a client to the Oracle database), you must configure the database to run with Smart 3D software.

The Smart 3D Reference Data Installation (see Reference Data Setup) delivers the following sample script files to the <Product Folder>\3DRefData\Tools\OracleScriptsToInitDB\MinimumAccessPrivileges folder.

  1. ..\MinimumAccessPrivileges\SP3DUser_ROLES_Def.SQL

  2. ..\MinimumAccessPrivileges\SP3DProjectAdminstrator_ROLES.SQL

  3. ..\MinimumAccessPrivileges\SP3DUser.SQL

  4. ..\MinimumAccessPrivileges\SP3DProjectAdministrator.SQL

  5. ..\MinimumAccessPrivileges\SP3DUser_ROLES_Access.SQL

These script files create the necessary schemas, users, and roles that are used to run the software on Oracle with minimum access privileges. The delivered script files, however, are only samples and must be edited to fit your server configurations.

  • An administrative user must run the scripts on the server. Before proceeding, place a copy of each of the sample script files on the server computer.

  • The scripts must be run in the order listed because previous scripts create items that are used in subsequent scripts. Do not deviate from the following sequence of steps.

  • Make a backup of the SP3DUser\SP3DAdministrator user and roles configuration if it already exists on the Oracle server.

Create the Roles Needed for Smart 3D Users and Administrators

  1. Log on to the Oracle database server computer using the local administrative account.

  2. Open SQL Plus.

    The SQL Plus dialog box displays.

  3. In the User Name field, type SYS as SYSDBA or SYSOPER.

  4. In the Password field, type the password for the SYS account.

  5. At the SQL prompt, type @<File Location>:\MinimumAccessPrivileges\SP3DUser_ROLES_Def.SQL, and press Enter.

    For example, if you placed a copy of the script file in the root folder on the C drive, type @C:\SP3DUSER_ROLES_Def.SQL.

  6. After the script finishes, type @<File Location>:\MinimumAccessPrivileges\SP3DProjectAdministrator_ROLES.SQL, and press Enter.

  7. After the second script finishes, click File > Exit.

Create the Users Needed for Smart 3D

  1. Log on to the Oracle database server computer using the local administrative account.

  2. Navigate to the SP3DUser.SQL script file, and open it in Notepad.

  3. If you are using OS authentication, edit the external user identified in the file as needed. Use the following syntax: DOMAIN NAME\USERNAME, and then click File > Save.

    User logins cannot contain spaces. Any typed alpha character must be capitalized.

  4. If you are using OS authentication, edit the external user identified in the file as needed. Use the following syntax: DOMAIN NAME\USERNAME, and then click File > Save.

    Any typed alpha character must be capitalized.

    -OR-

    If you are using database user authentication, edit the external user identified in the file and replace the database user name. Also, replace IDENTIFIED EXTERNALLY with IDENTIFIED BY [the user password].

    Example replace:

    CREATE USER "DOMAIN\USERNAME" PROFILE DEFAULT IDENTIFIED EXTERNALLY

    with:

    CREATE USER S3dStandardUser PROFILE DEFAULT IDENTIFIED BY S3duserPassword

    User logins cannot contain spaces. The database user name and password are case sensitive.

  5. Open SQL Plus.

    The Log On dialog box appears.

  6. In the User Name field, type SYS AS SYSDBA.

  7. In the Password field, type the password for the SYS account.

  8. At the SQL prompt, type @<File Location>:\SP3DUser.SQL, and press Enter.

    For example, if you placed a copy of the script file in the root folder on the C drive, type @C:\SP3DUser.SQL.

  9. After the script finishes, click File > Exit.

Create the Administrative User

  1. Log on to the Oracle database server computer using the local administrative account.

  2. Navigate to the SP3DProjectAdministrator.SQL script file, and open it in Notepad.

  3. If using OS authentication, edit the external user identified in the file as needed. Use the following syntax: DOMAIN NAME\USERNAME, and then click File > Save.

    Any typed alpha character must be capitalized.

    -OR-

    If using database user authentication, edit the external user identified in the file and replace the database user name. Also replace IDENTIFIED EXTERNALLY with IDENTIFIED BY [the user password].

    Example replace:

    CREATE USER "DOMAIN\USERNAME" PROFILE DEFAULT IDENTIFIED EXTERNALLY

    with:

    CREATE USER S3dAdminUser PROFILE DEFAULT IDENTIFIED BY S3dAdminUserPassword

    The database user name and password are case sensitive.

  4. Open SQL Plus.

    The Log On dialog box appears.

  5. In the User Name field, type SYS AS SYSDBA.

  6. In the Password field, type the password for the SYS account.

  7. At the SQL prompt, type @<File Location>:\SP3DProjectAdministrator.SQL and press Enter.

    For example, if you placed a copy of the script file in the root folder on the C drive, you type @C:\SP3DProjectAdministrator.SQL.

  8. After the script finishes, click File > Exit.

Grant Privileges to SP3D User Role

You should execute this process only if you have created the Smart 3D roles with minimum mandatory privileges using the scripts delivered in the OracleScriptsToInitDB\MinimumAccessPrivileges folder. The SP3DAdministrator should create all seven Smart 3D databases (Site, Site Schema, Catalog, Catalog Schema, Model, Report, and Report Schema) using the Smart 3D utilities.

  1. Log on to the Oracle database server computer using the local administrative account.

  2. Open SQL Plus.

    The Log On dialog box appears.

  3. In the User Name field, type SYS AS SYSDBA or SYSOPER.

  4. In the Password field, type the password for the SYS account.

  5. At the SQL prompt, type @<File Location>:\SP3DUser_ROLES_Access.SQL and press Enter.

    For example, if you placed a copy of the script file in the root folder on the C drive, you type @C:\SP3DUser_ROLES_Access.SQL.

  6. At the Enter value for database name: prompt from SQL, type the Site database name.

  7. Rerun the SP3DUser_ROLES_Access.SQL script six more times providing the Site Schema, Catalog, Catalog Schema, Model, Report, and Report Schema database names as you did for the Site database in step 6.

  • Any typed alpha character must be capitalized.

  • In the future, the Oracle administrator must run SP3DUser_ROLES_Access.SQL on all seven Smart 3D databases when there are any schema enhancements in databases. For example, whenever a view, procedure, new table, or any new object is added to the database, the Oracle administrator must grant permissions on each new object by running this script.

  • If you use minimum access privileges, there is an additional administrative burden on the Oracle database administrator to add permission on newly created database objects to the SP3D User role.