Normal Linux user for oracle database dumps


is it possible to allow normal linux user " neither root nor oracle " to be able to execute expdp and impdp and to have access to dpdump directory with rw access on oracle database 12c ?

i know this doesn't sound logic , however there is business need to avail it if possible

appreciate your support in advance

Best Answer

You can create a custom directory object for this user's use. Don't use the system-level directory created by default. Just make sure that both the user and Oracle will have read/write permissions to it. Also make sure the user has execute permissions on $ORACLE_HOME/bin, and a database account with read/write access to the directory object and whatever you want to import/export.

Be very careful with the individual permissions you grant to the database user. If this isn't just for an automated interface, but for an actual non-privileged user/person, then I would push back hard, as there are serious security implications:

  1. In general, unprivileged users should not have login access to the database server such as is required to use data pump in the first place. Only sys admins and DBAs should be able to log directly in to the database server.
  2. Using datapump to import objects or data for other schemas than the one actually running the import requires elevated database privileges that normal users shouldn't have. The ability to perform full imports, for example, allows the executing user to create other users, modify system settings, and generally make a hash out of everything if they don't know what they're doing.