Administrative Privileges

Administrative Privileges

Administrative privileges that are required for an administrator to perform basic database operations are granted through two special system privileges, SYSDBA and SYSOPER

Note:
The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open. Control of these privileges is totally outside of the database itself.
The SYSDBA and SYSOPER privileges can also be thought of as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other fashion. For example, you if you have the SYSDBA privilege, you can connect to the database by specifying CONNECT AS SYSDBA.

SYSOPER Privileges

1.Perform STARTUP and SHUTDOWN operations

2.CREATE SPFILE

3.ALTER DATABASE OPEN/MOUNT/BACKUP

4.ALTER DATABASE ARCHIVELOG

5.ALTER DATABASE RECOVER (Complete recovery only. Any form of incomplete recovery, such as UNTIL TIME|CHANGE|CANCEL|CONTROLFILE requires connecting as SYSDBA.)

6.Includes the RESTRICTED SESSION privilege

This privilege allows a user to perform basic operational tasks, but without the ability to look at user data.

SYSDBA Privileges

1.Perform STARTUP and SHUTDOWN operations

2.ALTER DATABASE: open, mount, back up, or change character set

3.CREATE DATABASE

4.DROP DATABASE

5.CREATE SPFILE

6.ALTER DATABASE ARCHIVELOG

7.ALTER DATABASE RECOVER

8.Includes the RESTRICTED SESSION privilege

Effectively, this system privilege allows a user to connect as user SYS.

When you connect with SYSDBA or SYSOPER privileges, you connect with a default schema, not with the schema that is generally associated with your username. For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC.

SQL> conn scott/tiger
Connected.
SQL> drop table admin_test purge;

Table dropped.

SQL> create table admin_test ( no number);

Table created.

SQL> conn scott/tiger as sysdba
Connected.
SQL> sho user
USER is "SYS"
SQL> select * from admin_test;
select * from admin_test
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> grant SYSOPER to scott;

Grant succeeded.

SQL> conn scott/tiger as sysoper
Connected.
SQL> select * from admin_test;
select * from admin_test
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> show user
USER is "PUBLIC"

For connecting SYSDBA privs to database any user need

1. SQLNET.AUTHENTICATION_SERVICES= (NTS) in sqlnet.ora
2. OS user is member of ORA_DBA group
3. Explicit granted SYSDBA role to user

For connection SYSOPER privs to database any user need

1. Explicit granted SYSOPER role to user