(Show Contents)

Creating SQL User IDs (Oracle)

Many sites wish to take advantage of the rich set of SQL security features that Oracle offers. To do so, you will wish to create different accounts for each department or for each user that will be accessing the SQL database.

New Database Users and Roles

The following SQL Script defines one way of creating a new SQL User ID. Replace the "NEWUSER" name with the name of the user account you wish to create.

CONNECT SYSTEM/MANAGER@HQ_ORCL;
CREATE USER "NEWUSER" IDENTIFIED BY "NEWUSER"
DEFAULT TABLESPACE "AFM_P1" TEMPORARY TABLESPACE "TEMPORARY_DATA";
GRANT CONNECT TO "NEWUSER";
GRANT RESOURCE TO "NEWUSER";
GRANT "AFM_ROLE" TO "NEWUSER"
CONNECT AFM_SECURE/AFM@HQ_ORCL
GRANT REFERENCES ON AFM_GROUPS TO "NEWUSER"
GRANT REFERENCES ON AFM_USERS TO "NEWUSER"

Notice that the default tablespace for this new user is "AFM_P1." If you have multiple tablespaces loaded on your database server, this is the way that the user will log into the appropriate tablespace based on their username.

Notice also that the script grants the AFM_ROLE to the new user. This grants the access to the ARCHIBUS tables belonging to user AFM so that this user can access all of these tables. You will want to revoke from this user rights to those tables that the user should not modify.

If you have a different role for your tablespace, for instance the HCN_ROLE established for the HCN_P1 tablespace as shown above, you will wish to grant that role to the new user instead. Here is a sample script, with the changes in blue:

CONNECT SYSTEM/MANAGER@HQ_ORCL;
CREATE USER "NEWUSER" IDENTIFIED BY "NEWUSER"
DEFAULT TABLESPACE "HCN_P1" TEMPORARY TABLESPACE
TEMPORARY_DATA;
GRANT CONNECT TO "NEWUSER";
GRANT RESOURCE TO "NEWUSER";
GRANT "HCN_ROLE" TO "NEWUSER";
CONNECT AFM_SECURE/AFM@HQ_ORCL
GRANT REFERENCES ON AFM_GROUPS TO "NEWUSER";
GRANT REFERENCES ON AFM_USERS TO "NEWUSER";

Mapping ARCHIBUS Roles to Database Users

If you controlling access to the database on a fine-grained basis, or if you are logging changes from certain groups of users, you will want to map ARCHIBUS roles to your database accounts.

ARCHIBUS establishes three connection pools:

• security – which controls access to the tables holding users, roles and security assignments
• schema – which is the connection used to read the tables holding the table and field data dictionary
• data – which controls users’ access to data.

For each project, you can control which database account each of these connection pools connects to by editing the connection data in the webapps\archibus\web-inf\config\afm-projects.xml file.

ARCHIBUS pools connections between multiple users for performance reasons. However, you might need more fine-grained control between groups of users. For instance, enterprise users might have lower rights than power users. For this purpose, you can map users to database accounts using the SQL User (sql_uid) and SQL Password (sql_pwd) fields in the ARCHIBUS Roles table. When these are present, ARCHIBUS all users of that role will log into the database with the specified database account information. This lets you give different access rights or log access from different sets of users.

The SQL User and SQL Password settings only apply to changes made through the “data” role.

Other Considerations

• Securing Workflow Rules. If you are locking down access, do remember to update the ARCHIBUS Workflow Rules security on the AbSystemAdministration workflow rules, as the rules for password management – for instance those that to change the password, to change SQL Security, etc. – use the secure role. Grant permission only to those with rights to make these changes.
• Startup Logic. There are two start up cases in which the ARCHIBUS product reads the security and schema roles:
  • When opening a project.
  • When creating a single sign-on user.
• Document Management. The workflow rules that upload and download document management do not use the SQL User and Password for the user’s role.
• Add-in Managers. When editing schema tables such as the ARCHIBUS Tables and Fields tables, add-in managers do so using the “data” role and their SQL Username and Password.

For more information on setting up audit logs please see Working with Audit Logs (Oracle).