How to do a GRANT on an entire schema

August 25, 2020

8 minutes

TL;DR: There is no schema grant command in Oracle, but this routine lets you get close.

Why no schema level grant?

A very common request on the various forums that exist for the Oracle Database is a “schema level grant”. The idea is that you could do something like


grant select on HR to SCOTT;

The concept sounds relatively straightforward but there would ne some nuances to cover off, for example:

Should it cover existing objects only or new ones as well?
Should it cover “all” objects? For example, if I had some AQ tables or DR$-prefixed tables for text indexes. Do I include them?
Should EXECUTE just cover PL/SQL or should it cover object types as well?
Should a lower level REVOKE override a schema level grant? What if a schema level grant then followed the revoke?

I’m not saying it can’t be done, but there’s a lot more to think about than you might first think.

A PL/SQL workaround

In the interim, if you have some firm rules on grants from an owning schema, here is a routine that can assist. By default it will grant the following privileges to the target recipient

TABLE – insert, update, delete, select, references (unless the table is external, in which case only select is given)
VIEW – insert, update, delete, select
SEQUENCE – select
PROCEDURE – execute
FUNCTION – execute
PACKAGE – execute
TYPE – execute


SQL> create or replace
  2  procedure schema_grant(p_owning_schema varchar2, p_recipient varchar2) is
  3
  4     errs_found boolean := false;
  5
  6     cursor c_all is
  7        select
  8           owner owner
  9          ,object_name
 10          ,object_type
 11          ,decode(object_type
 12                            ,'TABLE'   ,
 13                                decode(external,'YES','SELECT','SELECT,INSERT,UPDATE,DELETE,REFERENCES')
 14                            ,'VIEW'    ,'SELECT,INSERT,UPDATE,DELETE'
 15                            ,'SEQUENCE','SELECT'
 16                            ,'EXECUTE') priv
 17        from
 18          ( select o.owner,o.object_name,o.object_type,'NO' external
 19            from   dba_objects o
 20            where  o.owner = upper(p_owning_schema)
 21            and    o.object_type in ('FUNCTION','PACKAGE','PROCEDURE','SEQUENCE','TYPE','VIEW')
 22            and    o.generated = 'N'
 23            and    o.secondary = 'N'
 24            and    o.object_name not like 'AQ$%'
 25            union all
 26            select o.owner,o.object_name,o.object_type, t.external
 27            from   dba_objects o,
 28                   dba_tables t
 29            where  o.owner = upper(p_owning_schema)
 30            and    o.object_type  = 'TABLE'
 31            and    o.generated = 'N'
 32            and    o.secondary = 'N'
 33            and    o.object_name not like 'AQ$%'
 34            and    o.owner = t.owner
 35            and    o.object_name = t.table_name
 36          )
 37        order by decode(object_type  -- the order is only so views are granted after any likely
 38                        ,'VIEW', 1     -- objects referenced by them have already been granted
 39                        , 0) asc       -- as the grant would else fail due to view invalidity.
 40                ,owner
 41                ,object_name;
 42
 43     type t_grant_list is table of c_all%rowtype;
 44     r    t_grant_list;
 45
 46     l_ddl_indicator number;
 47
 48     procedure logger(m varchar2) is
 49     begin
 50       dbms_output.put_line(m);
 51     end;
 52
 53  begin
 54    open c_all;
 55    fetch c_all bulk collect into r;
 56    close c_all;
 57
 58    for i in 1 .. r.count loop
 59        dbms_output.put_line(rpad(r(i).object_type,20)||r(i).owner||'.'||r(i).object_name)   ;
 60
 61        begin
 62          logger('grant '||r(i).priv||' on '||r(i).owner||'.'||r(i).object_name||' to '||p_recipient);
 63          execute immediate 'grant '||r(i).priv||' on '||r(i).owner||'.'||r(i).object_name||' to '||p_recipient;
 64
 65        exception
 66          when others then
 67             logger('ERROR: '||sqlerrm);
 68             errs_found := true;
 69        end;
 70    end loop;
 71    if errs_found then
 72      logger('**** ERRORS FOUND ****');
 73    end if;
 74    logger('Finished, record count = '||r.count);
 75
 76  end;
 77  /

Procedure created.

The owner of this procedure will most likely need the GRANT ANY OBJECT PRIVILEGE in order for it to work. If any error is encountered trying to grant a privilege on an object, the routine will continue on. For example, here is an execution without the appropriate privileges


