Home » SQL & PL/SQL » SQL & PL/SQL » View tailoring
View tailoring [message #646742] Thu, 07 January 2016 13:50 Go to next message
geordibbk
Messages: 11
Registered: August 2015
Location: London
Junior Member
Oracle states that
'Views are very powerful because they allow you to tailor the presentation of data to different types of users.'.

Could someone expand on this..

For instance if I had 1 View (View1) how would I tailor View1 to display all rows for one specific USER (User1) and display only some rows for another USER (User2).

Both User1 and User2 would be executing the same sql statement 'SELECT * FROM View1'.

Re: View tailoring [message #646743 is a reply to message #646742] Thu, 07 January 2016 13:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
geordibbk wrote on Thu, 07 January 2016 11:50
Oracle states that
'Views are very powerful because they allow you to tailor the presentation of data to different types of users.'.

Could someone expand on this..

For instance if I had 1 View (View1) how would I tailor View1 to display all rows for one specific USER (User1) and display only some rows for another USER (User2).

Both User1 and User2 would be executing the same sql statement 'SELECT * FROM View1'.



Just as is done for the Data Dictionary USER_* views
The underlying SELECT contains WHERE OWNER = USER
Re: View tailoring [message #646807 is a reply to message #646743] Sat, 09 January 2016 21:24 Go to previous messageGo to next message
vittalg
Messages: 1
Registered: January 2016
Junior Member
Hi

Views in general help in

a. Column Hiding
b. Row hiding
c. Converting complex queries/joins into a view so that if the same query is accessed at multiple location you can replace by the view

You need to have separate view for each user and grant the same to that user

I don't think you can have one common view and have two different snapshots of data to be looked at by two different users at the same time

vittalg
Re: View tailoring [message #646808 is a reply to message #646807] Sat, 09 January 2016 22:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
vittalg wrote on Sat, 09 January 2016 19:24
Hi

I don't think you can have one common view and have two different snapshots of data to be looked at by two different users at the same time

vittalg


Just as is done for the Data Dictionary USER_* views
The underlying SELECT contains WHERE OWNER = USER

Each schema will only see their own tables when SELECT * FROM USER_TABLES
Re: View tailoring [message #646812 is a reply to message #646807] Sun, 10 January 2016 01:49 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum, vitalg. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

You can do this sort of thing:
orclz>
orclz> create view scott.emp_v as select * from scott.emp
  2  where
  3  sys_context('userenv','current_user')=ename or
  4  sys_context('userenv','current_user')='SYSTEM';

View created.

orclz>
orclz> conn scott/tiger
Connected.
orclz> select * from emp_v;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 1987-04-19:00:00:00       3000                    20

orclz> conn system/oracle
Connected.
orclz> select * from scott.emp_v;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17:00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20:00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22:00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02:00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28:00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01:00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09:00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19:00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17:00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08:00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23:00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03:00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03:00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23:00:00:00       1300                    10

14 rows selected.

orclz>

Re: View tailoring [message #648095 is a reply to message #646812] Tue, 16 February 2016 08:25 Go to previous messageGo to next message
geordibbk
Messages: 11
Registered: August 2015
Location: London
Junior Member
Thanks John for the great example illustrating how a single view is automatically tailored.

Just wondering if there is any advantage to using :
sys_context('userenv','current_user')

Instead of :
USER

Thanks
Re: View tailoring [message #648104 is a reply to message #648095] Tue, 16 February 2016 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From the documentation:

Quote:
CURRENT_USER: The name of the database user whose privileges are currently active. This may change during the duration of a session to reflect the owner of any active definer's rights object. When no definer's rights object is active, CURRENT_USER returns the same value as SESSION_USER. When used directly in the body of a view definition, this returns the user that is executing the cursor that is using the view; it does not respect views used in the cursor as being definer's rights.


And there:

Quote:
USER returns the name of the session user (the user who logged on)...


So it depends on the context:
SQL> create or replace procedure p
  2  is
  3  begin
  4    dbms_output.put_line('USER='||user);
  5    dbms_output.put_line('SESSION_USER='||sys_context('USERENV','SESSION_USER'));
  6    dbms_output.put_line('CURRENT_USER='||sys_context('USERENV','CURRENT_USER'));
  7  end;
  8  /

Procedure created.

SQL> grant execute on p to test;

Grant succeeded.

SQL> conn test/test
Connected.
TEST> set serveroutput on
TEST> exec michel.p
USER=TEST
SESSION_USER=TEST
CURRENT_USER=MICHEL

PL/SQL procedure successfully completed.

Re: View tailoring [message #648159 is a reply to message #648104] Wed, 17 February 2016 15:39 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
However USER is the logged on user, current_user is just the schema that the view resides in and then what is the point. His example should be
create view scott.emp_v as select * from scott.emp
where sys_context('userenv','SESSION_USER')=ename 
or sys_context('userenv','SESSION_USER')='SYSTEM';

or 

create view scott.emp_v as select * from scott.emp
where user =ename 
or user = 'SYSTEM';


It's also handy if you have an application that is used for different departments or companies. The Views can use

create view master_table_v as
select * 
from master_table
where company = sys_context('USERENV', 'CLIENT_INFO');


Where the CLIENT_INFO was set by the application using the dbms_application_info.set_client_info('TEST'); function. This allows you to partation your data without any coding. Oracle also has database tools that handle the filtering directly on the tables by directly modifying the update/delete and select commands directly in the optimizer. (fine access controls)
Previous Topic: Getting Desire Data According to the interval...
Next Topic: query start_date and end_Date
Goto Forum:
  


Current Time: Fri Apr 26 05:23:23 CDT 2024