oracle read only account

[newcg]

I want to create a new oracle user with ReadOnly access to my 'UserB' oracle account schema/data.

CREATE USER userRO IDENTIFIED BY userRO
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

GRANT CONNECT,
CREATE SESSION,
SELECT ANY TABLE to userRO;

--My question is when I log in as userRO and try to read data from UserB, I get an error. How do I do it.?

SQL> select count(*) from userB_table;
select count(*) from userB_table
*
ERROR at line 1:
ORA-00942: table or view does not exist

If I log in as UserB:
SQL> select count(*) from userB_table;

COUNT(*)
----------
0

thanks.

 

[SantaMufasa]

NewCQ,

"UserRO" does not own a table named, "userB_table", therefore, when "UserRO" attempts to access that table, s/he must qualify the access with the name of the Oracle user/schema that owns the table. Here is a revised query that should work for you:

select count(*) from [b]userB.[/b]userB_table;

Let us know your findings.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[newcg]

thanks mufasa.
That works, but with the priviledge I gave userRO, it cannot do anything else only read access, correct?

GRANT CONNECT,
CREATE SESSION,
SELECT ANY TABLE to userRO;

I see some post that says for read only , only grant connect and create session.. but how do they do a query without the grant select any table ? hmm.

 

[SantaMufasa]

NewCG,

Yes, I was wondering why you would give such a powerful privilege (SELECT ANY TABLE) to a read-only user.

I recommend that you do the following:

(As a DBA)
CREATE ROLE READ_ONLY_APP_TABLES;
GRANT READ_ONLY_APP_TABLES to userRO;

(As owner of the tables to read)
GRANT SELECT on table1 to READ_ONLY_APP_TABLES;
GRANT SELECT on table2 to READ_ONLY_APP_TABLES;
GRANT SELECT on table3 to READ_ONLY_APP_TABLES;
GRANT SELECT on table4 to READ_ONLY_APP_TABLES;

At that point, UserRO can read any of those four tables only. That is the appropriate method (from a security and privacy perspective) to manage your privileges.

Let us know your thoughts on this.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]