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
Post a Comment