CODEWITHSUNDEEP
oracle-databaseupdatessql-grant
Gerben
Gerben

Reputation: 117

grant update on table in different scheme

I have 3 schema's in my database:

Now I am writing a procedure to grant people access based on their job. Depending on their job, they need select, or select, update, insert and delete privileges on certain tables.

I know that usually you create roles for this, but I can't (DBA's are external, and they don't like roles.......)

When running the procure, I am inserting the new user, and which department they belong to. If the department is IT_SUPPORT for example, they will need to be able to update tables in the account account and the local_it account. My procedure is located in the local_it account.

Now, when I am trying to run a script like:

for x in (select * 
     from all_objects
     where owner in ('COLLDESK','LOCAL_IT')
     and object_type in ('TABLE','VIEW')
     and object_name not in ('IFM_letter_data','IFM_letter_data_V2')
     order by owner asc)
loop
  execute immediate 'grant update on ' || x.owner || '.' || x.object_name || ' to ' || v_user;
end loop;

I am getting an error, saying that I have insufficient privileges. Is it possible to grant update privileges on a table in a different schema? I am able to grant select privileges.

Thanks a lot in advance

Upvotes: 0

Views: 2857

Answers (1)

vav
vav

Reputation: 4694

Look at this: You have a friend that you trust and you gave him a key to your appartment. Are you OK if your friend will give a copy of your key to his friend?

It might be NO, it might be YES.

When you want to grant permissions to a user you may say that it is OK for that user to share access with the others.

So, in your case:

User with tables should grant you access to her tables, with permissions to share:

grant update on my_table to you with grant option

Then you can manage access to that table.

Upvotes: 1

Related Questions