In linux/Disk1/utility/oracle/readme.txt there is a more reasonable approach to grant permissions to the operational user ....
Recommend steps for setting up the BladeLogic Portal users and database:
4. Create a user without DBA role (operational user) and grant the permissions INSERT, UPDATE, DELETE and SELECT on the created tables
and EXECUTE on the procedures.
The subfolder utils/configuration contains the SQLPLUS script file 'grantperms.sql'. Execute the script as system user or schema owner.
Example: sqlplus <schema owner name>/<password>@<SID> @grantperms.sql
The script will prompt for the schema owner user name and operational user name. And grant the permissions to the operational user.
My approach to create the Bladelogic Portal database objects:
- I created a schema owner BLPortal and granted the following permissions
GRANT CONNECT TO BLPORTAL;
GRANT RESOURCE TO BLPORTAL;
- created the database schema with script oracle_master.sql
- created a BLPORTAL_OPER user with the following permissions
GRANT CONNECT TO BLPORTAL_OPER;
- create the permissions for the BLPORTAL_OPER user with script utility/oracle/utils/configuration/grantperms.sql (execute as BLPortal)
- created a logon trigger for BLPORTAL_OPER
CREATE OR REPLACE TRIGGER BLPORTAL_OPER.after_logon_trg
AFTER LOGON ON BLPORTAL_OPER.SCHEMA
EXECUTE IMMEDIATE ‘ALTER SESSION SET current_schema=BLPORTAL';
Some helper SQL scripts inside the utility/oracle/utils use direct access to Dynamic Performance Views
views. This should be substituted by a syscontext construct. This may help BMC to avoid the "fast" solution of granting dba priviledge to the BLPortal schema owner.
orig: select (select instance_name from v$instance) instancename,parameter, value from nls_database_parameters where parameter like '%CHARACTERSET%';
change to : select (select sys_context('userenv','instance_name') from dual)instancename,parameter, value from nls_database_parameters where parameter like '%CHARACTERSET%';