| problem in creating view [message #530605] |
Wed, 09 November 2011 05:22  |
emadnabil
Messages: 179
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 #530614 is a reply to message #530607] |
Wed, 09 November 2011 06:10  |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #530624 is a reply to message #530605] |
Wed, 09 November 2011 06:56 |
emadnabil
Messages: 179
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 |
 |
Littlefoot
Messages: 21823
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 |
cookiemonster
Messages: 13962
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 |
John Watson
Messages: 8962
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 #530633 is a reply to message #530628] |
Wed, 09 November 2011 07:56 |
|
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
