11 Replies Latest reply: Nov 15, 2006 10:40 AM by 518964 RSS

    grant select on tables

    518964
      hello,

      can you please inform me if i can create a script that dynamiccally grants access on tables to users.

      Obviously the following statement does not work but i want to generate something similar to it.

      grant select on
      (select table_name from user_tables)
      to a_user

      thank you in advance
        • 1. Re: grant select on tables
          488266
          if you mean from sqlplus:
          select 'grant select on '||table_name||' to user_name;' from user_tables;
          will create list of statements then spool to file
          and run the script.

          Cheers
          S.
          • 2. Re: grant select on tables
            518964
            dear Sahar

            i am already using this method.

            However i want to find out if there is a way of directly granting access to users via one sql statement such as the one (obviously it does not work as it is) that i provided in my exampe.

            a method like the one i am trying to find out if it exists will limit the time effort needed for similar tasks . (e.g grant select on views, directories packages etc).
            • 3. Re: grant select on tables
              488266
              well the way i do it
              is i have a scripts that create the table to be granted as
              I wrote above and run the created script.
              very fast , simple and clean ...:)
              or you can write a store procedure if u want do use
              dynamic sql for the above ( I personally don;t see much point in that )

              Cheers
              S.
              • 4. Re: grant select on tables
                388131
                Hi Costas,

                Have a look at dynamic SQL for this if you really feel it is required to do such things.
                I never did that. I always had roles to which I added new tables manually.

                Greetings,
                Guido
                • 5. Re: grant select on tables
                  518964
                  Dear Guido ,

                  Is there a good tutorial or a link from which i may have a look?

                  So far i was working manually having the appropriate roles as you did.

                  However there is a case where an external user shall have only select priviledges on my database. since the database changes frequently (tables, views,sequences, packages) i want to grant priviledges dynamically without having to create manually the scripts all the time.
                  doing the job manually is error-prone according to my opinion and needs to be done very carefully.

                  any way tnx for your response.
                  • 6. Re: grant select on tables
                    488266
                    Hi Costas
                    Dynamic sql is part of plsql,
                    as I mentioned above you will need to create pl sql block for it
                    any way in the pl sql guide below you will find the dynamic syntax

                    http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm

                    Cheers
                    S.
                    • 7. Re: grant select on tables
                      518964
                      tnx Sahar
                      • 8. Re: grant select on tables
                        465815
                        Hey,
                        try that:
                        begin
                        for i in (select table_name from all_tables) loop
                              execute immediate 'grant select on ' || i.table_name || ' to ' || user_name;
                        end loop;
                        end;
                        /
                        Amiel.
                        • 9. Re: grant select on tables
                          518964
                          tnx Amie D.

                          i've already created the required procedure.
                          • 10. Re: grant select on tables
                            JustinCave
                            Note that you can also create a DDL trigger that will submit a job that grants different users/ roles different privileges on newly created tables depending on logic you write. That can reduce the administrative overhead and is a life-saver when you have a third-party application that insists on dynamically creating new tables.

                            Justin
                            • 11. Re: grant select on tables
                              518964
                              justin can you please elaborate on your comment please?

                              i 've not created a ddl trigger before and definately it will be really interesting to try this out