CODEWITHSUNDEEP
oracle-database
Tech_Delhi
Tech_Delhi

Reputation: 1

Oracle Read only access to specific user

I have a schema and many users in it. For development I would like to give permission to the user to only read the tables and not having permission to manipulate database. Are there any commands to set access rights for a particular user to read only?

Upvotes: 0

Views: 4565

Answers (2)

Littlefoot
Littlefoot

Reputation: 143103

As Ed commented, you aren't allowed to do anything unless granted. For read-only users, you'd grant only the SELECT privilege on your tables. If you have only a few of them, do it manually. Otherwise, create a procedure which will do it for you. Here's an example.

These are my tables:

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
LINKS                          TABLE
SALGRADE                       TABLE

A procedure which loops through all tables in my schema and grants SELECT to user passed as a parameter:

SQL> create or replace procedure p_grant_ro(par_user in varchar2) is
  2    l_str varchar2(200);
  3  begin
  4    for cur_r in (select table_name from user_tables
  5                  order by table_name
  6                 )
  7    loop
  8      l_str := 'grant select on ' || cur_r.table_name ||
  9               ' to ' || dbms_assert.schema_name(par_user);
 10      dbms_output.put_line(l_str);
 11      execute immediate(l_str);
 12    end loop;
 13  end;
 14  /

Procedure created.

Testing:

SQL> set serveroutput on;
SQL> exec p_grant_ro('MIKE');
grant select on BONUS to MIKE
grant select on DEPT to MIKE
grant select on EMP to MIKE
grant select on LINKS to MIKE
grant select on SALGRADE to MIKE

PL/SQL procedure successfully completed.

SQL>

If you wonder what's the purpose of dbms_assert function's call: preventing possible SQL injection. Function takes care that parameter is an existing schema name.

SQL> exec p_grant_ro('TECH_DELHI');
BEGIN p_grant_ro('TECH_DELHI'); END;

*
ERROR at line 1:
ORA-44001: invalid schema
ORA-06512: at "SYS.DBMS_ASSERT", line 266
ORA-06512: at "SCOTT.P_GRANT_RO", line 8
ORA-06512: at line 1


SQL>

Upvotes: 1

Lukas_Multi
Lukas_Multi

Reputation: 29

You should be able to configure this in e.g. MYSQL Workbench. I think there is also a way to link this to an Apex user.

Upvotes: 0

Related Questions