Appendix I: Prerequisites Inventory Methods > Zero-Touch/Remote Inventory for Oracle > Required Permission to Run a Zero-Touch Oracle Inventory > Create_user

/* ************************************************

* Script to Create User with rights for oratrack

************************************************

* Version: 1.5.5.0

* Last Change: 25.04.2023

************************************************

* Changes:

* 10.06.2018 fix CDB selection if no CDB DB exists

* create userr

* grant select v_$CONTAINERS

* 01.08.2018 fix CDB selection if no lines in table

* create RVSUSER

* 02.08.2018 add check on v$database for CDB

* Get Parameter common_user_prefix

* fix create user for CDB

* 18.09.2018 Add more Comments

* 12.03.2019 fix CDB check

* 09.04.2019 fix Password and special chars

* 27.04.2020 fix Oracle 9i/10i

* 05.06.2020 fix CommonUserPrefix if not set

* fix reset Password

* 06.08.2021 add right for V$IM_SEGMENTS

* 25.04.2023 add alter user for PDB's

* change OLAP Comment

************************************************

* Please Change Variables

* sUsername With the default Username

* If the DB is a CDB / Multitenant DB

* it at automaticly C## in front of the

* Username

* sPassword With the Password what should be used

************************************************ */

SET SERVEROUTPUT ON

DECLARE

sUsername VARCHAR2(50) := 'RVUSER'; -- Basis Username, if Container, script set C## in Front or the defined Value

sPassword VARCHAR2(50) := 'RayVentory01'; -- Password for the User

sGlobalDB VARCHAR2(50) := ''; -- Temporary Needed

sCommonUserPrefix VARCHAR2(50) := ''; -- Temporary Nedded, used to Store the defined User Prefix if Container Database

c INT;

BEGIN

-- Start Check if CDB

-- Find Out if this Server is a Container Database, for this it Execute to Selects and see if Container exists, if not it create an Exception and end this script part

BEGIN

DBMS_OUTPUT.put_line('- Check if CDB Database User needed');

EXECUTE IMMEDIATE '

DECLARE

c INT;

exCustom EXCEPTION;

PRAGMA EXCEPTION_INIT(exCustom, -20001);

BEGIN

SELECT COUNT(*) INTO c FROM V$DATABASE WHERE CDB=''YES'';

IF c = 0 THEN

DBMS_OUTPUT.put_line(''-- CDB Database: NO'');

raise_application_error(-20001, ''CDB Database: NO'');

END IF;

DBMS_OUTPUT.put_line(''-- CDB Database: YES'');

END;

-- It looks like it is a Server with Container Database.

-- Get Parameter common_user_prefix if it is set, store it in sCommonUserPrefix

SELECT COUNT(*) INTO c FROM V$PARAMETER WHERE NAME='common_user_prefix';

-- If Parameter common_user_prefix is not set, set in the Variable sCommonUserPrefix the Oracle default Value C##

IF c = 0 THEN

sCommonUserPrefix := 'C##';

DBMS_OUTPUT.put_line('-- No common_user_prefix is set, use default C##');

ELSE

SELECT VALUE INTO sCommonUserPrefix FROM V$PARAMETER WHERE NAME='common_user_prefix';

DBMS_OUTPUT.put_line('-- Found common_user_prefix with Value: ' || sCommonUserPrefix);

END IF;

-- Add in Front of the sUsername the sCommonUserPrefix

sUsername := sCommonUserPrefix || sUsername;

-- Set extra Option for Container Databases, this is needed for create User, so that the user get rights on all Containers

sGlobalDB := ' CONTAINER=ALL';

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line('-- No CDB_FEATURE_USAGE_STATISTICS');

END;

-- End Check if CDB

-- Start Check / Create User / Reset Password / Unlock existing User

DBMS_OUTPUT.put_line('- Work with Username: ' || sUsername);

-- Check if Username exist

SELECT COUNT(*) INTO c FROM DBA_USERS WHERE USERNAME = UPPER(sUsername);

IF c > 0 THEN

-- Check if the existing User is Locked

