View tailoring [message #646742] |
Thu, 07 January 2016 13:50 |
|
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 #646812 is a reply to message #646807] |
Sun, 10 January 2016 01:49 |
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 #648159 is a reply to message #648104] |
Wed, 17 February 2016 15:39 |
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)
|
|
|