1. Home
  2. Knowledge Base
  3. Oracle 23c
  4. Oracle 23c Schema Level Privileges and Schema-Only Users
  1. Home
  2. Knowledge Base
  3. Security
  4. Oracle 23c Schema Level Privileges and Schema-Only Users

Oracle 23c Schema Level Privileges and Schema-Only Users

Oracle Database 23c now supports schema level privileges in addition to the existing object and system privileges. Application access can be granted at the schema level as opposed to individual objects like tables or views. Prior to Oracle 23c, if a new object is created in a particular schema, we will need to again grant whatever are the required privileges. But now with Oracle Database 23c we can grant a privilege for the entire schema, thereby simplifying application authorizations and improving security.

Schema-Only accounts were introduced in Oracle 18c and enhanced in 19c. Basically a schema only account is an account with no password and connects to the database as a proxy user.

With schema-only accounts we can separate the user accounts in the database which actually own objects as opposed to a normal user which needs access to those schema objects.

Haven’t we all faced this issue in the past where application access is blocked because the user account which owns the database objects has been locked for some reason – like the password has expired!

Let us have a look both schema-only accounts and the new 23c feature schema level privileges.

Identify existing schema-only accounts which exist in the database.

SQL> col username format a30
SQL> col authentication_type format a20
SQL> set pages 100
SQL> select username,authentication_type from dba_users order by 2;

USERNAME				 AUTHENTICATION_TYPE
---------------------------------------- --------------------
DBSFWUSER				 NONE
SYS$UMF 				 NONE
DGPDB_INT				 NONE
SYSKM					 NONE
DIP					 NONE
OJVMSYS 				 NONE
GSMUSER 				 NONE
REMOTE_SCHEDULER_AGENT			 NONE
SYSBACKUP				 NONE
MDDATA					 NONE
GSMCATUSER				 NONE
WMSYS					 NONE
XDB					 NONE
MDSYS					 NONE
OLAPSYS 				 NONE
SYSRAC					 NONE
ORDS_METADATA				 NONE
XS$NULL 				 NONE
LBACSYS 				 NONE
OUTLN					 NONE
DBSNMP					 NONE
APPQOSSYS				 NONE
APEX_220200				 NONE
SYSDG					 NONE
GGSYS					 NONE
ANONYMOUS				 NONE
FLOWS_FILES				 NONE
CTXSYS					 NONE
DVSYS					 NONE
DVF					 NONE
AUDSYS					 NONE
GSMADMIN_INTERNAL			 NONE
GGSHAREDCAP				 NONE
HRREST					 PASSWORD
SYS					 PASSWORD
OE					 PASSWORD
BI					 PASSWORD
SH					 PASSWORD
ORDS_PUBLIC_USER			 PASSWORD
HR					 PASSWORD
IX					 PASSWORD
PDBADMIN				 PASSWORD
AV					 PASSWORD
APEX_REST_PUBLIC_USER			 PASSWORD
APEX_PUBLIC_USER			 PASSWORD
APEX_LISTENER				 PASSWORD
SYSTEM					 PASSWORD
PM					 PASSWORD

48 rows selected.

Create a schema-only account with CREATE TABLE and CREATE SESSION privileges. Note the use of the NO AUTHENTICATION clause.

SQL> CREATE USER app_owner NO AUTHENTICATION;

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE TO app_owner;

Grant succeeded.

SQL> ALTER USER app_owner QUOTA UNLIMITED ON users;

User altered.

Create a password authenticated user with only CREATE SESSION privilege.

SQL> CREATE USER app_user IDENTIFIED BY Oracle_4U;

User created.

SQL> GRANT CREATE SESSION TO app_user;

Grant succeeded.

Query DBA_USERS to identify and differentiate password authenticated accounts from schema-only accounts.

SQL> select USERNAME, AUTHENTICATION_TYPE from dba_users where username in ('APP_OWNER','APP_USER');

USERNAME				 AUTHENTICATION_TYPE
---------------------------------------- --------------------
APP_OWNER				 NONE
APP_USER				 PASSWORD

Connect as the SYSTEM user and create the MYOBJECTS table in the APP_OWNER schema.

[oracle@localhost Desktop]$ sqlplus system/oracle@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue May 2 01:36:05 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Mon Apr 03 2023 17:02:53 +00:00

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0


SQL> CREATE TABLE app_owner.myobjects AS SELECT * FROM ALL_OBJECTS;

Table created.

SQL> SELECT COUNT(*) FROM app_owner.myobjects;

  COUNT(*)
----------
     75377

Connect as the APP_USER account and execute a SELECT statement on the MYOBJECTS table in the APP_OWNER schema. As expected, the query will fail with an ORA-00942 error.

[oracle@localhost Desktop]$ sqlplus app_user/Oracle_4U@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue May 2 01:39:53 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> SELECT COUNT(*) FROM app_owner.myobjects;
SELECT COUNT(*) FROM app_owner.myobjects
                               *
ERROR at line 1:
ORA-00942: table or view does not exist

Alter the APP_OWNER user account to allow connections via a proxy user.

[oracle@localhost Desktop]$ sqlplus system/oracle@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue May 2 01:43:01 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Tue May 02 2023 01:36:05 +00:00

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> ALTER USER app_owner GRANT CONNECT THROUGH app_user;

User altered.

Connect via the proxy account. Note that now the same SELECT statement which had failed earlier executes successfully.

[oracle@localhost Desktop]$ sqlplus app_user[app_owner]/Oracle_4U@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue May 2 01:44:21 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0


SQL> SHOW USER 
USER is "APP_OWNER"


