Grant access to different schema's

by New DBA

A user want to have select access to all schema's. How can we add him to all schema's. we don't want to give him dba role to him.

Comments for Grant access to different schema's

Average Rating starstarstarstarstar

Click here to add your own comments

Mar 02, 2015
Rating
starstarstarstarstar
Granting access to all schemas
by: Anonymous

You can't directly grant access to all objects in all schemas without granting system or sys privileges to the user. You would have to write a script to do this and this would have to be run by a dba (someone with the privilege to grant access to any object in tha database).

The question is though why does this user believe that he or she needs access to every table in the database even if it is only "seclet" access, Privileges should be granted on the basis only the minimum set of privileges required to do their job are granted to users.

The Oracle Databaae Administrators' Guide (see http://docs.oracle.com/database/121/ADMIN/secure.htm#ADMIN023) and teh 2 Day + Security Guide (http://docs.oracle.com/database/121/TDPSG/tdpsg_intro.htm#TDPSG94426) are useful manuals to read.

To go back to your question, though, something like the follwoing would create the script that when run would grant the required select privilege to the specified user.

SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO '||&grantee_user||';' FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('TABLE','VIEW') AND OWNER NOT LIKE '%SYS%';

You may prefer to specify which schemas should be included rtaher than which should be excluded. That depends on how many schemas tehre are in your database. The output from this script will look something like this:

GRANT SELECT ON APEX_040200.APEX_MIGRATION_REV_TABLES TO HR;
GRANT SELECT ON HR.REGIONS TO HR;

This can be saved to a file and then run from a privileged account.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.