Sample Connect as sysdba

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

o Includes the RESTRICTED SESSION privilege

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

SYSBACKUP This privilege allows a user to perform backup and recovery operations either from Oracle Recovery Manager (RMAN) or SQL*Plus.

SYSDG This privilege allows a user to perform Data Guard operations. You can use this privilege with either Data Guard Broker or the DGMGRL command-line interface.

SYSKM This privilege allows a user to perform Transparent Data Encryption keystore operations.

Podešavanje okruženja

set ORACLE_HOME=D:\app\darkoj\product\12.1.0\dbhome_1

set PATH=%ORACLE_HOME%/bin;%PATH%

set ORACLE_SID=ORCLD

SYSDBA i SYSOPER sistemske privilegije dozvoljavaju korišćenje instance baze čak i kada baza nije otvorena. Kontrola ovih privilegija je potpuno van baze.

Na primer, … ako imate SYSDBA privilegiju možete se konektovati korišćenjem CONNECT AS SYSDBA sintakse.

Konekcija na bazu

F:\MyPassport\Kurs\temp\12c\KURS\DBA Examples>sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 14 14:53:45 2015

Enter user-name: sys/elcaro@pdb2 as sysdba

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL> show user

USER is "SYS“

-- KOJI KORISNICI TRENUTNO POSTOJE U BAZI

SQL> desc all_users

Name Null? Type

----------------------------------------- -------- -------------------------

USERNAME NOT NULL VARCHAR2(128)

USER_ID NOT NULL NUMBER

CREATED NOT NULL DATE

COMMON VARCHAR2(3)

ORACLE_MAINTAINED VARCHAR2(1)

SQL> column USERNAME format a50

SQL> select * from all_users

USERNAME USER_ID CREATED COM O

-------------------------------------------------- ---------- --------- --- -

SCOTT1 118 14-SEP-15 NO N

HR 116 09-SEP-15 NO N

C##TESTN 113 05-JUL-15 YES N

C##TEST_USER3 111 04-JUL-15 YES N

C##USER1 110 01-JUL-15 YES N

TEST1 109 28-JUN-15 NO N

C##TEST 108 15-JUN-15 YES N

SCOTT 106 07-JUN-15 NO N

TOAD 105 31-MAY-15 NO N

TESTUSER 104 26-MAY-15 NO N

C##TEST_USER2 103 26-MAY-15 YES N

PDB2_ADM 102 26-MAY-15 NO N

TEST 107 15-JUN-15 NO N

DVF 99 28-JUN-13 YES Y

APEX_040200 98 28-JUN-13 YES Y

APEX_PUBLIC_USER 95 28-JUN-13 YES Y

FLOWS_FILES 94 28-JUN-13 YES Y

LBACSYS 92 28-JUN-13 YES Y

SPATIAL_CSW_ADMIN_USR 90 28-JUN-13 YES Y

SPATIAL_WFS_ADMIN_USR 87 28-JUN-13 YES Y

MDDATA 85 28-JUN-13 YES Y

OLAPSYS 82 28-JUN-13 YES Y

DVSYS 1279990 28-JUN-13 YES Y

SI_INFORMTN_SCHEMA 78 28-JUN-13 YES Y

ORDPLUGINS 77 28-JUN-13 YES Y

ORDDATA 76 28-JUN-13 YES Y

ORDSYS 75 28-JUN-13 YES Y

CTXSYS 73 28-JUN-13 YES Y

OJVMSYS 69 28-JUN-13 YES Y

WMSYS 61 28-JUN-13 YES Y

GSMCATUSER 60 28-JUN-13 YES Y

MDSYS 79 28-JUN-13 YES Y

ANONYMOUS 50 28-JUN-13 YES Y

XDB 49 28-JUN-13 YES Y

APPQOSSYS 48 28-JUN-13 YES Y

DBSNMP 47 28-JUN-13 YES Y

ORACLE_OCM 36 28-JUN-13 YES Y

DIP 23 28-JUN-13 YES Y

GSMUSER 22 28-JUN-13 YES Y

GSMADMIN_INTERNAL 21 28-JUN-13 YES Y

XS$NULL 2147483638 28-JUN-13 YES Y

OUTLN 13 28-JUN-13 YES Y

SYSKM 2147483619 28-JUN-13 YES Y

SYSDG 2147483618 28-JUN-13 YES Y

SYSBACKUP 2147483617 28-JUN-13 YES Y

SYSTEM 8 28-JUN-13 YES Y

AUDSYS 7 28-JUN-13 YES Y

SYS 0 28-JUN-13 YES Y

