Granting access to another database schema

There would be situations where you need to grant access to another schema in the database to manage or read from a table/view/trigger/index/synonym/sequence, etc.

The first rule to note is that whoever creates a table is the owner of that database object. For instance, if a UserA creates a table, UserA is the owner of that object. Thus, UserB cannot access the object except granted access on it or if such user has DBA privilege.

Type of access on database objects

These are the types of privileges you can grant a database schema on any database object. For instance, UserA can grant all these privileges to UserB (with admin option).

ALTER  
DELETE
INDEX
INSERT
UPDATE
SELECT
REFERENCES
ON COMMIT REFRESH
QUERY REWRITE
DEBUG
FLASHBACK

You can also grant privilege WITH GRANT OPTION. With grant option empowers the user to also transfer the privilege or grant same privilege to another user in the database. 

Where to confirm ownership

To check ownership of database objects, you need to query data dictionary views that store information about database objects. The key one are:

DBA_TABLES
DBA_VIEWS
DBA_SYNONYMS
ALL_TABLES
ALL_VIEWS
ALL_SYNONYMS
USER_TABLES
USER_VIEWS
USER_SYNONYMS

All objects starting with 'DBA_' are all objects in the database. Objects starting with 'ALL_' are objects a particular schema has privilege on while those starting with 'USER_' are those the particular user owns.

How to grant access

In order to allow read only access on tables, issue this command:
 

SQL> grant select on  pharmacist.license to  nurse;

Granting access to all database objects for another schema

Yes, it is possible to grant access to all tables owned by a certain user to another database schema. You can run a script to pull all the tables and grant the privilege immediately. Use the script below and adjust the schema name as appropriate.

Generating a script to grant readonly access to another database schema 

set heading off
set pagesize 1000
set feedback off

spool grant_select_table.sql
select 'GRANT SELECT ON '||table_name||' TO NURSE;' from user_tables;
spool off

spool grant_select_views.sql
select 'GRANT SELECT ON '||view_name||' TO NURSE;' from user_views;
spool off

select view_name from dba_views where owner='PHARMACIST';

[oracle@ ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 27 14:06:36 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @grant_select_table.sql
SQL> @grant_select_view.sql

connect NURSE/****@patients

SQL> select count(*) from pharmacist.license;
COUNT(*)
----------
     10512

SQL> select count(*) from pharmacist.note;
COUNT(*)
----------
     25828

After running the script, Nurse can now select on pharmacist's tables and views.