Understanding Schemas vs. Users- Users and Basic Security
A schema is a collection of database objects (such as tables and indexes). A user is an account to connect to the database with the username and password. Users can also own objects, which then the owner is the schema of the objects. Already discussed was how there are default schemas created to own database system objects.
When you connect as a user, by default you can manipulate objects in the schema owned by the user with which you connected to the database. For example, when you attempt to describe a table, Oracle by default accesses the current user’s schema. Therefore, there is no reason to preface the table name with the currently connected
user (owner). Suppose the currently connected user is INV_MGMT. Consider the following DESCRIBE command:
The prior statement is identical in function to the following statement:
You can alter your current user’s session to point at a different schema via the ALTER SESSION statement:
This statement does not grant the current user (in this example, INV_MGMT) any extra privileges. The statement does instruct Oracle to use the schema qualifier SALES for any subsequent SQL statements that reference database objects. If the appropriate privileges have been granted, the INV_MGMT user can access the SALES user’s objects without having to prefix the schema name to the object name.
Just as describe and desc are identical functions, describing the table ORDERS is the same as using SALES.ORDERS.
If alter session is set to SALES, the results are the same:
Schema-Only Accounts
As with some of the default accounts created, you can also create a schema-only account. These are designed to own the objects and not be used as accounts to log into the database and are created without a password. This is ideal for application schemas and holds all of the objects, so changes to these objects can be done if granted the privilege to access these accounts. These accounts have no privilege to log in directly to the database. So even without a password, there is no possibility to log in, and an error will occur.
In the dba_users table, this user will have an AUTHENTICATION_TYPE=NONE, and the password column will also be NULL. Schema-only accounts can have privileges granted to create tables and other objects; however, they cannot have any of the administrative privileges assigned to them. Even granting CREATE SESSION to the schema-only account will not allow you to log in to this schema account directly.
To perform the DDL statements in other accounts including the schema-only accounts, a proxy connection can be made. Here is an example using hsolosdba as our account as we log in to the database, and the schema-only account is app1.
That is correct; no FROM clause is needed in 23c. In prior versions, you would need to use FROM DUAL in the query. Also in 23c, you can grant privileges to the schema, and it doesn’t have to be for each object.
This schema can be created without a password so that it can simply be used as an application schema for the objects in the database. Privileges can be granted to the schema for all the objects of that schema.
You cannot log in as this schema account; the schema-only account is just the owner of the objects or the owner to run code and procedures. This is something to consider for application schemas; and, as we will see in the upcoming section “Managing Privileges,” grants and permissions can still be handled by roles for these objects.