48 rows selected.

……

Sistemske privilegije konektovanog korisnika

SQL> select * from dict where table_name like 'USER_SYS_PRIVS'

TABLE_NAME COMMENTS

-------------- ------------------------------------------------

USER_SYS_PRIVS System privileges granted to current user

--KOJE SU MOJE SISTEMSKE PRIVILEGIJE

SQL> column username format a20

SQL> column PRIVILEGE format a45

SQL> SELECT USERNAME,PRIVILEGE FROM USER_SYS_PRIVS;

USERNAME PRIVILEGE

-------------------- ---------------------------------------------

SYS CREATE ANY CREDENTIAL

SYS DROP ANY SQL TRANSLATION PROFILE

SYS USE ANY SQL TRANSLATION PROFILE

SYS ALTER ANY SQL TRANSLATION PROFILE

SYS UPDATE ANY CUBE BUILD PROCESS

SYS CREATE MINING MODEL

SYS DROP ANY ASSEMBLY

SYS DROP ANY EDITION

SYS CREATE EXTERNAL JOB

SYS MANAGE FILE GROUP

SYS ADMINISTER SQL TUNING SET

SYS MANAGE SCHEDULER

SYS CREATE ANY RULE

SYS CREATE ANY EVALUATION CONTEXT

SYS CREATE ANY CONTEXT

SYS MANAGE ANY QUEUE

SYS ALTER ANY INDEXTYPE

SYS CREATE ANY MATERIALIZED VIEW

SYS EXECUTE ANY PROCEDURE

SYS ALTER ANY ROLE

SYS GRANT ANY ROLE

SYS DROP PUBLIC DATABASE LINK

SYS CREATE DATABASE LINK

SYS CREATE ANY INDEX

SYS ALTER ANY CLUSTER

SYS REDEFINE ANY TABLE

SYS ALTER ROLLBACK SEGMENT

SYS ALTER SESSION

SYS AUDIT SYSTEM

SYS ALTER ANY CUBE BUILD PROCESS

SYS ADMINISTER SQL MANAGEMENT OBJECT

SYS DROP ANY MEASURE FOLDER

SYS CREATE ANY CUBE

SYS COMMENT ANY MINING MODEL

SYS DROP ANY MINING MODEL

SYS DROP ANY SQL PROFILE

SYS ALTER ANY RULE

SYS IMPORT FULL DATABASE

SYS CREATE RULE SET

SYS EXECUTE ANY EVALUATION CONTEXT

SYS FLASHBACK ANY TABLE

SYS CREATE ANY DIMENSION

SYS EXECUTE ANY INDEXTYPE

SYS DROP ANY INDEXTYPE

SYS CREATE ANY INDEXTYPE

SYS CREATE ANY OPERATOR

SYS CREATE LIBRARY

SYS CREATE ANY DIRECTORY

SYS CREATE PROFILE

SYS CREATE VIEW

SYS INSERT ANY TABLE

SYS SELECT ANY TABLE

SYS LOCK ANY TABLE

SYS DROP ANY TABLE

SYS SET CONTAINER

SYS CREATE CUBE

SYS INSERT ANY CUBE DIMENSION

SYS DROP ANY CUBE DIMENSION

SYS ALTER ANY CUBE DIMENSION

SYS MANAGE ANY FILE GROUP

SYS CREATE ANY SQL PROFILE

SYS EXECUTE ANY CLASS

SYS CREATE ANY JOB

SYS ADVISOR

SYS EXPORT FULL DATABASE

SYS RESUMABLE

SYS DROP ANY OUTLINE

SYS DROP ANY DIMENSION

SYS UNDER ANY TABLE

SYS EXECUTE ANY LIBRARY

SYS DROP ANY LIBRARY

SYS CREATE ANY LIBRARY

SYS ALTER ANY TYPE

SYS DROP ANY ROLE

SYS CREATE ROLE

SYS DROP ANY SEQUENCE

SYS ALTER ANY SEQUENCE

SYS CREATE SEQUENCE

SYS CREATE PUBLIC SYNONYM

SYS UPDATE ANY TABLE

SYS CREATE ROLLBACK SEGMENT

SYS UNLIMITED TABLESPACE

SYS CREATE TABLESPACE

SYS INHERIT ANY PRIVILEGES

SYS SELECT ANY CUBE BUILD PROCESS

SYS SELECT ANY MEASURE FOLDER

SYS EM EXPRESS CONNECT

SYS CREATE ANY CUBE BUILD PROCESS

SYS DELETE ANY MEASURE FOLDER

SYS CREATE MEASURE FOLDER

