Introduction
Security and access management for databases are among the key challenges in database environments. Oracle Database 19c allows us to effectively manage access by using predefined operating system groups. In this article, we will explore how to use these groups to segregate permissions and perform backup operations on the database without accessing user data.
Operating System Groups in Oracle Database 19c
If you have installed the oracle-database-preinstall package during the initial setup, it creates the Oracle user and the relevant groups.
Link to installation instructions
Additionally, you need to manually create the remaining groups required for the Grid installation.
You can use the id command to see the groups that the Oracle user belongs to:
[oracle@vahiddb1922 ~]$ id
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54327(asmdba),54328(asmoper),54329(asmadmin),54330(racdba)
The backupdba group is one of the default groups that allows users who need to perform backup operations. In this article, we will examine how to use this group for managing backups and limiting access.
Practical Steps
Creating and Configuring a User
Creating a New User and Adding to the backupdba Group
Given the predefined groups, we need to create a new user and add it to the backupdba group, and set a password for it. This is done as follows:
[root@vahiddb1922 ~]# useradd -m -G backupdba backupuser
[root@vahiddb1922 ~]# passwd backupuser
Changing password for user backupuser.
New password:
BAD PASSWORD: The password contains the user name in some form
Retype new password:
passwd: all authentication tokens updated successfully.
Setting Permissions
Based on your requirements, you can set specific permissions for the backupuser so that it only has the desired access.
Performing Backup Operations with the New User
Connecting to the Database
Connect to the database using the backupuser:
To do this, the operating system environment variables need to be set up as well.
[backupuser@vahiddb1922 ~]$ sqlplus / as sysbackup
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 23 17:24:53 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
Now let's see what we have access to:
[backupuser@vahiddb1922 ~]$ sqlplus / as sysbackup
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 23 17:26:57 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
SQL> SHOW PDBS
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL> alter session set container=pdb;
Session altered.
SQL> select username from dba_users where username='HR';
USERNAME
--------------------------------------------------------------------------------
HR
SQL> SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER='HR';
TABLE_NAME
--------------------------------------------------------------------------------
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
COUNTRIES
7 rows selected.
SQL> SELECT * FROM HR.REGIONS;
SELECT * FROM HR.REGIONS
*
ERROR at line 1:
ORA-01031:insufficient privileges