Friday, September 26, 2008

Create readonly user in oracle?

This article is tested in oracle9i/oracle10g.

Sometime, DBA requires to create a readonly schema to allow non-DBA users to monitor the database or application user wanted to only view the application data . When we create readonly user, it should have an ability to access all the DB dictionary includes v$ tables. We can also grant only select access to the application schema tables.

Here is the steps to setup the readonly schema. The below steps needs to be done in DBA users.

Step 1
create user readonly
identified by readonly;

Step 2
grant create synonym,
create session,
select any dictionary to readonly;

Step 3
create the synonym in readonly schema for application tables.

Now if we connect readonly schema, you can see application tables as well as all the database dictionary tables. You can also monitor the database. If you are using any version below oracle9i, you need to grant SELECT ANY CATALOG role instead of SELECT ANY DICTIONARY privilege.

No comments: