Forum Stats

  • 3,825,758 Users
  • 2,260,558 Discussions
  • 7,896,662 Comments

Discussions

GRANT SELECT on all tables in a schema

Tmicheli-Oracle
Tmicheli-Oracle Member Posts: 24 Red Ribbon
edited Jun 26, 2018 2:02PM in Database Ideas - Ideas

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

4a385ad7-27cb-4475-b1ce-a655372fb18fRichard Harrison .User259623 -OracleGeeky NerdmanFranck PachotmschmidtChris Antogniniuser481799user11980779Der BaboHans-Martin RuffTom321rajeev.y.ranjan-OracleSreesankar GMCarsten KaftanChandrakaanth RamamurthyMike Kutzvinaykumar2pankajrangaAndreas HuberPavel_pmarkmevansZlatko Sirotickt1Manish ChaturvediSven W.ZedDBAuser10212775caadecarvalhoBrian BakulactriebLothar FlatzEric_BedardborneselcarajandbJagadekarabhagatsinghulohmannVivek12-OracleMartin Preissabhinivesh.jainKiran PawarRodrigo Jorge DBArohanwaliasysassysdbaWilliam RobertsonDuncanG2957114Shaamil_ASHGeert GruwezMaciej TokarshiwoJ.Schnackenberg9919011003576Sergey KlimovPkSrinivasan SeJossue2988806user3578253_LC_LudovicoCaldaraPini DibaskT.S.user12218407Martin LutzAjay Vijayanmbe7pbredaErik Trip - Darwin ITApexBineOzgur Umut VurgunIT-HILLSInoLChizzle Packblkrishnarao1880712mathguyritan2000Daniel HillingerMKJ109302791483608N.B.HarbourGhostCraig ElliottLaurens WagemakersZohar ElkayamDavid Krch-Oracleuser9177712rvstuckedavemmJon TheriaultsensoftAparna Dutta-OracleNimish GargMike RipleyMatheus BoesingBluShadowAJ3203870KlausBUwe Küchler [OC]FecoExemptorRaphaelS7c6dc8b3-6220-4d52-b152-3cf8bdfb09323157330MikeLBrownuser7422517Gbenga AjakayeGareth.S.oraDavidMcWhinnieBlagyHristovauser12156491Andrew Fomin.Laurent SchneiderN BourgesrobhendrikxGunther PippèrrNiels HeckerTHEinternetIcaro.S44162646-10b4-4012-86e7-c5f973bfe35bewelinpuser107148791244776Raymond Lacb9b6fc-8b29-4a29-85dc-268900b5e0bc90cd643d-7f32-43ed-bdbf-371b64e32df8Dear DBA FrankJames G Kuser4812940Pete_ScottRobert Marzuser4670341User_JDNQ2User_OWAYSMettemusens207eb71eb-6423-4b87-a652-2d7f896256601920519pattonjgRokXValeriy Smirnov3418216Javier DurangoAdrian PngPKR1925872Francisco Miguel Biete Banonuser11691072Sean Waltersuser41126723683962Walt Lu-Oracleuser2168735Alex Lamar-OracleEvandro Lima-OracleEm SeaRui REmad Al-Mousauser439622335952683752704User_ASVDRUser_9QNANPat The DBA21800User910243567Jeffrey Kempmpershindirkvanhautef10r1n-OraclepnoskoMike NavickasbcarrizoMaher Alkhateeb1N82KazuhiroAlejolayaEdwin van Meerendonkjay-DKayKuser13493386ac91455e-f257-4fca-b5fa-0b83444c6b7c1587685Naeel MaqsudovSam_PUser_6L799Loïc Lefèvre-OracleNorbertKlMustafa_KALAYCI3807932philippe_eTatoRoL. Fernigriniuser10507778User_NHD7L3804354Alain Leg.Abhijit Gourwikki26msaratlijaFamilyGuy81Dejan T.amadisGerald Venzl-OracleThorsten KettnerFelix Manjarres8c36a0a2-eb83-4c9f-ac58-8ce031114b79user9355383Khalil DahhamSpeedChaseuser11970842Sayan Malakshinovuser12272351User_4CW94user12249850User_T2HV5User_PFCIYUser_JW2YJUser_1HAURsdstuberUser_G3UV3User_B5K15user3024260UW (Germany)andre.psantosdebebusUser_I5DFIUser_1JH88
258 votes

Under Review - Voting Still Open · Last Updated

«13456789

Comments

  • Pravin Takpire
    Pravin Takpire Member Posts: 1,762 Gold Trophy

    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

    rajeev.y.ranjan-OraclekrishnakripYaroslav Shkriuba
  • Franck Pachot
    Franck Pachot Member Posts: 912 Bronze Trophy

    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.

    Emad Al-MousaGbenga Ajakaye
  • Richard Harrison .
    Richard Harrison . Member Posts: 2,065 Gold Trophy

    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 Pachot3157330TatoRo3804354
  • Franck Pachot
    Franck Pachot Member Posts: 912 Bronze Trophy

    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
    Tmicheli-Oracle Member Posts: 24 Red Ribbon

    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
    CloudDB Member Posts: 1,059 Gold Badge

    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
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

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

    Cheers,
    Brian

    Kevan GellingN.B.J.Schnackenberg
  • 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
    BluShadow Member, Moderator Posts: 41,978 Red Diamond

    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.

    William RobertsonKarthick2003ApexBine