Solved

Deleting data from another user's schema

Posted on 2006-10-25
8
550 Views
Last Modified: 2013-12-11
The challenge that we have is to grant users update, insert and delete permissions on a table in another user's (USRALL) schema.

Here is what I've done:

1. Log in as the user USERALL and create an Oracle role through which the permissions will be given:

        create role ROL_EH_COREP identified using pack_authentication;
        Role created.

        grant select, insert, update, delete on EH_BII_REP_EXPECTED_DIM to ROL_EH_COREP;
        Grant succeeded.

2.  Assign this role to users:

        grant ROL_EH_COREP to johannes, soraya;
        Grant succeeded.

3.  Log in to the database as user JOHANNES and do a select from the table in the schema USRALL:

        alter session set current_schema=USRALL;
        Session altered.

4.  Now select data from the table just to show that there are data and that is selectable:

        select partition_key, underlying_table, unique_row_id from eh_bii_rep_expected_dims;

        (lots of rows are displayed, but removed from this question)

5.  Now delete data from it:

         delete eh_bii_rep_expected_dims;
         0 rows deleted.

There is no error message and it states that nothing is deleted.

What do I need to do to enable another schema's user to delete data in another schema?

Thanks
Jacques




0
Comment
Question by:soetseun
8 Comments
 
LVL 6

Expert Comment

by:DLyall
Comment Utility
Try "delete from usrall.eh_bii_rep_expected_dims" while logged on as JOHANNES.  I think its because you havn't created a synonym for the table "eh_bii_rep_expected_dims".  It expects to find it in the user "JOHANNES " current schema where it does not exist.

Log on as USRALL and issue "create synonym USRALL.eh_bii_rep_expected_dims for JOHANNES".
Then log on as user JOHANNES and try the delete again.
0
 

Author Comment

by:soetseun
Comment Utility
Hallo DLyall,

I have issued your create command and Oracle responded with:

ORA-00955: name is already used by an existing object

I don't really think that Oracle is looking for this table in the user's own schema, as I did change the 'current_schema' variable before issuing this command.

Any ideas on what this is, or other ideas perhaps?

Thanks
Jacques
0
 
LVL 10

Expert Comment

by:ravindran_eee
Comment Utility
Looks like a weird error..
Can u try truncating the table?
May be it will give insufficient priveleges error..
In that case please try the below steps..

create or replace procedure do_truncate
as
begin
   execute immediate 'truncate table t1';
end;
/

grant execute on do_truncate to 'user';

Now, as the JOHANNES user..

exec do_truncate;
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
just login as johannes and then try

delete from USRALL.EH_BII_REP_EXPECTED_DIM where rownum < 2;

Can you tell me what is the output for this.

Also do you have records in 'ALL_POLICIES'

select * from all_policies
where table_name ='EH_BII_REP_EXPECTED_DIM';

Thanks
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:soetseun
Comment Utility
Hallo all,

I will be at the client sight tomorrow, and post my findings to all your suggestions.

Thank you so far for the help and within 24 hours I will have proper feedback to all of you.

Thanks
Jacques
0
 

Author Comment

by:soetseun
Comment Utility
Both questions answered here in one post for all:

######################

Hallo ravindran_eee,

I have done as you requested with a bit of modification and here are the results:

1.  Created the do_truncate procedure:

 create or replace procedure do_truncate
 as
 begin
    execute immediate 'delete table  USRALL.EH_BII_REP_EXPECTED_DIMS where rownum < 2';
 end;

2.  Granted execute permission to the user JOHANNES:

     grant execute on do_truncate to JOHANNES;

3.  Login in as the user JOHANNES and execute this procedure:

     SQL> exec usrall.do_truncate;
     BEGIN usrall.do_truncate; END;
   
     *
     ERROR at line 1:
     ORA-00903: invalid table name
     ORA-06512: at "USRALL.DO_TRUNCATE", line 4
     ORA-06512: at line 1

This error message does not make sense, because when I copy and paste the table name from the creation command of the procedure, then I can select the data from this table as the user JOHANNES.  Why would it complain that the table does not exist?

######################

Hallo nav_kum_v,

Here is the output as requested:

1.  I logged in as the user JOHANNES and then immediately executed the following command:

      delete from USRALL.EH_BII_REP_EXPECTED_DIMS where rownum < 2;
      0 rows deleted.

2.  I then immediately executed the following command to show that there is data in the table and that the user JOHANNES can select it:

     select count(*) from USRALL.EH_BII_REP_EXPECTED_DIMS ;

       COUNT(*)
       ----------
       162

3.  I then selected, modified your SQL for correct syntax, from ALL_POLICIES (apologies for the eye-sore format of the output):

       select * from all_policies where object_name = 'EH_BII_REP_EXPECTED_DIMS';

OBJECT_OWNER                   OBJECT_NAME                    POLICY_GROUP                   POLICY_NAME                    PF_OWNER                       PACKAGE                        FUNCTION                       SEL INS UPD DEL CHK ENA STA
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ --- --- --- --- --- --- ---
USRALL                         EH_BII_REP_EXPECTED_DIMS       SYS_DEFAULT                    EH_BII_REP_EXPECTED_DIMS       USRALL                         PACK_POLICY                    GET_PK_RD_WS_PREDICATE         YES NO  YES YES NO  YES NO
USRALL                         EH_BII_REP_EXPECTED_DIMS       SYS_DEFAULT                    EH_BII_REP_EXPECTED_DIMS_A     USRALL                         PACK_POLICY                    GET_CTX_ACCESS_PREDICATE_RD_WS NO  YES YES YES YES YES NO


This really baffles me.  The user JOHANNES has permissions to do anything with this table (select, insert, update and delete), so what is the missing piece here?

Please help me, I am getting bolder by the day.

Thanks
Jacques




0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 400 total points
Comment Utility
Hi,
 
you have row level security enabled here. I mean there is a package in oracle DBMS_RLS. Refer to more information on oracle online documentation.

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_rls.htm#998100

Basically, a function will be created into which logic will be coded as per our requirement and attached as a policy to tables. This will help us to enforce row level security meaning we say that only those users can see the data and others cannot.

If you think, you need to delete the data somehow irrespective of all the above i am saying

just drop the policy for the table in question and then delete. It will get deleted for sure.

DBMS_RLS.DROP_POLICY('hr', 'employees', 'emp_policy');  -- this is to drop policy. If you drop it, then u need to create it using dbms_rls.add_policy(....);
DBMS_RLS.ENABLE_POLICY('hr', 'employees', 'emp_policy', FALSE) ; -- to enable or disable

In the given examples, put your schema name (USRALL),your table name and policy name. U can take this from your pasted output

Thanks
0
 

Author Comment

by:soetseun
Comment Utility
Thank you for the quick response;  I appreciate it.

The solution was thus:

exec dbms_rls.enable_policy('USRALL', 'EH_BII_REP_EXPECTED_DIMS', 'EH_BII_REP_EXPECTED_DIMS_A', FALSE);

After I disabled (or dropped) the policy, I could delete the rows in the table.

Thanks again.

Regards
Jacques
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now