2 Replies Latest reply on Jul 17, 2015 4:23 AM by Alexander Metzler

    Bladelogic_Portal database schema

      Hi all,

      I just came across the following document describing the manual creation of the bladelogic_portal database scheme.

      https://docs.bmc.com/docs/display/public/blp12/Setting+up+an+Oracle+database

       

      Some of the SQL Statements are unneccesary, some grant permissions very generously. As a whole this document leaves an impression of a very fast and easy solution disregarding security aspects.

      I dont think this is petty-minded, to me it is question of conscientiousness, quality and professionality.

       

      Perhaps someone at BMC takes some time and adjusts the script and documentation.

       

      _____________

      CREATE BIGFILE TABLESPACE <PORTAL_DB_NAME> DATAFILE '<DATAFILE_LOCATION>/<PORTAL_DB_NAME>.dbf' SIZE 5125M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

      CREATE BIGFILE TABLESPACE <PORTAL_DB_NAME>_INDEX DATAFILE '<DATAFILE_LOCATION>/<PORTAL_DB_NAME>_index.dbf' SIZE 5125M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

      CREATE USER <PORTAL_DB_NAME> PROFILE DEFAULT IDENTIFIED BY <PORTAL_DB_PASSWORD> DEFAULT TABLESPACE <PORTAL_DB_NAME> TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;

       

      GRANT CONNECT TO <PORTAL_DB_NAME>;

      GRANT DBA TO <PORTAL_DB_NAME>;

      commit;

       

      CREATE USER <PORTAL_OPERATIONAL_OWNER> PROFILE DEFAULT IDENTIFIED BY <PORTAL_DB_PASSWORD>  DEFAULT TABLESPACE  <PORTAL_DB_NAME> TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;

       

      GRANT INSERT ANY TABLE TO <PORTAL_OPERATIONAL_OWNER>;

      GRANT UPDATE ANY TABLE TO <PORTAL_OPERATIONAL_OWNER>;

      GRANT DELETE ANY TABLE TO <PORTAL_OPERATIONAL_OWNER>;

      GRANT SELECT ANY TABLE TO <PORTAL_OPERATIONAL_OWNER>;

      GRANT EXECUTE ANY PROCEDURE TO <PORTAL_OPERATIONAL_OWNER>;

      GRANT CONNECT TO <PORTAL_OPERATIONAL_OWNER>;

      commit;

        • 1. Re: Bladelogic_Portal database schema

          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.

          • 2. Re: Bladelogic_Portal database schema

            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

            BEGIN

              DBMS_APPLICATION_INFO.set_module(USER, ‘Initialized’);

              EXECUTE IMMEDIATE ‘ALTER SESSION SET current_schema=BLPORTAL';

            END;

            /

             

            ______________

             

            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.

             

            ie.

            --showcharset.sql

            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%';