Create Oracle Admin Users with Limited Privileges - Intergraph Smart Engineering Manager - 11 - Installation & Upgrade - Hexagon

When working on an Oracle platform, you can create a database user with limited privileges which allow that user to perform Smart Engineering Manager procedures such as creating sites and plants, associating applications, and backing up and restoring sites and plants.

Two types of users can be created:

• USER_ADMIN: This user can execute any command in Smart Engineering Manager except for backup and restore.

• BACKUP_RESTORE: This user can run the backup and restore procedures in addition to all the procedures that USER_ADMIN can run.

• You can also create a role that provides backup and restore privileges and assign that role as needed to an existing USER_ADMIN.

• You need to know the Oracle instance's system username and password to be able to create these users or roles.

• To check the privileges of an existing user, run a command based on the syntax: SELECT GRANTEE,GRANTED_ROLE from dba_role_privs WHERE GRANTEE like upper('<user name>');

  For example, if the system user has the name IN_DBAMN, the required script is:

  SELECT GRANTEE,GRANTED_ROLE from dba_role_privs WHERE GRANTEE like upper('IN_DBAMN');

Create an Admin User (USER_ADMIN)

The following script can be run to create a user with a minimum set of privileges covering Smart Engineering Manager procedures such as creating sites and plants and associating applications.

This user will not be able to run the backup or restore procedures unless special system privileges are granted directly to the user or the user is assigned a role with such privileges.

CREATE USER USER_ADMIN

IDENTIFIED BY USER_ADMIN

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK;

------- 2 Roles for USER_ADMIN

GRANT SELECT_CATALOG_ROLE TO USER_ADMIN WITH ADMIN OPTION;

GRANT EXECUTE_CATALOG_ROLE TO USER_ADMIN WITH ADMIN OPTION;

ALTER USER USER_ADMIN DEFAULT ROLE ALL;

-------- 14 System Privileges for USER_ADMIN

GRANT CREATE TRIGGER TO USER_ADMIN WITH ADMIN OPTION;

GRANT CREATE DATABASE LINK TO USER_ADMIN WITH ADMIN OPTION;

GRANT DROP USER TO USER_ADMIN WITH ADMIN OPTION;

GRANT CREATE SEQUENCE TO USER_ADMIN WITH ADMIN OPTION;

GRANT CREATE CLUSTER TO USER_ADMIN WITH ADMIN OPTION;

GRANT ALTER SESSION TO USER_ADMIN WITH ADMIN OPTION;

GRANT CREATE PROCEDURE TO USER_ADMIN WITH ADMIN OPTION;

GRANT CREATE VIEW TO USER_ADMIN WITH ADMIN OPTION;

GRANT CREATE USER TO USER_ADMIN;

GRANT CREATE SESSION TO USER_ADMIN WITH ADMIN OPTION;

GRANT CREATE SYNONYM TO USER_ADMIN WITH ADMIN OPTION;

GRANT ALTER USER TO USER_ADMIN WITH ADMIN OPTION;

GRANT CREATE TABLE TO USER_ADMIN WITH ADMIN OPTION;

GRANT ALTER SYSTEM TO USER_ADMIN WITH ADMIN OPTION;

ALTER USER USER_ADMIN QUOTA UNLIMITED ON USERS;

Create a User with Backup and Restore Privileges (BACKUP_RESTORE)

The following script can be run to create a database user who, in addition to being able to perform basic Smart Engineering Manager activities can also perform backup and restore.

This user requires IMP_FULL_DATABASE and EXP_FULL_DATABASE privileges to be able to perform backup and restore successfully.

CREATE USER BACKUP_RESTORE

IDENTIFIED BY BACKUP_RESTORE

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK;

------- 4 Roles for BACKUP_RESTORE

GRANT IMP_FULL_DATABASE TO BACKUP_RESTORE;

GRANT EXP_FULL_DATABASE TO BACKUP_RESTORE;

GRANT EXECUTE_CATALOG_ROLE TO BACKUP_RESTORE WITH ADMIN OPTION;

GRANT SELECT_CATALOG_ROLE TO BACKUP_RESTORE WITH ADMIN OPTION;

ALTER USER BACKUP_RESTORE DEFAULT ROLE ALL;

------- 14 System Privileges for BACKUP_RESTORE

GRANT ALTER USER TO BACKUP_RESTORE WITH ADMIN OPTION;

GRANT ALTER SYSTEM TO BACKUP_RESTORE WITH ADMIN OPTION;

GRANT CREATE SEQUENCE TO BACKUP_RESTORE WITH ADMIN OPTION;

GRANT DROP USER TO BACKUP_RESTORE WITH ADMIN OPTION;

GRANT CREATE USER TO BACKUP_RESTORE;

GRANT CREATE SESSION TO BACKUP_RESTORE WITH ADMIN OPTION;

GRANT CREATE VIEW TO BACKUP_RESTORE WITH ADMIN OPTION;

GRANT CREATE TRIGGER TO BACKUP_RESTORE WITH ADMIN OPTION;

GRANT CREATE PROCEDURE TO BACKUP_RESTORE WITH ADMIN OPTION;

GRANT CREATE DATABASE LINK TO BACKUP_RESTORE WITH ADMIN OPTION;

GRANT CREATE SYNONYM TO BACKUP_RESTORE WITH ADMIN OPTION;

GRANT ALTER SESSION TO BACKUP_RESTORE WITH ADMIN OPTION;

GRANT CREATE CLUSTER TO BACKUP_RESTORE WITH ADMIN OPTION;

GRANT CREATE TABLE TO BACKUP_RESTORE WITH ADMIN OPTION;

------- 1 Tablespace Quota for BACKUP_RESTORE

ALTER USER BACKUP_RESTORE QUOTA UNLIMITED ON USERS;

Create a Backup and Restore Role

Since this role contains high-risk privileges, you can grant this role temporarily to the USER_ADMIN as needed for running backup and restore procedures by running the following script:

CREATE ROLE BKRESTORE_ROLE;

GRANT EXP_FULL_DATABASE, IMP_FULL_DATABASE TO BKRESTORE_ROLE;

------- GRANT BKRESTORE_ROLE TO USER_ADMIN;

GRANT BKRESTORE_ROLE TO USER_ADMIN;