Creating a Sybase Database

1. Use the Sybase DbInit utility or Sybase Central to create a blank destination database.
2. Use Sybase Central to create two SQL users: "afm" and "afm_secure".
3. All tables in Sybase are owned by user "afm". Log in as user AFM and use the Database Update Wizard log to create the afm_users and afm_groups tables (as well as all the other Archibus tables).
4. Use the Schema Change Wizard to create foreign keys on the afm_tbls, afm_flds, and afm_groups table. The foreign keys from these tables are not created when the wizard creates the tables from scratch.

The Database Update Wizard will set up all necessary security permissions. It will grant permissions on afm_users and afm_groups to user "afm_secure" so that Archibus will be able to log into the password security account and read the user information. It will also grant update permissions to afm_users to be able write any login access failures for a user account.

Note: If you are creating a database to receive data from an existing Sybase database that has trailing blanks, you should use the -b (blank padding) switch. Sybase’s blank padding switch instructs the program to ignore trailing blanks for comparison purposes and to pad strings that are fetched into character arrays. For example, the two strings 'Smith' and 'Smith ' would be treated as equal in a database created with blank-padding.

Creating a Microsoft SQL Server Database

1. Create two SQL login IDs, "afm" and "afm_secure", in the master database.
2. Copy the blank Model.Mdf database that comes with SQL Server to a new file with the desired name.
3. Attach this new file to the database.
4. Create two SQL user ID's in this new database, "afm" and "afm_secure". Associate the login ID's to these users.
5. All tables in MS SQL are also owned by user "afm". Same as in Sybase, have the Database Update Wizard log in as user AFM, and create the afm_users and afm_groups tables (as well as all the other Archibus tables).
6. Use the Schema Change Wizard to create foreign keys on the afm_tbls, afm_flds, and afm_groups tables. The foreign keys from these tables are not created when the wizard creates the tables from scratch.

The Database Update Wizard will set up all necessary security permissions. It will grant reference and select permissions on afm_users and afm_groups to user "afm_secure".

Creating an Oracle Database

To create a database, follow these steps.

1. Edit the Afmoscm_perm7/8.sql file to reflect your tablespace location and size, and your system manager account.
2. Run this .Sql file in SQL Worksheet to create the tablespace, the afm and afm_secure users, the afm_role, and the public synonyms for the afm_groups and afm_users tables.
3. Have the Database Update Wizard log in as "afm" and create all tables, including afm_users and afm_groups, and all fkey references between them, and the other Archibus tables (e.g. afm_mods which validates on afm_groups).
4. Use the Schema Change Wizard to check foreign keys on the afm_tbls, afm_flds, and afm_groups table.

Altering an Oracle Database for Archibus Security

If you wish to use Archibus security, remember that the Archibus program will first log in as afm_secure to read the afm_users table. It will access this table via the "afm_secure" public synonym:

Afmoscm_perm7/8.sql

Change this synonym to refer to the afm_users table owned by user "afm":

CREATE PUBLIC SYNONYM AFM_USERS FOR AFM.AFM_USERS;

Changing the Ownership of the Archibus Security Tables in an Oracle Database

If you wish to use Archibus security, and would like to use the same security file in multiple tablespaces, you should change the of afm_users and afm_groups tables to be owned by afm_secure, just as they are in the AfmoScm7/8.Dmp files. To do so, follow these steps.

1. Export your database to a MyProject.Dmp file.
2. In a new tablespace, import the afm_users and afm_groups table into this new tablespace changing their owner to afm_secure:

imp system/manager fromuser=afm touser=afm_secure file=MyProject.Dmp log=MyProject.log tables=(afm_users, afm_groups)

3. Log in as afm_secure/afm, and create synonyms for these tables and grant user "afm" references:

CREATE PUBLIC SYNONYM AFM_GROUPS FOR AFM_SECURE.AFM_GROUPS;
CREATE PUBLIC SYNONYM AFM_USERS FOR AFM_SECURE.AFM_USERS;
GRANT REFERENCES ON AFM_GROUPS TO AFM;
GRANT REFERENCES ON AFM_USERS TO AFM;

4. Import the other tables. The afm_users and afm_groups will not import, as they are already in the tablespace.

imp system/manager fromuser=afm touser=afm file=MyProject.Dmp log=MyProject.log

Using Multiple SQL IDs in an Oracle Database

As discussed in the Archibus for Oracle document, if you are using multiple tablespaces, you will be logging into the database with a different SQL user ID than "afm", and this User ID will have a different default tablespace.

This user ID will need a set of synonyms for accessing the Archibus tables. To create this list of synonyms.

1. Edit the afmohcn_synonyms.sql script file changing "HCN" to the name of your user. Run this .Sql script file in SQL Worksheet.
2. Create the AFM_ROLE that you will use to grant initial permissions to each user you establish. You will revoke from this role all permissions that do not apply. Do so by creating an .Sql script and executing it in SQL Worksheet.

To create the widest permission necessary issue the following:

GRANT AFM_ROLE TO AFM_SECURE
GRANT AFM_ROLE TO AFM
GRANT AFM_ROLE TO "SYSTEM" WITH ADMIN OPTION

Then issue this for every table that you wish to grant permissions to:

GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON <table name> to "AFM_ROLE"

You can find the widest possible list of all tables to include in the role with the following query:

SELECT * FROM SYSTABLE where table_type = 'BASE' and creator = 'AFM'