Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2001
    Posts
    40

    Unanswered: Follow up question to select from sysibm.sysdummy1

    So, I posted a question few weeks ago on how to get the system timestamp from the db2 database. And the answer was
    select current timestamp from sysibm.sysdummy1.

    The above query works like a charm. Gives me exactly what I need. But the problem is, I want this to work for a non-DBA user. It looks like unless I grant the DBADM role to a user, he is not able to select from the sysibm.sysdummy1 table. I definetly don't want to grant DBADM to this user. I don't want to do something like this either
    grant select on sysibm.sysdummy1 to testuser

    Is there any other system level privilige like connect or create tab or something like that (not a object level privilege) that I can grant to a user that will give him the right to select from sysibm.sysdummy1?
    Looks like DBADM does it, but that is off risky for me.

    Thanks

  2. #2
    Join Date
    Apr 2002
    Posts
    18
    You have a couple of alternatives here neither of which use SYSIBM.SYSDUMMY1.

    The following can be coded against a table which the user already has SELECT permissions on

    select current timestamp from user.table1 fetch first 1 row only

    This will work as CURRENT TIMESTAMP may be retrieved from any DB2 table not just SYSIBM.SYSDUMMY1. FETCH FIRST 1 ROW ONLY is coded as the query will return a result for each row on the table. SYSIBM.SYSDUMMY1 only has 1 row on it, hence no need for the extra FETCH predicates.

    Alternative is to effectively clone SYSIBM.SYSDUMMY1 and create a table with 0 or 1 rows on it. Then simply change the name of the table in the select from SYSIBM.SYSDUMMY1 to your new table name once you have given the user the relevant permissions on the new table.

    I don't understand why your not willing for your user to have SELECT permissions against SYSIBM.SYSDUMMY1 in the first place. Why not?

    Greg

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Follow up question to select from sysibm.sysdummy1

    Have you tried

    db2 values(current TIMESTAMP)

    to retrive the system timestamp .

    HTH

    Cheers

    Sathyaram


    Originally posted by ryanveach
    So, I posted a question few weeks ago on how to get the system timestamp from the db2 database. And the answer was
    select current timestamp from sysibm.sysdummy1.

    The above query works like a charm. Gives me exactly what I need. But the problem is, I want this to work for a non-DBA user. It looks like unless I grant the DBADM role to a user, he is not able to select from the sysibm.sysdummy1 table. I definetly don't want to grant DBADM to this user. I don't want to do something like this either
    grant select on sysibm.sysdummy1 to testuser

    Is there any other system level privilige like connect or create tab or something like that (not a object level privilege) that I can grant to a user that will give him the right to select from sysibm.sysdummy1?
    Looks like DBADM does it, but that is off risky for me.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •