Problem solve Get help with specific problems with your technologies, process and projects.

Roles used for transferring data provide too many privileges

We are required to follow very strict security guidelines for our production databases. One of those guidelines states that a schema owner cannot have create session privileges, except when updating structures. Any user that can connect to the database cannot own any objects. Normally this is not a problem. We have one schema owner, and all users are granted specific access (through roles) to objects in that schema.

Here's my problem: we need to transfer data from one computer to another (not connected via a network) on a regular basis. We have worked out a process, using export and import, to do this. The user running this process must be able to connect to the database. However, this person cannot own any tables. In order to export and import from and to a different schema, they must have exp_full_database and imp_full_database roles. These roles provide way too many privileges (for example, create any table). Is there any other way around this? Any advice is appreciated!

Any user that is able to create tables in another schema will likely have too much privileges for the environment you're describing. Your standard is admirable, but it seems almost too restrictive to get the work done.

The only other potential solution for your problem (which has several restrictions of its own) is to use a logical standby database on one of the hosts. This may or may not work for you depending on your requirements. The logical standby database can be "fed" from archived redo logs. So, instead of doing an export on your primary and copying that file to the secondary server, copy the archived redo logs. Once they are restored to the secondary system, they can be applied to update the secondary server.

Dig Deeper on Oracle database security

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.