OraFAQ Forum: Security » PDB SYSOPER/SYSDBA privileges

Home » RDBMS Server » Security » PDB SYSOPER/SYSDBA privileges (12.*)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PDB SYSOPER/SYSDBA privileges [message #665813]

Fri, 22 September 2017 04:57

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I created a user in a PDB then grant it SYSOPER (example there is 12.1 but it is the same in 12.2 but for the query result columns):

***SYS***> alter session set container=mikc2DB1;

Session altered.

***SYS***> create user test identified by test;

User created.

***SYS***> grant sysdba to test;

Grant succeeded.

***SYS***> alter session set container=cdb$root;

Session altered.

***SYS***> select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
SYSDG                          FALSE FALSE FALSE FALSE TRUE  FALSE          1
SYSBACKUP                      FALSE FALSE FALSE TRUE  FALSE FALSE          1
SYSKM                          FALSE FALSE FALSE FALSE FALSE TRUE           1
MICHEL                         TRUE  FALSE FALSE FALSE FALSE FALSE          1
TEST                           FALSE TRUE  FALSE FALSE FALSE FALSE          3

So far so far good, my user is there with the correct privilege and CON_ID.
Now I deleted the password file then query again the view:

***SYS***> select * from v$pwfile_users;
USERNAME  SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
--------- ----- ----- ----- ----- ----- ----- ----------
TEST      FALSE TRUE  FALSE FALSE FALSE FALSE          3

1 row selected.

All but the new PDB account disappeared, so obviously the PDB SYSOPER/SYSDBA is not recorded in the password file so, and this is my question, where is it recorded?

Report message to a moderator

Re: PDB SYSOPER/SYSDBA privileges [message #665815 is a reply to message #665813]

Fri, 22 September 2017 06:17

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

This might be similar to the way that container-specific instance parameters are managed: they do not go to the spfile. If you set a parameter then unplug the container from its CDB and plug it into another CDB, the parameter goes with it. Which tells me that they must be stored somewhere in the container's data dictionary. I've just unplugged and plugged back in a PDB with a SYSDBA grant, and the row in v$pwfile_users did disappear and then come back.

autotrace tells me that v$pwfile_users is actually X$KZSRT, is there some way to reverse engineer that?

Report message to a moderator

Re: PDB SYSOPER/SYSDBA privileges [message #665818 is a reply to message #665815]

Fri, 22 September 2017 08:35

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks for the answer and test.

x$kzsrt is just the way Oracle indicates which procedure it has to call when this table (or the associated v$ view) is queried.

Quote:

Which tells me that they must be stored somewhere in the container's data dictionary.

This is what I though and asked in case someone knows it before I try to reverse engineer or hack Oracle data and code. Smile

But maybe it is not exposed in the dictionary (I mean in any SYS table).

Report message to a moderator

Re: PDB SYSOPER/SYSDBA privileges [message #665819 is a reply to message #665818]

Fri, 22 September 2017 10:50

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

OK, found it, it is not in the PDB but in the CDB, table SYS.CDB_LOCAL_ADMINAUTH$ (view CDB_LOCAL_ADMIN_PRIVS).
When you plus a PDB, Oracle checks its CON_UID against the ones in this table to allow or not PDB admin logins.
This means if you set SYSOPER/SYSDBA on a PDB, unplug it and plug it into another CDB then you lose these settings.
(Note: I only checked it on 12.1.0.1).

Report message to a moderator

Re: PDB SYSOPER/SYSDBA privileges [message #665821 is a reply to message #665819]

Fri, 22 September 2017 11:50

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

That table is metadata linked, which should mean that only the definition is shared and each container has its own data:

w122> select  SHARING from cdb_objects where object_name='CDB_LOCAL_ADMINAUTH$';

SHARING
------------------
METADATA LINK

so the privilege really should go with the container. But that isn't working because there is only one segment:

jw122> select con_id,segment_name,header_file,bytes from cdb_segments where segment_name='CDB_LOCAL_ADMINAUTH$';

    CON_ID SEGMENT_NAME                   HEADER_FILE      BYTES
---------- ------------------------------ ----------- ----------
         1 CDB_LOCAL_ADMINAUTH$                     1      65536

jw122>

By contrast, look at obj$ where I see the result I would expect:

jw122> select  con_id,SHARING,object_id,data_object_id from cdb_objects where object_name='OBJ$';

    CON_ID SHARING             OBJECT_ID DATA_OBJECT_ID
---------- ------------------ ---------- --------------
         1 METADATA LINK              18             18
         4 METADATA LINK              18             18

jw122> select con_id,segment_name,header_file,bytes from cdb_segments where segment_name='OBJ$';

    CON_ID SEGMENT_NAME                   HEADER_FILE      BYTES
---------- ------------------------------ ----------- ----------
         4 OBJ$                                    22   10485760
         1 OBJ$                                     1   10485760

jw122>

So now I am totally confused. It looks to me as though Oracle is doing something that it shouldn't be doing: creating a metadata linked object but not actually propagating it to all containers.

Perhaps there is some special code in the background because of the security implications.

Report message to a moderator

Re: PDB SYSOPER/SYSDBA privileges [message #665902 is a reply to message #665821]

Fri, 29 September 2017 10:14

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Hi john, sorry for late answer I havz been off Oracle for a week.
I agree with, I have even dump the segment to see what's inside. Wink

My current purpose is to compare all 12.* versions from a security point of view, here's what I get with the same test case (in short same thing everywhere).

12.1.0.1.0

121010 SYS> @v

Oracle version: 12.1.0.1.0

121010 SYS> alter session set container=mikc11DB1;

Session altered.

121010 SYS> create user test identified by test;

User created.

121010 SYS> grant sysdba to test;

Grant succeeded.

121010 SYS> alter session set container=cdb$root;

Session altered.

121010 SYS> select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
SYSDG                          FALSE FALSE FALSE FALSE TRUE  FALSE          1
SYSBACKUP                      FALSE FALSE FALSE TRUE  FALSE FALSE          1
SYSKM                          FALSE FALSE FALSE FALSE FALSE TRUE           1
MICHEL                         TRUE  FALSE FALSE FALSE FALSE FALSE          1
TEST                           FALSE TRUE  FALSE FALSE FALSE FALSE          3

121010 SYS> select SHARING from cdb_objects where object_name='CDB_LOCAL_ADMINAUTH$' and con_id != 2;
SHARING
-------------
METADATA LINK

1 row selected.

121010 SYS> select con_id,segment_name,header_file,bytes from cdb_segments
  2  where segment_name='CDB_LOCAL_ADMINAUTH$' and con_id != 2;
    CON_ID SEGMENT_NAME                   HEADER_FILE      BYTES
---------- ------------------------------ ----------- ----------
         1 CDB_LOCAL_ADMINAUTH$                     1      65536

1 row selected.

121010 SYS> select con_id, file_id, block_id, blocks from cdb_extents
  2  where segment_name='CDB_LOCAL_ADMINAUTH$' and con_id != 2;
    CON_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         1          1       1464          8

1 row selected.

12.1.0.2.0

121020 SYS> @v

Oracle version: 12.1.0.2.0

121020 SYS> alter session set container=mikc12DB1;

Session altered.

121020 SYS> create user test identified by test;

User created.

121020 SYS> grant sysdba to test;

Grant succeeded.

121020 SYS> alter session set container=cdb$root;

Session altered.

121020 SYS> select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
SYSDG                          FALSE FALSE FALSE FALSE TRUE  FALSE          1
SYSBACKUP                      FALSE FALSE FALSE TRUE  FALSE FALSE          1
SYSKM                          FALSE FALSE FALSE FALSE FALSE TRUE           1
TEST                           TRUE  FALSE FALSE FALSE FALSE FALSE          3

5 rows selected.

121020 SYS> select SHARING from cdb_objects where object_name='CDB_LOCAL_ADMINAUTH$' and con_id != 2;
SHARING
-------------
METADATA LINK
METADATA LINK

2 rows selected.

121020 SYS> select con_id,segment_name,header_file,bytes from cdb_segments
  2  where segment_name='CDB_LOCAL_ADMINAUTH$' and con_id != 2;
    CON_ID SEGMENT_NAME                   HEADER_FILE      BYTES
---------- ------------------------------ ----------- ----------
         1 CDB_LOCAL_ADMINAUTH$                     1      65536

1 row selected.

121020 SYS> select con_id, file_id, block_id, blocks from cdb_extents
  2  where segment_name='CDB_LOCAL_ADMINAUTH$' and con_id != 2;
    CON_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         1          1       1464          8

1 row selected.

12.1.0.2.160719

12102L SYS> @v

Oracle version: 12.1.0.2.160719

12102L SYS> alter session set container=mikcDB1;

Session altered.

12102L SYS> create user test identified by test;

User created.

12102L SYS> grant sysdba to test;

Grant succeeded.

12102L SYS> alter session set container=cdb$root;

Session altered.

12102L SYS> select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
SYSDG                          FALSE FALSE FALSE FALSE TRUE  FALSE          1
SYSBACKUP                      FALSE FALSE FALSE TRUE  FALSE FALSE          1
SYSKM                          FALSE FALSE FALSE FALSE FALSE TRUE           1
MICHEL                         TRUE  FALSE FALSE FALSE FALSE FALSE          0
TEST                           TRUE  FALSE FALSE FALSE FALSE FALSE          3

6 rows selected.

12102L SYS> select con_id, SHARING from cdb_objects
  2  where object_name='CDB_LOCAL_ADMINAUTH$' and con_id != 2 order by 1;
    CON_ID SHARING
---------- -------------
         1 METADATA LINK
         3 METADATA LINK
         4 METADATA LINK
         5 METADATA LINK

4 rows selected.

12102L SYS> select con_id,segment_name,header_file,bytes from cdb_segments
  2  where segment_name='CDB_LOCAL_ADMINAUTH$' and con_id != 2 order by 1;
    CON_ID SEGMENT_NAME                   HEADER_FILE      BYTES
---------- ------------------------------ ----------- ----------
         1 CDB_LOCAL_ADMINAUTH$                     1      65536

1 row selected.

12102L SYS> select con_id, file_id, block_id, blocks from cdb_extents
  2  where segment_name='CDB_LOCAL_ADMINAUTH$' and con_id != 2 order by 1, 2;
    CON_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         1          1       1464          8

1 row selected.

12.2.0.1.0

122010 SYS> @v

Oracle version: 12.2.0.1.0

122010 SYS> alter session set container=mikc21DB1;

Session altered.

122010 SYS> create user test identified by test;

User created.

122010 SYS> grant sysdba to test;

Grant succeeded.

122010 SYS> alter session set container=cdb$root;

Session altered.

122010 SYS> col username format a30
122010 SYS> select username, sysdba, sysoper, sysasm, sysbackup, sysdg, syskm, con_id from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
SYSDG                          FALSE FALSE FALSE FALSE TRUE  FALSE          1
SYSBACKUP                      FALSE FALSE FALSE TRUE  FALSE FALSE          1
SYSKM                          FALSE FALSE FALSE FALSE FALSE TRUE           1
TEST                           TRUE  FALSE FALSE FALSE FALSE FALSE          3

5 rows selected.

122010 SYS> select con_id, SHARING from cdb_objects
  2  where object_name='CDB_LOCAL_ADMINAUTH$' and con_id != 2 order by 1;
    CON_ID SHARING
---------- ------------------
         1 METADATA LINK
         3 METADATA LINK

2 rows selected.

122010 SYS> select con_id,segment_name,header_file,bytes from cdb_segments
  2  where segment_name='CDB_LOCAL_ADMINAUTH$' and con_id != 2 order by 1;
    CON_ID SEGMENT_NAME                   HEADER_FILE      BYTES
---------- ------------------------------ ----------- ----------
         1 CDB_LOCAL_ADMINAUTH$                     1      65536

1 row selected.

122010 SYS> select con_id, file_id, block_id, blocks from cdb_extents
  2  where segment_name='CDB_LOCAL_ADMINAUTH$' and con_id != 2 order by 1, 2;
    CON_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         1          1       1464          8

1 row selected.

12.2.0.1.170718

12201L SYS> @v

Oracle version: 12.2.0.1.170718

12201L SYS> alter session set container=mikc2DB1;

Session altered.

12201L SYS> create user test identified by test;

User created.

12201L SYS> grant sysdba to test;

Grant succeeded.

12201L SYS> alter session set container=cdb$root;

Session altered.

12201L SYS> col username format a30
12201L SYS> select username, sysdba, sysoper, sysasm, sysbackup, sysdg, syskm, con_id from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
SYSDG                          FALSE FALSE FALSE FALSE TRUE  FALSE          1
SYSBACKUP                      FALSE FALSE FALSE TRUE  FALSE FALSE          1
SYSKM                          FALSE FALSE FALSE FALSE FALSE TRUE           1
TEST                           TRUE  FALSE FALSE FALSE FALSE FALSE          3

5 rows selected.

12201L SYS> select con_id, SHARING from cdb_objects
  2  where object_name='CDB_LOCAL_ADMINAUTH$' and con_id != 2 order by 1;
    CON_ID SHARING
---------- ------------------
         1 METADATA LINK
         3 METADATA LINK
         4 METADATA LINK

3 rows selected.

12201L SYS> select con_id,segment_name,header_file,bytes from cdb_segments
  2  where segment_name='CDB_LOCAL_ADMINAUTH$' and con_id != 2 order by 1;
    CON_ID SEGMENT_NAME                   HEADER_FILE      BYTES
---------- ------------------------------ ----------- ----------
         1 CDB_LOCAL_ADMINAUTH$                     1      65536

1 row selected.

12201L SYS> select con_id, file_id, block_id, blocks from cdb_extents
  2  where segment_name='CDB_LOCAL_ADMINAUTH$' and con_id != 2 order by 1, 2;
    CON_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         1          1       1464          8

1 row selected.

Report message to a moderator

Previous Topic:	Grant user full access to other schema
Next Topic:	CREATE ANY CONTEXT privilege

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 02 15:35:44 CST 2025