Trying to create a read-only user

[vince_laurent]

Our HR dept would like to have READ only access to a few tables and have asked that I create a user to allow them to pull stuff into MS Access. Since SAPR3 owns all the tables I thought I just had to, after I created the user, as SAPR3 grant select to those few tables. Didn't work. What did I miss?

SQL> connect system/*******

Connected.

SQL> CREATE USER "HRRPT"

2 IDENTIFIED BY "*******"

3 PROFILE "DEFAULT"

4 TEMPORARY TABLESPACE "PSAPTEMP"

5 DEFAULT TABLESPACE "PSAPUSER1D";

User created.

SQL> grant create session to hrrpt;

Grant succeeded.

SQL> connect sapr3/*******

Connected.

SQL> grant select on pevsh to hrrpt;

Grant succeeded.

SQL> connect hrrpt/*******

Connected.

SQL> desc pevsh;

ERROR:

ORA-04043: object pevsh does not exist

SQL> desc 'sapr3'.'pevsh';

SP2-0565: Illegal identifier

What am I missing?

Thanks!

[markus_doehr2 · Accepted solution]

You're aware that this is not allowed if you have licensed Oracle through SAP? Check note 581312:

[...]

As of point 3, it follows that direct access to the Oracle database is only allowed for tools from the areas of system administration and monitoring. If other software is used, the following actions, among other things, are therefore forbidden at database level:

• Creating database users

• Create database objects

• Querying/changing/creating data in the database

• Using ODBC or other SAP external access methods

This means that additional application software is only allowed if this accesses the database through SAP interfaces (for example, RFC, SAP J2EE or BAPI).

[...]

If you want to go for "read only", give them permission for queries (SQ00) in conjunction with an Info-Set for the tables they want to read.

--

Markus

[vince_laurent · Accepted solution]

I guess I should clarify - this is NOT for Production. All the user needs is READ access to a few tables to verify some things that are TOO large to come down in excel AND too large to create a view for.

[markus_doehr2 · Accepted solution]

I understand your intention - the license issue stays though

Check Note 700548 - FAQ: Oracle authorizations

It depends on the Oracle release you're using

--

Markus

[vince_laurent · Accepted solution]

Thanks.... I'll look elsewhere for a solution.

Vince

Oracle 9.2.0.8 on HP-UX11i

[markus_doehr2 · Accepted solution]

It works here as follows:

> connect system/*****

Connected.

SQL> CREATE USER hr

2 IDENTIFIED BY hr

3 PROFILE "DEFAULT"

4 TEMPORARY TABLESPACE "PSAPTEMP"

5 DEFAULT TABLESPACE "PSAPBEN620";

User created.

SQL> grant create session to hr;

Grant succeeded.

SQL> connect sapr3/******

Connected.

SQL> GRANT SELECT

2 ON "SAPR3"."T000" TO "HR";

Grant succeeded.

SQL> connect hr/hr

Connected.

SQL> select count (*) from "SAPR3"."T000";

COUNT(*)

-

---

24

SQL> desc "SAPR3"."T000";

Name Null? Type

-

---

-

---

-

---

MANDT NOT NULL VARCHAR2(9)

MTEXT NOT NULL VARCHAR2(75)

ORT01 NOT NULL VARCHAR2(75)

MWAER NOT NULL VARCHAR2(15)

ADRNR NOT NULL VARCHAR2(30)

CCCATEGORY NOT NULL VARCHAR2(3)

CCCORACTIV NOT NULL VARCHAR2(3)

CCNOCLIIND NOT NULL VARCHAR2(3)

CCCOPYLOCK NOT NULL VARCHAR2(3)

CCNOCASCAD NOT NULL VARCHAR2(3)

CCSOFTLOCK NOT NULL VARCHAR2(3)

CCORIGCONT NOT NULL VARCHAR2(3)

CCIMAILDIS NOT NULL VARCHAR2(3)

CCTEMPLOCK NOT NULL VARCHAR2(3)

CHANGEUSER NOT NULL VARCHAR2(36)

CHANGEDATE NOT NULL VARCHAR2(24)

LOGSYS NOT NULL VARCHAR2(30)

You need to grant explicitly including schema name.

--

Markus

[vince_laurent]

Thanks for all the suggestions. I might give something a try in the future but right now the developer is back to looking at CATT scripts and such.

[Former Member]

Create public synonym for the table(s).

for example:

SQL> connect sapr3/*******

Connected.

SQL> create public synonym pevsh for pevsh;