Allow SELECT to a user in all schema tables

Allow Select To A User In All Schema Tables.

Sometimes, you want to grant SELECT to all tables that have a schema or user associated with another user. Unfortunately, Oracle does not directly support using a single SQL statement.

To work around this, you can select all the table names of a user (or a schema) and assign the privilege of the SELECT object in each table to a donor. The following stored method explains the concept:

Creating A User In Oracle Database

CREATE USER TEST_USER IDENTIFIED BY TEST_123
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP
    PROFILE DEFAULT
    ACCOUNT UNLOCK;

The Following Stored Method Explains The Concept

CREATE PROCEDURE grant_select (username VARCHAR2, grantee VARCHAR2)
AS
BEGIN
    FOR r IN (SELECT owner, table_name
                FROM all_tables
               WHERE owner = username)
    LOOP
        EXECUTE IMMEDIATE   'GRANT SELECT ON '
                         || r.owner
                         || '.'
                         || r.table_name
                         || ' to '
                         || grantee;
    END LOOP;
END;

This Example Allows User Dw To Access Select Objects From All Tables In The User Ot:

EXEC grant_select('OT','TEST_USER');

2nd Method

DECLARE
    o_type            VARCHAR2 (60) := '';
    o_name            VARCHAR2 (60) := '';
    o_owner           VARCHAR2 (60) := '';
    l_error_message   VARCHAR2 (500) := '';
BEGIN
    FOR R
        IN (  SELECT owner, object_type, object_name
                FROM all_objects
               WHERE owner = 'TEST_USER' AND object_type IN ('TABLE', 'VIEW')
            ORDER BY 1, 2, 3)
    LOOP
        BEGIN
            o_type := r.object_type;
            o_owner := r.owner;
            o_name := r.object_name;
            DBMS_OUTPUT.PUT_LINE (o_type || ' ' || o_owner || '.' || o_name);

            EXECUTE IMMEDIATE   'grant select on '
                             || o_owner
                             || '.'
                             || o_name
                             || ' to USERNAME';
        EXCEPTION
            WHEN OTHERS
            THEN
                l_error_message := SQLERRM;
                DBMS_OUTPUT.PUT_LINE (
                       'Error with '
                    || o_type
                    || ' '
                    || o_owner
                    || '.'
                    || o_name
                    || ': '
                    || l_error_message);
                CONTINUE;
        END;
    END LOOP;
END;
/

3rd Method

CREATE OR REPLACE PROCEDURE GRANT_SELECT (to_user IN VARCHAR2)
AS
    CURSOR ut_cur IS SELECT table_name FROM user_tables;

    RetVal    NUMBER;
    sCursor   INT;
    sqlstr    VARCHAR2 (250);
BEGIN
    FOR ut_rec IN ut_cur
    LOOP
        sqlstr :=
            'GRANT SELECT ON ' || ut_rec.table_name || ' TO ' || to_user;
        sCursor := DBMS_SQL.open_cursor;
        DBMS_SQL.parse (sCursor, sqlstr, DBMS_SQL.native);
        RetVal := DBMS_SQL.execute (sCursor);
        DBMS_SQL.close_cursor (sCursor);
    END LOOP;
END grant_select;

🔗 Demo Application-
URL- Demo Application
Username - demo, Pass- demo

I hope everyone will like it. Please watch the full video,
Comment on any of your problems, I will try my best to solve the problem, In-Shah Allah. Everyone's cooperation is desirable. Visit my blog site, new technology related videos, you will get different types of tutorials of Oracle Apex, and hopefully, you can use them in your daily work.
Please stay tuned by subscribing to the YouTube channel, and encourages new videos to be uploaded.
=================
Visit my site to get more collaborative posts about Oracle Apex and subscribe to my YouTube channel. Thanks.
Comment on any of your issues, I will try my best to solve the problem, In-Shah Allah. Everyone's cooperation is desirable.
Visit my blog site, new technology-related videos, you will get different types of tutorials of Oracle Apex, and hopefully, you can use them in your daily work.
==============================

🙍🏾‍ Md jABER HOSSEN
📲 Mobile- +8801760688286
📨 Email- jaberit786@gmail.com
🌐 FB- facebook.com/mdjaber.hossen1
Please Subscribe to My Channel

Many thanks for visiting the site.