wildcard in grant

[Former Member]

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

[Former Member · Accepted solution]

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?

[lbreddemann · Accepted solution]

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

[Former Member]

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++.

[lbreddemann]

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