Adding cloud control users using emcli





In trying to find a way to get emcli to authenticate without having to type the password all the time (to enable it to be called from another program without having to hardcode passwords) i went on a journey to see what is and isn't possible.

Reading the docs there seem to be 3 main 'types' of user

1) (EM_USER) standard cloud control 'repository' user - basically an oracle account in the OMS database with the associated metadata about the user in the cloud control repository tables
2) (EXTERNAL_USER) sso user - user defined and authenticated in some central sso 'thingy' - needs loads of middleware stuff installed.
3) (DB_EXTERNAL_USER) Enterprise user - user is defined in oid/ovd and there is some pass through from the database/application to authenticate against that

2+3 were out to start with, we don't have any fusion middleware infrastructure in place and the oid install we did have was quite problematic and didn't work with all the version of oracle we had at the time (9i). As a concept though oid is great and we will likely revisit it at some point.

I did read somewhere that it was possible to set up a user as type 3) and this could actually be an externally identified normal oracle account - i thought this odd when i read it and indeed it is not correct.

Also - type 2+3 accounts are not natively available via the GUI , though it seems type 2 can be added by amending some config to allow it.

Anyway - back to option 1. I want to create an emcli user that can log on to the database without a password - essentially the same as when you say for a database account "identified externally".

My initial attempt as i briefly mentioned was to create this as a DB_EXTERNAL_USER via emcli - see the command below

emcli create_user -name="OPS\$ORACLE" -email="a.b@c.com" -type="DB_EXTERNAL_USER"
Error: User with the same name already exists


So that initially looks promising as a genuinely do have that user already.

 Lets drop the user and try again

drop user ops$oracle;

User dropped.

emcli create_user -name="OPS\$ORACLE" -email="a.b@c.com" -type="DB_EXTERNAL_USER"
User OPS created

Hold on thats not what i wanted it's interpreted $ORACLE as a variable

I tried to escape it with \ but that just returns "Invalid username"

Hmm - lets try emcli in 'scripting' mode - perhaps that stops the $ interpretation. (it took a while to get the syntax/setup right for scripting mode......)

 emcli
Oracle Enterprise Manager 12c EMCLI with Scripting option Version 12.1.0.3.0.
Copyright (c) 1996, 2013 Oracle Corporation and/or its affiliates. All rights reserved.

Type help() for help and exit() to get out.

emcli>login()
Error: EM URL is not set. Do set_client_property('EMCLI_OMS_URL', '<value>')
Or set it as environment variable.

emcli>set_client_property('EMCLI_OMS_URL', 'http://oms:7788/em')
emcli>login(username='myadminuser')
Enter password :  *********

Login successful

emcli>create_user (name="OPS$ORACLE",email="a.b@c.com",type="DB_EXTERNAL_USER")
Syntax Error: Invalid username

emcli>create_user (name="OPS\$ORACLE",email="a.b@c/com",type="DB_EXTERNAL_USER")
Syntax Error: Invalid username


So neither method works. hmmmm. Lets change os_authent_prefix to null so we dont have to have the ops$ prefix on it. *this needs a database restart to pick up.

(also at this point i figured out how to make the user a super admin in one go rather than going into the gui afterwards to set this - the privs are not listed in the docs - but hidden away is a command that shows you all the relevant settings)

this command is

 emcli get_supported_privileges

From this i can see the privilege i want is "SUPER_USER"

anyway I'm drifting off the point....

Lets create the user now

emcli create_user -name="ORACLE" -email="a.b@c.com" -type="DB_EXTERNAL_USER"  -privilege="SUPER_USER"
User "ORACLE" created successfully


So now we have the user - and its there in cloud control



but....... it is an enterprise user - so it has to be in OID - all the command has done is create the metadata for it in cloud control - it's not usable.

So lets delete that and try again with a normal 'repository' user.

emcli create_user -name="ORACLE" -email="a.b@c.com" -privilege="SUPER_USER" -password="DUMMY"
User "ORACLE" created successfully


All looks ok so far

We can log on with is ok to sqlplus

sqlplus oracle/DUMMY@CLOUDDB

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 3 15:06:49 2014

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

Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

ORACLE@CLOUDDB>select * from session_roles;

ROLE
------------------------------
MGMT_USER

ORACLE@CLOUDDB>select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION

ORACLE@CLOUDDB>


But - it has a password - lets change it to be an external user and see if that works.

alter user oracle identified externally;

User altered.


sqlplus /

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 3 15:07:45 2014

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

Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

ORACLE@CLOUDDB>


So thats good for sqlplus (which i knew would work) - but what about emcli....?

Well the short answer is no - it doesn't work. You are always prompted for a password, null passwords and / as the username does not work - this seems to be a dead end.

At this point i'd practically given up when i noticed something i missed in the docs.

There is an 'autologin' feature for emcli - that seems to remember your credentials and not prompt you for the password - this looks like just what we need - lets try that. (first we change the oracle password in the db back to "DUMMY" from 'identified externally')

emcli setup -url=http://server:7788/em -username=oracle -autologin
Oracle Enterprise Manager 12c 3.
Copyright (c) 1996, 2013 Oracle Corporation and/or its affiliates. All rights reserved.

Enter password

Emcli setup successful


This login seems to work fine after the normal timeout period (45 mins). Not sure if it survives an OMS restart though - guess i'll find out in due course.

And there we have it - and actually the whole use setup could have been done from the gui - it seemed the emcli setup handles the rest. An interesting exercise nonetheless as i learnt a fair bit about emcli and EM users in general.






Comments