Use administrative privileges

Contents

  1. 1 Overview
  2. 2 References
  3. 3 Scenario
    1. 3.1 Outline
    2. 3.2 Grant SYSBACKUP Privilege
    3. 3.3 Verify Granted Privilege
    4. 3.4 Connect Using Privileges

Overview

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

References

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.