Re: Database Link Permissions

From: Walt <walt_askier_at_SHOESyahoo.com>
Date: Fri, 12 May 2006 19:17:27 -0400
Message-ID: <126a5s7roes1l00@corp.supernews.com>

Walt wrote:

> 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.

Continuing the conversation with myself... the *right* way to do this is not with views, but with packages or stored proceedures.

Privileged user A owns the link and creates stored procs or packages that do what needs to be done. A grants execute rights on the proc to users B and C.

This way, users B and C can't see or do anything on the remote database that's not allowed by the proc.

Obvious, eh?

//Walt Received on Fri May 12 2006 - 18:17:27 CDT