How to give access to run awr reports from normal users.
Generally, we run AWR from some administrator accounts (usually from sys or system users).
In order to generate the AWR reports from normal user we need to grant the follow privileges to him.
Let's follow the following plan:
1. Create user.
2. Grant the advisor privilege.
3. Grant execute permission on dbms_workload_repository package.
4. Grant select any dictionary
Here here is the complete summary of the activity
===================================================================
SQL> create user awradmin identified by pass123;
User created.
SQL> grant connect,resource to awradmin;
Grant succeeded.
SQL> grant advisor to awradmin;
Grant succeeded.
SQL> grant execute on dbms_workload_repository to awradmin;
Grant succeeded.
SQL> grant select any dictionary to awradmin;
Grant succeeded.
SQL> conn awradmin/awradmin123
Connected.
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
SQL>
===================================================================
In some cases it may be not possible to grant all permissions so in that case you can use follow the plan described below.
GRANT SELECT ON SYS.V_$DATABASE TO awradmin;
GRANT SELECT ON SYS.V_$INSTANCE TO awradmin;
GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO awradmin;
GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO awradmin;
GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO awradmin;
SQL> GRANT SELECT ON SYS.V_$DATABASE TO awradmin;
Grant succeeded.
SQL> GRANT SELECT ON SYS.V_$INSTANCE TO awradmin;
Grant succeeded.
SQL> GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO awradmin;
Grant succeeded.
SQL> GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO awradmin;
Grant succeeded.
SQL> GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO awradmin;
Grant succeeded.
SQL>
=====================================================================
There is a fast way to do that :) You need to grant role CONNECT, SELECT_CATALOG_ROLE and OEM_MONITOR to user and that is all.
GRANT CONNECT TO awradmin;
GRANT SELECT_CATALOG_ROLE TO awradmin;
GRANT OEM_MONITOR TO awradmin;
The role "SELECT_CATALOG_ROLE" contains the privileges:
SELECT ON DBA_HIST_SNAPSHOT
SELECT ON DBA_HIST_DATABASE_INSTANCE
SELCT ON V_$INSTANCE
SELECT ON V_$DATABASE
The role "OEM_MONITOR" contains the privilege EXECUTE ON DBMS_WORKLOAD_REPOSITORY.
SQL> GRANT CONNECT TO awradmin;
Grant succeeded.
SQL> GRANT SELECT_CATALOG_ROLE TO awradmin;
Grant succeeded.
SQL> GRANT OEM_MONITOR TO awradmin;
Grant succeeded.
SQL>
Thank you, Enjoy ... :)
