Re: Database Link Permissions

Walt wrote:

> I've got a database link that needs to be used by two different users.
> Currently, it's a Public database link, which is great in the sense
> that both users who need it can use it, but very bad in the sense that
> every other user who shouldn't need it can use it too. So, I'd like to
> create a Private Database Link and grant both users the right to use
> it. However, I'm not seeing any way to do that - if it was a table,
> sequence, view, etc no problem, but I'm not seeing anything in the BNF
> syntax diagram for the grant statement that pertains to DB links, and
> educated guesses like "grant select on [linkname] to MYUSER" don't
> work.
>
> Anybody done this? Shoud I just create two diffferent private links,
> one owned by each user, and call it a day?
>
> BTW, there's a fair amount of code base that references the db link,
> and I'd strongly prefer not to change the name of the link and then
> have to find and change it everywhere the code references it.
>
> Oracle 9.2 W2k3

Ok, nobody really answered my question, so I'll try to answer it myself:

As near as I can determine, a database link is not an object like a table, view, sequence, etc. that can be granted rights to. Your options are either create it as a public link, which means everybody can see it, or create it as private which means only the owner can see it. If it's a private db link, there is no way to grant rights to another user to use it. Moreover, even users with DBA priviliges can't see it or use it.   Not even SYS.

So, the short answer is all or nothing. Public or private. Everybody or just the owner. No in between.

That is, for rights to the db link. But my real *business* problem is not that I need to have several users access the same database link, but that several users need to access the data on the remote database. A subtle but slightly different requirement. And one that is solvable:

Create a private database link owned by user A. Figure out what tables user B needs to see and have user A create a view of those tables. Grant user B rights to the view. Problem solved, and in a better way than I was trying to do it.

OK, it doesn't quite meet the needs of no renaming, but I'll take the hit for the better security.

//Walt Received on Tue May 09 2006 - 17:11:11 CDT