How could I prevent a user from querying SELECT on other schemas in Oracle?

Question

I'm using Oracle 11g(11.2.0.1.0). I created about 20 tablespaces and users. And the data came by [Tools] - [Database Copy] on Oracle SQL Developer.

Somehow I found that a user is using SELECT query on the table from another schema. I want to prevent it for security. How should I change my grant options?

I read "Oracle Database Security Guide 11g Release 2(11.2)", but couldn't find the solution clearly.

Here are my creating and granting queries.

create user [USER_NAME]
identified by [PASSWORD]
default tablespace [TABLESPACE_NAME]
temporary tablespace TEMP;

grant create session,
      create database link,
      create materialized view,
      create procedure,
      create public synonym,
      create role,
      create sequence,
      create synonym,
      create table,
      drop any table,
      create trigger,
      create type,
      create view to [USER_NAME];

alter user [USER_NAME] quota unlimited on [TABLESPACE_NAME];

And here is the SELECT result of session_privs on a user.

SQL> SELECT * FROM session_privs;

PRIVILEGE
--------------------------------------------------------------------------------
CREATE SESSION
CREATE TABLE
DROP ANY TABLE
CREATE SYNONYM
CREATE PUBLIC SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE ROLE
CREATE PROCEDURE
CREATE TRIGGER

PRIVILEGE
--------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW
CREATE TYPE

13 rows selected.

I want to prevent a user from querying SELECT on other schemas.

For example, the following query

-- connected with USER1
SELECT *
  FROM USER2.table1;

should make an error like:

ERROR: USER1 doesn't have SELECT privilege on USER2.

---

Edited:

• Use appropriate terms (changed some words from tablespace to schema)
• Add SELECT result of session_privs on a user
• Add the method of how the data came by.

Answer 1

It was my fault. I missed that I had added some roles.

To copy data using Oracle SQL Developer, I added predefined roles to users. The roles were exp_full_database and imp_full_database.

According to Oracle Database Security Guide: Configuring Privilege and Role Authorization, exp_full_database contains these privileges:

• SELECT ANY TABLE
• BACKUP ANY TABLE
• EXECUTE ANY PROCEDURE
• EXECUTE ANY TYPE
• ADMINISTER RESOURCE MANAGER
• INSERT, DELETE, UPDATE ON SYS.INCVID, SYS.INCFIL AND SYS.INCEXP

and roles:

• EXECUTE_CATALOG_ROLE
• SELECT_CATALOG_ROLE

Those roles are not required now. So the answer is removing them from users.

REVOKE exp_full_database, imp_full_databsae FROM USER1;

And I get the result I wanted.

-- connected with USER1
SELECT * FROM USER2.TABLE1;

ERROR at line 1:
ORA-01031: insufficient privileges