Here’s a common task: You need to create a database user with read only access. You have some power users who know how to use SQL, and they are allowed to see all of the data in the system. You can use these commands to create such a user (in Oracle):
create user PSRO identified by PSRO; grant create session, select any table, select any dictionary to PSRO;
Note: You can probably find this all over the web, but I wanted to capture it on my blog so that I could find the syntax easier.
Hi.
Be careful. Depending on the Oracle version, access to the dictionary can leave you open to attack..
Also, the ability to SELECT implicitly gives you the ability to SELECT FOR UPDATE, even without update privilege on any objects. This means the user can lock rows and cause a denial of service attack.
Users should only have the privileges they need and nothing more. I would consider this “read anything” user to be a power user, which should be avoided, or at least protected from general access.
Cheers
Tim…
Tim,
Thank you so much for your comment. I have had on my list to come back and update this with something better and specific, but things keep interrupting me. Thanks for your feedback. I’ll get it done one of these days.
Thanks,
Stephen