-
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.
-
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
Jeff Hunter
-
Code:
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
Jeff Hunter
-
It's working now...
----------------------------------------
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...
-
Re: It's working now...
I redo the whole test, and it seems to work now. Not sure what was wrong previously... [/B]
AHEM*user_error*AHEM
MH
I remember when this place was cool.
-
My work on this planet is done.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|