When deploying BMC_DEBUG to a live environment, there are a couple of decisions you need to make regarding how the packages and tables are installed.
Installing BMC_DEBUG in its own schema
This is the recommended way of installing BMC_DEBUG. You should create a new database user which will own the BMC_DEBUG packages and tables, and grant the user the following privileges :-
CREATE SESSION
CREATE TABLE
CREATE PROCEDURE
These are the minimum privileges required for BMC_DEBUG to compile and run, although if possible you should grant the user SELECT privilege on :-
V$MYSTAT
V$STATNAME
V$SESSION_EVENT
V$SESS_TIME_MODEL
as this will allow the collection of statistics. BMC_DEBUG will compile and run without these privileges, but if you attempt to collect statistics for instrumented procedures, an error will be written to the log table stating that the necessary V$ views are not accessible.
Once this is done, you can then grant EXECUTE privilege on the BMC_DEBUG packages to any users which own PL/SQL code which is to be instrumented.
For example, if you installed BMC_DEBUG into the user DBG_OWNER, and your application PL/SQL code was owned by the user APP_OWNER, you would connect to DBG_OWNER in SQL*Plus and run :-
SQL> GRANT EXECUTE ON bmc_debug TO app_owner; Grant succeeded. SQL> GRANT EXECUTE ON bmc_error TO app_owner; Grant succeeded. SQL> GRANT EXECUTE ON bmc_exceptions TO app_owner; Grant succeeded.
Then in SQL*Plus as the APP_OWNER user, you would create synonyms for the packages :-
SQL> CREATE SYNONYM bmc_debug FOR dbg_owner.bmc_debug; Synonym created. SQL> CREATE SYNONYM bmc_error FOR dbg_owner.bmc_error; Synonym created. SQL> CREATE SYNONYM bmc_exceptions FOR dbg_owner.bmc_exceptions; Synonym created.
This would allow packages and procedure in the APP_OWNER schema to run any BMC_DEBUG procedures and functions. APP_OWNER would not need any privilege on the BMC_DEBUG tables.
End users who execute PL/SQL which is instrumented do not need any privileges on the BMC_DEBUG packages or tables.
Installing BMC_DEBUG into the same schema as the application PL/SQL
It is perfectly possible to install BMC_DEBUG into the same schema which owns the application code you wish to instrument. To do this, you would just run the installation scripts against the user which owns your application, and you wouldn't need to grant EXECUTE privilege on the packages or create synonyms, as the objects would exist in the same schema.
One disadvantage of doing this is that it is more difficult to restrict access to the parameter tables, in order to control who can modify them. It may be that you don't want developers who can login as the application owner to be able to modify BMC_DEBUG parameters which affect the behaviour of BMC_DEBUG on your live system.