Oracle Permissions – Schema Grants and Tablespaces

oraclepermissionstablespaces

I have a schema with its tablespace where are application's data are located. Now I need a separate schema which will have an access only to select the data. I wish to create a set of views for that schema.

My questions are:

is it possible to create schema at the same tablespace and limit its grants just to some views (without giving an access to the original tables) ?
is it possible to create separate tablespace for that schema and create views and grants that will work across both of them ?
if both are possible than which one should be used and why ?

Thank you in advance.

update:

I created 3 schemas:

A is the one which has tables FOO

 grant select on FOO to B

B is to create the view

create or replace view BAR as
select * from A.FOO

C is suppose to be able to select * from B.BAR but if try to (as B):

grant select on BAR to C

I'm getting an error:

Error: ORA-01720: grant option does not exist for 'A.FOO'

Basicaly I wish to achieve 3 levels:

A (original table) < B (view definition) < C (select only from the view)

But it seems that it requires me to grant C direct access to read the original table which fails all the idea.

Best Answer

is it possible to create schema at the same tablespace and limit its grants just to some views (without giving an access to the original tables) ?

Yes. And if your concern is limited to just read-only access to those tables then you don't even need to create separate views, you can simply create new schema and grant select on those tables.

is it possible to create separate tablespace for that schema and create views and grants that will work across both of them ?

Yes. If you just create views on this schema then you even don't need to worry to manage separate tablespace because views(is a named and validated SQL query) are stored in the Oracle data dictionary.

if both are possible than which one should be used and why?

It's up to you. In my opinion, if you place schema in to the separate tablespace then you can transport it to another database with ease. Also it makes possible to perform point-in time recovery. Additionally, we can place such tablespace to different disks as per the IO performance requirements.

Update

You have to have granted select on FOO with grant option to user B in order for user 'B' to grant select on view to user 'C' as demonstrated below.

SQL> conn a/a
Connected.

SQL> grant select on t1 to b with grant option;

Grant succeeded.

SQL> conn b/b
Connected.

SQL> create or replace view v_t1 as select * from a.t1;

View created.

SQL> grant select on v_t1 to c;

Grant succeeded.

SQL> conn c/c
Connected.
SQL> select * from b.v_t1;

    ID
----------
     1

Related Solutions

Configure Oracle user in Enterprise Manager to CREATE VIEW on any of his tables

I'm not sure if I'm understanding your question properly. I created a user with the ability to create views for his own schema.

SQL> create user view_user identified by password default tablespace users quota unlimited on users;

User created.

SQL> grant create session, create table, create view to view_user;

Grant succeeded.

SQL> connect view_user
Enter password: 
Connected.
SQL> CREATE TABLE Members (ID number(10), Name varchar2(100), Street_No varchar2(100), ZIP number(5), City varchar2(100));

Table created.

SQL> CREATE VIEW MembersNamesOnly AS SELECT ID, Name FROM Members;

View created.

SQL>

Postgresql – How to GRANT for all tables across all schemas

If you need to do this only once, the quickest way is probably the following.

Find all the schemas you want to touch by querying, for example, the pg_namespace system catalog:

SELECT nspname 
FROM pg_namespace
WHERE nspname LIKE 'shard%'; -- tweak this to your needs

Then we can expand the query output into GRANT statements:

SELECT 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || nspname || ' TO foo;'
FROM pg_namespace
WHERE nspname LIKE 'shard%';

This will give you an enormous amount of statements, which you can just copy over and run. As you have a big number of schemas, the easiest is possibly the following (running it in psql):

\copy (SELECT 'GRANT ...' ...) TO some_local_file.sql

\i some_local_file.sql

Of course, if you prefer, you could do this using dynamic SQL, too - I find the above solution slightly better, as it leaves a file behind with the actions taken, that can then be checked in under version control.

If you happen to use a psql version 9.6 (or later), there is just another possibility using \gexec:

SELECT 'GRANT SELECT ...'
... -- you can even omit the semicolon here 
\gexec

Notes:

I've written an answer about a similar issue a while ago - you may find useful details there.
there is no possibility of doing this from GRANT, unfortunately. As it is not uncommon what you need here, I'd expect this functionality appearing sooner or later.
just to emphasize, the above commands will do the trick for the already existing tables. Future tables need the default privileges (which you've set, for a given role).

update:

Best Answer

Related Solutions

Configure Oracle user in Enterprise Manager to CREATE VIEW on any of his tables

Postgresql – How to GRANT for all tables across all schemas

Related Question