Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 988
  • Last Modified:

How to restrict Oracle users from deleting rows in another schema?

I set up a brand new user in Oracle called Smith and give him "Connect" role only, default table space and quota to unlimited access to his table spaces.

There's no other system privileges or object privileges granted.

However, for some reason, if i log in as this user, i can delete rows from another schema, for example Doe, if i do this:

delete from Doe.testtable where testcolumn=1234;

How can I stop this?
  • 4
  • 4
  • 4
  • +1
10 Solutions
DavidSenior Oracle Database AdministratorCommented:
1.  Somehow you have given SMITH DELETE privilege on DOE's objects.  Instead of the CONNECT role, try replacing it with just CREATE SESSION.
2.  There may be a role granted to PUBLIC that allows such behaviour.  Not best practice.
3.  Confirm and report back which other roles or sys privs you have granted to SMITH.  
Check the grants on Doe.testtable  and see if your user has other privileges on the table (or that table is public).

The situation you are describing should not happen the way you are describing it. Check below code for an example:

connect / as sysdba;
create user user1 identified by hello;
grant connect, create table to user1;
create user user2 identified by hello;
grant connect to user2;
connect user1/hello;
create table testtable (testcolumn number);
insert into testtable values(123);
insert into testtable values(1234);
select * from testtable; -- this displays two rows
connect user2/hello;
delete from user1.testtable where testcolumn=1234; -- this gives an error

Open in new window

Mark GeerlingsDatabase AdministratorCommented:
Maybe Doe has done something like:
grant all on testtable to public;

I like to log in as SYS and run this script to add an object named: "table_privs" to the database.  Then I can:
select * from table_privs where table_name = 'TESTTABLE';
to see who (which users and/or roles) have grants on the table.

Here is the script:
(Feel free to change the lengths after the "substr" operators (like:10, 13, 24, etc.) to better fit your system if the values I used cause data to be truncated.)

create or replace view table_privs (GRANTEE,
as select substr(ue.name,1,24), substr(u.name,1,13),
 substr(o.name,1,30), substr(ur.name,1,13), substr(tpm.name,1,10),
decode(oa.option$, 1, 'YES', 'NO'), oa.sequence#
from sys.objauth$ oa, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue,
table_privilege_map tpm
where oa.obj# = o.obj#
and oa.grantor# = ur.user#
and oa.grantee# = ue.user#
and oa.col# is null
and u.user# = o.owner#
and oa.privilege# = tpm.privilege
and (userenv('SCHEMAID') in (oa.grantor#, oa.grantee#, o.owner#)
 or oa.grantee# = 1) -- grantee# 1 = "PUBLIC";
create public synonym table_privs for sys.table_privs;
grant select on table_privs to public;

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

PaperTigerAuthor Commented:
Yup, it seems all objects under Doe are PUBLIC.

How can I change them in one shot? I only want Doe to manage his own. 3

How do I change them back if it doesn't work?
DavidSenior Oracle Database AdministratorCommented:
One quick approach is to clear all privileges from PUBLIC, then recreate the ones worth keeping.  In part it depends upon how many other users used DOE.  Perhaps create one or more roles for DOE's objects, grant the roles, then REVOKE from PUBLIC.  If there are more than a handful, I'd go with dynamic SQL.
set pages 0 define off
spool <somefile>
SELECT 'REVOKE ' || privilege || ' ON DOE.' || table_name || ';'
    FROM ALL_TAB_PRIVS WHERE grantee = 'PUBLIC' AND grantor = 'DOE'
spool off
review and then execute your spooled file
>>How can I change them in one shot? I only want Doe to manage his own. 3

Doe will have access to his own tables, everything else needs to be specifically granted. You will have to evaluate what access other users need (i.e., user5 needs to be able to "select" from Doe.testtable_1234 but doesn't need to "delete")
PaperTigerAuthor Commented:
Here's what I got. Does this mean DOE only has 1 table set as PUBLIC???

I didn't set it - i was the only DBA here.

SQL> SELECT 'REVOKE ' || privilege || ' ON DOE.' || table_name || ';'
  2     FROM ALL_TAB_PRIVS WHERE grantee = 'PUBLIC' AND grantor = 'DOE';
REVOKE ALTER ON DOE.IP_TEST;                                              
REVOKE DELETE ON DOE.IP_TEST;                                            
REVOKE INDEX ON DOE.IP_TEST;                                              
REVOKE INSERT ON DOE.IP_TEST;                                            
REVOKE SELECT ON DOE.IP_TEST;                                            
REVOKE UPDATE ON DOE.IP_TEST;                                            
REVOKE REFERENCES ON DOE.IP_TEST;                                        

7 rows selected.

SQL> spool off;
DavidSenior Oracle Database AdministratorCommented:
One typo on my part, sorry.  Line 1 REVOKE should be REVOKE PUBLIC.  To cross check, the syntax is select * from all_tab_privs where table_schema = 'DOE'.
Mark GeerlingsDatabase AdministratorCommented:
It looks like that was the only table that Doe had granted rights on to 'PUBLIC'.  You  do not need to revoke the "select" rights, if you want to allow others to still read (but not be able to add, change or delete) the data in this table.
Mark GeerlingsDatabase AdministratorCommented:
You will have to add: "from PUBLIC" to each of those revoke commands.  They are incomplete now.  They should be like this:
PaperTigerAuthor Commented:
i am curious why it became public. Like I said, i was the only one creating them via our ERP tool. I looked at a few other users. it seems only recently created tables have PUBLIC on, but not old ones.
Mark GeerlingsDatabase AdministratorCommented:
That looks like a "feature" of your ERP tool to me.  Maybe a configuration setting got changed (by accident?) or maybe a patch was applied recently?
DavidSenior Oracle Database AdministratorCommented:
Those pesky electrons !  They never hold still, do they?
PaperTigerAuthor Commented:
all of you guys are right. so i just split them evenly. thank you so much for the help.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now