Grant problems with Roles

[RichardF]

Hi,

Could anyone explain what is going on here.
Many thanks,
Rich.

connect sys/pass as sysdba

create role admin_role;
grant insert any table to admin_role;
grant select any table to admin_role;
grant execute any procedure to admin_role;

create user admin01 identified by password;
grant connect to admin01;
grant admin_role to admin01;
alter user admin01 default role connect, admin_role;

grant create table to scratch;
grant create any view to scratch;

-- 
connect scratch/pass 
create table abc
(
  IDENT VARCHAR2(30) PRIMARY KEY, 
  CREATED DATE DEFAULT SYSDATE NOT NULL
);

-- fails?  table or view does not exist
-- even though the role has select any priv
create view admin01.abc_v as 
select ident, created from scratch.abc;

-- however
connect sys/pass
grant select on scratch.abc to admin01;

connect scratch/pass
-- succeeds
create view admin01.abc_v as 
select ident, created from scratch.abc;

Programmers are tools for converting caffeine into code

 

[Thargy]

Yes,

scratch does not have the right to select from admin01 until specifically granted, therefore it can't be used to create the view. You're trying to create a view from something which is not "visible" to scratch at that point.

Granting create any table does NOT grant that user and/or role the right to select from any table.

Regards

T

 

[RichardF]

Hi,

This doesn't make sense to me.

Scratch does not need the right to select from admin01 because, if you look at the code, it is scratch who is trying to create the view in admin01 (and there are no tables in admin01 to select from).

Additionally scratch already has the SELECT ANY TABLE priv (it was just omitted from the above code).

Finally admin01 has select any table priv under the admin_role - ( admin01: select * from scratch.abc works ).

Richard

 

[RichardF]

ok, i found out why. From Oracle documentation on CREATE VIEW:


Prerequisites

To create a view in your own schema, you must have the CREATE VIEW system privilege. To create a view in another user's schema, you must have the CREATE ANY VIEW system privilege.

To create a subview, you must have the UNDER ANY VIEW system privilege or the UNDER object privilege on the superview.

The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. [COLOR=red yellow]The owner must be granted these privileges directly, rather than through a role.[/color]

To use the basic constructor method of an object type when creating an object view, one of the following must be true:

* The object type must belong to the same schema as the view to be created.
* You must have the EXECUTE ANY TYPE system privileges.
* You must have the EXECUTE object privilege on that object type.



Programmers are tools for converting caffeine into code

 

[ChrisHunt]

The fun continues when, having created your view, you try granting access to third parties:

http://www.extraconnections.co.uk/blog/fun-with-oracle-permissions

I wish there was an init.ora switch you could throw that would make roles work as you'd expect. As it is, they're really hamstrung for any kind of development work. Guess that's why almost every system I've ever worked on has a single schema to own all the objects.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd