Why the role is not working?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Why the role is not working?

  1. #1
    Join Date
    May 2001
    Posts
    285

    Unhappy 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!

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    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

  3. #3
    Join Date
    May 2001
    Posts
    285
    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!

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    May 2001
    Posts
    285

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

  8. #8
    Join Date
    Jan 2001
    Posts
    3,131

    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.

  9. #9
    Join Date
    May 2002
    Posts
    2,645
    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
  •  



Click Here to Expand Forum to Full Width