SYS CREATE ANY CUBE DIMENSION

SYS ALTER ANY MINING MODEL

SYS EXECUTE ANY ASSEMBLY

SYS ALTER ANY ASSEMBLY

SYS CREATE ASSEMBLY

SYS CHANGE NOTIFICATION

SYS ADMINISTER ANY SQL TUNING SET

SYS DROP ANY RULE SET

SYS DEBUG ANY PROCEDURE

SYS ENQUEUE ANY QUEUE

SYS EXECUTE ANY TYPE

SYS CREATE ANY TRIGGER

SYS CREATE ANY PROCEDURE

SYS ALTER DATABASE

SYS DROP ANY VIEW

SYS CREATE ANY VIEW

SYS CREATE SYNONYM

SYS DROP ROLLBACK SEGMENT

SYS DROP USER

SYS CREATE USER

SYS EXEMPT DML REDACTION POLICY

SYS FLASHBACK ARCHIVE ADMINISTER

SYS DROP ANY CUBE BUILD PROCESS

SYS UPDATE ANY CUBE

SYS SELECT ANY CUBE

SYS SELECT ANY CUBE DIMENSION

SYS DELETE ANY CUBE DIMENSION

SYS CREATE CUBE DIMENSION

SYS ALTER ANY EDITION

SYS ALTER ANY SQL PROFILE

SYS CREATE RULE

SYS ALTER ANY EVALUATION CONTEXT

SYS CREATE EVALUATION CONTEXT

SYS ON COMMIT REFRESH

SYS MERGE ANY VIEW

SYS CREATE ANY OUTLINE

SYS DROP ANY CONTEXT

SYS GLOBAL QUERY REWRITE

SYS QUERY REWRITE

SYS UNDER ANY VIEW

SYS CREATE INDEXTYPE

SYS CREATE ANY TYPE

SYS CREATE TYPE

SYS DROP ANY DIRECTORY

SYS DROP ANY MATERIALIZED VIEW

SYS ANALYZE ANY

SYS ALTER PROFILE

SYS FORCE ANY TRANSACTION

SYS AUDIT ANY

SYS DROP ANY INDEX

SYS CREATE ANY CLUSTER

SYS ALTER ANY TABLE

SYS CREATE SESSION

SYS ALTER SYSTEM

SYS CREATE PLUGGABLE DATABASE

SYS CREATE ANY MEASURE FOLDER

SYS ALTER ANY CUBE

SYS SELECT ANY MINING MODEL

SYS CREATE ANY MINING MODEL

SYS CREATE JOB

SYS EXECUTE ANY RULE

SYS DROP ANY EVALUATION CONTEXT

SYS GRANT ANY OBJECT PRIVILEGE

SYS ALTER ANY OUTLINE

SYS ALTER ANY LIBRARY

SYS DROP ANY TYPE

SYS GRANT ANY PRIVILEGE

SYS ALTER RESOURCE COST

SYS ALTER ANY TRIGGER

SYS CREATE TRIGGER

SYS FORCE TRANSACTION

SYS DROP ANY CLUSTER

SYS CREATE CLUSTER

SYS COMMENT ANY TABLE

SYS CREATE ANY TABLE

SYS RESTRICTED SESSION

SYS EXEMPT DDL REDACTION POLICY

SYS LOGMINING

SYS CREATE ANY SQL TRANSLATION PROFILE

SYS UPDATE ANY CUBE DIMENSION

SYS CREATE CUBE BUILD PROCESS

SYS CREATE ANY ASSEMBLY

SYS SELECT ANY TRANSACTION

SYS EXECUTE ANY PROGRAM

SYS EXECUTE ANY RULE SET

SYS ALTER ANY RULE SET

SYS DEQUEUE ANY QUEUE

SYS ALTER ANY DIMENSION

SYS DROP ANY OPERATOR

SYS ALTER ANY OPERATOR

SYS CREATE MATERIALIZED VIEW

SYS ALTER ANY PROCEDURE

SYS CREATE PROCEDURE

SYS CREATE ANY SEQUENCE

SYS DROP PUBLIC SYNONYM

SYS ALTER ANY INDEX

SYS DELETE ANY TABLE

SYS CREATE TABLE

SYS ALTER USER

SYS BECOME USER

SYS DROP TABLESPACE

SYS ALTER TABLESPACE

SYS ALTER ANY MEASURE FOLDER

SYS CREATE CREDENTIAL

SYS CREATE SQL TRANSLATION PROFILE

SYS INSERT ANY MEASURE FOLDER

SYS DROP ANY CUBE

SYS EXECUTE ASSEMBLY

SYS CREATE ANY EDITION

SYS READ ANY FILE GROUP

SYS DROP ANY RULE

SYS CREATE ANY RULE SET

SYS DEBUG CONNECT SESSION

SYS ADMINISTER DATABASE TRIGGER

SYS ADMINISTER RESOURCE MANAGER

SYS CREATE DIMENSION

SYS EXECUTE ANY OPERATOR

SYS CREATE OPERATOR

SYS UNDER ANY TYPE

SYS ALTER ANY MATERIALIZED VIEW

SYS DROP PROFILE

SYS DROP ANY TRIGGER

SYS DROP ANY PROCEDURE

SYS CREATE PUBLIC DATABASE LINK

SYS SELECT ANY SEQUENCE

SYS DROP ANY SYNONYM

SYS CREATE ANY SYNONYM

SYS BACKUP ANY TABLE

SYS MANAGE TABLESPACE

219 rows selected.

Role dodeljene konektovanom korisniku

SQL> select * from dict where table_name like 'USER_ROLE_PRIVS'

TABLE_NAME

------------------------------

COMMENTS

---------------------------------------------------------------

USER_ROLE_PRIVS

Roles granted to current user

SQL> SELECT USERNAME,GRANTED_ROLE FROM USER_ROLE_PRIVS;

USERNAME GRANTED_ROLE

-------------------- ----------------------------------------

SYS ADM_PARALLEL_EXECUTE_TASK

SYS APEX_ADMINISTRATOR_ROLE

SYS APEX_GRANTS_FOR_NEW_USERS_ROLE

SYS AQ_ADMINISTRATOR_ROLE

SYS AQ_USER_ROLE

SYS AUDIT_ADMIN

SYS AUDIT_VIEWER

SYS AUTHENTICATEDUSER

SYS C##TEST_ROLE

SYS CAPTURE_ADMIN

SYS CDB_DBA

SYS CONNECT

SYS CSW_USR_ROLE

SYS CTXAPP

SYS DATAPUMP_EXP_FULL_DATABASE

SYS DATAPUMP_IMP_FULL_DATABASE

SYS DBA

SYS DBFS_ROLE

SYS DBHADOOP

SYS DELETE_CATALOG_ROLE

SYS DV_REALM_OWNER

SYS DV_REALM_RESOURCE

SYS EJBCLIENT

SYS EM_EXPRESS_ALL

SYS EM_EXPRESS_BASIC

SYS EXECUTE_CATALOG_ROLE

SYS EXP_FULL_DATABASE

SYS GATHER_SYSTEM_STATISTICS

SYS GDS_CATALOG_SELECT

SYS GSMADMIN_ROLE

SYS GSMUSER_ROLE

SYS GSM_POOLADMIN_ROLE

SYS HS_ADMIN_EXECUTE_ROLE

SYS HS_ADMIN_ROLE

SYS HS_ADMIN_SELECT_ROLE

SYS IMP_FULL_DATABASE

SYS JAVADEBUGPRIV

SYS JAVAIDPRIV

SYS JAVASYSPRIV

SYS JAVAUSERPRIV

SYS JAVA_ADMIN

SYS JAVA_DEPLOY

SYS JMXSERVER

SYS LBAC_DBA

SYS LOGSTDBY_ADMINISTRATOR

SYS OEM_ADVISOR

SYS OEM_MONITOR

SYS OLAP_DBA

SYS OLAP_USER

SYS OLAP_XS_ADMIN

SYS OPTIMIZER_PROCESSING_RATE

SYS ORDADMIN

SYS PDB_DBA

SYS PROVISIONER

SYS RECOVERY_CATALOG_OWNER

SYS RESOURCE

SYS SCHEDULER_ADMIN

SYS SELECT_CATALOG_ROLE

SYS SPATIAL_CSW_ADMIN

SYS SPATIAL_WFS_ADMIN

SYS WFS_USR_ROLE

SYS WM_ADMIN_ROLE

SYS XDBADMIN

SYS XDB_SET_INVOKER

SYS XDB_WEBSERVICES

SYS XDB_WEBSERVICES_OVER_HTTP

SYS XDB_WEBSERVICES_WITH_PUBLIC

SYS XS_CACHE_ADMIN

SYS XS_NAMESPACE_ADMIN

SYS XS_RESOURCE

SYS XS_SESSION_ADMIN

76 rows selected.

Database Voult Sysdba Connection

Ova vrsta konekcije nije moguća u slučaju korišćenja Database Vault softvera

Enter user-name: / as sysdba

ERROR

ORA-01031 insufficient privileges