Restrict ALTER DATABASE command in Oracle

Raj Kushawaha

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

pmdba

... 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).

Emad Al-Mousa

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

Best way to achieve this goal is "Database Vault"

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

Gaz in Oz

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

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.