Posted by 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
Learning is not a spectator sport 
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
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!
This is why I love blogging – I pick up new things from the community. Thanks for the info!
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).
Really a big help Connor. Thanks a lot
do you have one which is for a role?
Not sure what you mean – you mean grant the privs to a role not to a user?
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
can you give me a little more details/context
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.
Would be great to find out why the privileges for the second schema were not added to the role.