| linux [message #513481] |
Mon, 27 June 2011 15:30  |
lg123
Messages: 225
Registered: August 2008
|
Senior Member |
|
|
The table and the view have the same name. Eg.,
table name -> abcd.efgh
view name -> abcd.efgh
How to grant/revoke privileges to the user in such a way that the user should access only the view and not the base table?
|
|
|
|
|
|
| Re: linux [message #513486 is a reply to message #513481] |
Mon, 27 June 2011 15:46  |
John Watson
Messages: 8989
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
orcl> create table efgh (c1 date);
Table created.
orcl> create view efgh as select * from efgh;
create view efgh as select * from efgh
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
orcl>
|
|
|
|
|
|
|
|
| Re: linux [message #513491 is a reply to message #513490] |
Mon, 27 June 2011 16:46 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Then abcd.efgh is NOT really a table, it's a materialized view.
A materialized view shows up in "user_tables" in the data dictionary, but it's still only one part of the materialized view, not a "real" table.
|
|
|
|
| Re: linux [message #513495 is a reply to message #513491] |
Mon, 27 June 2011 17:23 |
lg123
Messages: 225
Registered: August 2008
|
Senior Member |
|
|
SQL> select owner, object_name, object_type from dba_objects where owner in 'abcd';
OWNER OBJECT_NAME OBJECT_TYPE
---------- -------------------- ---------------
abcd efgh TABLE
abcd efgh MATERIALIZED VIEW
The above output shows a table and a materialized view with the same name. So the table is a materialized view?
|
|
|
|
|
|
| Re: linux [message #513498 is a reply to message #513496] |
Mon, 27 June 2011 18:23 |
lg123
Messages: 225
Registered: August 2008
|
Senior Member |
|
|
|
Nothing fake here. I cannot reveal the company details. Thats why I typed myself. The owner is in upper case only. But it doesnt mean that I am simply cheating.
|
|
|
|
| Re: linux [message #513499 is a reply to message #513498] |
Mon, 27 June 2011 19:30 |
John Watson
Messages: 8989
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I think I understand your problem. Try running this script:
conn / as sysdba
drop user "abcd" cascade;
grant dba to "abcd" identified by abcd;
select owner, object_name, object_type from dba_objects where owner in 'abcd';
create materialized view "abcd"."efgh" as select * from dual;
select owner, object_name, object_type from dba_objects where owner in 'abcd';
drop materialized view "abcd"."efgh";
select owner, object_name, object_type from dba_objects where owner in 'abcd';
create table "abcd"."efgh" as select * from dual;
select owner, object_name, object_type from dba_objects where owner in 'abcd';
create materialized view "abcd"."efgh" on prebuilt table as select * from dual;
select owner, object_name, object_type from dba_objects where owner in 'abcd';
Do you see? A materialized view is a logical structure referring to a container table. You can draw an analogy with a synonym. So it makes no sense to separate access to the MV from access to the table.
(ps - sorry, BS, I couldn't resist using a non-faked example that matched the original)
|
|
|
|
|
|
| Re: linux [message #513641 is a reply to message #513500] |
Tue, 28 June 2011 08:15 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
To illustrate that a MATERIALIZED view creates bot a materialized view and a table object:
SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM all_objects
2 WHERE object_name = 'FOOBAR';
no rows selected
SQL>
SQL> CREATE materialized VIEW foobar AS SELECT 1 FROM dual;
Materialized view created.
SQL>
SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM all_objects
2 WHERE object_name = 'FOOBAR';
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
FOOBAR TABLE
FOOBAR MATERIALIZED VIEW
SQL>
SQL> drop materialized VIEW foobar;
Materialized view dropped.
SQL>
SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM all_objects
2 WHERE object_name = 'FOOBAR';
no rows selected
SQL>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
