How to grant access to all tables in a schema

Here in this article we will discuss on how to grant access to all tables in a schema in oracle database as well as we will focus on schema owners and application users oracle concept. This is required when you need access to some table in another schema or while setting up enterprise applications we need multiple users setup to run the application where one user will be the schema owner/application owner for patching and upgrade activities and one user will be the application user/ runtime user with limited privileges to connect to DB and run the application. So for this kind of app owner, app user setup we have to grant access to required or all tables in the target schema.

We can achieve the same in below steps.

Assumptions:

1. We will be using two users throughout the steps.
schemaowner1= The user who is owning the target schema and all tables having privileges to even drop tables.
appuser1= The user who needs access to the schemaowner1 user’s tables,views etc.
2. Here we are granting access to ‘FUNCTION’,’PACKAGE’,’PACKAGE BODY’,’PROCEDURE’,’SEQUENCE’,’TABLE’,’TYPE’,’TYPE BODY’,’VIEW’ object types you can reduce it by dropping the object type names from the queries.

3. Here we are granting select,insert,update,delete,execute privileges to appuser1, you can customize the grants by dropping the grants from the sql query itself.

Steps to create the public synonyms and grants :

1. Create a new user who will have access to all the tables of the parent schema/target schema.

define user_name = 'appuser1';
define password = 'secret123';
define target_schema_data_tablespace= 'tablespace_data';
CREATE USER &&user_name IDENTIFIED BY &&password
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON &&tablespace
PROFILE DEFAULT
ACCOUNT UNLOCK
/

2.  Create public synonyms for all tables in target schema using below query. This is required to make the tables available for other users to access.

2.a) Login to schema as the schema owner user schemaowner1 (use any DB tool like SQL developer) and run below query to get the SQL statements for all the tables.

note: you can customize below query to have public synonyms for perticular object type only like tables only or views only etc.

SELECT 'create public synonym '||rtrim(OBJECT_NAME)||' for '||user|| '.' ||rtrim(OBJECT_NAME)||';commit;'

FROM user_objects
WHERE object_type IN ('FUNCTION',
                         'PACKAGE',
                         'PACKAGE BODY',
                         'PROCEDURE',
                         'SEQUENCE',
                         'TABLE',
                         'TYPE',
                         'TYPE BODY',
                         'VIEW')
AND not exists (SELECT null
                FROM   dba_synonyms
                WHERE  owner = 'PUBLIC'
                  AND  dba_synonyms.synonym_name = user_objects.object_name
                  AND  dba_synonyms.table_owner  = user);

2.b) Export the output without header information and run them as system/sysdba user to create the public synonyms for all the tables,views, etc.

3. Provide grants or access rights to objects available in schemaowner1 via public synonyms created in previous step:

3.a) Login to schema as schemaowner1 user(use any DB tool like sql developer) and run below query to get the grants sql statements generated.

select 'grant select,insert,update,delete on schemaowner1.' ||object_name ||' to appuser1;' from dba_objects where object_type IN ('TABLE','VIEW') and owner='schemaowner1';

select 'grant execute on schemaowner1.' ||object_name ||' to appuser1;' from dba_objects where object_type IN ('FUNCTION','PACKAGE','PACKAGE BODY','PROCEDURE','TYPE','TYPE BODY') and owner='schemaowner1';

select 'grant select on schemaowner1.' ||object_name ||' to appuser1;' from dba_objects where object_type IN ('SEQUENCE') and owner='schemaowner1';

OR incase you dont like to have multiple files as output and run one by one, you can use below bulky query to generate all the grants into single file but do remember few grants will error out as not allowed. E.g. Execute on a table as pointed out in one of the comments.

SELECT 'grant select ON '||user|| '.' ||rtrim(OBJECT_NAME)||' to appuser1; commit;' 
|| ' grant insert ON '||user|| '.' ||rtrim(OBJECT_NAME)||' to appuser1; commit;' 
|| ' grant update ON '||user|| '.' ||rtrim(OBJECT_NAME)||' to appuser1; commit;' 
|| ' grant delete ON '||user|| '.' ||rtrim(OBJECT_NAME)||' to appuser1; commit;' 
|| ' grant execute ON '||user|| '.' ||rtrim(OBJECT_NAME)||' to appuser1; commit;'
FROM user_objects
WHERE object_type IN ('FUNCTION',
                         'PACKAGE',
                         'PACKAGE BODY',
                         'PROCEDURE',
                         'SEQUENCE',
                         'TABLE',
                         'TYPE',
                         'TYPE BODY',
                         'VIEW');

           3.b) Export the output without header info and run them as system/sysdba user.

4. Now to test you can execute any select statement on schemaowner1 tables as appuser1.

More information’s on Schema Owners and Application Users can be obtained here

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.