Thursday, July 23, 2015

Connecting with a user which has SYSDBA privilege, you act like SYS user

bash-3.2$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 23 11:12:10 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL>
SQL> show user
USER is "SYS"
SQL>
SQL>
SQL>
SQL> show parameter REMOTE_LOGIN_PASSWORDFILE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL>
SQL>

SQL>
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

SQL>
SQL>
SQL>
SQL> create user admin identified by admin;

User created.

SQL> grant sysdba to admin;

Grant succeeded.

SQL> grant dba to admin;

Grant succeeded.

SQL>
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
ADMIN                          TRUE  FALSE FALSE

SQL>

SQL>
SQL> grant sysoper to admin;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
ADMIN                          TRUE  TRUE  FALSE


SQL>conn admin/admin
SQL> show user
USER is "ADMIN"
SQL>
SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 SYSAUX                         YES NO  YES
         4 USERS                          YES NO  YES
         3 TEMP                           NO  NO  YES
         6 EXAMPLE                        YES NO  YES
         5 UNDOTBS2                       YES NO  YES

6 rows selected.

but when you go to shutdown  databasethen use sysdba role

SQL>
SQL> conn admin/admin as sysdba
Connected.
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

SQL> conn admin/admin as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL>
SQL>

if use without  as sysdba so you got ora error

SQL>
SQL> conn admin/admin
Connected.
SQL>
SQL> shut immediate
ORA-01031: insufficient privileges
SQL>


No comments:

Post a Comment

About the Author : Anurag Pandey | Database cloud Architect | Oracle | SQL Server | PostgreSQL | AI and Data Platform Enthusiast

Anurag Pandey is a Database Architect with expertise in Oracle, PostgreSQL, SQL Server, Linux, and enterprise database platforms and specializes in database architecture, performance tuning, high availability, disaster recovery, cloud modernization, and operational excellence. With extensive experience in enterprise-scale environments, Anurag is passionate about leveraging AI, automation, and modern data platforms to improve reliability, scalability, and business outcomes. He actively contributes to technical communities through knowledge sharing, architecture guidance, and thought leadership in database and data engineering domains.

Follow more articles at

Visit Blog

Oracle Database Deep Health Check Checklist

Oracle Database Deep Health Check Checklist Recommended assessment levels Level Purpose Typical scope L1 Operational health Availability, sp...