Home » RDBMS Server » Server Administration » Grant Select rights (Oracle 12c Win 2008)
Grant Select rights [message #684304] Wed, 05 May 2021 09:08 Go to next message
Messages: 1
Registered: May 2021
Junior Member

We need to grant Select rights to schema2 all objects belongs to schema1 also the future objects.

there is possible please ?

Thank you

Best Regards,
Re: Grant Select rights [message #684305 is a reply to message #684304] Wed, 05 May 2021 11:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator



Re: Grant Select rights [message #684306 is a reply to message #684304] Wed, 05 May 2021 15:20 Go to previous message
Messages: 1375
Registered: September 2013
Senior Member
As detailed in the links provided by Michael, you cannot grant a privilege on an object that does not exist. The proper approach is to create a ROLE, grant the privs to the ROLE, then grant the role to the users. When a new table is created, grant privs on the table to the role as part of the rollout process for the new table.

create role business_user_role;
grant select on app_schema.table_a to business_user_role;
grant select on app_schema.table_b to business_user_role;
grant business_user_role to bob;
grant business_user_role to carol;
grant business_user_role to ted;
grant business_user_role to alice;
then later:
create table app_schema.table_c
   (col_a varchar2(10)
grant select on table app_schema.table_c to business_user_role;
Previous Topic: ORA-01918: user 'HR' does not exist
Next Topic: VARCHAR2 Stored in DB in Terms of Bytes
Goto Forum:

Current Time: Tue Mar 28 10:01:05 CDT 2023