Oracle Schema Privileges Other Users I have a user with only CONNECT privileges and I'd like to know if RESOURCE would be enough to query objects in other schemas and create objects in my own schema based on that.

i.e.: create a view in my own schema by selecting tables from other schemas.

Would a SELECT ANY TABLE privilege be enough or does my user have to be granted specific object privileges for any action on another user?
A little late but this block will surely help someone.


Replace OWNER_SCHEMA_NAME with desired table owner schema.
Replace NEW_TARGET_SCHEMA_NAME in below script to the schema you want to give permissions.
Please take a look at the permissions below (which are placed as sample) before you execute this script.
Similarly, you can build a script for revoking permissions.


here goes the pl/sql block.

BEGIN
    FOR t IN (
        SELECT
            object_name,
            object_type
        FROM
            all_objects
        WHERE
            owner = 'OWNER_SCHEMA_NAME'
            AND   object_type IN (
                'TABLE',
                'VIEW',
                'PROCEDURE',
                'FUNCTION',
                'PACKAGE',
                'SEQUENCE'
            )
    ) LOOP
        IF
            t.object_type IN (
                'TABLE',
                'VIEW'
            )
        THEN
            EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, INSERT, DELETE ON OWNER_SCHEMA_NAME.'
            || t.object_name
            || ' TO NEW_TARGET_SCHEMA_NAME';

        ELSIF
            t.object_type IN (

                'SEQUENCE'
            )
        THEN
            EXECUTE IMMEDIATE 'GRANT SELECT ON OWNER_SCHEMA_NAME.'
            || t.object_name
            || ' TO NEW_TARGET_SCHEMA_NAME';

        ELSIF t.object_type IN (
            'PROCEDURE',
            'FUNCTION',
            'PACKAGE'
        ) THEN
            EXECUTE IMMEDIATE 'GRANT EXECUTE ON OWNER_SCHEMA_NAME.'
            || t.object_name
            || ' TO NEW_TARGET_SCHEMA_NAME';

        END IF;
    END LOOP;
END;