SELECT COUNT(*) INTO c FROM DBA_USERS WHERE USERNAME = UPPER(sUsername) AND ACCOUNT_STATUS LIKE '%LOCKED%';

IF c > 0 THEN

-- The User is Locked, Unlock the account

DBMS_OUTPUT.put_line('-- Unlock Account');

EXECUTE IMMEDIATE 'ALTER USER ' || sUsername || ' ACCOUNT UNLOCK';

END IF;

-- Set the Password to the actual Password Value

EXECUTE IMMEDIATE 'ALTER USER ' || sUsername || ' IDENTIFIED BY "' || sPassword || '"' ;

DBMS_OUTPUT.put_line('-- Set actual password');

ELSE

-- User not exist, create User

-- USER SQL

EXECUTE IMMEDIATE 'CREATE USER ' || sUsername || ' IDENTIFIED BY "' || sPassword || '" ' || sGlobalDB;

DBMS_OUTPUT.put_line('-- Create User');

-- SYSTEM PRIVILEGES

-- Add Session rights

EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO ' || sUsername || sGlobalDB;

DBMS_OUTPUT.put_line('-- Add Session permission');

END IF;

-- End Check / Create User / Reset Password / Unlock existing User

-- Start Base Tables, this should allways exist

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_LOB_PARTITIONS TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_LOB_SUBPARTITIONS TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_LOBS TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_OBJECTS TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_SEGMENTS TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_TAB_PARTITIONS TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_TAB_SUBPARTITIONS TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_VIEWS TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_TABLES TO ' || sUsername ;

-- EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_ALL_TABLES TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_TABLESPACES TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_USERS TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DUAL TO ' || sUsername ;

-- EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.USER_ROLE_PRIVS TO ' || sUsername ;

-- EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.USER_SYS_PRIVS TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.V_$ARCHIVE_DEST_STATUS TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.V_$DATABASE TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.V_$INSTANCE TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.V_$LICENSE TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.V_$OPTION TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.V_$PARAMETER TO ' || sUsername ;

-- EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.V_$SESSION TO ' || sUsername ;

-- EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.V_$SESSION_CONNECT_INFO TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.V_$VERSION TO ' || sUsername ;

DBMS_OUTPUT.put_line('-- Add permission for System Tables and Views');

-- End Base Tables, this should allways exist

-- Start the following tables not exists in Oracle 9i

SELECT COUNT(*) INTO c FROM DBA_VIEWS WHERE VIEW_NAME = 'DBA_ADVISOR_TASKS';

IF c > 0 THEN

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_ADVISOR_TASKS TO ' || sUsername ;

DBMS_OUTPUT.put_line('-- Add permission for DBA_ADVISOR_TASKS');

END IF;

-- EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_AUDIT_TRAIL TO ' || sUsername ;

SELECT COUNT(*) INTO c FROM DBA_VIEWS WHERE VIEW_NAME = 'DBA_AWS';

IF c > 0 THEN

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_AWS TO ' || sUsername ;

DBMS_OUTPUT.put_line('-- Add permission for DBA_AWS');

END IF;

SELECT COUNT(*) INTO c FROM DBA_VIEWS WHERE VIEW_NAME = 'DBA_ENCRYPTED_COLUMNS';

IF c > 0 THEN

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_ENCRYPTED_COLUMNS TO ' || sUsername ;

DBMS_OUTPUT.put_line('-- Add permission for DBA_ENCRYPTED_COLUMNS');

END IF;

SELECT COUNT(*) INTO c FROM DBA_VIEWS WHERE VIEW_NAME = 'DBA_FEATURE_USAGE_STATISTICS';

IF c > 0 THEN

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_FEATURE_USAGE_STATISTICS TO ' || sUsername ;

DBMS_OUTPUT.put_line('-- Add permission for DBA_FEATURE_USAGE_STATISTICS');

END IF;

SELECT COUNT(*) INTO c FROM DBA_VIEWS WHERE VIEW_NAME = 'DBA_RECYCLEBIN';

IF c > 0 THEN

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_RECYCLEBIN TO ' || sUsername ;

