DB User with read only access?

[Former Member]

Hi All,

For MaxDB 7.5, I wish to create a new DB user that will have read only access to all tables. I have connected with a DBA user account to create the new user with STANDARD access, but from the documentation this user will still have write access.

Could you give me example syntax? Do I use GRANT or GRANT USER command?

Thanks!

Alan

[lbreddemann]

Hi Alan,

a user that has no write privileges cannot have it's own schema objects. Think about this. This user cannot create anything or enter any data. All it can do is read stuff.

Ok, so what you need is a user (STANDARD is ok here) that is able to read other users stuff.

For this there exists the mighty mighty GRANT command.

Even better there is something called ROLE which can hold permissions for many objects just like any user can and addtionally can be granted as well.

So, what you might want to do is:

1. Create a Role, let's say "READ_ONLY"

2. Grant SELECT on all tables that should be read to this role. If you've got views that should be read as well, grant the SELECT for the view AND the base tables.

(Have you read what GRANT USER does? If so, then how should that lead to a read only grant??)

3. Now grant the "READ_ONLY" role to your user.

Now your user can access the tables via SELECT.

Don't forget to fully address the tables with <owner>.<tablename>. Otherwise the tables wouldn't be found.

With 7.6 (why are you still on stinky old 7.5 ?) you've something more elegant - schemas.

Perhaps you'll discover the use of it, once you get to read the documentation ...

regards,

Lars