Views, DBLinks and Priv "Workarounds" [message #461089] |
Wed, 16 June 2010 09:05 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
Afternoon all.
I encountered something unexpected today.
As I'm sure you're all well aware I cannot create a view over an object I cannot grant privs on.
What I can do, however, is create a circular DB link back to my server, use that to create a view over a table I lack grant privs on.
In other words
Take the example of UserA & UserB. UserA has a table (table1) and he grants select on table1 to UserB
UserB cannot execute:
Create view myview as select * from UserA.table1
But, if UserB creates a DBlink(called DBlink for ease) to the server he can execute this:
Create view myview as select * from UserA.Table1@Dblink
This successfully executes.
My question is...why? Best guess asking around our guys is it divides privs seperately, processes the DBlink first as a subquery/internal view using credentials supplied in the link then creates the view over that effectively circumventing the fact that I lack privs directly.
Any insight would be interesting.
Thanks in advance.
|
|
|
|
|
Re: Views, DBLinks and Priv "Workarounds" [message #461094 is a reply to message #461092] |
Wed, 16 June 2010 09:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13923 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Yes it'll be because of the role.
I think it basically boils down to the fact that because you are using a DB link you are accessing the other schema as though you are the owner of the other schema and doing it this way you don't actually need any privs at all (other than create view obviously).
Try revoking select from the role and see if you can still create the view in the other schema via the db link, bet you can.
Realistically though, if you're supposed to be creating views in another schema you should be given direct grants on the underlying tables. Using db links like this is big security hole.
|
|
|
|
|
|
Re: Views, DBLinks and Priv "Workarounds" [message #461101 is a reply to message #461097] |
Wed, 16 June 2010 10:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13923 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You might be creating the view in your schema but the select runs as the owner of the db link. Basically what you originally said I guess, I'm just putting a different emphasis.
Sounds like you need to sort out the relationship with the DBAs.
From a security point of view, views should really be created in the same schema as the base tables, then you'd grant select on the views to the required roles.
If security is that big a concern then there is no way you should have create database link anyway. Because with it you can bypass all the security.
|
|
|
|
|
Re: Views, DBLinks and Priv "Workarounds" [message #461112 is a reply to message #461108] |
Wed, 16 June 2010 10:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
Dont suppose I have to actually run it at home. This should do the job
--creating userA
create user usera identified by aa;
grant create session to usera;
grant create table to usera;
grant create role to usera;
grant unlimited tablespace to usera;
--creating userb
create user userb identified by bb;
grant create session to userb;
grant create table to userb;
grant create database link to userb;
grant create view to userb;
grant unlimited tablespace to userb;
--connect as user A, make the table, role and grants
conn usera/aa
create table table1 as select * from dual;
create role myrole;
grant select on table1 to myrole;
grant myrole to userb;
--connect as userb, query the table directly, fail to create a view,
--create a dblink, query via DB link then finally create the view using DBlink
conn userb/bb
select * from usera.table1;
create view myview as select * from usera.table1;
create database link dblink connect to USERB identified by bb using 'ocp11g';
select * from usera.table1@dblink;
create view myview as select * from usera.table1@dblink;
select * from myview;
That should replicate what I'm seeing.
Perhaps redundant in light of earlier posts but may be useful in the future for any users searching etc.
[Updated on: Wed, 16 June 2010 11:25] by Moderator Report message to a moderator
|
|
|
Re: Views, DBLinks and Priv "Workarounds" [message #461116 is a reply to message #461112] |
Wed, 16 June 2010 11:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68653 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
1/ Nothing strange to what you see, it is the expected behaviour
2/ CREATE DATABASE LINK is NOT an innocent privilege and should not be granted to someone without a good reason and never to a end user
3/ I know this is for the exemple but UNLIMITED TABLESPACE should never be granted.
Regards
Michel
|
|
|