SQL> set serverout on
SQL> exec schema_grant('HR','SCOTT')
PROCEDURE           HR.ADD_JOB_HISTORY
grant EXECUTE on HR.ADD_JOB_HISTORY to SCOTT
ERROR: ORA-01031: insufficient privileges
TABLE               HR.COUNTRIES
grant SELECT,INSERT,UPDATE,DELETE,REFERENCES on HR.COUNTRIES to SCOTT
ERROR: ORA-01031: insufficient privileges
TABLE               HR.DEPARTMENTS
grant SELECT,INSERT,UPDATE,DELETE,REFERENCES on HR.DEPARTMENTS to SCOTT
ERROR: ORA-01031: insufficient privileges
SEQUENCE            HR.DEPARTMENTS_SEQ
grant SELECT on HR.DEPARTMENTS_SEQ to SCOTT
ERROR: ORA-01031: insufficient privileges
TABLE               HR.EMPLOYEES
grant SELECT,INSERT,UPDATE,DELETE,REFERENCES on HR.EMPLOYEES to SCOTT
ERROR: ORA-01031: insufficient privileges
SEQUENCE            HR.EMPLOYEES_SEQ
grant SELECT on HR.EMPLOYEES_SEQ to SCOTT
ERROR: ORA-01031: insufficient privileges
TABLE               HR.JOBS
grant SELECT,INSERT,UPDATE,DELETE,REFERENCES on HR.JOBS to SCOTT
ERROR: ORA-01031: insufficient privileges
TABLE               HR.JOB_HISTORY
grant SELECT,INSERT,UPDATE,DELETE,REFERENCES on HR.JOB_HISTORY to SCOTT
ERROR: ORA-01031: insufficient privileges
TABLE               HR.LOCATIONS
grant SELECT,INSERT,UPDATE,DELETE,REFERENCES on HR.LOCATIONS to SCOTT
ERROR: ORA-01031: insufficient privileges
SEQUENCE            HR.LOCATIONS_SEQ
grant SELECT on HR.LOCATIONS_SEQ to SCOTT
ERROR: ORA-01031: insufficient privileges
TABLE               HR.REGIONS
grant SELECT,INSERT,UPDATE,DELETE,REFERENCES on HR.REGIONS to SCOTT
ERROR: ORA-01031: insufficient privileges
PROCEDURE           HR.SECURE_DML
grant EXECUTE on HR.SECURE_DML to SCOTT
ERROR: ORA-01031: insufficient privileges
VIEW                HR.EMP_DETAILS_VIEW
grant SELECT,INSERT,UPDATE,DELETE on HR.EMP_DETAILS_VIEW to SCOTT
ERROR: ORA-01031: insufficient privileges
**** ERRORS FOUND ****
Finished, record count = 13

PL/SQL procedure successfully completed.

And here is more typical output that you would hope to see once the appropriate privilege is in place


SQL> grant grant any object privilege to ADMIN

Grant succeeded.

SQL> exec schema_grant('HR','SCOTT')
PROCEDURE           HR.ADD_JOB_HISTORY
grant EXECUTE on HR.ADD_JOB_HISTORY to SCOTT
TABLE               HR.COUNTRIES
grant SELECT,INSERT,UPDATE,DELETE,REFERENCES on HR.COUNTRIES to SCOTT
TABLE               HR.DEPARTMENTS
grant SELECT,INSERT,UPDATE,DELETE,REFERENCES on HR.DEPARTMENTS to SCOTT
SEQUENCE            HR.DEPARTMENTS_SEQ
grant SELECT on HR.DEPARTMENTS_SEQ to SCOTT
TABLE               HR.EMPLOYEES
grant SELECT,INSERT,UPDATE,DELETE,REFERENCES on HR.EMPLOYEES to SCOTT
SEQUENCE            HR.EMPLOYEES_SEQ
grant SELECT on HR.EMPLOYEES_SEQ to SCOTT
TABLE               HR.JOBS
grant SELECT,INSERT,UPDATE,DELETE,REFERENCES on HR.JOBS to SCOTT
TABLE               HR.JOB_HISTORY
grant SELECT,INSERT,UPDATE,DELETE,REFERENCES on HR.JOB_HISTORY to SCOTT
TABLE               HR.LOCATIONS
grant SELECT,INSERT,UPDATE,DELETE,REFERENCES on HR.LOCATIONS to SCOTT
SEQUENCE            HR.LOCATIONS_SEQ
grant SELECT on HR.LOCATIONS_SEQ to SCOTT
TABLE               HR.REGIONS
grant SELECT,INSERT,UPDATE,DELETE,REFERENCES on HR.REGIONS to SCOTT
PROCEDURE           HR.SECURE_DML
grant EXECUTE on HR.SECURE_DML to SCOTT
VIEW                HR.EMP_DETAILS_VIEW
grant SELECT,INSERT,UPDATE,DELETE on HR.EMP_DETAILS_VIEW to SCOTT
Finished, record count = 13

