Database Reference
In-Depth Information
CLOSE csr_return_sel_objs_for_schema;
COMMIT;
END grant_schema_wide_select;
SQL> exec grant_schema_wide_select('PUBLIC','SYSTEM');
PL/SQL procedure successfully completed.
These grants would normally be done as part of a release process rather than as a helpdesk user management
procedure. However, if the procedure were going to be used interactively by humans, one should consider applying
DBMS_ASSERT to validate input as per previous examples. Additionally input from the database in terms of object
names should also be validated in very high security environments to mitigate the risk of 'object injection' where table
names contain SQL by virtue of being double quoted. Use of both input and output validation will slow down schema
wide granting script so need to balance the risk for your environment.
The owner of
grant_schema_wide_select
will require
create session
,
create procedure
,
unlimited
tablespace
(or lower preferably), and
grant any object privilege
to complete the above schema wide select.
The preceding code is reliable, with verification following.
SQL> create user sctest identified by o;
User created.
SQL> exec grant_schema_wide_select('SCTEST','SYSTEM');
SQL> SELECT COUNT(*) FROM DBA_TAB_PRIVS WHERE GRANTEE='SCTEST';
COUNT(*)
----------
215
SQL> SELECT COUNT(*) FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('TABLE','VIEW','SEQUENCE') AND
OWNER='SYSTEM';
COUNT(*)
----------
215
What we will notice later is that GAOP (
GRANT ANY OBJECT PRIVILEGE
) allows control of the SYSTEM schema,
and the SYSTEM schema actually passes its DBA role to its procedures. Therefore, GAOP represents an escalation
opportunity through SYSTEM; more on that later.
The main point here is that we have some code for granting schema-wide privileges with just one command,
which is very useful for application schema management. An application schema is likely to have new objects in
sync with the release schedule, which will require grants to be made. Doing those grants manually is laborsome. The
problem with using a role (other than public) is that the role will not be accessible through Definer's Rights—hence
the need for individual object grants done automatically as follows:
exec grant_schema_wide_select('APP_ACCOUNT','APP_SCHEMA');
The downside is that the granting process may take a while depending on the size of the schema.
