-
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
-
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
-
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).
-
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
-
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 ?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|