Establish the Tablespace, User, and Role (Oracle)

The AfmoHq_Perm9/11.sql or AfmoScm_Perm9/11.sql scripts establish the first tablespace named AFM_P1 with the users AFM and AFM_SECURE.

To load a different project named "HCN" in its own tablespace, you would use a similar script, but you would change the tablespace name from "AFM_P1" to "HCN_P1" and the user name from AFM to "HCN." This is shown in the script below, in which these changes are in blue. This example file is provided as AfmoHcn_Perm9.sql or AfmoHcn_Perm8.sql.

CONNECT SYSTEM/MANAGER
SPOOL HCNPERM.LST

Create tablespace HCN_P1. Be sure to modify the data file path and tablespace parameters as required .

CREATE TABLESPACE " HCN_P1 "
DATAFILE "C:\Orant\Dbs\HCN_P1.Ora"
SIZE 40M REUSE DEFAULT STORAGE (INITIAL 10240 NEXT 10240
MINEXTENTS 1 MAXEXTENTS 256 PCTINCREASE 0) ONLINE;

Create SQL user HCN with default tablespace HCN_P1

CREATE USER " HCN " IDENTIFIED BY " HCN " DEFAULT TABLESPACE " HCN_P1 "
TEMPORARY TABLESPACE "TEMPORARY_DATA"

Create role HCN_ROLE and grant user privileges.

CREATE ROLE "HCN_ROLE"
REVOKE "HCN_ROLE" FROM SYSTEM;
GRANT CONNECT, RESOURCE, CREATE PUBLIC SYNONYM TO HCN
GRANT "HCN_ROLE" TO "SYSTEM WITH ADMIN OPTION;
GRANT "HCN_ROLE" TO "HCN"

Grant references on AFM_SECURE’s security tables to HCN.

CONNECT AFM_SECURE/AFM
GRANT REFERENCES ON AFM_GROUPS TO HCN;
GRANT REFERENCES ON AFM_USERS TO HCN ;

SPOOL OFF

Note : This example defines one set of security tables and user privileges for multiple tablespaces. The scenario creates one AFM_SECURE user, with associated information and privileges, to reside in the first tablespace, AFM_P1. You can, however, configure multiple security logins - one for each project - if desired.