3 Securing Oracle

Overview

This section contains best practices for securing an Oracle database.

To improve security, creating additional database users with minimal privileges is recommended. These users should be configured based on the principle of least privilege, that is, they should only have privileges that are essential for performing the intended functions.

Creating users

Assuming that the pluggable database (PDB) owner is usr_owner, creating two additional users with the corresponding privileges (for daily operations) are recommended:

• usr_srv - user for running Zabbix server;
• usr_web - user for running Zabbix frontend and API.

These users must be created by the PDB owner (usr_owner) using the following commands:

CREATE USER usr_srv IDENTIFIED BY "usr_srv" DEFAULT TABLESPACE "usr_owner" TEMPORARY TABLESPACE temp;
       CREATE USER usr_web IDENTIFIED BY "usr_web" DEFAULT TABLESPACE "usr_owner" TEMPORARY TABLESPACE temp;
       
       GRANT CREATE SESSION, DELETE ANY TABLE, INSERT ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE, SELECT ANY SEQUENCE TO usr_srv;
       GRANT CREATE SESSION, DELETE ANY TABLE, INSERT ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE, SELECT ANY SEQUENCE TO usr_web;

After creating the users, proceed to creating synonyms.

Generating synonyms

The script below creates synonyms, so that usr_srv and usr_web can access tables in the usr_owner schema without specifying the schema explicitly.

BEGIN
       FOR x IN (SELECT owner,table_name FROM all_tables WHERE owner ='usr_owner')
       LOOP
         EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM usr_srv.'|| x.table_name ||' FOR '||x.owner||'.'|| x.table_name;
         EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM usr_web.'|| x.table_name ||' FOR '||x.owner||'.'|| x.table_name;
       END LOOP;
       END;
       /