How can i to GRANT in a SCHEMA to a user

Hi Sachin, I think we can not use a simple scrit like that for this, so i think we hace to write it inside a little bit more complex one like this example:

Grant SELECT on all tables in a schema to a user

Sometimes, you want to grant SELECT on all tables which belong to a schema or user to another user. Unfortunately, Oracle doesn’t directly support this using a single SQL statement.

To work around this, you can select all table names of a user (or a schema) and grant the SELECT object privilege on each table to a grantee.

The following stored procedure illustrates the idea:

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;

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ramón Miguel Quintana Rosa5y ago
Hi Sachin, I think we can not use a simple scrit like that for this, so i think we hace to write it inside a little bit more complex one like this example:

Grant SELECT on all tables in a schema to a user

Sometimes, you want to grant SELECT on all tables which belong to a schema or user to another user. Unfortunately, Oracle doesn’t directly support this using a single SQL statement.

To work around this, you can select all table names of a user (or a schema) and grant the SELECT object privilege on each table to a grantee.

The following stored procedure illustrates the idea:
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;
0
Sachin Singh5y ago
Grant Execute On Schema::[schema_name] To [user_name]
0