GRANT SELECT on all tables in a schema

Tmicheli-Oracle

User often are asking for a single statement to Grant privileges in a single step.

there are multiple workarounds for not have a GRANT SELECT on all table

LOOP    EXECUTE IMMEDIATE 'GRANT SELECT ON ' || x.table_name || ' TO <<someone>>'; END LOOP;  FOR x IN (SELECT * FROM user_tables)

or

declare
cursor c1 is select table_name from user_tables;
cmd varchar2(200);
begin
for c in c1 loop
cmd := 'GRANT SELECT ON '||c.table_name|| <<TO YOURUSERNAME>>;
execute immediate cmd;
end loop;
end;

Oracle could implement a schema level grant

GRANT SELECT ON <schema name> to <<user>>

This request is now referenced with ER: 16899440 - SCHEMA WIDE PRIVILEGES

Pravin Takpire

I think it is better not give grant all tables in schema in one go. It can be dangerous by security point of view.

regards

Pravin

Richard Harrison .

This has been needed for a long time - any workaround just ends up as a mess with ddl triggers and jobs or granting too high a level of privileges.

SQL Server even has the feature and Oracle doesn't!

grant select on schema to userx;

Franck Pachot

Hi,

The danger is that it allows to grant for objects that are not already there.

I prefer to have a procedure that does all the grants for existing objects. You deploy a new table. You deploy the grants with it.

Regards,

Franck.

Richard Harrison .

Hi,

The danger is that it allows to grant for objects that are not already there.

I prefer to have a procedure that does all the grants for existing objects. You deploy a new table. You deploy the grants with it.

Regards,

Franck.

Hi Franck,

That's exactly why i would want it - to grant for new objects i add rather than messing around with workaround solutions - i appreciate in live or very secure environments you might have to be careful. But in dev environments this would be very useful (at least for us...)

CHeers,

Rich

Franck Pachot

You are right about development environments. The lack of valid solution often leads to 'select any' privileges which are bad.

It think it's not that obvious to implement as it will happen dynamically. Who will be the grantee? Does it need to be audited as a grant ?

The best workaround i know is using DDL triggers: Executing DDL in the DDL trigger | Dion Cho - Oracle Performance Storyteller

Anyway I voted up. It could be nice to have it at list through roles (each new schema 'xxx' having automatically a 'xxx_read' role for example, that we can grant to users)
Regards,

Franck.

Tmicheli-Oracle

We, Oracle are working on our internal process as to how to evaluate and prioritize the IDEAS submitted.  But the more votes obviously the more priority we will put on the request.  However votes/popularity alone will not determine the priority.

As we move through the process the IDEA will change stages: (not in flow order)

- Active

- Already Offered

- Archived

- Coming Soon

- For Future Consideration

- in Progress

- Partially Implemented

- Under Review

CloudDB

I uses mostly below method for achieve the same requirement

https://hiteshgondalia.wordpress.com/2013/12/27/configure-read-only-user-for-oam-purpose/

Thanks ...

Hitgon

BPeaslandDBA

Granting SELECT on an entire schema would sit better with me if there were then a way to DENY on individual tables.

Cheers,
Brian

50840890-fb4d-4b4c-a19e-defd0250de5e

Hi,

Log to SQLPLUS then type:

SQL> SET PAGESIZE 1000

SQL> SELECT 'GRANT SELECT, UPDATE, DELETE ON '||TABLE_NAME||' TO <USERNAME>;' FROM USER_TABLES;

This will generate the script you need. Just copy and past in the SQLPLUS editor and you will be set. : )

HRG

BluShadow

Hi,

Log to SQLPLUS then type:

SQL> SET PAGESIZE 1000

SQL> SELECT 'GRANT SELECT, UPDATE, DELETE ON '||TABLE_NAME||' TO <USERNAME>;' FROM USER_TABLES;

This will generate the script you need. Just copy and past in the SQLPLUS editor and you will be set. : )

HRG

50840890-fb4d-4b4c-a19e-defd0250de5e wrote:

Hi,

Log to SQLPLUS then type:

SQL> SET PAGESIZE 1000
SQL> SELECT 'GRANT SELECT, UPDATE, DELETE ON '||TABLE_NAME||' TO <USERNAME>;' FROM USER_TABLES;

This will generate the script you need. Just copy and past in the SQLPLUS editor and you will be set. : )

HRG

The OP isn't asking a question about how to do it, they are suggesting an "Idea" in the "Idea" area of the database space, to enhance the database with a new feature.