Use administrative privileges

Overview

There are multiple pre-set users that divide up administrative privileges.

References

• Oracle 12.1 Database Security Guide
  • 4 Configuring Privilege and Role Authorization
    • Managing Administrative Privileges
• Oracle 12.1 Database Reference
  • V$PWFILE_USERS

Scenario

Outline

A common user, called C##USER, will be granted SYSBACKUP privilege in the JAR database on PADSTOW.

Grant SYSBACKUP Privilege

I ran the following SQL to grant the SYSBACKUP privilege to the common user, C##USER, in the JAR database:

grant sysbackup to c##user; alter user c##user identified by password1;

Verify Granted Privilege

I ran the following SQL to see what administrative privileges have been granted in the JAR database:

select * from v$pwfile_users;

The output was:

USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID ------------------------------ ----- ----- ----- ----- ----- ----- ---------- SYS TRUE TRUE FALSE FALSE FALSE FALSE 0 C##USER FALSE FALSE FALSE TRUE FALSE FALSE 1

Connect Using Privileges

I ran the following SQL to connect to the JAR database without using SQL*Net:

SQL> connect c##user/password1 as sysbackup Connected. SQL> show user USER is "SYSBACKUP" SQL> connect c##user/password1 as sysoper Connected. SQL> show user USER is "PUBLIC"

The user and password is ignored for Bequeath connections.

I checked the TNS alias for the root container as follows:

SQL> !tnsping jar_padstow TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 02-APR-2020 20:33:37 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = padstow.yaocm.id.au)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = jar_padstow.yaocm.id.au))) OK (10 msec)

I ran the following SQL to connect to the JAR database using SQL*Net:

SQL> connect c##user/password1@jar_padstow as sysbackup Connected. SQL> show user USER is "SYSBACKUP" SQL> connect c##user/password1@jar_padstow as sysdba ERROR: ORA-01031: insufficient privileges Warning: You are no longer connected to ORACLE. SQL>

Now the privileges are checked properly.