PL/SQL procedure successfully completed.

Keeping privileges fresh

These grants are obviously “point in time” grants, which means objects created after this procedure has been run will not be picked up. You could run this routine at regular intervals, but a grant is DDL and obviously it is generally not a great idea to be running lots of DDL repeatedly on the database.

If your intention is to try keep the “schema level” grant up to date, for example, on say a development environment where objects are being created and changed regularly, then here is an extended version of the procedure with usage notes underneath it.


SQL> create or replace
  2  procedure schema_grant(p_owning_schema varchar2, p_recipient varchar2, p_complete boolean default false) is
  3
  4     errs_found boolean := false;
  5
  6     cursor c_all is
  7        select
  8           owner owner
  9          ,object_name
 10          ,object_type
 11          ,decode(object_type
 12                            ,'TABLE'   ,
 13                                decode(external,'YES','SELECT','SELECT,INSERT,UPDATE,DELETE,REFERENCES')
 14                            ,'VIEW'    ,'SELECT,INSERT,UPDATE,DELETE'
 15                            ,'SEQUENCE','SELECT'
 16                            ,'EXECUTE') priv
 17        from
 18          ( select o.owner,o.object_name,o.object_type,'NO' external
 19            from   dba_objects o
 20            where  o.owner = upper(p_owning_schema)
 21            and    o.object_type in ('FUNCTION','PACKAGE','PROCEDURE','SEQUENCE','TYPE','VIEW')
 22            and    o.generated = 'N'
 23            and    o.secondary = 'N'
 24            and    o.object_name not like 'AQ$%'
 25            union all
 26            select o.owner,o.object_name,o.object_type, t.external
 27            from   dba_objects o,
 28                   dba_tables t
 29            where  o.owner = upper(p_owning_schema)
 30            and    o.object_type  = 'TABLE'
 31            and    o.generated = 'N'
 32            and    o.secondary = 'N'
 33            and    o.object_name not like 'AQ$%'
 34            and    o.owner = t.owner
 35            and    o.object_name = t.table_name
 36          )
 37        order by decode(object_type  -- the order is only so views are granted after any likely
 38                        ,'VIEW', 1     -- objects referenced by them have already been granted
 39                        , 0) asc       -- as the grant would else fail due to view invalidity.
 40                ,owner
 41                ,object_name;
 42
 43     cursor c_partial is
 44        with objs as
 45        (
 46        select /*+ materialize */ owner,object_name,object_type,priv_count
 47        from
 48          (
 49          select /*+ materialize */ owner,object_name,object_type,decode(object_type,'VIEW',4,1) priv_count
 50          from   dba_objects
 51          where  owner = upper(p_owning_schema)
 52          and    object_type in ('FUNCTION','PACKAGE','PROCEDURE','SEQUENCE','TYPE','VIEW')
 53          and    generated = 'N'
 54          and    secondary = 'N'
 55          and    object_name not like 'AQ$%'
 56          and    status != 'INVALID'
 57          union all
 58          select o.owner,o.object_name,o.object_type, decode(t.external,'YES',1,5) priv_count
 59          from   dba_objects o,
 60                 dba_tables t
 61          where  o.owner = upper(p_owning_schema)
 62          and    o.object_type  = 'TABLE'
 63          and    o.generated = 'N'
 64          and    o.secondary = 'N'
 65          and    o.object_name not like 'AQ$%'
 66          and    o.owner = t.owner
 67          and    o.object_name = t.table_name
 68          and    o.status != 'INVALID'
 69          )
 70        ),
 71        obj_privs as (
 72          select o.owner,o.object_name table_name,o.object_type,p.privilege,p.grantee
 73          from   dba_tab_privs p,
 74                 dba_objects o
 75          where  o.owner = p.owner
 76          and    o.object_name = p.table_name
 77          and    o.owner != 'SYS'
 78        )
 79        select
 80                 owner owner
 81                ,object_name
 82                ,object_type
 83                ,decode(object_type
 84                                  ,'TABLE'   ,'SELECT,INSERT,UPDATE,DELETE,REFERENCES'
 85                                  ,'VIEW'    ,'SELECT,INSERT,UPDATE,DELETE'
 86                                  ,'SEQUENCE','SELECT'
 87                                  ,'EXECUTE') priv
 88              from
 89              (
 90                select owner,object_name,object_type
 91                from
 92                (   select owner,object_name,object_type,priv_count
 93                    from   objs
 94                    minus
 95                    select owner, table_name, object_type,
 96                           least(count(*),decode(object_type,'TABLE',5,'VIEW',4,1))
 97                    from   obj_privs
 98                    where  grantee = upper(p_recipient)
 99                    and    privilege in ('SELECT','INSERT','UPDATE','DELETE','REFERENCES','EXECUTE')
100                    and    owner = upper(p_owning_schema)
101                    group by owner, table_name, object_type
102                )
103              )
104              order by decode(object_type  -- the order is only so views are granted after any likely
105                              ,'VIEW', 1     -- objects referenced by them have already been granted
106                              , 0) asc       -- as the grant would else fail due to view invalidity.
107                      ,owner
108                      ,object_name;
109
110     type t_grant_list is table of c_all%rowtype;
111     r    t_grant_list;
112
113     l_ddl_indicator number;
114
115     procedure logger(m varchar2) is
116     begin
117       dbms_output.put_line(m);
118     end;
119
120  begin
121    if p_complete then
122      logger('Starting complete run');
123
124      open c_all;
125      fetch c_all bulk collect into r;
126      close c_all;
127    else
128      logger('Starting partial run');
129
130      begin
131        select 1
132        into   l_ddl_indicator
133        from dba_objects
134        where owner = upper(p_owning_schema)
135        and last_ddl_time > sysdate-1/24
136        and rownum = 1;
137
138        open c_partial;
139        fetch c_partial bulk collect into r;
140        close c_partial;
141      exception
142        when no_data_found then
143          logger('Finished, no ddl in past 60 mins');
144          return;
145      end;
146    end if;
147
148    for i in 1 .. r.count loop
149        dbms_output.put_line(rpad(r(i).object_type,20)||r(i).owner||'.'||r(i).object_name)   ;
150
151        begin
152          logger('grant '||r(i).priv||' on '||r(i).owner||'.'||r(i).object_name||' to '||p_recipient);
153          execute immediate 'grant '||r(i).priv||' on '||r(i).owner||'.'||r(i).object_name||' to '||p_recipient;
154
155        exception
156          when others then
157             logger('ERROR: '||sqlerrm);
158             errs_found := true;
159        end;
160    end loop;
161    if errs_found then
162      logger('**** ERRORS FOUND ****');
163    end if;
164    logger('Finished, record count = '||r.count);
165
166  end;
167  /

