Forum Stats

  • 3,839,350 Users
  • 2,262,484 Discussions
  • 7,900,945 Comments

Discussions

Restrict ALTER DATABASE command in Oracle

Raj Kushawaha
Raj Kushawaha Member Posts: 245
edited Jan 17, 2020 12:33PM in Database Security - General

Dear Community Experts,

I have my oracle instances are running by oracle OS user and there are many other users in the database with dba role assigned to them.

What I want is that only oracle OS users connected to the database can run the ALTER DATABASE or ALTER TABLESPACE commands and other third party user connecting from remote systems or connected to database using different OS user accounts can't run the ALTER DATABASE and ALTER TABLESPACE  commands.

Please help me understand how can I achieve this database security goals.

Regards,

-Raj

Andris Perkons-Oracle

Answers

  • pmdba
    pmdba Member Posts: 103 Bronze Badge
    edited Dec 27, 2019 10:52AM
    ... there are many other users in the database with dba role assigned to them.

    I've run into this sort of problem on numerous occasions. The short answer is "don't give them the DBA role". The DBA role is insanely over-powered for what most people - and especially developers or application admins - actually need. If you are providing the database as a service to these people and you don't want them making configuration changes to initialization parameters, storage configuration, etc., then create a custom role that gives them whatever privileges they actually do need, and no more. Use the list of privileges assigned to the DBA role as a checklist/guideline to work through with the user (assuming you even need to ask them).

    Andris Perkons-Oracle
  • Emad Al-Mousa
    Emad Al-Mousa Member Posts: 716 Bronze Trophy
    edited Dec 29, 2019 10:57AM

    Hi,

    if your environment is CDB (Multitenant) you can use LOCKDOWN PROFILE feature, where it will perform such restriction you are describing.

    Regards,

    Emad

  • Emre OZTURK
    Emre OZTURK Member Posts: 6 Red Ribbon
    edited Jan 15, 2020 4:02AM

    Best way to achieve this goal is "Database Vault"

    it needs license but you can do almost everything with DB Vault.

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Jan 16, 2020 11:48PM

    You could take the sledgehammer aproach and revoke "alter database" from DBA role, then explicitly grunt it to those who actually need it...

    Fur example:

    SQL> revoke alter database from dba;Revoke succeeded.SQL> grant alter database to gaz;Grant succeeded.SQL>
  • pmdba
    pmdba Member Posts: 103 Bronze Badge
    edited Jan 17, 2020 12:33PM

    This runs the risk of breaking some other part of Oracle that depends on that role, and the role can be overwritten/restored to original privilege level during a patch installation or upgrade without telling you. The only way to be sure is to build custom roles that follow the principle of least privilege from the start and then reassign users.

    andrewmy