DBMS_OUTPUT.put_line('-- Add permission for DBA_RECYCLEBIN');

END IF;

SELECT COUNT(*) INTO c FROM DBA_VIEWS WHERE VIEW_NAME = 'DBA_REGISTRY';

IF c > 0 THEN

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_REGISTRY TO ' || sUsername ;

DBMS_OUTPUT.put_line('-- Add permission for DBA_REGISTRY');

END IF;

SELECT COUNT(*) INTO c FROM DBA_VIEWS WHERE VIEW_NAME = 'DBA_SQL_PROFILES';

IF c > 0 THEN

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_SQL_PROFILES TO ' || sUsername ;

DBMS_OUTPUT.put_line('-- Add permission for DBA_SQL_PROFILES');

END IF;

SELECT COUNT(*) INTO c FROM DBA_VIEWS WHERE VIEW_NAME = 'DBA_SQLSET';

IF c > 0 THEN

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_SQLSET TO ' || sUsername ;

DBMS_OUTPUT.put_line('-- Add permission for DBA_SQLSET');

END IF;

SELECT COUNT(*) INTO c FROM DBA_VIEWS WHERE VIEW_NAME = 'V_$BLOCK_CHANGE_TRACKING';

IF c > 0 THEN

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.V_$BLOCK_CHANGE_TRACKING TO ' || sUsername ;

DBMS_OUTPUT.put_line('-- Add permission for V_$BLOCK_CHANGE_TRACKING');

END IF;

-- End the following tables not exists in Oracle 9i

-- Start the following tables not exists in Oracle 9i and 10i

SELECT COUNT(*) INTO c FROM DBA_VIEWS WHERE VIEW_NAME = 'DBA_CUBES';

IF c > 0 THEN

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_CUBES TO ' || sUsername ;

DBMS_OUTPUT.put_line('-- Add permission for DBA_CUBES');

END IF;

SELECT COUNT(*) INTO c FROM DBA_VIEWS WHERE VIEW_NAME = 'DBA_MINING_MODELS';

IF c > 0 THEN

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_MINING_MODELS TO ' || sUsername ;

DBMS_OUTPUT.put_line('-- Add permission for DBA_MINING_MODELS');

END IF;

SELECT COUNT(*) INTO c FROM DBA_TABLES WHERE TABLE_NAME = 'MODEL$';

IF c > 0 THEN

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.MODEL$ TO ' || sUsername ;

DBMS_OUTPUT.put_line('-- Add permission for MODEL$');

END IF;

-- End the following tables not exists in Oracle 9i and 10i

IF sGlobalDB = ' CONTAINER=ALL' THEN

EXECUTE IMMEDIATE 'alter user ' || sUsername || ' set container_data=all for SYS.V_$BLOCK_CHANGE_TRACKING container=current' ;

EXECUTE IMMEDIATE 'alter user ' || sUsername || ' set container_data=all for SYS.V_$ARCHIVE_DEST_STATUS container=current' ;

EXECUTE IMMEDIATE 'alter user ' || sUsername || ' set container_data=all for SYS.V_$DATABASE container=current' ;

EXECUTE IMMEDIATE 'alter user ' || sUsername || ' set container_data=all for SYS.V_$INSTANCE container=current' ;

EXECUTE IMMEDIATE 'alter user ' || sUsername || ' set container_data=all for SYS.V_$LICENSE container=current' ;

EXECUTE IMMEDIATE 'alter user ' || sUsername || ' set container_data=all for SYS.V_$OPTION container=current' ;

EXECUTE IMMEDIATE 'alter user ' || sUsername || ' set container_data=all for SYS.V_$PARAMETER container=current' ;

EXECUTE IMMEDIATE 'alter user ' || sUsername || ' set container_data=all for SYS.V_$VERSION container=current' ;

DBMS_OUTPUT.put_line('---- Add Container Special');

END IF;

-- Start Add Global Views

-- Globale Views

-- Check if Global Views exist

SELECT COUNT(*) INTO c FROM DBA_VIEWS WHERE VIEW_NAME = 'GV_$DATABASE';

