No question at this time
DBA Top 10
1 A. Kavsek 12400
2 M. Cadot 7300
3 B. Vroman 6400
4 P. Wisse 5600
5 J. Péran 1900
6 . Lauri 1500
7 T. P 500
7 R. Pattyn 500
9 A. Chavan 300
9 D. Johnson 300
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48458
Total active users1485
Act. users last 24h4
Act. users last hour0
Registered user hits last week184
Registered user hits last month815
Go up

grant select one schema to another schema
Next thread: Data retention for dba_hist_undostat and v$undostat
Prev thread: Table decorated with parallel option and DOP is AUTO

Message Score Author Date
Hi master, i have one schema like test which is...... javed akhtar Nov 26, 2019, 13:55
You posted this question 2 months ago and got 2 ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Nov 26, 2019, 14:25
Sorry Michel, i was find the same thread but no...... javed akhtar Nov 26, 2019, 16:43
Hi Michel, thanks for reminder but i changed my...... javed akhtar Nov 26, 2019, 16:56
Hello Javed, you can NOT grant immediately "ins...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman Nov 26, 2019, 17:08
Indeed: https://www.dba-village.com/village/dvp_f...... Bruno Vroman Nov 26, 2019, 17:10
Hi, I would also suggest to minimize the use...... Joël Péran Nov 27, 2019, 09:44
SQL> CREATE OR REPLACE TRIGGER create_table_trigge...... javed akhtar Nov 29, 2019, 13:36
Not surprising. Read your code. ... Michel Cadot Nov 29, 2019, 14:42
Hello Javed, first of all your "philosophy" is ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman Nov 29, 2019, 14:44

Follow up by mail Click here


Subject: grant select one schema to another schema
Author: javed akhtar, India
Date: Nov 26, 2019, 13:55, 10 days ago
Os info: linux
Oracle info: 12c
Message: Hi master,

i have one schema like test which is having 10 tables, i have granted select to test_1 via grant command.

but now the problem is here, in test schema APP team created 10 more tables and now i have to grant those newly added tables to test_1 and so on.

i have the trigger but how i can modify, every time when the new table add in test schema the select permission grant to test_1;

CREATE OR REPLACE TRIGGER grant_on_creation
AFTER CREATE ON test.schema
DECLARE
new_obj_name varchar2(30);
BEGIN
SELECT ora_dict_obj_name
INTO new_obj_name
FROM dual
WHERE ora_dict_obj_type = 'TABLE';
execute immediate 'grant select on ' || new_obj_name || ' to test_1';
END;
/


Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: grant select one schema to another schema
Author: Michel Cadot, France
Date: Nov 26, 2019, 14:25, 10 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
You posted this question 2 months ago and got 2 "definitive" solutions, why don't you use them?

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: grant select one schema to another schema
Author: javed akhtar, India
Date: Nov 26, 2019, 16:43, 10 days ago
Message: Sorry Michel,

i was find the same thread but not able to find.

i really apologies for inconvenience.

can you please share the link for my previous question.

thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: grant select one schema to another schema
Author: javed akhtar, India
Date: Nov 26, 2019, 16:56, 10 days ago
Message: Hi Michel,

thanks for reminder but i changed my trigger as per your suggestion

CREATE OR REPLACE TRIGGER create_table_trigger
AFTER CREATE ON SCHEMA
BEGIN
IF SYS.DICTIONARY_OBJ_TYPE = 'TABLE' THEN
FOR t IN (SELECT table_name FROM user_tables) LOOP
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || t.table_name || ' TO test_1';
END LOOP;
END IF;
END;
/

Trigger created.

i am creating the trigger in the same schema.

SQL> create table ttr(id number(10));
create table ttr(id number(10))
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.GRANT_ON_CREATION'
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 8

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: grant select one schema to another schema
Author: Bruno Vroman, Belgium
Date: Nov 26, 2019, 17:08, 10 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello Javed,

you can NOT grant immediately "inside the trigger". Hence the trick is that the trigger has to submit a job that will grant the privilege.

You might have a look at https://www.dba-village.com/village/dvp_scripts.ScriptDetails?ScriptIdA=5888

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: grant select one schema to another schema
Author: Bruno Vroman, Belgium
Date: Nov 26, 2019, 17:10, 10 days ago
Message: Indeed:
https://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=93906
Bruno
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: grant select one schema to another schema
Author: Joël Péran, France
Date: Nov 27, 2019, 09:44, 10 days ago
Message: Hi,

I would also suggest to minimize the use of DUAL in triggers and so on. One of my friends has seen a database litteraly crashing due to the misuse of dual in triggers, repeteadly procedures and so on. A lot of programmer tend to use dual to check the value of a sequence before doing an insert, instead of directly using the sequence.nextval in the INSERT order ...
So be careful with Dual ...