Procedure created.

This version can operate in two modes

COMPLETE mode – acts as per the other version. It will perform all the grants
PARTIAL mode – checks if there has been any objects modified in the last 60 minutes, and if so, will determine a list of “missing” privileges and only grant those

Thus typical usage would be to run a COMPLETE mode (p_complete=>true) initially to over off most of the grants, and then every “n” mins (for example, 10mins) run the routine in PARTIAL mode, and it will pick up those new objects that have been created on your database. In this way, we can give the perception which is closer to the utopia of a schema level grant.

Hopefully you find these useful. Of course, looking after the GRANTs on a schema should be part of a rigorous security solution for your database. I’ve covered what I consider to be a best practice for this in the video below

13 responses to “How to do a GRANT on an entire schema”

pjkansara

August 26, 2020 at 12:29 am

Hi Connor,

Ever since we have entered cloud era, I have started thinking about writing less code and depend more on built-ins provided by the vendors (including Oracle database features). At least three reasons, I, as a developer may end up writing sub-optimal code causing performance issues compared to Oracle writing the same code in low level optimized language. Two, I may not cover/understand all aspects of security compared to Oracle’s security team validating code for HIPPA/FERPA/PCI etc. compliant as part of Oracle licenses. And three, imagine a new object type is offered by Oracle in version 21c, now I have to maintain/certify code.

