A database account, the objects it owns, and the work it is allowed to perform are related through identity and privileges. Separating these responsibilities lets an application own its tables, a reader query the required data, and an administrator perform maintenance with appropriate access.
Oracle DBA Lesson 66 Part 1 — Separate Login, Ownership and Administration
User, schema and access
Every database user has a schema with the same name. The schema is the collection of objects that user owns. It can be empty. APP_OWNER.EMPLOYEES is the EMPLOYEES table in the APP_OWNER schema. APP_READER has its own schema even when it owns no tables.
| Question | What to inspect |
|---|---|
| Who connects? | Authentication method and actual session identity |
| Which schema owns the object? | The object's owner, such as APP_OWNER |
| What work is allowed? | Applicable system/object privileges and enabled roles in the intended container |
For a normal authenticated login, CREATE SESSION permits a database session. Reading another schema's table also requires appropriate access to that object. A bounded grant is:
GRANT SELECT ON app_owner.employees TO app_reader;This grants selection of the specified table. The table remains owned by APP_OWNER. The recipient does not need the owner's privileges or a broad administrative role to read it.
Authentication and session access alone do not determine every operation a user may perform. Include relevant grants, enabled roles, and any configured security controls when assessing effective access.
See schema objects and ownership and creating user accounts.
Ownership without direct authentication
A schema-only account has no authentication method of its own and cannot directly authenticate. It can own objects and have privileges. DBA_USERS.AUTHENTICATION_TYPE = 'NONE' identifies a schema-only account.
Separately authenticated, configured proxy access is one supported way to work with a schema-only owner.
Status and authentication type are read together:
| USERNAME | ACCOUNT_STATUS | AUTHENTICATION_TYPE | ORACLE_MAINTAINED | COMMON |
|---|---|---|---|---|
| APP_OWNER | OPEN | NONE | N | NO |
| APP_READER | OPEN | PASSWORD | N | NO |
OPEN alongside NONE still describes a schema-only account. For a normal password login, inspect authentication, status, CREATE SESSION, the intended service and container, and the applicable policy. These inventory rows alone do not show a successful connection or list object privileges.
See schema-only accounts.
CURRENT_SCHEMA and name lookup
In an ordinary APP_READER session:
ALTER SESSION SET CURRENT_SCHEMA = app_owner;
SELECT employee_id FROM employees;The unqualified table reference now resolves against APP_OWNER and targets APP_OWNER.EMPLOYEES. The session remains APP_READER and retains its own privileges. The selection still needs the relevant grant.
Inspect the session identity and the lookup context:
SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') AS session_user,
SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') AS current_schema
FROM dual;SESSION_USER records the session user. CURRENT_SCHEMA records the default schema for object resolution. After the change above, those values are APP_READER and APP_OWNER. Administrative and proxy connections use their own session semantics. In those connections, SESSION_USER alone does not identify the authenticating person.
See ALTER SESSION for CURRENT_SCHEMA.
Supplied accounts and administrative duties
SYS owns the critical dictionary structures maintained by Oracle. SYSTEM supports administrative information and Oracle tools and options. Application tables belong in an application schema. For daily work, use an individually identified administrator with privileges appropriate to the duty.
| Duty | Administrative privilege | Interpretation |
|---|---|---|
| Backup and recovery | SYSBACKUP | Enables the documented backup/recovery operations |
| Data Guard administration | SYSDG | Enables documented Data Guard operations |
| Encryption key management | SYSKM | Enables documented keystore/key-management operations |
These are powerful, task-focused administrative privileges. Their names also identify Oracle-supplied accounts. A grant to an individually managed administrator keeps routine human work off those supplied logins. The privilege is exercised when connecting with the appropriate AS clause. Authentication, password-file or OS configuration, and container scope must support that connection.
SYSDBA provides broader administrative powers. A backup duty is a reason to evaluate SYSBACKUP instead of automatically assigning SYSDBA. The predefined DBA role and the SYSDBA administrative privilege are distinct mechanisms. Administrative connection modes can use special session identities and current schemas, so keep the original authentication and audit context when investigating accountability.
SYSRAC is used by the Oracle Clusterware agent through operating system authentication for RAC operations. The 19c Administrator's Guide specifies that this privilege cannot be granted to database users and is unsupported in a password file. It is an infrastructure agent path, not the normal human operator privilege.
See administrative accounts and authentication and managing administrative privileges.
Inventory in the intended container
On Oracle Database 19c, in the intended application PDB, confirm the connected user and container, then read the selected accounts:
SHOW USER
SHOW CON_NAME
SELECT username, account_status, authentication_type,
oracle_maintained, common
FROM dba_users
WHERE username IN ('SYS', 'SYSTEM', 'DBSNMP',
'APP_OWNER', 'APP_READER',
'BACKUP_ADMIN')
ORDER BY username;SHOW commands run in SQL*Plus. The query runs in the database. It inventories the selected users in the connected container. A missing row can indicate a different name, container, or visibility condition.
| Column | Useful interpretation |
|---|---|
| USERNAME | Account identifier; the associated schema has the same name |
| ACCOUNT_STATUS | Status such as OPEN, LOCKED or password-expiration states; read with authentication and policy |
| AUTHENTICATION_TYPE | Authentication method, including PASSWORD, EXTERNAL, GLOBAL or NONE |
| ORACLE_MAINTAINED | Y means created/maintained by Oracle-supplied scripts; follow the documented Oracle component procedure |
| COMMON | YES identifies a common identity; NO identifies a local user in this context. Review privilege scope separately |
The inventory does not enumerate object grants, enabled roles, or administrative privileges. Inspect the existing grant and audit records for the required task. When ORACLE_MAINTAINED is Y for an unfamiliar account, identify its component and dependencies and use its documented Oracle procedure.
See DBA_USERS.
Recap
Separate who connects, which schema owns the objects, and which privileges allow the work. Every user has a same-named schema, and that schema can be empty. CREATE SESSION permits a normal authenticated session. GRANT SELECT ON app_owner.employees TO app_reader lets the reader query that table while APP_OWNER keeps ownership. ALTER SESSION SET CURRENT_SCHEMA changes default name resolution only. The session user keeps its own privileges. In administrative and proxy connections, SESSION_USER alone does not identify the authenticating person.
AUTHENTICATION_TYPE = 'NONE' identifies a schema-only account. It can own objects and hold privileges, and it has no direct authentication method. OPEN with NONE is still schema-only. Configured proxy access is a separate way to work with that owner. Match an administrator's duty to SYSBACKUP, SYSDG, or SYSKM, and connect with the corresponding AS clause. Evaluate SYSBACKUP for backup work before assigning the broader SYSDBA privilege. The DBA role and SYSDBA are distinct. SYSRAC is the Clusterware agent privilege for RAC. It cannot be granted to database users and is unsupported in a password file. Read status, authentication, Oracle maintenance, and common or local scope from DBA_USERS in the intended container. When ORACLE_MAINTAINED is Y, identify the component and use its documented procedure.
No comments:
Post a Comment