Why the role is not working?

[elaine3839]

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!

[stecal]

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

[elaine3839]

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!

[Shestakov]

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.

[marist89]

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

[marist89]

system@nt817.us> 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
system@nt817.us> grant connect, create role to user1;

Grant succeeded.

Elapsed: 00:00:00.20
system@nt817.us> create user user2  identified by user2
  2   temporary tablespace temp
  3   default tablespace users;

User created.

Elapsed: 00:00:00.20
system@nt817.us> grant connect to user2;

Grant succeeded.

Elapsed: 00:00:00.10
system@nt817.us> connect user1/user1
Connected.
system@nt817.us> @d:\scripts\login
user1@nt817.us>  create table xyz (x number(10), y number(10), z number(10));

Table created.

Elapsed: 00:00:00.30
user1@nt817.us>  insert into xyz values (1,2,3);

1 row created.

Elapsed: 00:00:00.20
user1@nt817.us> commit;

Commit complete.

Elapsed: 00:00:00.00
user1@nt817.us> create role role1;

Role created.

Elapsed: 00:00:00.20
user1@nt817.us> grant role1 to user2;

Grant succeeded.

Elapsed: 00:00:00.10
user1@nt817.us> grant select on xyz to role1;

Grant succeeded.

Elapsed: 00:00:00.10
user1@nt817.us> connect user2/user2
Connected.
user1@nt817.us> @d:\scripts\login
user2@nt817.us> select * from user1.xyz;

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

Elapsed: 00:00:00.10

[elaine3839]

----------------------------------------
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...

[Mr.Hanky]

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

AHEM*user_error*AHEM


MH

[stecal]

My work on this planet is done.