I agree, there are nuances when it comes to what should be the hierarchy if I as a DBA first GRANT SELECT on SCHEMA and then execute REVOKE on a TABLE within the same SCHEMA. In my mind it’s no different then INCLUDE and EXCLUDE feature of DATAPUMP. There is a well defined Oracle document explaining what Oracle will internally do if you define both. Just like that, it’s my (DBA) responsibility to make sure I understand what I am doing if I am also expecting Oracle to provide me syntax like

GRANT SELECT ON ALL TABLES IN schema_name TO ROLE role_name;
 GRANT SELECT ON ALL VIEWS IN schema_name TO ROLE role_name;

BTW, above syntax is perfectly valid in Snowflake Datawarehouse.

Now, about future objects

GRANT SELECT ON FUTURE TABLES IN schema_name TO ROLE role_name;
 GRANT SELECT ON FUTURE VIEWS IN schema_name TO ROLE role_name;

Again, perfectly valid statement in Snowflake Datawarehouse.

I am sure Oracle will eventually offer a similar syntax with well defined internal order and limitations just like anything else.

Thanks for sharing code and helping all of us learn something new everyday.

Cheers!

Parag

Reply
Sam

August 26, 2020 at 12:58 am

Thanks Connor….this has been a long-time need for us in our environment, which is an Analytics shop.

We have several business areas represented by corresponding schemas and Data Scientists from each business area have to (1) collaborate/share objects from their corresponding schemas, and (2) they constantly have the need to create and drop objects, as part of their Analytics project work to refine the Analytical products which have a frequent business change requirements. This is a very common scenario in Analytics shops and your above solution has been long due, so thanks a lot for sharing.

I had to develop a similar procedure to grant object privileges from N schemas to other M schemas and P users. As you mentioned, the DDL was extremely costly as sometimes, even an introduction of 10 new tables in a given schema would quickly result into many DDL GRANTs to M schemas and P users. And as the number of objects kept on creeping up, the performance of this procedure kept on suffering.

After some help from the PLSQL community, I was pointed to look into a table named USER_TAB_PRIVS_MADE in 12c (the version I have), which keeps track of what privileges were already granted and to whom. Hence, I simply had to modify my cursor’s SQL to use this table to compare against and grant only those objects that had not been granted previously. Hope this helps!

Reply
1. Connor McDonald
  
  August 26, 2020 at 6:59 am
  
  This is why I love blogging – I pick up new things from the community. Thanks for the info!
  
  Reply
Bernd Eckenfels

October 29, 2021 at 1:01 am

I totally agree with you, the problems you mentioned about schema gants make it complicated. if you look for example on Postgresql they have special cases for granting on existing and future schema objects, and why it seems to work quite well and is handy (especially if you have to deal with multiple application users) it does make things not necesarily easier. But what really be cool for oracle would be if you can grant for roles or other new permission objects which can then be assigned to multiple users, so classes of users with the same access profile could be changed in a common place. for ms sql this really makes permission managment so much cleaner. (and/or you use db_reader role and be done for a database level grant).

Reply
Gladston Radhakrishnan

July 13, 2022 at 5:48 pm

Really a big help Connor. Thanks a lot

Reply
josette

September 27, 2022 at 6:04 am

do you have one which is for a role?

Reply
1. Connor McDonald
  
  September 27, 2022 at 8:21 am
  
  Not sure what you mean – you mean grant the privs to a role not to a user?
  
  Reply
Frederick Campbell

February 8, 2023 at 5:27 pm

Hi Connor,
Great post! I have implemented your example (full & partial run), but facing an issue. The initial full run works, but when running again for another schema no privileges are added to that role.

Any help or ideas greatly appreciated.

Thanks!
Frederick

Reply
Connor McDonald

February 8, 2023 at 9:00 pm

can you give me a little more details/context

Reply
1. Frederick
  
  February 8, 2023 at 9:52 pm
  
  ok. In our database we a have privileged user with the DBA role and Grant Any Object Privilege among other privs. In that Schema there is a role and we have assigned the grant select on all tables/views in the same schema to that role. We need to add grant select on tables/views in other schemas to the role. The second run was a partial and not the full, even though the owning schema is different.
  
  Reply
2. Frederick C.
  
  February 13, 2023 at 4:37 pm
  
  Would be great to find out why the privileges for the second schema were not added to the role.
  
  Reply
gerrat

October 5, 2023 at 8:47 pm

Oracle has finally fixed this in 23c:
https://oracle-base.com/articles/23c/schema-privileges-23c

Reply
1. Sam_P
  
  October 6, 2023 at 3:05 am
  
  Thank you for sharing this gem! 🙂
  
  Reply