Thank you. This seems to be a lot of privileges. Are all of these are
minimum required to be able to run sql tuning advisor/create
profile/baselines? I just want to ensure to not provide any additional
privileges which are not needed. And just to go with minimum required ones.
Is " ADMINISTER SQL MANAGEMENT OBJECT" just a wrapper of those
create/alter/drop profile privilege? or does it have some other
elevated privilege too?
On Mon, Nov 15, 2021 at 12:09 AM Lok P <loknath.73@xxxxxxxxx> wrote:
I think you will need below privilege for the performance group to be able
to run tuning advisor and create profile/baseline etc.
ADVISOR :- For tuning advisor
ADMINISTER SQL TUNING SET :- For able to create and modify sql tuning set
created by self
ADMINISTER ANY SQL TUNING SET :- For able to modify sql tuning set of any
user
CREATE ANY SQL PROFILE :- For creating Sql profile
ALTER ANY SQL PROFILE :- For enabling/disabling Sql profile
DROP ANY SQL PROFILE :- For dropping Sql profile
ADMINISTER SQL MANAGEMENT OBJECT :- Above three profile specific roles are
now deprecated and replaced with this new role.
In addition to above you may need - EXECUTE ON DBMS_SQLTUNE.
On Sun, Nov 14, 2021 at 12:10 AM Pap <oracle.developer35@xxxxxxxxx> wrote:
Hi, We have a team which is going to mainly look into database
performance issues and will not do any administrative work(say e.g. script
deployment,backup recovery, upgrade, migration, replication etc). And we
want to make sure to have required privileges to the performance group but
no elevated privilege should be given.
What I can think of is, mainly to investigate the historical and current
performance issues in the production database, we should have a "select
catalogue role" so that all the dba_* views can be read/queried along with
application tables. And also they should be able to run the sql tuning
advisor , create profile/baselines/patch etc. I think 11.2 was creating a
profile role specifically. But 19C has something to replace that and a
superior one called 'ADMINISTER SQL MANAGEMENT OBJECT'. So I want to
understand, is this ADMINISTER SQL MANAGEMENT OBJECT privilege is safe
without any elevated privilege underlying within? And/Or if any additional
privilege is required for this group?
