DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: privileges managing

Hybrid View

  1. #1
    Join Date
    Feb 2003
    Posts
    23

    privileges managing

    Suppose that we have u1, u2 and a role SELECT_U1_TAB (grant select on U1's tabs) granted to U2.
    Suppose that we have a U2's view :V2 (using an U1 tab). U2 must have an object privilege on U1 tab used by the view.

    Desiring have a role BY SCHEMA with select privilege (Role 1: SELECT_U1_TAB, Role2 : SELECT_U2_TAB) in order to manage privilege with role (and not with explicit object privilege), this is not always possible as it is the case when a view is using a tab in another schema (U2 view using an U1 tab).

    Anyone have a proposition or an idea to avoid managing explicit privilege (Indeed, we want to have something like the privilege SELECT ANY TABLE but BY SCHEMA and not for all SCHEMAS).

    Thanks in advance

  2. #2
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    If what I'm thinking your wanting to do is correct. If you go out on the database and create a role, then login as the schema owner and run the below script. That should give you restricted select only on that particular schema.

    set echo off termout on pages 0 feedback off
    accept site prompt 'Enter db name: '
    spool grant_select.&site..sql
    select 'grant select on '||table_name||' to insert role here;'
    from user_tables;
    spool off
    set echo on termout on pages 24 feedback on
    spool truncate.lst
    @grant_select.&site..sql


    Then just assign the role to whomever you want to have select privs to the schema you associated the role with.

    Too easy
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  3. #3
    Join Date
    Feb 2003
    Posts
    23
    Hello,

    I don't know if I was clear. So let's give you a real example :

    I have a schema U1 and a role SELECT_U1_TAB (this role allow to do a select on U1 tab...).

    I have a schema U2 with a particular view U2V (in this U2 view, we do a select on a U1 table). U2 can select U1 TAB (has the Role : SELECT_U1_TAB). Suppose that U2V is defined as follow (select * from U1.T1).

    U2 can do the following operation : select * from U1.T1
    but he can do : select * from U2V

    (with SELECT ANY TABLE given to U2, we can do : select * from U2V. But we dont want to give U2 the privilege to select on other schema. We would like to do select only on U1 schema ... We would like a SELECT ANY TABLE by schema and avoid using explicit object privilege : select * from U2V works if we grant to U2 an explicit object privilege to U2 on U1.T1).

  4. #4
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Lol, I don't know if its me or what, but lets see if we can work this out.
    We know we don't want to give "select any table" to u2. So, I'm thinking if you go out and log in as U1 and run that script I gave you and insert the role SELECT_U1_TAB where it says to. When it's finished, the role SELECT_U1_TAB will have select on all tables assigned to U1. Then on U2 assign the role SELECT_U1_TAB to it, That way anytime the user U2 needs to select data from U1's tables it will be able to. The important thing though that needs to happen wgen you're logged in as U2 is that, when your selecting from U1's tables that the tables be prefaced with a U1(example, U1.my_tables) If you don't, your select script will fail.

    Same sheet of music yet?
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  5. #5
    Join Date
    Feb 2003
    Posts
    23
    I agree with you OracleDoc but try to create a view(v2)in U2 schema using a U1 tab (U2 view : select * from u1.t1).

    U2 can do : select * from u1.t1
    U2 CANNOT do : select * from u2.v2 (you need an explicit privilege on u1.t1 ...).

    Try this and let me know if I am wrong ?

  6. #6
    Join Date
    Jul 2000
    Posts
    521
    You are not wrong. But, neither is Oracle - as this is documented. If you need to create views (and an PL/SQL env), you need to have privs that are 'directly' granted. Privs received through ROLE do not work.
    svk

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