Why the role is not working?

user1 logged in
create table table1 (id integer not null);
create role role1;
grant select on table1 to role1; //so the role could select from table1
grant role1 to user2; // so user2 should be able to select table1, right?

user2 logged in
select * from user1.table1;
ORA-00942: table or view does not exist

Why role1 is not working here? How do I check which privileges have been granted to role1? i.e. which data dictionary view should I check?

Thanks!

Code:

---

SQL> show user
USER is "ME"
SQL> create table table1 (id number not null);

Table created.
(inserted two rows)
SQL> create role see_table1;

Role created.

SQL> grant select on table1 to see_table1;

Grant succeeded.

SQL> grant see_table1 to scott;

Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> select * from table1;
select * from table1
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from me.table1;

        ID
----------
        1
        2

---

Hmm.. As I posted in my original post, I've already used the qualified table name when user2 tried to select from user1's table.

select * from user1.table1;

but it still doesn't work. Any idea?

Also, can you answer the other part of my question? i.e. How do I check what kind of priviledges have been granted to a role?

Thanks!

Can u post tool's name where u got error:
....
user2 logged in
select * from user1.table1;
ORA-00942: table or view does not exist
I gess it wasn't sqlplus.

Quote:

---

Originally posted by elaine3839
How do I check what kind of priviledges have been granted to a role?

---

dba_role_privs
dba_roles
dba_tab_privs
dba_sys_privs

Code:

---

[email protected]> create user user1 identified by user1
  2  temporary tablespace temp
  3  default tablespace users
  4  quota unlimited on users;

User created.

Elapsed: 00:00:00.20
[email protected]> grant connect, create role to user1;

Grant succeeded.

Elapsed: 00:00:00.20
[email protected]> create user user2  identified by user2
  2  temporary tablespace temp
  3  default tablespace users;

User created.

Elapsed: 00:00:00.20
[email protected]> grant connect to user2;

Grant succeeded.

Elapsed: 00:00:00.10
[email protected]> connect user1/user1
Connected.
[email protected]> @d:\scripts\login
[email protected]>  create table xyz (x number(10), y number(10), z number(10));

Table created.

Elapsed: 00:00:00.30
[email protected]>  insert into xyz values (1,2,3);

1 row created.

Elapsed: 00:00:00.20
[email protected]> commit;

Commit complete.

Elapsed: 00:00:00.00
[email protected]> create role role1;

Role created.

Elapsed: 00:00:00.20
[email protected]> grant role1 to user2;

Grant succeeded.

Elapsed: 00:00:00.10
[email protected]> grant select on xyz to role1;

Grant succeeded.

Elapsed: 00:00:00.10
[email protected]> connect user2/user2
Connected.
[email protected]> @d:\scripts\login
[email protected]> select * from user1.xyz;

        X          Y          Z
---------- ---------- ----------
        1          2          3

Elapsed: 00:00:00.10

---

----------------------------------------
Hmm.. As I posted in my original post, I've already used the qualified table name when user2 tried to select from user1's table.

select * from user1.table1;

but it still doesn't work. Any idea?
----------------------------------------

I redo the whole test, and it seems to work now. Not sure what was wrong previously...

Quote:

---

I redo the whole test, and it seems to work now. Not sure what was wrong previously... [/B]

---

AHEM*user_error*AHEM

:)
MH

My work on this planet is done.