Join / Log in
We have been asked by a client who is using IDS 12.10 to set a particular user to have read only access to the database.
Just a bit of background - this is the user they will use to access the database using ODBC for reporting. The ODBC driver is a read-write driver.
By default we have grant full access to 'public' as we allow any user to be able to install updates to the software (and therefore the database). We can revoke all privileges to the 'odbc' user and then only grant select access but because public has dba access this will allows updates to take place. It's going to be high risk if we change the public access to connect only but how would we grant full access to all tables for the other users. When they add another user to the system then how can we guarantee the new user will have full access permissions to all database tables?
Any assistance on how we could achieve this would be appreciated.
My preferred method is to revoke write privileges on all tables from "public", create a role with full access, then assign that as the default role for certain users. I have a script to generate the statements for all tables. Call me tomorrow, and I'll take you through the process. You will probably need to tighten up who can apply schema changes, but having a consistent object owner is a good thing anyway.
Thank you for the wonderfully concise answer to Greg's question. I am currently looking into tightening my company's IDS security practices and this thread helps me a lot.
Happy New Year!