IF c > 0 THEN

-- Add right to the Global View

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.GV_$DATABASE TO ' || sUsername ;

DBMS_OUTPUT.put_line('-- Add permission for Globale View GV_$DATABASE');

IF sGlobalDB = ' CONTAINER=ALL' THEN

EXECUTE IMMEDIATE 'alter user ' || sUsername || ' set container_data=all for SYS.GV_$DATABASE container=current' ;

DBMS_OUTPUT.put_line('---- Add Container Special');

END IF;

-- Check if Global Views exist

SELECT COUNT(*) INTO c FROM DBA_VIEWS WHERE VIEW_NAME = 'GV_$INSTANCE';

IF c > 0 THEN

-- Add right to the Global View

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.GV_$INSTANCE TO ' || sUsername ;

DBMS_OUTPUT.put_line('-- Add permission for Globale View GV_$INSTANCE');

IF sGlobalDB = ' CONTAINER=ALL' THEN

EXECUTE IMMEDIATE 'alter user ' || sUsername || ' set container_data=all for SYS.GV_$INSTANCE container=current' ;

DBMS_OUTPUT.put_line('---- Add Container Special');

END IF;

-- Check if Global Views exist

SELECT COUNT(*) INTO c FROM DBA_VIEWS WHERE VIEW_NAME = 'GV_$PARAMETER';

IF c > 0 THEN

-- Add right to the Global View

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.GV_$PARAMETER TO ' || sUsername ;

DBMS_OUTPUT.put_line('-- Add permission for Globale View GV_$PARAMETER');

IF sGlobalDB = ' CONTAINER=ALL' THEN

EXECUTE IMMEDIATE 'alter user ' || sUsername || ' set container_data=all for SYS.GV_$PARAMETER container=current' ;

DBMS_OUTPUT.put_line('---- Add Container Special');

END IF;

-- End Add Global Views

-- Start Multitenant / Containers

BEGIN

-- Check if Table CDB_FEATURE_USAGE_STATISTICS exist and if CDB is Flaged in V$DATABASE. IF CDB then add rights on CDB_FEATURE_USAGE_STATISTICS

EXECUTE IMMEDIATE '

DECLARE

c INT;

BEGIN

SELECT COUNT(*) INTO c FROM CDB_FEATURE_USAGE_STATISTICS;

SELECT COUNT(*) INTO c FROM V$DATABASE WHERE CDB=''YES'';

IF c = 0 THEN

DBMS_OUTPUT.put_line(''-- no CDB Database, no permission for CDB_FEATURE_USAGE_STATISTICS needed'');

ELSE

EXECUTE IMMEDIATE ''GRANT SELECT ON SYS.CDB_FEATURE_USAGE_STATISTICS TO ' || sUsername || ''' ;

EXECUTE IMMEDIATE ''alter user ' || sUsername || ' set container_data=all for SYS.CDB_FEATURE_USAGE_STATISTICS container=current'' ;

DBMS_OUTPUT.put_line(''-- Add permission for CDB_FEATURE_USAGE_STATISTICS (need for Multitenant)'');

END IF;

END;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line('-- No CDB_FEATURE_USAGE_STATISTICS');

END;

-- Check if View v_$CONTAINERS exist, if yes grant rights to the RayVentory User

SELECT COUNT(*) INTO c FROM DBA_VIEWS WHERE VIEW_NAME = 'V_$CONTAINERS';

IF c > 0 THEN

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.V_$CONTAINERS TO ' || sUsername ;

DBMS_OUTPUT.put_line('-- Add permission for View V_$CONTAINERS');

IF sGlobalDB = ' CONTAINER=ALL' THEN

EXECUTE IMMEDIATE 'alter user ' || sUsername || ' set container_data=all for SYS.V_$CONTAINERS container=current' ;

DBMS_OUTPUT.put_line('---- Add Container Special');

END IF;

ELSE

DBMS_OUTPUT.put_line('-- No View V_$CONTAINERS');

END IF;

-- End Multitenant / Containers

-- Check if rights for Inmemory exists

