Home » RDBMS Server » Server Administration » problem in creating view (database 10G)
problem in creating view [message #530605] Wed, 09 November 2011 05:22 Go to next message
emadnabil
Messages: 155
Registered: August 2007
Senior Member
Hii all

I have a strange problem when creating a view in user from another user

I have a user called "Cash_tst"

its syntax creation is

-- Create the user 
create user CASH_TST
  identified by ""
  default tablespace CASH
  temporary tablespace TEMP
  profile DEFAULT
  quota unlimited on cash;
-- Grant/Revoke object privileges 
grant connect to CASH_TST;
grant dba to CASH_TST;
grant resource to CASH_TST;

-- Grant/Revoke system privileges 
grant create any view to CASH_TST;
grant unlimited tablespace to CASH_TST;




I want to create a view

CREATE VIEW TAMER
AS SELECT *
FROM [b]AROFL[/b].RA_CUSTOMER_TRX_LINES_ALL_BEFO


"AROFL" is another user on the same database
when try to create the view "tamer" i got message of
"insufficent privilege"
although i granted "create any view" to the user "cash_tst"



Thanks



Re: problem in creating view [message #530606 is a reply to message #530605] Wed, 09 November 2011 05:26 Go to previous messageGo to next message
Littlefoot
Messages: 19817
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
AROFL should grant SELECT on its RA_CUSTOMER_TRX_LINES_ALL_BEFO table to CASH_TST.
Re: problem in creating view [message #530607 is a reply to message #530606] Wed, 09 November 2011 05:35 Go to previous messageGo to next message
cookiemonster
Messages: 11203
Registered: September 2008
Location: Rainy Manchester
Senior Member
And get rid of create any view. Create view is more that enough.
Any privs should generally be avoided.
Re: problem in creating view [message #530614 is a reply to message #530607] Wed, 09 November 2011 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And so get rid of RESOURCE and DBA, and also CONNECT, and also UNLIMITED TABLESPACE. (It is clear you don't know and understand what you do.)
Do NOT use predefined roles, create your own.

In the end, if the view is really the one you mention, it should be better (depending on your final needs) to create a synonym.

Regards
Michel

[Updated on: Wed, 09 November 2011 06:11]

Report message to a moderator

Re: problem in creating view [message #530619 is a reply to message #530614] Wed, 09 November 2011 06:30 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
Michel, your saying :get rid of UNLIMITED TABLESPACE.
which sys priv can grant to user compare to the UNLIMITED TABLESPACE?
Re: problem in creating view [message #530622 is a reply to message #530619] Wed, 09 November 2011 06:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Grant quota on user target tablespace.

Regards
Michel
Re: problem in creating view [message #530624 is a reply to message #530605] Wed, 09 November 2011 06:56 Go to previous messageGo to next message
emadnabil
Messages: 155
Registered: August 2007
Senior Member
I tried to select from user "Cash_tst"
the table RA_CUSTOMER_TRX_LINES_ALL_BEFO

SELECT *
FROM [b]AROFL[/b].RA_CUSTOMER_TRX_LINES_ALL_BEFO


it works fine

small hint
i found that when i make this grant to the user "cash_tst" the view is created with no problems
grant select on AROFL.RA_CUSTOMER_TRX_LINES_ALL_BEFO
to CASH_TST;

so why i should make this ???
and i don't want to make this grant
the user have default permission
Re: problem in creating view [message #530626 is a reply to message #530624] Wed, 09 November 2011 07:11 Go to previous messageGo to next message
Littlefoot
Messages: 19817
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
AROFL user owns data stored in RA_CUSTOMER_TRX_LINES_ALL_BEFO table.

If AROFL, as the owner, grants SELECT privileges to another user (or, optionally, PUBLIC), then that another user (in your case, CASH_TST) will be able to see them.

The fact that CASH_TST has a privilege to create a view is just one step. Without it, it wouldn't be able to create a view. Another step is a privilege to actually use someone else's data. I don't understand what do you mean by saying that AROFL shouldn't grant SELECT on his data to CASH_TST. How do you expect CASH_TST to see AROFL's data, then?

P.S. It is all described in the Security Guide. Perhaps you should take some time and read it.

[Updated on: Wed, 09 November 2011 07:13]

Report message to a moderator

Re: problem in creating view [message #530627 is a reply to message #530626] Wed, 09 November 2011 07:17 Go to previous messageGo to next message
cookiemonster
Messages: 11203
Registered: September 2008
Location: Rainy Manchester
Senior Member
For that matter - why bother creating the view?
It adds no value what so ever. You might as well just select direct from the table.

The reason you can select from the table without the select grant is because the DBA role has the SELECT ANY TABLE priv.
This doesn't work when creating the view as that only considers privs granted directly to the user, not via a role, when deciding what is allowed.

And for clarity - CREATE ANY VIEW allows you to create views in other schemas. It does not allow you to create views against any table, you need the correct select privs for that.

Why don't you go and read the docs on privileges instead of guessing what they do.
Re: problem in creating view [message #530628 is a reply to message #530626] Wed, 09 November 2011 07:20 Go to previous messageGo to next message
John Watson
Messages: 4806
Registered: January 2010
Location: Global Village
Senior Member
I've never understood the reason for this either. The DBA role includes SELECT ANY TABLE, but that isn't good enough to create a view: you need to be granted SELECT ANY TABLE (or SELECT on the one table) directly. Try this:

conn / as sysdba
drop user jon cascade;
grant dba to jon identified by jon;
conn jon/jon
select * from scott.dept;
create view v1 as select * from scott.dept;
conn / as sysdba
grant select any table to jon;
conn jon/jon
create view v1 as select * from scott.dept;

Here's how it works for me, on 11.2.0.2:
orcl> conn / as sysdba
Connected.
orcl> drop user jon cascade;

User dropped.

orcl> grant dba to jon identified by jon;

Grant succeeded.

orcl> conn jon/jon
Connected.
orcl> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

orcl> create view v1 as select * from scott.dept;
create view v1 as select * from scott.dept
                                      *
ERROR at line 1:
ORA-01031: insufficient privileges


orcl> conn / as sysdba
Connected.
orcl> grant select any table to jon;

Grant succeeded.

orcl> conn jon/jon
Connected.
orcl> create view v1 as select * from scott.dept;

View created.

orcl>

Clearly, gaining the provilege through a role is enough to query the table, but not enough to create a view on it.
Re: problem in creating view [message #530629 is a reply to message #530628] Wed, 09 November 2011 07:26 Go to previous messageGo to next message
cookiemonster
Messages: 11203
Registered: September 2008
Location: Rainy Manchester
Senior Member
Because roles can be enabled and disabled on the fly. The view, same as packages, procedures and functions needs to be able to work regardless.
Re: problem in creating view [message #530630 is a reply to message #530629] Wed, 09 November 2011 07:42 Go to previous messageGo to next message
John Watson
Messages: 4806
Registered: January 2010
Location: Global Village
Senior Member
Sussed. Thanks for that, CM.
Re: problem in creating view [message #530633 is a reply to message #530628] Wed, 09 November 2011 07:56 Go to previous message
Michel Cadot
Messages: 59815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I've never understood the reason for this either.


There is nothing to understand just to admit, this is how it has been defined in ISO standard.

Regards
Michel
Previous Topic: how to find unused columns
Next Topic: user quota issue (2 Merged)
Goto Forum:
  


Current Time: Fri Nov 28 01:14:50 CST 2014

Total time taken to generate the page: 0.06388 seconds