Schema Read privilage

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Gladiator
    #1

    Schema Read privilage

    Hi ,

    Can someone tell me how do i grant a Read privilage on all the tables
    in a schema X to a different user who is not the owner of the Schema .
    I dont want to grant him the Alterin privilage to the user.I just want
    the user to read the tables in a schema for which he is not the owner.

    I have more than 100 tables with different names. so it is difficult
    to give grant select on each table.

    Thanks in advance,
    Kamalnath.V.
    Tags: None
    • Serge Rielau
      #2
      Re: Schema Read privilage

      Gladiator wrote:
      Hi ,
      >
      Can someone tell me how do i grant a Read privilage on all the tables
      in a schema X to a different user who is not the owner of the Schema .
      I dont want to grant him the Alterin privilage to the user.I just want
      the user to read the tables in a schema for which he is not the owner.
      >
      I have more than 100 tables with different names. so it is difficult
      to give grant select on each table.
      Now, now.. it's not THAT hard.... (untested)

      db2 "SELECT 'GRANT SELECT ON "' || tabschema || '"."' || tabname || '"
      TO USER joe' FROM SYSCAT.TABLES WHERE tabschema = 'JILL'" grant.ddl
      db2 -tvf grant.ddl

      Cheers
      Serge
      --
      Serge Rielau
      DB2 Solutions Development
      IBM Toronto Lab

        Comment

        • Tomas
          #3
          Re: Schema Read privilage

          I would also add a -x to the db2 command generating the grants, to
          avoid having the header and footer lines fail when you go to execute
          it later:

          db2 -x "select 'grant select..." grant.ddl
          db2 -tvf grant.ddl

          /T

          On Mar 18, 10:15 am, Serge Rielau <srie...@ca.ibm .comwrote:
          Gladiator wrote:
          Hi ,
          >
           Can someone tell me how do i grant a Read privilage on all the tables
          in a schema X to a different user who is not the owner of the Schema .
          I dont want to grant him the Alterin privilage to the user.I just want
          the user to read the tables in a schema for which he is not the owner.
          >
          I have more than 100 tables with different names. so it is difficult
          to give grant select on each table.
          >
          Now, now.. it's not THAT hard.... (untested)
          >
          db2 "SELECT 'GRANT SELECT ON "' || tabschema || '"."' || tabname || '"
          TO USER joe' FROM SYSCAT.TABLES WHERE tabschema = 'JILL'" grant.ddl
          db2 -tvf grant.ddl
          >
          Cheers
          Serge
          --
          Serge Rielau
          DB2 Solutions Development
          IBM Toronto Lab

            Comment

            • Gladiator
              #4
              Re: Schema Read privilage

              On Mar 18, 12:36 pm, Tomas <thal...@gmail. comwrote:
              I would also add a -x to the db2 command generating the grants, to
              avoid having the header and footer lines fail when you go to execute
              it later:
              >
              db2 -x "select 'grant select..." grant.ddl
              db2 -tvf grant.ddl
              >
              /T
              >
              On Mar 18, 10:15 am, Serge Rielau <srie...@ca.ibm .comwrote:
              >
              Gladiator wrote:
              Hi ,
              >
              Can someone tell me how do i grant a Read privilage on all the tables
              in a schema X to a different user who is not the owner of the Schema .
              I dont want to grant him the Alterin privilage to the user.I just want
              the user to read the tables in a schema for which he is not the owner.
              >
              I have more than 100 tables with different names. so it is difficult
              to give grant select on each table.
              >
              Now, now.. it's not THAT hard.... (untested)
              >
              db2 "SELECT 'GRANT SELECT ON "' || tabschema || '"."' || tabname || '"
              TO USER joe' FROM SYSCAT.TABLES WHERE tabschema = 'JILL'" grant.ddl
              db2 -tvf grant.ddl
              >
              Cheers
              Serge
              --
              Serge Rielau
              DB2 Solutions Development
              IBM Toronto Lab

              Hi Serge ,

              Thanks a lot for your help. It worked. That made my work easier.

              Kamalnath.V,
              DB2 DBA.

                Comment

                Previous template Next
                Working...