Oracle 23c > Security

Navigating Schema Privileges in Oracle Database 23c

/ Data Security 23c, Oracle DB 23c New Features / By

In Oracle Database 23c, schema privileges provide an easy way to simplify grants when users or roles require access to all objects within a schema. This guide will walk through the setup, granting, testing, and revoking of schema privileges, accompanied by new and diverse code examples.

Setup

Before delving into schema privileges, let’s establish the initial environment. The subsequent code creates two test users, a role, and populates the schema with distinct objects.

-- Connect as sysdba
CONN sys/oracle@//localhost:1521/PDB2 AS sysdba

-- Remove existing users
DROP USER IF EXISTS user_alpha CASCADE;
DROP USER IF EXISTS user_beta CASCADE;

-- Create users and grant roles
CREATE USER user_alpha IDENTIFIED BY alpha_pass QUOTA UNLIMITED ON USERS;
ALTER USER user_alpha QUOTA 10m ON SYSTEM;
GRANT DB_DEVELOPER_ROLE TO user_alpha;

CREATE USER user_beta IDENTIFIED BY beta_pass QUOTA UNLIMITED ON USERS;
GRANT CREATE SESSION TO user_beta;

CREATE ROLE alpha_schema_role;

-- Connect as user_alpha
CONN user_alpha/alpha_pass@//localhost/pdb2

-- Create various objects in the schema
CREATE SEQUENCE alpha_seq;

CREATE TABLE alpha_table (id NUMBER);
INSERT INTO alpha_table VALUES (alpha_seq.NEXTVAL);
COMMIT;

CREATE VIEW alpha_view AS SELECT * FROM alpha_table;

CREATE OR REPLACE PROCEDURE alpha_proc AS BEGIN NULL; END;
/

Grant Schema Privileges

Now, let’s provide different schema privileges to user_beta and the role.

-- Connect as user_alpha
CONN user_alpha/alpha_pass@//localhost/pdb2

-- Grant privileges on Sequences
--  Observe we are not defining specific sequence. The gran is based on the object type, not object name.
GRANT SELECT ANY SEQUENCE ON SCHEMA user_alpha to user_beta;

-- Grant privileges on Tables, Views
GRANT SELECT ANY TABLE ON SCHEMA user_alpha TO user_beta;
GRANT INSERT ANY TABLE ON SCHEMA user_alpha TO user_beta;
GRANT UPDATE ANY TABLE ON SCHEMA user_alpha TO user_beta;
GRANT DELETE ANY TABLE ON SCHEMA user_alpha TO user_beta;

-- grant the same privileges to the role:
GRANT SELECT ANY TABLE ON SCHEMA user_alpha TO alpha_schema_role;
GRANT INSERT ANY TABLE ON SCHEMA user_alpha TO alpha_schema_role;
GRANT UPDATE ANY TABLE ON SCHEMA user_alpha TO alpha_schema_role;
GRANT DELETE ANY TABLE ON SCHEMA user_alpha TO alpha_schema_role;


-- Grant privileges on Procedures
GRANT EXECUTE ANY PROCEDURE ON SCHEMA user_alpha TO user_beta;
GRANT EXECUTE ANY PROCEDURE ON SCHEMA user_alpha TO alpha_schema_role;

Test Schema Privileges

Now, let’s assess the granted privileges.

-- Connect as user_beta
CONN user_beta/beta_pass@//localhost/pdb2

-- Test Sequences
SQL> SELECT user_alpha.alpha_seq.NEXTVAL FROM DUAL;

   NEXTVAL
----------
         2

-- Test Tables
SQL> SELECT COUNT(*) FROM user_alpha.alpha_table;

  COUNT(*)
----------
         1


-- Test Views
SQL> SELECT * FROM user_alpha.alpha_view;

        ID
----------
         1


-- Test Procedures
SQL> EXEC user_alpha.alpha_proc;

PL/SQL procedure successfully completed.

Considerations

Here are some key points to consider when working with schema privileges:

  • The system privilege applies to both current and future objects in the schema.
  • Schema privileges can be granted to both users and roles.
  • From a security standpoint, schema privileges might pose a challenge as they grant access to all objects of a specific type, contradicting the “least privileges” principle.
  • Some system and admin privileges are excluded from schema privileges.

Pre-23c Scenarios

Before 23c, we have two options to implement a similar task: granting Objects privileges, or granting ANY system privileges.

Object privileges in Oracle database control actions on specific database objects, such as tables or views, while schema privileges operate at a higher level, granting permissions on all objects within a schema, simplifying access management for multiple related objects within the same schema.

Before Oracle Database 23c, if you wanted to grant broad access across all objects to a user or role, you might have considered using the “ANY” privileges, such as SELECT ANY TABLE, INSERT ANY TABLE, or EXECUTE ANY PROCEDURE. These privileges allowed the specified actions on any table, view, or procedure within the database, regardless of the schema. Schema privileges introduced in Oracle 23c offer a more controlled, granular, and secure approach by confining permissions to objects within a specific schema, aligning better with the principle of least privilege.

Conclusion

In conclusion, schema privileges offer a robust tool for managing access to objects within a schema, but careful consideration should be given to security implications. Always prioritize the “least privileges” principle for a more secure database environment.

Scroll to Top