SELECT COUNT(*) INTO c FROM DBA_VIEWS WHERE VIEW_NAME = 'V_$IM_SEGMENTS';

IF c > 0 THEN

-- Add rights for Inmemory exists

EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.V_$IM_SEGMENTS TO ' || sUsername ;

DBMS_OUTPUT.put_line('-- Add permission for Inmemory V_$IM_SEGMENTS');

IF sGlobalDB = ' CONTAINER=ALL' THEN

EXECUTE IMMEDIATE 'alter user ' || sUsername || ' set container_data=all for SYS.V_$IM_SEGMENTS container=current' ;

DBMS_OUTPUT.put_line('---- Add Container Special');

END IF;

-- End Add rights for Inmemory exists

-- Management Tabels

BEGIN

EXECUTE IMMEDIATE '

DECLARE

c INT;

BEGIN

SELECT COUNT(*) INTO c FROM MGMT_ADMIN_LICENSES;

EXECUTE IMMEDIATE ''GRANT SELECT ON SYSMAN.MGMT_ADMIN_LICENSES TO ' || sUsername || ''' ;

EXECUTE IMMEDIATE ''GRANT SELECT ON SYSMAN.MGMT_LICENSE_CONFIRMATION ' || sUsername || ''' ;

EXECUTE IMMEDIATE ''GRANT SELECT ON SYSMAN.MGMT_LICENSE_DEFINITIONS TO ' || sUsername || ''' ;

EXECUTE IMMEDIATE ''GRANT SELECT ON SYSMAN.MGMT_LICENSES TO ' || sUsername || ''' ;

EXECUTE IMMEDIATE ''GRANT SELECT ON SYSMAN.MGMT_TARGETS TO ' || sUsername || ''';

DBMS_OUTPUT.put_line(''-- Add permission for Management Tables'');

END;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line('-- No Management Tables');

END;

-- ODM_DOCUMENT

BEGIN

EXECUTE IMMEDIATE '

DECLARE

c INT;

BEGIN

SELECT COUNT(*) INTO c FROM DBA_TABLES WHERE TABLE_NAME = ''ODM_DOCUMENT'';

IF c > 0 THEN

EXECUTE IMMEDIATE ''GRANT SELECT ON CONTENT.ODM_DOCUMENT TO ' || sUsername || ''' ;

DBMS_OUTPUT.put_line(''-- Add permission for ODM_DOCUMENT'');

ELSE

DBMS_OUTPUT.put_line(''-- No ODM_DOCUMENT Table'');

END IF;

END;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line('-- No ODM_DOCUMENT Table');

END;

-- DM Tables

BEGIN

EXECUTE IMMEDIATE '

DECLARE

c INT;

BEGIN

SELECT COUNT(*) INTO c FROM DMSYS.DM$MODEL;

EXECUTE IMMEDIATE ''GRANT SELECT ON DMSYS.DM$MODEL TO ' || sUsername || ''' ;

EXECUTE IMMEDIATE ''GRANT SELECT ON DMSYS.DM$OBJECT TO ' || sUsername || ''' ;

EXECUTE IMMEDIATE ''GRANT SELECT ON DMSYS.DM$P_MODEL TO ' || sUsername || ''' ;

DBMS_OUTPUT.put_line(''-- Add permission for DM Tables'');

END;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line('-- No DM Tables');

END;

-- DBA_DV_REALM

BEGIN

EXECUTE IMMEDIATE '

DECLARE

c INT;

BEGIN

SELECT COUNT(*) INTO c FROM DBA_VIEWS WHERE VIEW_NAME = ''DBA_DV_REALM'';

IF c > 0 THEN

EXECUTE IMMEDIATE ''GRANT SELECT ON DVSYS.DBA_DV_REALM TO ' || sUsername || ''' ;

DBMS_OUTPUT.put_line(''-- Add permission for DBA_DV_REALM'');

ELSE

DBMS_OUTPUT.put_line(''-- No Table DBA_DV_REALM'');

END IF;

END;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line('-- No Table DBA_DV_REALM');

END;

-- Label Secuity

BEGIN

