Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    May 2006
    Posts
    11

    Unanswered: Newbie Roles Help

    Hi

    I am new to Oracle:- using 9i at the moment to learn.

    I need to create roles.

    Eg:- I want to create a role admin.

    Lets say I have 4 tables and they can the roles are like this

    table1 = S
    table2 =S, I
    table3 =S,I,U
    Table4 =S,I,U,D

    THen I want to create a role supervisor that can

    S,I,U,D on all the above tables.

    How can I do this all at once.

    Thanks in advance
    Kimmy

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How can I do this all at once.
    "This" is what???
    All? All users?
    Quantify "at once".
    I suggest you RTFM - Concepts Manual found at
    http//tahiti.oracle.com
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2006
    Posts
    11
    HI

    I want to create all the roles in one sql script.

    Kimmy

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Sure you can - just string all the CREATE ROLE and GRANT commands together in a file called create_roles.sql (or whatever) and then run that in SQL Plus like this:

    SQL> @create_roles.sql

  5. #5
    Join Date
    May 2006
    Posts
    11
    Thanks

    How would my sql be then example if I am given select and update on 2 tables and select only on another 3 tables, do I create 2 sql-- one for select and update and then another for select oly or can they be in one sql statement.

    Kimmy

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You can't do that using only one statement. For example, store those statements into "create_roles.sql":
    Code:
    CREATE ROLE rl_sel;
    CREATE ROLE rl_sel_upd;
    
    GRANT SELECT ON emp TO rl_sel;
    GRANT SELECT ON dept TO rl_sel;
    
    GRANT SELECT, UPDATE ON emp TO rl_sel_upd;
    GRANT SELECT, UPDATE ON dept TO rl_sel_upd;
    Execute this script as Tony showed you (@create_roles.sql on SQL*Plus prompt).

    Now, when you'd like to grant select on those tables to another user (let's call it "littlefoot"), you'd do that as

    > GRANT rl_sel TO littlefoot;

  7. #7
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    It's perhaps worth mentioning that the roles you define should generally be meaningful in business terms, for example REPORTS_USER for a role that can query some tables and execute certain packages.

  8. #8
    Join Date
    May 2006
    Posts
    11
    Thanks

    Ok I picked this up from the net

    Code:
    create role SYSMAN_ROLE;
    
    grant connect, resource, create session, delete any table, update any table, select any table,drop any table, create any table, alter any table to SYSMAN_ROLE;
    Now I understand most of the above, but can someone please explain the
    following to me please

    Code:
    grant connect, resource, create session ...
    THanks
    Kimmy
    >>I am still a newbie <<

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    How come you didn't pick this up from the net?

  10. #10
    Join Date
    May 2006
    Posts
    11
    Quote Originally Posted by Littlefoot
    How come you didn't pick this up from the net?

    Thanks for that

    Kimmy
    >>I am still a newbie <<

  11. #11
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Specifically the section on the CONNECT, RESOURCE, and DBA predefined roles. Originally (many Oracle versions ago) these were actual system privileges, but for a while now they have been defined as default roles with a selection of system privileges granted to them such as CREATE SESSION, and Oracle has been saying they are for backward compatibility only and you should really define your own.

  12. #12
    Join Date
    May 2006
    Posts
    11
    Hi Guys

    Extending the concept of ROLES alittle.Lets say I have created 7 tables and
    I grant select on any table to a ROLE.Will that ROLE only have access to those 7 tables or will that ROLE have access to those 7 tables + select on other system tables as well.

    Kimmy
    >>I am still a newbie <<

  13. #13
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    a "select any table" is a separate grant and depending on the version of the database would mean being able to select from any table in the database or (oracle 9 and above) select any non-system table in the database.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  14. #14
    Join Date
    May 2006
    Posts
    11
    HI Guys

    Is this not valid :

    GRANT SELECT ON emp , SELECT ON dept TO rl_sel;

    Kimmy
    >>I am still a newbie <<

  15. #15
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, as the documentation (to which links have already been provided) shows.

    One table per GRANT statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •