69 - [Oracle 19C] to Create SYS user - example

 

 

• Login as sysdba

show con_name
/*
CON_NAME 
------------------------------
CDB$ROOT
*/


show user
/*
USER is "SYS"
*/


select * from session_privs; --and see the count
/*
...

253 rows selected. 
*/


show con_id;
/*
CON_ID 
------------------------------
1
*/


select * from V$TABLESPACE where con_id=1
/*
       TS# NAME                           INC BIG FLA ENC     CON_ID
---------- ------------------------------ --- --- --- --- ----------
         1 SYSAUX                         YES NO  YES              1
         0 SYSTEM                         YES NO  YES              1
         2 UNDOTBS1                       YES NO  YES              1
         4 USERS                          YES NO  YES              1
         3 TEMP                           NO  NO  YES              1
*/

 

 

 

• to Create SYS user

create user c##copysis identified by copysis
default tablespace users temporary tablespace temp account unlock;
/*
User C##COPYSIS created.
*/


# container=all 옵션을 추가해야 다른 스키마에 접근이 가능하다

grant create session, dba,sysdba to c##copysis container=all; 
/*
Grant succeeded.
*/


select * from dba_users where username=upper('c##copysis');
/*

*/

 

 

 

• c##copysis 유저를 로그인시 as sysdba 옵션을 사용한 경우

SQL> conn c##copysis/copysis@orcl as sysdba
Connected.


SQL> show user
USER is "SYS"


SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT


SELECT * FROM SESSION_PRIVS;
/*
...

253 rows selected.
*/


select * from role_sys_privs where role='DBA'
/*
...

235 rows selected.
*/


SELECT * FROM SESSION_PRIVS
WHERE PRIVILEGE NOT IN (select PRIVILEGE from role_sys_privs where role='DBA' );
/*
PRIVILEGE                               
----------------------------------------
UNLIMITED TABLESPACE
SYSDBA
SYSOPER
EXEMPT ACCESS POLICY
EXEMPT IDENTITY POLICY
ALTER PUBLIC DATABASE LINK
ALTER DATABASE LINK
ADMINISTER KEY MANAGEMENT
KEEP DATE TIME
KEEP SYSGUID
PURGE DBA_RECYCLEBIN

PRIVILEGE                               
----------------------------------------
EXEMPT REDACTION POLICY
INHERIT ANY PRIVILEGES
TRANSLATE ANY SQL
INHERIT ANY REMOTE PRIVILEGES
READ ANY ANALYTIC VIEW CACHE
WRITE ANY ANALYTIC VIEW CACHE
TEXT DATASTORE ACCESS

18 rows selected. 
*/

 

 

 

• c##copysis 유저를 로그인시 as sysdba 옵션을 사용 안 한 경우, 16개의 시스템 권한이 차이난다.

SQL> conn c##copysis/copysis@orcl
Connected.


SQL> show user
USER is "C##COPYSIS"


SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT


SELECT * FROM SESSION_PRIVS;
/*
...

237 rows selected.(옵션의 차이에 따라 row 개수가 달라진다. 253 as sysdba)
*/


select * from role_sys_privs where role='DBA';
/*
...

235 rows selected.(the same as sysdba)
*/


SELECT * FROM SESSION_PRIVS
WHERE PRIVILEGE NOT IN (select PRIVILEGE from role_sys_privs where role='DBA' );
/*
PRIVILEGE
----------------------------------------
UNLIMITED TABLESPACE
EXEMPT REDACTION POLICY
*/