SQL> SELECT COUNT(*) FROM app_owner.myobjects;

  COUNT(*)
----------
     75377

Note the SESSION_USER and PROXY_USER accounts.  Query the ALL_TABLES data dictionary view to verify the owner of the MYOBJECTS table.

SQL> col user format a20
SQL> col session_user format a20
SQL> col proxy_user format a20
SQL> select user, sys_context('USERENV','SESSION_USER') as session_user,sys_context('USERENV','PROXY_USER') as proxy_user from dual;

USER		     SESSION_USER	  PROXY_USER
-------------------- -------------------- --------------------
APP_OWNER	     APP_OWNER		  APP_USER


SQL> SELECT OWNER FROM ALL_TABLES WHERE TABLE_NAME='MYTABLES';

OWNER
--------------------------------------------------------------------------------
APP_OWNER

Alter the schema-only user account to change it to a password authenticated user account.

[oracle@localhost Desktop]$ sqlplus system/oracle@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue May 2 01:49:22 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Tue May 02 2023 01:43:01 +00:00

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> ALTER USER app_owner IDENTIFIED BY Oracle_4U;

User altered.

SQL> col AUTHENTICATION_TYPE format a30
SQL> SELECT AUTHENTICATION_TYPE from dba_users where username='APP_OWNER';

AUTHENTICATION_TYPE
------------------------------
PASSWORD

SQL> quit
Disconnected from Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

We can now connect as the APP_OWNER account using a password.

[oracle@localhost Desktop]$ sqlplus app_owner/Oracle_4U@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue May 2 01:52:12 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL>
SQL> quit
Disconnected from Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

Alter the APP_OWNER account to convert it back to a schema-only account.

[oracle@localhost Desktop]$ sqlplus system/oracle@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue May 2 01:55:28 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Tue May 02 2023 01:52:53 +00:00

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> ALTER USER app_owner NO AUTHENTICATION;

User altered.

SQL> quit
Disconnected from Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

Now the earlier SQL*Plus connection used by the APP_OWNER account will fail as a password is being provided for a schema-only account

[oracle@localhost Desktop]$ sqlplus app_owner/Oracle_4U@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue May 2 01:56:00 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid credential or not authorized; logon denied


Enter user-name:

Connect as the APP_USER account and attempt a SELECT statement on the APP_OWNER.MYOBJECTS table. Note that the query will fail as the APP_USER account does not have the required object level privileges.

[oracle@localhost Desktop]$ sqlplus app_user/Oracle_4U@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue May 2 02:05:51 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Tue May 02 2023 01:44:21 +00:00

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> SELECT COUNT(*) FROM app_owner.myobjects;
SELECT COUNT(*) FROM app_owner.myobjects
                               *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> quit
Disconnected from Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

Oracle Database 23c introduces Schema Privileges to simplify application authorizations and access as well as improving security. Privileges can now be granted at the schema level as opposed to the individual object level.

SELECT ANY TABLE privilege for all tables in the APP_OWNER schema (existing as well as new tables) can be provided to a specific database user via a single statement as shown below.

[oracle@localhost Desktop]$ sqlplus system/oracle@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue May 2 02:07:27 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Tue May 02 2023 01:55:28 +00:00

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> GRANT SELECT ANY TABLE ON SCHEMA app_owner TO app_user;

Grant succeeded.

SQL> quit
Disconnected from Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

The APP_OWNER user account now has SELECT privileges on all tables in the APP_OWNER schema.

[oracle@localhost Desktop]$ sqlplus app_user/Oracle_4U@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue May 2 02:08:11 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Tue May 02 2023 02:05:51 +00:00

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> SELECT COUNT(*) FROM app_owner.myobjects;

  COUNT(*)
----------
     75377

SQL> SELECT COUNT(*) FROM app_owner.mytables;

  COUNT(*)
----------
       118

Create a new table MYINDEXES in the APP_OWNER schema.

[oracle@localhost Desktop]$ sqlplus app_user[app_owner]/Oracle_4U@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue May 2 02:10:55 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Tue May 02 2023 01:52:12 +00:00

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> CREATE TABLE myindexes AS SELECT * FROM ALL_INDEXES;

Table created.

Even though explicit object level access to the MYINDEXES table has not been granted to the APP_USER account, the user is able to access the new table in the APP_OWNER schema because of the schema level privileges which were granted earlier.

[oracle@localhost Desktop]$ sqlplus app_user/Oracle_4U@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue May 2 02:11:44 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Tue May 02 2023 02:10:55 +00:00

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> SELECT COUNT(*) FROM app_owner.myindexes;

  COUNT(*)
----------
       113

Schema level privileges granted currently are SELECT ANY TABLE only. So, the DELETE statement attempted by the APP_USER account will fail.

SQL> delete app_owner.myindexes;
delete app_owner.myindexes
                 *
ERROR at line 1:
ORA-41900: missing DELETE privilege on "APP_OWNER"."MYINDEXES"

Query the USER_SCHEMA_PRIVS and SESSION_SCHEMA_PRIVS data dictionary views to identify the schema level privileges granted to the user APP_USER.

SQL> select privilege,schema from USER_SCHEMA_PRIVS;

PRIVILEGE
----------------------------------------
SCHEMA
--------------------------------------------------------------------------------
SELECT ANY TABLE
APP_OWNER


SQL> select privilege,schema from SESSION_SCHEMA_PRIVS;

PRIVILEGE
----------------------------------------
SCHEMA
--------------------------------------------------------------------------------
SELECT ANY TABLE
APP_OWNER
Updated on May 3, 2023

Was this article helpful?

Related Articles

Leave a Comment