Skip to Content
avatar image
Former Member

wildcard in grant

Hello, do i have any way to use grant with one statement perms to a complete schema or database? or have i to do for every table that i want to others users can see, i have to do for all tables in schemas with +80 tables 😔

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Feb 20, 2008 at 04:56 PM

    i have used this query to create the script to grant to all the tables in a schema:

    SELECT 'GRANT ALL ON ' & SCHEMANAME & '.' & TABLENAME & ' TO REPLER' FROM TABLES WHERE SCHEMANAME='SCHEMA'

    and i get lines like this:

    GRANT ALL ON SCHEMA.CHEQUE_REGALO_TIPO TO REPLER //

    GRANT ALL ON SCHEMA.DEVOLUCION_CABECERA TO REPLER //

    GRANT ALL ON SCHEMA.AGENCIA_TRANSPORTE TO REPLER //

    but when i pase in sql studio and execute it i get this error:

    Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed

    Syntax error or access violation;-3014 POS(50) Invalid end of SQL statement

    it seems the '//' should be in new line, no? can i get this from the maxdb select query or should i create an script to add // between lines before exec it?

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Soraya,

      unfortunately you're right.

      The '//' has to be after a newline which cannot be generated via the SQL statement.

      Sorry, I didn't thought that this would be a problem as one could use search and replace in a text editor to add the newline.

      Anyhow, I made up a SQL solution that works as well:

      select TEXT FROM (
              select rowno as LINE , '\\' as TEXT from tables
              where schemaname =<SCHEMANAME>
         union ALL
              select rowno as LINE, 'GRANT ALL ON ' || tablename || ' TO <ROLENAME>' as TEXT from tables
              where schemaname ='SAPSDB'
      ) order by LINE
      

      What happens here is, that for each line a additional line containing just the "
      " is fetched. To mix both resultsets together I sort it by the LINE-number.

      Hope that is convinient to you 😉

      KR Lars

      Edited by: Lars Breddemann on Feb 20, 2008 6:30 PM

  • Feb 19, 2008 at 10:55 PM

    Hi Soraya,

    the privileges have to be granted for each object seperately.

    It might be a good idea to create one or more roles and granting access to the roles.

    These roles than can be used to grant several access privileges at once.

    Usually the development of such grant-statements should happen together with the development of the tables themselves.

    If you want to create a grant script for the existing tables you can try to use the old trick to let the database create the statements for you:

    select 'GRANT ALL ON ' || tablename || ' TO <ROLENAME>  //' from tables where schemaname =<SCHEMA>
    

    You can then take the output of this as a grant script.

    But be aware that usually the access privilieges for each and every object should be thought over well - so using such a mass-grant script is not likely to improve the security of your application.

    KR Lars

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 20, 2008 at 06:11 PM

    for future forum members reference, i have passed the file exported from the sql for this bash line.

    cat originalSqlFile.txt | while read LINEA; do echo $LINEA >>sqlFinalGrant.txt; echo "//" >>sqlFinalGrant.txt; done

    i have done this from a linux box so if you want to open later with notepad in windows you can use unix2dos to convert new lines or open directly in a detecting new line format capable editor like Notepad++.

    Add comment
    10|10000 characters needed characters exceeded