Multiple SQL Users in a Single Tablespace

When you set up your first tablespace according to the default, you established the AFM_ROLE as part of the default AFM_P1 tablespace. This role grants SELECT, INSERT, UPDATE, and DELETE privileges to each table owned by AFM.

If all of your users log into the database as user AFM, you need not create any other users or assign them the AFM_ROLE.

If you do choose to create multiple user accounts, you can give them the privileges they need to work with the Archibus tables by assigning them the AFM_ROLE.

For example, if USER1 and USER2 are both assigned the AFM_ROLE as shown below, they can access and work with all of the tables owned by user AFM.

Grant "AFM_ROLE" To "USER1";
Grant "AFM_ROLE" To "USER2";

Multiple SQL Users in Multiple Tablespaces

When working with multiple tablespaces, each Oracle tablespace should have a unique Oracle role created for its owner’s tables. For instance, for a second tablespace, HCN_P1, with tables owned by user "HCN", you should establish a new role called "HCN_ROLE" in order to grant SELECT, INSERT, UPDATE and DELETE privileges to all of the tables owned by user "HCN".

If all of your users log into the database as user "HCN", you need not create any other users or assign them the HCN_ROLE.

If you do choose to create multiple user accounts, you can give them the privileges they need to work with the Archibus tables by assigning them the HCN_ROLE.

For example, if USER3 and USER4 are both assigned the HCN_ROLE as shown below, they can access and work with all of the tables owned by user AFM.

Grant "HCN_ROLE" To "USER3";
Grant "HCN_ROLE" To "USER4";

Creating a New Role

To create a new role, such as HCN_ROLE, use the \Afmxx\Schema\Oracle\AfmoHcn_Role.sql as a template, and follow these steps.

1. Edit the start of the \Afmxx\Schema\Oracle\Afmohcn_Role.sql script to reflect the user account.

CONNECT HCN/HCN
SPOOL HCN_ROLE.LST

2. Use the search and replace feature of your text editor to reflect the tablespace that owns the table.

GRANT ALTER, DELETE, INSERT, SELECT, UPDATE ON HCN.AC TO HCN_ROLE;

3. Use the following command line to execute the Synonyms.sql script file.

sqlplus @AfmoHcn_Role.Sql

The resulting script file should look similar to the following. Strings that you would alter appear in blue.

CONNECT HCN/HCN
SPOOL HCN_ROLE.LST
GRANT ALTER, DELETE, INSERT, SELECT, UPDATE ON HCN.AC TO
HCN_ROLE ;
GRANT ALTER, DELETE, INSERT, SELECT, UPDATE ON HCN.ACBU TO
HCN_ROLE ;
GRANT ALTER, DELETE, INSERT, SELECT, UPDATE ON
HCN.ACTIVITYTYPE TO HCN_ROLE ;
GRANT ALTER, DELETE, INSERT, SELECT, UPDATE ON
HCN.ACTIVITY_LOG
TO HCN_ROLE ;