-
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.
It is better to ask and appear ignorant, than to remain silent and remain ignorant.
Oracle OCP DBA 9i,
C++, Java developer
-
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
Last edited by Sameer; 11-27-2002 at 05:33 AM.
-
Sameer, your solution is not realy a sollution. Couple of remarks:
1.
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.
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.
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....
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
It is better to ask and appear ignorant, than to remain silent and remain ignorant.
Oracle OCP DBA 9i,
C++, Java developer
-
sorry about my typos...
I have created an object type, created a function that returns the object type wrapped it.
Cheers.
It is better to ask and appear ignorant, than to remain silent and remain ignorant.
Oracle OCP DBA 9i,
C++, Java developer
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|