Home » RDBMS Server » Security » Views, DBLinks and Priv "Workarounds" (Oracle 10.2.0.3)
Views, DBLinks and Priv "Workarounds" [message #461089] Wed, 16 June 2010 09:05 Go to next message
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 #461091 is a reply to message #461089] Wed, 16 June 2010 09:11 Go to previous messageGo to next message
cookiemonster
Messages: 13778
Registered: September 2008
Location: Rainy Manchester
Senior Member
I think first of all you're going to have to explain why the method without the DB link doesn't work, it does for me.
Re: Views, DBLinks and Priv "Workarounds" [message #461092 is a reply to message #461091] Wed, 16 June 2010 09:15 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Might it be because my privs come via a role? I've a bunch of tables I can select from, but cannot create views over but I do have the create view priv.

Edit: I tend to forget most of our privs come in via roles. Sorry about the confusion.

[Updated on: Wed, 16 June 2010 09:20]

Report message to a moderator

Re: Views, DBLinks and Priv "Workarounds" [message #461094 is a reply to message #461092] Wed, 16 June 2010 09:24 Go to previous messageGo to next message
cookiemonster
Messages: 13778
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 #461097 is a reply to message #461094] Wed, 16 June 2010 09:45 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
I'm not creating the view in the other schema, its in my own.

Effectively its working around the fact that objects granted via roles cant have views put over them.

I agree its a thoroughly grey (at best) area in terms of security.


As background essentially we have a warehouse type schema which hold the core tables that users query and I was hoping to set up some views in my own schema over these to help out newbies in my team with common joins/avoiding pitfalls etc. I realise this should be taken up with the DBAs and done properly etc but the relationship/engagement model isnt what it should be to the point they try and charge us extra for doing their job but I digress. Short version is there's a lot wrong and this is the easiest solution I've the power to make happen.

Of course being unable to create views directly I inadvertantly discovered it could be done via a link and wondered why. Seemed highly irregular Smile

Guess its just one of these things due to the tables being granted via roles rather than directly.
Re: Views, DBLinks and Priv "Workarounds" [message #461098 is a reply to message #461097] Wed, 16 June 2010 09:52 Go to previous messageGo to next message
Michel Cadot
Messages: 66784
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a full test case including the database link creation as well as your users, roles and privileges.

Regards
Michel
Re: Views, DBLinks and Priv "Workarounds" [message #461100 is a reply to message #461098] Wed, 16 June 2010 09:59 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Will do, but it'll be tomorrow. I need to do this on my box at home, I lack the privs in the office Smile
Re: Views, DBLinks and Priv "Workarounds" [message #461101 is a reply to message #461097] Wed, 16 June 2010 10:01 Go to previous messageGo to next message
cookiemonster
Messages: 13778
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 #461102 is a reply to message #461101] Wed, 16 June 2010 10:05 Go to previous messageGo to next message
cookiemonster
Messages: 13778
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's spelled out in Oracle® Database Administrator's Guide Chapter 29 Distributed Database Concepts
Re: Views, DBLinks and Priv "Workarounds" [message #461108 is a reply to message #461101] Wed, 16 June 2010 10:19 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
cookiemonster wrote on Wed, 16 June 2010 16:01

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.



Could not agree more. Its something I raise on pretty much a weekly basis.


Its not security concerns, it would appear to be a total lack of will, to be perfectly blunt. And that's being polite Smile


Appreciate the link, will review tomorrow.
Re: Views, DBLinks and Priv "Workarounds" [message #461112 is a reply to message #461108] Wed, 16 June 2010 10:40 Go to previous messageGo to next message
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
Michel Cadot
Messages: 66784
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
Previous Topic: Single Sign On
Next Topic: need to create a user with access to only two tables
Goto Forum:
  


Current Time: Wed Jan 22 02:08:59 CST 2020