Role to access the table only but not data from Table in Oracle Exadata

Question

Users have create table statement with select statement from multiple tables from multiple schema. I want to restrict them to read data and allow them to create empty table in their schema with metadata only not data. This I want to do at user access and roles level. Please tell me how I can do this?

I have tried giving them read access on underlying tables but users can see data as well.

Create table cust_acct_details 
as 
select * 
from ep_rel.acct a  
inner join ep_dnf.Cust_account ca 
on a.acct_id  = ca.acct_id

Tables should create without data.

score 0 · Answer 1 · answered Sep 12 '19 at 10:09

0

Add below condition to your code

-- condition to add where 1<>1

Create table cust_acct_details 
as 
select * 
from ep_rel.acct a  
inner join ep_dnf.Cust_account ca 
on a.acct_id  = ca.acct_id 
where 1<>1

Please make sure there are unique column names in your select statement. Oracle will not allow same column name in one table. Please use alias instead of *.

answered Sep 12 '19 at 10:09

CMK

40
2
10

Hi Chandrakant, I agree with your query but it is not me who is querying the data. The data is queried by users. I don't know if they will use such condition. – v83rahul Sep 12 '19 at 10:14

Jon Heller · Answer 2 · 2019-09-26T12:42:08.543

If you remove all tablespace privileges from a user they can still create tables but they won't be able to populate them.

For example, if you run this PL/SQL block to revoke all tablespace quotas from one user:

begin
    for users in
    (
        select 'alter user '||username||' quota 0 on '||tablespace_name v_sql
        from dba_ts_quotas
        where username = 'TEST_USER'
        order by 1
    ) loop
        execute immediate users.v_sql;
    end loop;
end;
/

Now the user can create tables but will get an error if they try to add rows:

SQL> create table test1(a number);

Table created.

SQL> insert into test1 values(1);
insert into test1 values(1)
            *
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'

For metadata, users can always see the metadata in their own schema. To allow them to view the metadata in other schema, run a grant like:

grant select_catalog_role to the_user;

Then that user can view the metadata either in the ALL_ data dictionary views, or using DBMS_METADATA.GET_DDL.

...thanks.. but my question was different....We already have tables with data....We want user to access metadata but not data....Is there anything we can do for such scenario? — v83rahul, Sep 26 '19 at 09:55

Role to access the table only but not data from Table in Oracle Exadata

2 Answers2