Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Priscila.

Asked: March 06, 2017 - 5:55 pm UTC

Last updated: June 13, 2020 - 9:37 am UTC

Version: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

Viewed 100K+ times! This question is

You Asked

Hello!!

I did some research, but I still need help, please.

I have eight schemas: A, B, C, E, F, G and H.

I need to give schema A, full access (select, insert, update, delete) to schema B, C, D, E, F, G and H (all objects: TABLE, VIEW, PACKAGE, PROCEDURE, FUNCTION, SEQUENCE, SYNONYM).

1. How do I get all the objects in a schema?

2. Will this query work? I mean, will schema A be able to select, insert, update and delete on all objects in schema B?

set lines 300
set pages 0
spool grant.sql
select 'grant select, insert, update, delete on B.'||objecT_name||' to A;' from dba_objects where object_type in ('TABLE','VIEW','PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE', 'SYNONYM') and owner='B';
spool off

Thank you!

and Connor said...

It will work, but you need to edit your privs based on object type, eg

select 
  case 
    when object_type = 'SEQUENCE' then 'SELECT'
    when object_type = 'TABLE' then 'SELECT,INSERT,UPDATE,DELETE'
    when object_type in ('PACKAGE','FUNCTION','PROCEDURE') then 'EXECUTE'
    etc
    etc


and you dont need to do grants on synonyms.

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

May 16, 2017

Jeremy, May 16, 2017 - 4:53 pm UTC

Incomplete in terms of tying it together. When providing an answer, make it complete please.
Connor McDonald
May 17, 2017 - 3:32 pm UTC

Agreed....wouldnt it be terrible if people had to apply their own logic and skills. Much better if they just could cut/paste anything they found on the internet.

Jeremy please repeat 10 times

A reader, May 18, 2017 - 12:46 pm UTC

Connor is a fisherman
He teaches how to fish
He won't catch the fish for you.
You find this stinky
Fish always stinks

was looking for code that I could copy-paste

A reader, August 15, 2018 - 8:34 am UTC


Lal Cyril, October 11, 2018 - 6:58 am UTC

What if, i want to grant the same for future objects?
Any ways other than some sort of DDL trigger options
Chris Saxon
October 11, 2018 - 3:05 pm UTC

Include the grant when you create the object? ;)

But if you're looking for an automated way, it's either a DDL trigger or a regular job that looks for objects missing grants

John Keymer, October 12, 2018 - 8:11 am UTC

Personally if what you want is a "full schema grant" then I'd look at creating a new role, granting everything to that, then you have a "schema A full access" role that you can just grant to as many different users as you require - i.e. if you have another user that needs similar access, you don't need to go through the hassle of granting all the individual grants again, and if you add a new object you only need grant it once.

Ramki, June 10, 2020 - 6:27 am UTC

HI Tom,

May be simple one , i am not getting it.
i want create new DB user say myuser1
myuser1 do read only for all schema
but
myuser1 shall have all privilege for his own schema
i.e
myuser1 shall create , drop tables / index /views , select/insert/update/delete on tables/views of his own schema object alone
and create/execute procedure , create trigger ...

below code make readonly to all & myuser1 user

CREATE USER myuser1 IDENTIFIED BY myuser1
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON USERS;
GRANT CREATE SESSION TO myuser1;
GRANT READ ANY TABLE TO myuser1;
GRANT SELECT ANY DICTIONARY TO myuser1;

 
# sqlplus myuser1/myuser1

SQL> CREATE TABLE myuser1.test (
  id  NUMBER
);

ORA-01031: insufficient privileges


Connor McDonald
June 13, 2020 - 9:37 am UTC

You need this as well.

grant create table to myuser1

and depending on what else you want to do, some of these

CREATE JOB
CREATE MATERIALIZED VIEW
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW


A reader, June 16, 2020 - 8:43 am UTC


have you granted resource role?

Amrutharao Doppalapudi, April 23, 2021 - 8:11 am UTC

Hello,

Try below line.

sqlplus / as sysdba

SQL> Grant connect, resource to myuser1;

if you do not have sys dba access reach out to your DBA team

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library