asked on

grant user/role question

Have user/role question. I make a simple example to explain what I want to achieve.
1) sqlplus login as sys

2) create 2 schemas

create user S1 identified by <pwd1>
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE TEMP
PROFILE default
ACCOUNT UNLOCK;
GRANT CONNECT TO S1 ;
ALTER USER S1 DEFAULT ROLE ALL;
alter user S1 quota 20M on USERS;

create user S2 identified by <pwd2>
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE TEMP
PROFILE default
ACCOUNT UNLOCK;
GRANT CONNECT TO S2;
ALTER USER S2 DEFAULT ROLE ALL;
alter user S2 quota 20M on USERS;

3) create 2 tables and insert some data
create table S1.T1(id number);
insert into S1.T1 values(10);

create table S2.T2(id number);
insert into S2.T2 values(20);

commit;

4) grant select on T1 to S2
grant select on S1.T1 to S2;

5) sqlplus login to S2
select * from S1.T1;

You will get one value from S1.T1
10

4) sqlplus login back to sys and create one role

create role R2 not identified;
revoke select on S1.T1 from S2;
grant select on S1.T1 to R2;
grant R2 to S2;

5) sqlplus login to S2 again.
when issuing the following
select * from S1.T1;

I encountered error message below

"ORA-00942: table or view does not exist"

--end of the example

My questions:

1) what's wrong with the above?
2) how to correct it if it can be?

The database is 12c one without PDB/CDB.

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

johnsone's test was a few seconds faster than mine. therefore, not for points.

I just wanted to second his post since I also replicated it. The steps also work for me.

jl66

ASKER

Thanks for the info and tip. Yes. I double checked the commands and setting. They are correct but the results are different from what you both got. The same consistent results are from my 2 databases located in different servers.

How can I know what went wrong with it? Thanks a lot.

johnsone

I cannot replicate what you are seeing, so I have no idea what it could be. I did it twice and it worked fine both times. The only thing that I can think of is missing a qualifying name, or you aren't posting the exact commands you are using. Could be something with a name or a synonym that is getting in the way, but hard to tell without being able to replicate it.

jl66

ASKER

I went on another server and simply copied/pasted the commands there. The result is the same as before.
Have you switched the user login as I mentioned in the note?

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnsone

This is exactly what I did:

SQL> @tst
SQL> create user S1 identified by pwd1
  2    DEFAULT TABLESPACE  users
  3    TEMPORARY TABLESPACE TEMP
  4    PROFILE default
  5    ACCOUNT UNLOCK;

User created.

SQL>   GRANT CONNECT TO S1 ;

Grant succeeded.

SQL>   ALTER USER S1 DEFAULT ROLE ALL;

User altered.

SQL>   alter user S1 quota 20M on USERS;

User altered.

SQL> 
SQL> create user S2 identified by pwd2
  2    DEFAULT TABLESPACE  users
  3    TEMPORARY TABLESPACE TEMP
  4    PROFILE default
  5    ACCOUNT UNLOCK;

User created.

SQL>   GRANT CONNECT TO S2;

Grant succeeded.

SQL>   ALTER USER S2 DEFAULT ROLE ALL;

User altered.

SQL>   alter user S2 quota 20M on USERS;

User altered.

SQL> 
SQL> create table S1.T1(id number);

Table created.

SQL> insert into S1.T1 values(10);

1 row created.

SQL> 
SQL> create table S2.T2(id number);

Table created.

SQL> insert into S2.T2 values(20);

1 row created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> grant select on S1.T1 to S2;

Grant succeeded.

SQL> 
SQL> connect s2/pwd2
Connected.
SQL> select * from S1.T1;

        ID                                                                      
----------                                                                      
        10                                                                      

SQL> 
SQL> connect / as sysdba
Connected.
SQL> create role R2 not identified;

Role created.

SQL> revoke select on S1.T1 from S2;

Revoke succeeded.

SQL> grant select on S1.T1 to R2;

Grant succeeded.

SQL> grant R2 to S2;

Grant succeeded.

SQL> 
SQL> connect s2/pwd2
Connected.
SQL> select * from S1.T1;

        ID                                                                      
----------                                                                      
        10                                                                      

SQL> 
SQL> spool off

Open in new window

Only change is I set valid passwords for the 2 users and added the connect statements to switch users.

I guess slightwv and I posted at the same time. Just to be consistent with original I used SYS, so this shows that both ways don't produce the error.

jl66

ASKER

Thanks a lot for the explanation.

slightwv (䄆 Netminder)

Did you figure out what was causing the issue?

jl66

ASKER

After rebuilding DB, the issue is gone.

slightwv (䄆 Netminder)

Thanks for the update.