I use a trigger to grant select on newly created table in admin schema to role emp_role which has been granted to user emp_user. So emp_user can have select privilege to any tables created by admin user. The following trigger code is working in Oracle 12c but not in 18c with no error. In 18c, emp_user can not select on tables created by admin. But in 12c, emp_user can select on tables created by admin. Should I make anything change on code in 18c?
Step 1: Login as admin, I created an emp_role, and grant the role to user emp_user. I also created a trigger in admin schema to grant select on newly created table to emp_role.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.6.0.0.0
SQL>
create role emp_role;
Role created.
SQL>
grant emp_role to emp_user;
Grant succeeded.
SQL>
create or replace TRIGGER admin.grant_sel_emp_role
after create on admin.schema
declare
stm_str1 varchar2(255);
l_job number;
begin
if ( ora_dict_obj_type = 'TABLE' )
then
stm_str1 := 'execute immediate "grant select on ' || ora_dict_obj_name || ' to emp_role";';
dbms_job.submit( l_job, replace(stm_str1,'"','''') );
end if;
end;
/Trigger created.
SQL>
create table test(t number);
Table created.
Step 2: Login as emp_user
SQL>
select * from admin.test;
SQL Error: ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
Are you sure the job has run?
Because it works fine for me in 18c; though I do need to wait a minute to ensure the job completes:
select banner from v$version;
BANNER
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
create role emp_role;
grant create session, emp_role to emp_user
identified by emp_user;
create or replace TRIGGER chris.grant_sel_emp_role
after create on chris.schema
declare
stm_str1 varchar2(255);
l_job number;
begin
if ( ora_dict_obj_type = 'TABLE' ) then
stm_str1 := 'execute immediate "grant select on ' || ora_dict_obj_name || ' to emp_role";';
dbms_job.submit( l_job, replace(stm_str1,'"','''') );
end if;
end;
/
create table test ( t number );
conn emp_user/emp_user
select * from chris.test;
ORA-00942: table or view does not exist
select privilege
from all_tab_privs
where table_schema = 'CHRIS'
and table_name = 'TEST';
no rows selected
exec dbms_session.sleep ( 60 );
select * from chris.test;
no rows selected
select privilege
from all_tab_privs
where table_schema = 'CHRIS'
and table_name = 'TEST';
PRIVILEGE
SELECT So I'd start by adding some intrumentation to ensure the job is running correctly!