Forum Stats

  • 3,851,903 Users
  • 2,264,050 Discussions
  • 7,904,900 Comments

Discussions

Hello I want to restrict my users to only see parts of the database they should, how?

3027807
3027807 Member Posts: 10
edited Sep 15, 2015 3:12AM in General Database Discussions

Hi,

I am trying to restrict access to my database so the users can only see what they should only see.

For example, user Andrew should only be able to see students with a teacherid of 1 and a schoolid of 2. Where user Billy should only be able to access students with a teacherid of 3 and a schoolid of 4. User Charlie should only be able to access all students with a schoolid of 5.

At the moment, all users can access everything and I need to restrict these users access.

Can anyone help me with these and provide any solutions?

Thanks,

Rory

Answers

  • Unknown
    edited Sep 14, 2015 10:04PM
    I am trying to restrict access to my database so the users can only see what they should only see.
    
    
    

    Why? What PROBLEM are you trying to solve?

    Or is this a homework assignment or interview question?

    Your 'requirement' is for a VERY ADVANCED problem and is NOT something a beginner should tackle until they know the basics.

    If you REALLY want to get into it then start by reading about the Virtual Private Database functionality as discussed in the docs

    http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm

    What Is Oracle Virtual Private Database?

    Oracle Virtual Private Database (VPD) enables you to create security policies to control database access at the row and column level. Essentially, Oracle Virtual Private Database adds a dynamic WHERE clause to a SQL statement that is issued against the table, view, or synonym to which an Oracle Virtual Private Database security policy was applied.
    

    The topic is so advanced that it isn't possible to provide a 'simple' answer in a forum environment.

    You need to actually TRY implementing what the docs describe and then post questions if you run into problems.

    But the FIRST STEP is to learn WHAT the functionality is and HOW to use it. We can't do that for you in a forum.

  • Unknown
    edited Sep 14, 2015 10:00PM
    3027807 wrote:
    
    Hi,
    
    I am trying to restrict access to my database so the users can only see what they should only see.
    
    For example, user Andrew should only be able to see students with a teacherid of 1 and a schoolid of 2. Where user Billy should only be able to access students with a teacherid of 3 and a schoolid of 4. User Charlie should only be able to access all students with a schoolid of 5.
    
    At the moment, all users can access everything and I need to restrict these users access.
    
    Can anyone help me with these and provide any solutions?
    
    Thanks,
    Rory
    

    Is the application 3-tier?

    with Oracle everything is forbidden, except that which is explicitly GRANTED.

    If too many GRANT have been issued then they need to be REVOKED

  • 3027807
    3027807 Member Posts: 10
    edited Sep 14, 2015 10:05PM

    Creating a new school reporting system to finish my degree, its a matter of data integrity on the database.

  • 3027807
    3027807 Member Posts: 10
    edited Sep 14, 2015 10:09PM

    Okay,

    I had already looked over this but I was unsure if this was what I was really looking for.

    Howevr I do really appreciate it and will look over it in more depth now.


    Thanks,

    Rory

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    edited Sep 14, 2015 10:36PM

    VPD is the right way.

    A simpler "solution"  (that may meet your course requirements if the course didn't teach you about VPD) is to create different VIEWs and have the filtering done by the VIEWs and grant each user access to the corresponding view.  This is manageable with the three users you name but becomes more difficult if you have more users on whom different access controls need to be enforced.

    Hemant K Chitale

  • Kaleem Ahmed
    Kaleem Ahmed Member Posts: 243
    edited Sep 15, 2015 12:34AM

    You can use one of two approaches to restrict user to view data you wants:

    1. VPD

    2. Create separate Views for individuals and assign select privilege

  • WadhahDaouehi
    WadhahDaouehi Member Posts: 863 Gold Badge
    edited Sep 15, 2015 3:10AM

    Hi,

    I totally agree with ALL, that Oracle VPD is the right choice (Virtual Private Database (VPD), a feature of Oracle Database Enterprise Edition)!!! I recommend you to implement/test it on your Test database environment before implementing it on your Oracle Production database!!

    Some examples: https://oracle-base.com/articles/8i/virtual-private-databases

    Best regards

  • JohnWatson2
    JohnWatson2 Member Posts: 4,487 Silver Crown
    edited Sep 15, 2015 3:12AM

    VPD is straightforward to setup for your case. Here's a simple example:

    connect / as sysdba

    create table scott.names(c1 varchar2(10));

    insert into scott.names values('SCOTT');

    insert into scott.names values('SYSTEM');

    create or replace function sys.vpdfunc

    (object_schema varchar2,

    object_name varchar2)

    return varchar2

    as

    begin

    return 'c1 = sys_context(''userenv'',''current_user'')';

    end;

    exec dbms_rls.add_policy('SCOTT','NAMES','P1','SYS','VPDFUNC');

    conn scott/tiger

    select * from scott.names;

    conn system/manager;

    select * from scott.names;

    Or, if you want to top marks for your project, you can do it with Real Application Security. Here's a demo I recorded live the other day,

    Getting Started with Oracle 12c Real Application Security (RAS)

This discussion has been closed.