Regards,
Joel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: grant select one schema to another schema
Author: javed akhtar, India
Date: Nov 29, 2019, 13:36, 7 days ago
Message: SQL> CREATE OR REPLACE TRIGGER create_table_trigger
AFTER CREATE ON SCHEMA
DECLARE
j INTEGER;
BEGIN
dbms_job.submit(j, 'prc_grant;');
END;
/
2 3 4 5 6 7 8
Trigger created.


SQL> create or replace procedure prc_grant is
begin
FOR t IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || t.table_name || ' TO test2';
END LOOP;
end;
/ 2 3 4 5 6 7

Procedure created.


SQL> create table jk(id number);

Table created.

SQL> insert into jk values(1);

1 row created.

SQL> commit;

Commit complete.

SQL>


now i am login into another schema "test1"

SQL> select * from jk;
select * from jk
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from test.jk;
select * from test.jk
*
ERROR at line 1:
ORA-00942: table or view does not exist


Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: grant select one schema to another schema
Author: Michel Cadot, France
Date: Nov 29, 2019, 14:42, 7 days ago
Message:
Not surprising.
Read your code.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: grant select one schema to another schema
Author: Bruno Vroman, Belgium
Date: Nov 29, 2019, 14:44, 7 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello Javed,

first of all your "philosophy" is not the right one: each time that is an object created you ask for a grant select on ALL existing tables...
The script I gave works only on the object that has just been created.
So I will not search in detail what is wrong if something is wrong in the code... (amybe simply the fact that you gran tto test2 and try to select as test1 ;-)

Instead I show a simplified version and a test case:
SQL> CREATE USER scott IDENTIFIED BY "et4365_g__RG5"

2 DEFAULT TABLESPACE users
3 QUOTA 10M ON users;
User created.

SQL> GRANT CREATE SESSION TO scott;
Grant succeeded.

SQL> GRANT CREATE TABLE, CREATE TRIGGER, CREATE VIEW TO scott;
Grant succeeded.

SQL> CREATE USER test2 IDENTIFIED BY "UJ576_RTbrJ";
User created.

SQL> GRANT CREATE SESSION TO test2;
Grant succeeded.

SQL> GRANT EXECUTE ON dbms_lock TO test2;
Grant succeeded.

SQL> CONNECT scott/"et4365_g__RG5"
Connected.
SQL> CREATE OR REPLACE TRIGGER scott.grant_select_to_test2
2 AFTER CREATE ON scott.SCHEMA
3 DECLARE
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 j INTEGER;
6 p1 VARCHAR2(4000);
7 BEGIN
8 IF ora_dict_obj_type IN ( 'TABLE', 'VIEW' )
9 THEN
10 p1 := 'BEGIN' || CHR(10) ||
11 ' EXECUTE IMMEDIATE ''GRANT SELECT ON scott.' ||
12 ora_dict_obj_name || ' TO test2'';' || CHR(10) ||
13 'END;';
14 dbms_job.submit( j, p1 );
15 END IF;
16 COMMIT;
17 END grant_select_to_test2;
18 /
Trigger created.

SQL> CREATE TABLE t ( n NUMBER );
Table created.

SQL> INSERT INTO t VALUES ( 3 );
1 row created.

SQL> INSERT INTO t VALUES ( -1 );
1 row created.

SQL> CREATE VIEW v AS SELECT sysdate + n dte FROM t WHERE n > 0;
View created.

SQL> CONNECT test2/"UJ576_RTbrJ"
Connected.

SQL> SELECT * FROM scott.t;
SELECT * FROM scott.t
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> SELECT * FROM scott.v;
SELECT * FROM scott.v
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> EXEC DBMS_LOCK.SLEEP( 10 )
PL/SQL procedure successfully completed.

SQL> SELECT * FROM scott.t;
N
----------
3
-1

SQL> SELECT * FROM scott.v;
DTE
--------------------
02-DEC-2019 14:35:51
One word of explanation: the first SELECTs performed by test2 give "ORA-00942" because the jobs granting the privileges have not been run yet. Then I wait 10 seconds (necessary time might be more or less in your database) and the same SELECTs are now successful, showing that the jobs were successfully run.

For your case you might "mix" this over-simplified version (didactic) and the more complex version I had given previously ((taking care of objects dropped very soon after their creation)) to reach something that suits you (presumably granting to ROLE(s) instead of USER(s), and maybe more than "SELECT", and maybe to more than 1 grantee...)

Best regards,

Bruno.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here