Schema privileges allow us to simplify grants where a user or role needs privileges on all objects in a schema.
◉ Setup
The examples in this article require the following setup.
Create two test users and a role.
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba
drop user if exists testuser1 cascade;
drop user if exists testuser2 cascade;
drop role if exists t1_schema_role;
create user testuser1 identified by testuser1 quota unlimited on users;
grant db_developer_role to testuser1;
create user testuser2 identified by testuser2 quota unlimited on users;
grant create session to testuser2;
create role t1_schema_role;
Create some objects in the first test user.
conn testuser1/testuser1@//localhost:1521/freepdb1
-- Sequences
create sequence t1_seq;
create sequence t2_seq;
-- Tables
create table t1 (id number);
insert into t1 values (t1_seq.nextval);
commit;
create table t2 (id number);
insert into t2 values (t2_seq.nextval);
commit;
-- Views
create view t1_v as select * from t1;
create view t2_v as select * from t2;
-- Procedures
create or replace procedure p1 as
begin
null;
end;
/
create or replace procedure p2 as
begin
null;
end;
/
◉ Grant Schema Privileges
The following code shows how to perform various schema privilege grants to users and roles.
conn testuser1/testuser1@//localhost:1521/freepdb1
-- Sequences
grant select any sequence on schema testuser1 to testuser2;
grant select any sequence on schema testuser1 to t1_schema_role;
-- Tables, views, materialized views
grant select any table on schema testuser1 to testuser2;
grant insert any table on schema testuser1 to testuser2;
grant update any table on schema testuser1 to testuser2;
grant delete any table on schema testuser1 to testuser2;
grant select any table on schema testuser1 to t1_schema_role;
grant insert any table on schema testuser1 to t1_schema_role;
grant update any table on schema testuser1 to t1_schema_role;
grant delete any table on schema testuser1 to t1_schema_role;
-- Procedures, functions and packages
grant execute any procedure on schema testuser1 to testuser2;
grant execute any procedure on schema testuser1 to t1_schema_role;
◉ Test Schema Privileges
The following code tests the grants we made earlier.
conn testuser2/testuser2@//localhost:1521/freepdb1
-- Sequences
select testuser1.t1_seq.nextval;
NEXTVAL
----------
2
SQL>
select testuser1.t2_seq.nextval;
NEXTVAL
----------
2
SQL>
-- Tables
select count(*) from testuser1.t1;
COUNT(*)
----------
1
SQL>
select count(*) from testuser1.t2;
COUNT(*)
----------
1
SQL>
-- Views
select * from testuser1.t1_v;
ID
----------
1
SQL>
select * from testuser1.t2_v;
ID
----------
1
SQL>
-- Procedures
exec testuser1.p1;
PL/SQL procedure successfully completed.
SQL>
exec testuser1.p2;
PL/SQL procedure successfully completed.
SQL>
◉ Auditing
Privileges granted at the schema level show up in the audit trail in the normal way, as demonstrated below.
We create and enable a new audit policy on some of the TESTUSER1 objects.
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba
noaudit policy test_audit_policy;
drop audit policy test_audit_policy;
create audit policy test_audit_policy
actions delete on testuser1.t1,
insert on testuser1.t1,
update on testuser1.t1,
select on testuser1.t1_seq
when 'sys_context(''userenv'', ''session_user'') = ''TESTUSER2'''
evaluate per session
container = current;
audit policy test_audit_policy;
We connect to the TESTUSER2 user and insert some data by referencing a sequence.
conn testuser2/testuser2@//localhost:1521/freepdb1
insert into testuser1.t1 (id) values (testuser1.t1_seq.nextval);
commit;
We check the audit trail.
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba
-- You might need to flush the audit information before it is visible.
-- exec dbms_audit_mgmt.flush_unified_audit_trail;
column event_timestamp format a30
column dbusername format a10
column action_name format a20
column object_schema format a10
column object_name format a20
select event_timestamp,
dbusername,
action_name,
object_schema,
object_name
from unified_audit_trail
where dbusername = 'TESTUSER2'
order BY event_timestamp;
EVENT_TIMESTAMP DBUSERNAME ACTION_NAME OBJECT_SCH OBJECT_NAME
------------------------------ ---------- -------------------- ---------- --------------------
30-APR-23 05.00.39.944494 PM TESTUSER2 SELECT TESTUSER1 T1_SEQ
30-APR-23 05.00.39.948816 PM TESTUSER2 INSERT TESTUSER1 T1
SQL>
We can see the individual actions are audited as expected.
◉ Views
The following views display information about schema privileges.
- DBA_SCHEMA_PRIVS
- ROLE_SCHEMA_PRIVS
- USER_SCHEMA_PRIVS
- SESSION_SCHEMA_PRIVS
- V$ENABLEDSCHEMAPRIVS
Here are some examples of their usage.
conn testuser2/testuser2@//localhost:1521/freepdb1
column username format a10
column privilege format a25
column schema format a10
select * from user_schema_privs;
USERNAME PRIVILEGE SCHEMA ADM COM INH
---------- ------------------------- ---------- --- --- ---
TESTUSER2 EXECUTE ANY PROCEDURE TESTUSER1 NO NO NO
TESTUSER2 SELECT ANY SEQUENCE TESTUSER1 NO NO NO
TESTUSER2 DELETE ANY TABLE TESTUSER1 NO NO NO
TESTUSER2 UPDATE ANY TABLE TESTUSER1 NO NO NO
TESTUSER2 INSERT ANY TABLE TESTUSER1 NO NO NO
TESTUSER2 SELECT ANY TABLE TESTUSER1 NO NO NO
6 rows selected.
SQL>
select * from session_schema_privs;
PRIVILEGE SCHEMA
------------------------- ----------
EXECUTE ANY PROCEDURE TESTUSER1
SELECT ANY SEQUENCE TESTUSER1
DELETE ANY TABLE TESTUSER1
UPDATE ANY TABLE TESTUSER1
INSERT ANY TABLE TESTUSER1
SELECT ANY TABLE TESTUSER1
6 rows selected.
SQL>
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba
column role format a20
column privilege format a25
column schema format a10
select * from role_schema_privs;
ROLE PRIVILEGE SCHEMA ADM COM INH
-------------------- ------------------------- ---------- --- --- ---
T1_SCHEMA_ROLE EXECUTE ANY PROCEDURE TESTUSER1 NO NO NO
T1_SCHEMA_ROLE SELECT ANY SEQUENCE TESTUSER1 NO NO NO
T1_SCHEMA_ROLE DELETE ANY TABLE TESTUSER1 NO NO NO
T1_SCHEMA_ROLE UPDATE ANY TABLE TESTUSER1 NO NO NO
T1_SCHEMA_ROLE INSERT ANY TABLE TESTUSER1 NO NO NO
T1_SCHEMA_ROLE SELECT ANY TABLE TESTUSER1 NO NO NO
6 rows selected.
SQL>
◉ Revoke Schema Privileges
The following code shows how to revoke the schema privileges we granted earlier.
conn testuser1/testuser1@//localhost:1521/freepdb1
-- Sequences
revoke select any sequence on schema testuser1 from testuser2;
revoke select any sequence on schema testuser1 from t1_schema_role;
-- Tables, views, materialized views
revoke select any table on schema testuser1 from testuser2;
revoke insert any table on schema testuser1 from testuser2;
revoke update any table on schema testuser1 from testuser2;
revoke delete any table on schema testuser1 from testuser2;
revoke select any table on schema testuser1 from t1_schema_role;
revoke insert any table on schema testuser1 from t1_schema_role;
revoke update any table on schema testuser1 from t1_schema_role;
revoke delete any table on schema testuser1 from t1_schema_role;
-- Procedures, functions and packages
revoke execute any procedure on schema testuser1 from testuser2;
revoke execute any procedure on schema testuser1 from t1_schema_role;
◉ Considerations
Here are some things to consider when using this functionality.
- The schema privileges can be granted to, and revoked from, users and roles.
- The grant for a specific object type only has to be issued once. Any newly created objects of the same type will automatically be available via the grant.
- From a security perspective schema privileges can present a problem. We always discuss using "least privileges" to minimize the attack surface of our system. Schema privileges go against the "least privileges" principle by granting access to all objects of a specific type. For many use cases we should avoid schema privileges, which makes our lives harder, but potentially safer.
- There are many system and admin privileges that are excluded from schema privileges, listed here.
Source: oracle-base.com
Posts
0 comments:
Post a Comment