Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2007
    Posts
    3

    Unanswered: Informix - Grant Statement - newbie doubt

    Hi,

    I am newbie to Informix...

    I need to Grant a user select permission on all the tables in the database.

    How do I do this? I know I can do "Grant select on <TblName> to <User>"....but I have a lot of tables and dont want to do this manually for each and every table.

    Thanks,
    Karthik

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi,

    Run this query in dbaccess and write the output to a *.sql file. Make sure the single and double quotes are in exactly the same place or exactly the other way around.
    Code:
    SELECT 'grant select on ' || trim(tabname) || ' to "user" as "', trim(owner) || '";'
    FROM systables WHERE tabid > 99;
    Here the single quotes mark the literal strings that come in place of columns in the SELECT clause. The double quotes are regarded as stringmembers then and printed out.
    If your output comes with "(experssion)" as first field of every line (in UNIX) remove those from the *.sql file before running. This could also be done with following perl script, used as an output pipe:
    Code:
    #!/usr/bin/perl                                                       
                                                                          
    use warnings;                                                         
    use strict;                                                           
    sub quotes                                                            
    {                                                                     
        my $string = shift;                                               
        my $cnt = 0;                                                      
        my $idx = -1;                                                     
        if (($idx = index ($string, "\"")) >= 0)                          
        {                                                                 
            $cnt = $cnt + 1 + quotes(substr ($string, $idx + 1));         
        }                                                                 
        return $cnt;                                                      
    }                                                                     
                                                                          
    my $line = "";                                                        
    my $quote = 0;                                                        
                                                                          
    while (<>)                                                            
    {                                                                     
        my $i = (my @inp = split);                                        
        if ($i > 1)                                                       
        {                                                                 
            for (my $j = 1; $j < $i; $j++)                                
            {                                                             
                my $text = $inp[$j];                                      
                chomp $text if ($j == $i);                                
                $quote = quotes($line);                                   
                $text = " " . $text if ($line ne "" && ($quote % 2 == 0));
                $quote = 0;                                               
                $line = $line . $text;                                    
            }                                                             
        }                                                                 
        elsif ($line !~ /^$/)                                             
        {                                                                 
            print "$line\n";                                              
            $line = "";                                                   
        }                                                                 
    }                                                                     
    print "$line\n";
    For that make sure a perl interpreter is installed on your system (default on many UNIX/Linux systems) and this perlscript is stored in a PATH-directory. In dbaccess then just choose "Ouput" -> "To-pipe" and fill-in: "scriptname > filename.sql". Finally run the filename.sql in dbaccess.

    Regards

  3. #3
    Join Date
    Jul 2007
    Posts
    3
    Thanks! Will try this and let you know if I have any problems....

Posting Permissions

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