CODEWITHSUNDEEP
oracle-databaseoracle10g
nax
nax

Reputation: 1224

Create a user who can only query data from a tablespace (oracle)

I have 3 users in my oracle Database

user_admin user user_query

I want to configure the last one to deny him of all kind of insert,update,delete but form the full tablespace not from a table, view or procedure...

The point is to have a secure-only-querying data user.

But i can't do it for a tablespace, at least as far i know.

Any idea?

Upvotes: 0

Views: 1831

Answers (3)

Kevin Rohrssen
Kevin Rohrssen

Reputation: 221

You can loop through all table that use the tablespace in question and grant select. I would try to stay away from powerful privs like "SELECT ANY TABLE", as that would apply to the entire database.

For example, if your tablespace is named XXX then:

BEGIN
  FOR tbl IN (SELECT owner, table_name
                FROM dba_tables dt
               WHERE dt.tablespace_name = 'XXX') LOOP
  EXECUTE IMMEDIATE 'GRANT SELECT ON ' || tbl.owner || '.' || tbl.table_name || ' TO USER_QUERY';
  END LOOP;
END;

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52386

Firstly, the use of the CONNECT and RESOURCE roles is discouraged, and documented as such.

That aside, no, there is no privilege for granting by tablespace, or even by user. For one thing, a partitioned table or index can use multiple tablespaces, none of which might be the default for that object.

Grants are at the object level. You could create a procedure to grant privileges to a user (or better to a role) based on the tablespace of a table though.

Upvotes: 1

Kelly
Kelly

Reputation: 3709

there is the following which grants select to any table or view (except those owned by SYS):

    grant select any table to user_query;

It doesn't restrict to a single tablespace though - any table in the entire database would be available for select.

Upvotes: 1

Related Questions