Home » RDBMS Server » Security » linux (Oracle 10.2.0.3)
linux [message #513481] Mon, 27 June 2011 15:30 Go to next message
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 #513484 is a reply to message #513481] Mon, 27 June 2011 15:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: linux [message #513486 is a reply to message #513481] Mon, 27 June 2011 15:46 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #513487 is a reply to message #513486] Mon, 27 June 2011 15:59 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
It is not just a view. It is a materialized view.
Re: linux [message #513490 is a reply to message #513487] Mon, 27 June 2011 16:21 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
So what?
Re: linux [message #513491 is a reply to message #513490] Mon, 27 June 2011 16:46 Go to previous messageGo to next message
ThomasG
Messages: 3211
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 Go to previous messageGo to next message
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 #513496 is a reply to message #513495] Mon, 27 June 2011 17:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>The above output shows a table and a materialized view with the same name. So the table is a materialized view?
The posted results are FAKED & a fraud.

OBJECT & OWNER would/should be UPPERCASE!

I doubt validity of this thread in toto.

You are obfuscating reality.
Re: linux [message #513498 is a reply to message #513496] Mon, 27 June 2011 18:23 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #513500 is a reply to message #513499] Mon, 27 June 2011 19:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>(ps - sorry, BS, I couldn't resist using a non-faked example that matched the original)
I do not care about schema or object names, but post SQL should be valid & REPRODUCIBLE code
Re: linux [message #513641 is a reply to message #513500] Tue, 28 June 2011 08:15 Go to previous message
ThomasG
Messages: 3211
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>
Previous Topic: Limit Sessions per User in RAC
Next Topic: delete oracle wallet
Goto Forum:
  


Current Time: Thu Mar 28 17:26:18 CDT 2024