Search

Top 60 Oracle Blogs

Recent comments

How to do a GRANT on an entire schema

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.