Hide view definitions

Hi,
I am facing a bit of a problem:
I have User A, which owns a table (T1) and a view on the table (V1).
I have User B, which has select privilege on A.V1 view.
If user B queries the ALL_VIEWS view, he can see the view definition.
I don't want user B to see the view text.
I tried creating a second view (V2) in User A's schema, that performs
a SELECT from V1, but the user can still see the query text in
ALL_VIEWS view.

How can i hide the view text from user B?

Thanks,
R.

Create SYNONYM for the view and grant SELECT priviledge on the SYNONYM to user B

Code:

---

SQL> connect myuser/mypass
Connect durchgeführt.
SQL>
SQL> create view sam as select * from helloworld where name like 'P%';

View created.

SQL> create synonym samv for sam;

Synonym created.

SQL> grant select on samv to scott;

Grant succeeded.
SQL> connect scott/tiger
Connect durchgeführt.
SQL>
SQL>
SQL> select * from myuser.samv where rownum = 1;
NAME
------------------------------
Production

SQL> select * from all_views where view_name = 'SAMV';

Es wurden keine Zeilen ausgewählt

---

Sameer, your solution is not realy a sollution. Couple of remarks:

1.

Quote:

---

Originally posted by Sameer

Code:

---

SQL> create view sam as select * from helloworld where name like 'P%';
View created.

SQL> create synonym samv for sam;
Synonym created.

SQL> grant select on samv to scott;
Grant succeeded.

---

---

You can't realy grant any privilege on a synonym. Synonyms don't have any method or functionalyty that can be granted. What you actualy executed with your "GRANT SELECT ON samv TO scott" is "GRANT SELECT ON myuser.sam TO scott". Sysnonym is not an object that something can be granted on, it is only an object through which something can be granted on another object.

2.

Quote:

---

Originally posted by Sameer

Code:

---

SQL> select * from myuser.samv where rownum = 1;
NAME
------------------------------
Production

---

---

Scott can get the same output by not using that synonym, he can query the view SAM directly:

Code:

---

SQL> select * from myuser.sam where rownum = 1;

NAME
----------
Production

---

3.

Quote:

---

Originally posted by Sameer

Code:

---

SQL> select * from all_views where view_name = 'SAMV';

Es wurden keine Zeilen ausgewählt

---

---

But this does not mean that you have hidden the code of the view MYUSER.SAM from scott! Check this:

Code:

---

SQL> connect scott/tiger
Connected.
SQL> select * from all_views where view_name = 'SAMV';

no rows selected

SQL> select view_name, text from all_views where view_name = 'SAM';
VIEW_NAME
------------------------------
TEXT
--------------------------------------------------
SAM
select "NAME" from helloworld where name like 'P%'

---

Vhat you realy need to do if you want SCOTT to be able to query the view SAM, yet he will not be able to view the code of the view SAM is the following:

- create another view SAMV that simply selects form SAM
- grant select on SAMV to scott
- (optionaly) create a synonym SAM through which scott will reference the view SAMV, though he will essentialy get the results from view SAM.

Code:

---

SQL> connect myuser/myuser
Connected.
SQL> select text from user_views where view_name = 'SAM';

TEXT
-----------------------------------------------------------
select "NAME" from helloworld where name like 'P%'

SQL> create view samv as select * from sam;

View created.

SQL> select * from sam;

NAME
----------
Production

SQL> grant select on samv to scott;

Grant succeeded.

SQL> create synonym scott.sam for myuser.samv;

Synonym created.

SQL> connect scott/tiger
Connected.
SQL> select * from sam;

NAME
----------
Production

SQL> select view_name, text from all_views where view_name like 'SAM%';

VIEW_NAME
------------------------------
TEXT
----------------------------------------
SAMV
select "NAME" from sam

---

So now SCOTT has the access to the contents of view SAM, yet he is not able to see the business logic incorporated in that view....

Hi Guys,
Thanks for your notes.
I have managed to come up with an idea.
I have create an object type, created a function the returns the object type wrapped it.
Then created a view based on the wrapped function.
The user querys the all_views and gets the select from the function, which he does not have permissions on and is wrapped anyway.

Thanks for you efforts.
R.

sorry about my typos...
I have created an object type, created a function that returns the object type wrapped it.


Cheers.

Quote:

---

Originally posted by jmodic
Sameer, your solution is not realy a sollution. Couple of remarks:
"NAME" from sam
.
.
So now SCOTT has the access to the contents of view SAM, yet he is not able to see the business logic incorporated in that view.... [/B]

---

yup!.. I agree, Thanks

Sameer