EXECUTE IMMEDIATE '

DECLARE

c INT;

BEGIN

SELECT COUNT(*) INTO c FROM DBA_TABLES WHERE TABLE_NAME = ''LBAC$POLT'';

IF c > 0 THEN

EXECUTE IMMEDIATE ''GRANT SELECT ON DVSYS.LBACSYS.LBAC$POLT TO ' || sUsername || ''' ;

DBMS_OUTPUT.put_line(''-- Add permission for LBACSYS.LBAC$POLT'');

ELSE

DBMS_OUTPUT.put_line(''-- No Table LBACSYS.LBAC$POLT'');

END IF;

END;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line('-- No Table LBACSYS.LBAC$POLT');

END;

-- GEOM Tables

BEGIN

EXECUTE IMMEDIATE '

DECLARE

c INT;

BEGIN

SELECT COUNT(*) INTO c FROM DBA_VIEWS WHERE VIEW_NAME = ''ALL_SDO_GEOM_METADATA'';

IF c > 0 THEN

EXECUTE IMMEDIATE ''GRANT SELECT ON MDSYS.ALL_SDO_GEOM_METADATA TO ' || sUsername || ''' ;

EXECUTE IMMEDIATE ''GRANT SELECT ON MDSYS.SDO_GEOM_METADATA_TABLE TO ' || sUsername || ''' ;

DBMS_OUTPUT.put_line(''-- Add permission for GEOM Tables'');

ELSE

DBMS_OUTPUT.put_line(''-- No GEOM Tables'');

END IF;

END;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line('-- No GEOM Tables');

END;

-- ODM Table

BEGIN

EXECUTE IMMEDIATE '

DECLARE

c INT;

BEGIN

SELECT COUNT(*) INTO c FROM DBA_TABLES WHERE TABLE_NAME = ''ODM_MINING_MODEL'';

IF c > 0 THEN

EXECUTE IMMEDIATE ''GRANT SELECT ON ODM.ODM_MINING_MODEL TO ' || sUsername || ''' ;

EXECUTE IMMEDIATE ''GRANT SELECT ON ODM.ODM_RECORD TO ' || sUsername || ''' ;

DBMS_OUTPUT.put_line(''-- Add permission for ODM Tables'');

ELSE

DBMS_OUTPUT.put_line(''-- No ODM Tables'');

END IF;

END;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line('-- No ODM Tables');

END;

-- OLAP Views

BEGIN

EXECUTE IMMEDIATE '

DECLARE

c INT;

BEGIN

SELECT COUNT(*) INTO c FROM DBA_VIEWS WHERE VIEW_NAME = ''DBA$OLAP_CUBES'';

IF c > 0 THEN

EXECUTE IMMEDIATE ''GRANT SELECT ON OLAPSYS.DBA$OLAP_CUBES TO ' || sUsername || ''' ;

DBMS_OUTPUT.put_line(''-- Add permission for OLAP View DBA$OLAP_CUBES'');

ELSE

DBMS_OUTPUT.put_line(''-- No OLAP View DBA$OLAP_CUBES'');

END IF;

END;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line('-- No OLAP View DBA$OLAP_CUBES');

END;

-- Oracle ERP only

SELECT COUNT(*) INTO c FROM DBA_TABLES WHERE TABLE_NAME = 'fnd_app_servers';

IF c > 0 THEN

EXECUTE IMMEDIATE 'GRANT SELECT ON applsys.fnd_app_servers TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON applsys.fnd_nodes TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON applsys.fnd_product_installations TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON applsys.fnd_application_tl TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON applsys.fnd_responsibility TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON applsys.fnd_user TO ' || sUsername ;

EXECUTE IMMEDIATE 'GRANT SELECT ON apps.fnd_user_resp_groups TO ' || sUsername ;

DBMS_OUTPUT.put_line('-- Add permission for ERP Tables');

ELSE

DBMS_OUTPUT.put_line('-- No ERP Tables');

END IF;

END;

EXIT;

Create_user_1.5.5.0.sql

Create_user_1.5.5.0.sql