0

To begin, please keep in mind that I am a very green DBA. I have done prior research and have not really found anything too promising, or maybe it just seems that way because I am still struggling in my understanding of the monster that is Oracle. So please forgive me and kindly point me in the proper direction if my question sounds arbitrary.

Scenario:

  • some users create database links into several heavy data warehouses.
  • these users neglect to close or drop DB link.
  • users process continues to run and bogs down the DW's performance.

Goal:

  • prevent these users from creating database links, BUT still allow DBAs to create DB links

How could this be done? Could the answer be within OID capabilities? It seems that there could be to me, but my understanding of Oracle does not seem sufficient enough to be able to determine for sure.

UPDATE: After investigating into user privileges, I discovered that privileges are not the problem. A remote database can be accessed with just a select statement and without a dblink. Revoking the CREATE DATABASE LINK privilege would not stop a user from accessing database B from database A.

However, this is only true when the DB_DOMAIN = "blank" and the global name = "fooDB"

A link is required if the DB_DOMAIN = foo.com and the global name = "fooDB.foo.com"

I could not find any documentation similar to this scenario or that explains why this happens. Why do I need to create a link when the global name = "fooDB.foo.com" but not when the global name = "fooDB"?

3

I think you are approaching this from the wrong end. A database link includes the user ID and password to connect to the remote server; if the user ID does not exists on the remote server or is not allowed to connect, the problem does not exist. If you don't want certain users to run queries against your data warehouse, don't authorize them there.

If you check the SQL reference you will see that users require the CREATE DATABASE LINK privilege to create those links. You can revoke the privilege from some users, but that would not prevent them from connecting directly to the remote database and running their queries there.

  • Thanks @mustaccio, it did kind of feel like I have been perusing more of a work around rather than a solution. I'll investigate further on our user privileges. – exit_1 Feb 19 '15 at 16:34
1

If you don't want a user to create a database link, don't grant them the CREATE DATABASE LINK or CREATE PUBLIC DATABASE LINK privileges.

My guess is that you've granted the developers a role that you didn't realize had that privilege-- the CONNECT role in at least some versions of Oracle includes CREATE DATABASE LINK. I think that's been removed in more recent versions but I'd need to validate that. This is one of the many reasons that using the predefined roles CONNECT and RESOURCE is highly discouraged in a production environment-- the privileges associated with them are generally much more expansive than you want (or than the name implies) and they tend to change over time. You're much better off creating your own roles, granting those roles whatever privileges your users actually need, and granting those custom roles to your users.

As for the underlying issue of user queries bogging down a data warehouse, I would tend to expect that you want to use Resource Manager on the data warehouse to limit the resources that lower-priority users get when there is contention for resources. You might be able to get away with a lighter weight solution based on profiles as well to put a hard cap on the amount of resources any single query can consume before it is killed. Resource Manager, though, is a much more robust set of tools to prioritize requests.

  • Thank you @Justin Cave, I think I'll look more into CONNECT and RESOURCE roles and investigate further and what is granted to the users. – exit_1 Feb 19 '15 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.