8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 24ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Schema Privileges in Oracle Database 23ai

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

Traditional auditing was deprecated in Oracle 21c, and has been desupported in Oracle 23ai. Make sure you are using Unified Auditing. You can read about handling the transition to unified auditing here.

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.

For more information see:

Managing Schema Privileges

Hope this helps. Regards Tim...

Back to the Top.