Dec
21
2018
--

Percona Server for MongoDB Authentication Using Active Directory

authentication

mongodb authentication with active directoryThis article will walk you through using the SASL library to allow your Percona Server for MongoDB instance to authenticate with your company’s Active Directory server. Percona Server for MongoDB includes enterprise level features, such as LDAP authentication, audit logging and with the 3.6.8 release a beta version of data encryption at rest, all in its open source offering.

Pre set-up assumptions

In this article we will make a couple of assumptions:

  1. You have an Active Directory server up and running and that it is accessible to the server that you have Percona Server for MongoDB installed on.
  2. These machines are installed behind a firewall as the communications between the two servers will be in plain text. This is due the fact that we can only use the SASL mechanism of PLAIN when authenticating and credentials will be sent in plain text.
  3. You have sudo privilege on the server you are going to install Percona Server for MongoDB on.

Installing Percona Server for MongoDB

The first thing you are going to need to do is to install the Percona Server for MongoDB package. You can get this in a couple of different ways. You can either install from the Percona repositories, or you can download the packages and install them manually.

Once you have Percona Server for MongoDB installed, we want to start the mongod service and make sure it is set to run on restart.

sudo systemctl start mongod
sudo systemctl enable mongod

Now that the service is up and running, we want to open the mongo shell and add a database administrator user. This user will be authenticated inside of the MongoDB server itself and will not have any interactions with the Active Directory server.

To start the mongo shell up, type mongo from a terminal window. Once you do this you will see something similar to the following:

Percona Server for MongoDB shell version v3.6.8-2.0
connecting to: mongodb://127.0.0.1:27017
Percona Server for MongoDB server version: v3.6.8-2.0
Server has startup warnings:
2018-12-11T17:48:47.471+0000 I STORAGE [initandlisten]
2018-12-11T17:48:47.471+0000 I STORAGE [initandlisten] ** WARNING: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine
2018-12-11T17:48:47.471+0000 I STORAGE [initandlisten] **          See http://dochub.mongodb.org/core/prodnotes-filesystem
2018-12-11T17:48:48.197+0000 I CONTROL [initandlisten]
2018-12-11T17:48:48.197+0000 I CONTROL [initandlisten] ** WARNING: Access control is not enabled for the database.
2018-12-11T17:48:48.197+0000 I CONTROL [initandlisten] **          Read and write access to data and configuration is unrestricted.
2018-12-11T17:48:48.197+0000 I CONTROL [initandlisten] **          You can use percona-server-mongodb-enable-auth.sh to fix it.
2018-12-11T17:48:48.197+0000 I CONTROL [initandlisten]

Notice the second warning that access control is not enabled for the database. Percona Server for MongoDB comes with a script that you can run that will enable authentication for you, but we can also do this manually.

We will go ahead and manually add a user in MongoDB that has the root role assigned to it. This user will have permission to do anything on the server, so you will want to make sure to keep the password safe. You will also not want to use this user for doing your day to day work inside of MongoDB.

This user needs to be created in the admin database as it needs to have access to the entire system. To do this run the following commands inside of the mongo shell:

> use admin
switched to db admin
> db.createUser({"user": "admin", "pwd": "$3cr3tP4ssw0rd", "roles": ["root"]})
Successfully added user: { "user" : "admin", "roles" : [ "root" ] }

Now that we have a user created in MongoDB we can go ahead and enable authorization. To do this we need to modify the /etc/mongod.conf file and add the following lines:

security:
  authorization: enabled
setParameter:
  authenticationMechanisms: PLAIN,SCRAM-SHA-1

Notice that we have two mechanisms set up for authentication. The first one, PLAIN, is used for authenticating with Active Directory. The second one, SCRAM-SHA-1 is used for internal authentication inside of MongoDB.

Once you’ve made the changes, you can restart the mongod service by running the following command:

sudo systemctl restart mongod

Now if you were to run the mongo shell again, you wouldn’t see the access control warning any more, and you would need to log in as your new user to be able to run any commands.

If you were to try to get a list of databases before logging in you would get an error:

> show dbs;
2018-12-11T21:50:39.551+0000 E QUERY [thread1] Error: listDatabases failed:{
"ok" : 0,
"errmsg" : "not authorized on admin to execute command { listDatabases: 1.0, $db: \"admin\" }",
"code" : 13,
"codeName" : "Unauthorized"
} :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
Mongo.prototype.getDBs@src/mongo/shell/mongo.js:65:1
shellHelper.show@src/mongo/shell/utils.js:849:19
shellHelper@src/mongo/shell/utils.js:739:15
@(shellhelp2):1:1

Let’s go ahead and run the mongo shell and then log in with our admin user:

> use admin
switched to db admin
> db.auth("admin", "$3cr3tP4ssw0rd")
1

If you are successful you will get a return value of 1. If authentication fails, you will get a return value of 0. Failure is generally due to a mistyped username or password, but you could also be trying to authenticate in the wrong database. In MongoDB you must be in the database that the user was created in before trying to authenticate.

Now that we’ve logged in as the admin user, we will add a document that will be used to verify that our Active Directory based user can successfully access the data at the end of this post.

> use percona
switched to db percona
> db.test.insert({"message": "Active Directory user success!"})
WriteResult({ "nInserted" : 1 })

Install the Cyrus SASL packages

Now that we have a Percona Server for MongoDB instance set up and it is secured, we need to add some packages that will allow us to communicate properly with the Active Directory server.

For RedHat use the following command

sudo yum install -y cyrus-sasl cyrus-sasl-plain

For Ubuntu use this command

sudo app install -y sasl2-bin

Next we need to update the SASL configuration to use LDAP instead of PAM, which is the default. To do this we need to edit the file /etc/sysconfig/saslauthd, remembering to backup up your original file first.

For RedHat we use the following commands

sudo cp /etc/sysconfig/saslauthd /etc/sysconfig/saslauthd.bak
sudo sed -i -e s/^MECH=pam/MECH=ldap/g /etc/sysconfig/saslauthd

For Ubuntu we use these commands instead

sudo cp /etc/default/saslauthd /etc/default/saslauthd.bak
sudo sed -i -e s/^MECHANISMS="pam"/MECHANISMS="ldap"/g /etc/default/saslauthd 
sudo sed -i -e s/^START=no/START=yes/g /etc/default/saslauthd

We also need to create the file /etc/saslauthd.conf with contents similar to the following (replace values as necessary for your Active Directory installation):

ldap_servers: ldap://LDAP.EXAMPLE.COM
ldap_mech: PLAIN
ldap_filter: cn=%u,CN=Users,DC=EXAMPLE,DC=COM
ldap_search_base:CN=Users,DC=EXAMPLE,DC=COM
ldap_filter:(cn=%u)
ldap_bind_dn:CN=ADADMIN,CN=Users,DC=EXAMPLE,DC=COM
ldap_password:ADADMINPASSWORD

Now that we’ve got SASL set up, we can start the saslauthd process and set it to run on restart.

sudo systemctl start saslauthd
sudo systemctl enable saslauthd

Next we need to allow the mongod process to write to the saslauthd mux socket and change the permissions on the owning directory to 755 so MongoDB can write to it. This is the default on RedHat, but not for Ubuntu.

On Ubuntu you can either change the permissions on the folder

sudo chmod 755 /run/saslauthd

Or you could add the mongod user to the sasl group

sudo usermod -a -G sasl mongod

Test the users

The SASL installation provides us with a tool to test that our Active Directory users can be logged in from this machine. Let’s go ahead and test to see if we can authenticate with our Active Directory user.

sudo testsaslauthd -u aduser -p ADP@assword1

You should see 0: OK "Success." if authentication worked.

Create a SASL config file for MongoDB

To allow MongoDB to use SASL to communicate with Active Direcory, we need to create a configuration file.

Create the requisite directory if it doesn’t exist:

mkdir -p /etc/sasl2

And then we need to create the file /etc/sasl2/mongodb.conf and place the following contents into it:

pwcheck_method: saslauthd
saslauthd_path: /var/run/saslauthd/mux
log_level: 5
mech_list: plain

Add Active Directory user to MongoDB

Now we can finally add our Active Directory user to our MongoDB instance:

$ mongo
Percona Server for MongoDB shell version v3.6.8-2.0
connecting to: mongodb://127.0.0.1:27017
Percona Server for MongoDB server version: v3.6.8-2.0
> use admin
switched to db admin
> db.auth("admin", "$3cr3tP4ssw0rd")
1
> use $external
switched to db $external
> db.createUser({"user": "aduser", "roles": [{"role": "read", "db": "percona"}]})
Successfully added user: {
        "user" : "aduser",
        "roles" : [
                {
                        "role" : "read",
                        "db" : "percona"
                }
        ]
}

As you can see from the above, when we create the user that will be authenticated with Active Directory, we need to be in the special $external database and we don’t supply a password as we would when we create a MongoDB authenticated user.

Now let’s try to log in with our Active Directory based user. First we need exit our current mongo shell and restart it, and then we can log in with our Active Directory user:

> exit
bye
$ mongo
Percona Server for MongoDB shell version v3.6.8-2.0
connecting to: mongodb://127.0.0.1:27017
Percona Server for MongoDB server version: v3.6.8-2.0
> use $external
switched to db $external
> db.auth({"mechanism": "PLAIN", "user": "aduser", "pwd": "adpassword", "digestPassword ": false})
1
> use percona
switched to db percona
> db.test.find()
{ "_id" : ObjectId("5c12a47904a287e45fcb580e"), "message" : "Active Directory user success!" }

As you can see above our Active Directory based user was able to authenticate and then change over to the percona database and see the document we stored earlier.

You will notice that our auth() call above is different than the one we used to log in with MongoDB based users. In this case we need to pass in a document with not only the user and password, but also the mechanism to use. We also want to set digestPassword to false.

You can also log in directly from the command line with the following:

mongo percona --host localhost --port 27017 --authenticationMechanism PLAIN --authenticationDatabase \$external --username dduncan --p

There are a couple of things to note here if you’re not used to using the command line to log in:

  1. We place the --password option at the end of the command line and do not provide a password here. This will cause the application to prompt us for a password.
  2. You will also automatically be placed into the percona database, or whatever database name you provide after mongo.
  3. You need to escape the $external database name with a backslash (\) or the terminal will treat $external as an environment variable and you will most likely get an error.

Conclusion

In conclusion, it is easy to connection your Percona Server for MongoDB instance to your corporate Active Directory server. This allows your MongoDB users to use the same credentials to log into MongoDB as they do their corporate email and workstation.


Photo by Steve Halama on Unsplash

Nov
27
2018
--

Setup Compatible OpenLDAP Server for MongoDB and MySQL

Set up LDAP authentication for MySQL and MongoDB

Set up LDAP authentication for MySQL and MongoDBBy the end of this article, you should be able to have a Percona Server for MongoDB and Percona Server for MySQL instance able to authenticate on an OpenLDAP backend. While this is mostly aimed at testing scenarios, it can be easily extended for production by following the OpenLDAP production best practices i.e. attending to security and high availability.

The first step is to install OpenLDAP via the

slapd

  package in Ubuntu.

sudo apt update
sudo apt install slapd ldap-utils

During installation, it will ask you for a few things listed below:

  • DNS Domain Name:
    ldap.local
  • Organization Name:
    Percona
  • Administrator password:
    percona

All these values are arbitrary, you can choose whatever suits your organization—especially the password.

Once

slapd

  is running, we can create our logical groups and actual users on the LDAP server. To make it simple, we use LDIF files instead of GUIs. Our first file,

perconadba.ldif

 contains our

perconadba

  group definition. Take note of the root name part

dc=ldap,dc=local

  it is simply the broken down value of our DNS Domain Name during the installation of

slapd

 .

dn: ou=perconadba,dc=ldap,dc=local
objectClass: organizationalUnit
ou: perconadba

We can add this definition into LDAP with the command shown below. With the

-W

  option, it will prompt you for a password.

ldapadd -x -W -D "cn=admin,dc=ldap,dc=local" -f perconadba.ldif

The next step is to create our user in LDAP, this user will be looked up by both MongoDB and MySQL during authentication to verify their password. Our LDIF file (

percona.ldif

 ) would look like this:

dn: uid=percona,ou=perconadba,dc=ldap,dc=local
objectClass: top
objectClass: account
objectClass: posixAccount
objectClass: shadowAccount
cn: percona
uid: percona
uidNumber: 1100
gidNumber: 100
homeDirectory: /home/percona
loginShell: /bin/bash
gecos: percona
userPassword: {crypt}x
shadowLastChange: -1
shadowMax: -1
shadowWarning: -1

The

-1

  values for the

shadow*

  fields are important, we set them to negative to mean the password shadow does not expire. If these are set to zero (0), then MySQL will not be able to authenticate since PAM will complain that the password has expired and needs to be changed.

We can then add this user into LDAP, again the command below will ask for the admin password we entered during slapd’s installation.

ldapadd -x -W -D "cn=admin,dc=ldap,dc=local" -f percona.ldif

To verify, we can search for the user we just entered using the command below. Notice we used the -w parameter to specify the admin password inline.

ldapsearch -x -D 'cn=admin,dc=ldap,dc=local' -w percona \
	-b 'ou=perconadba,dc=ldap,dc=local' '(uid=percona)'

As last step on setting up our LDAP user properly is to give it a valid password. The -s parameter below is the actual password we will set for this user.

ldappasswd -s percona -D "cn=admin,dc=ldap,dc=local" -w percona \
	-x "uid=percona,ou=perconadba,dc=ldap,dc=local"

At this point you should have a generic LDAP server that should work for both MongoDB and MySQL.

PAM Configuration for MySQL

To make this work for a MySQL and support PAM authentication, take note of the following configuration files. Instructions on setting up PAM for MySQL is aplenty on this blog I just need to specify Ubuntu Bionic specific configuration files to make it work.

/etc/nslcd.conf

The only important difference with this configuration—compared to Jaime’s post for example—is the values for

filter

 . If you are using Windows Active Directory, the map values are also important (posixAccount objectClass has been deprecated on recent release of Windows Active Directory).

uid nslcd
gid nslcd
uri ldap:///localhost
base ou=perconadba,dc=ldap,dc=local
filter passwd (&(objectClass=account)(objectClass=posixAccount))
filter group (&(objectClass=shadowAccount)(objectClass=account))
map    passwd uid           uid
map    passwd uidNumber     uidNumber
map    passwd gidNumber     gidNumber
map    passwd homeDirectory "/home/$uid"
map    passwd gecos         uid
map    passwd loginShell    "/bin/bash"
map    group gidNumber      gidNumber
binddn cn=admin,dc=ldap,dc=local
bindpw percona
tls_cacertfile /etc/ssl/certs/ca-certificates.crt

/etc/nsswitch.conf

Also for nsswitch.conf, make sure that passwd, group and shadow does LDAP lookups.

...
passwd:         compat systemd ldap
group:          compat systemd ldap
shadow:         compat systemd ldap
gshadow:        files ldap
...

SASL for MongoDB

Adamo’s excellent post on MongoDB LDAP Authentication has all the details on configuring MongoDB itself. To complement that, if you use this LDAP test setup, you need the take note of the following configuration files with specific differences.

/etc/mongod.conf

In the

mongod.conf

  configuration file, I explicitly added the saslauthd socket path.

security:
  authorization: enabled
setParameter:
  saslauthdPath: /var/run/saslauthd/mux
  authenticationMechanisms: PLAIN,SCRAM-SHA-1

/etc/saslauthd.conf

For the saslauthd daemon configuration, the configuration has no actual difference – just take note I used differing values based on the LDAP setup above. Specifically, the

ldap_filter

  and

ldap_search_base

  are key options here which are concatenated during an LDAP search to come up with the

percona

  user’s account information.

ldap_servers: ldap://localhost:389/
ldap_search_base: ou=perconadba,dc=ldap,dc=local
ldap_filter: (uid=%u)
# Optional: specify a user to perform ldap queries
ldap_bind_dn: CN=admin,DC=ldap,DC=local
# Optional: specify ldap user’s passwordi
ldap_password: percona

Enterprise quality features should not be complex and expensive. Tell us about your experience with our software and external authentication in the comments below!

Jun
27
2018
--

Webinar 6/28: Securing Database Servers From External Attacks

securing database servers

securing database serversPlease join Percona’s Chief Evangelist Colin Charles on Thursday, June 28th, 2018, as he presents Securing Database Servers From External attacks at 7:00 AM PDT (UTC-7) / 10:00 AM EDT (UTC-4).

 

A critical piece of your infrastructure is the database tier, yet people don’t pay enough attention to it judging by how many are bitten via poorly chosen defaults, or just a lack understanding of running a secure database tier. In this talk, I’ll focus on MySQL/MariaDB, PostgreSQL, and MongoDB, and cover external authentication, auditing, encryption, SSL, firewalls, replication, and more gems from over a decade of consulting in this space from Percona’s 4,000+ customers.

Register Now

 

Colin Charles

Chief Evangelist

Colin Charles is the Chief Evangelist at Percona. He was previously on the founding team of MariaDB Server in 2009, and had worked at MySQL since 2005, and been a MySQL user since 2000. Before joining MySQL, he worked actively on the Fedora and OpenOffice.org projects. He’s well known within open source communities in APAC, and has spoken at many conferences. Experienced technologist, well known in the open source world for work that spans nearly two decades within the community. Pays attention to emerging technologies from an integration standpoint. Prolific speaker at many industry-wide conferences delivering talks and tutorials with ease. Interests: application development, systems administration, database development, migration, Web-based technologies. Considered expert in Linux and Mac OS X usage/administration/roll-out’s. Specialties: MariaDB, MySQL, Linux, Open Source, Community, speaking & writing to technical audiences as well as business stakeholders.

The post Webinar 6/28: Securing Database Servers From External Attacks appeared first on Percona Database Performance Blog.

May
23
2018
--

Okta introduces ‘Sign in with Okta’ service

Consider that there are millions of Okta users out there using the service to sign into their company applications with a single set of credentials. Yet getting customers to work together using Okta authentication was an enormous task for developers. Okta wanted to simplify it, so they created a service they are calling it ‘Sign in with Okta.’

The new API allows developers to add a few lines code and give Okta customers the ability to sign into one another’s websites in a similar way that OAuth allows you to use your Google or Facebook credentials to sign onto consumer sites.

Frederic Kerrest, COO and co-founder at Okta, says the ‘Sign in with Okta’ uses an extension of OAuth called OpenID Connect, which his company has been supporting since 2016. He says the new service gives customers the ability to expand the use of their Okta credentials beyond their own set of internal applications to sign into customer and partner sites. This extends the Okta functionality and brand and helps to make it a kind of standard way of logging in (or that’s the hope).

When developers add this functionality, the user sees a “Sign in with Okta” button on the website or service they are accessing. They can then use their Okta login to get into these sites under whatever rules the site owner has defined.

Site with ‘Sign in with Okta’ button. Photo: Okta

While Okta has provided APIs for developers prior to today, they didn’t provide a package like this that simplifies the process. This forced developers to use the SAML standard to make it work. While there’s nothing wrong with this approach, it can be time-consuming and put a lot of burden on developers to write software and connectors, while updating and maintaining them, Kerrest explained. This removes all of that complexity from the process.

This means that when two businesses are on Okta, they can trust one another because they do business together, and instead of setting up the SAML connection, a process that could take days, they can do it an hour with the Okta API tool, according to Kerrest.

“[Sign in with Okta] is a much easier way for customers or partners to seamlessly integrate into our environment. They could do it before, but we are ‘widgetizing’ it now,” he said.

May
15
2018
--

Auth0 snags $55M Series D, seeks international expansion

Auth0, a startup based in Seattle, has been helping developers with a set of APIs to build authentication into their applications for the last five years. It’s raised a fair bit of money along the way to help extend that mission, and today the company announced a $55 million Series D.

This round was led by led by Sapphire Ventures with help from World Innovation Lab, and existing investors Bessemer Venture Partners, Trinity Ventures, Meritech Capital and K9 Ventures. Today’s investment brings the total raised to $110 million. The company did not want to share its valuation.

CEO Eugenio Pace said the investment should help them expand further internationally. In fact, one of the investors, World Innovation Lab, is based in Japan and should help with their presence there. “Japan is an important market for us and they should help explain to us how the market works there,” he said.

The company offers an easy way for developers to build in authentication services into their applications, also known as Identification as a Service (IDaaS). It’s a lot like Stripe for payments or Twilio for messaging. Instead of building the authentication layer from scratch, they simply add a few lines of code and can take advantage of the services available on the Auth0 platform.

That platform includes a range of service such as single-sign on, two-factor identification, passwordless log-on and breached password detection.

They have a free tier, which doesn’t even require a credit card, and pay tiers based on the types of users — regular versus enterprise — along with the number of users. They also charge based on machine-to-machine authentication. Pace reports they have 3500 paying customers and tens of thousands of users on the free tier.

All of that has added up to a pretty decent business. While Pace would not share specific numbers, he did indicate the company doubled its revenue last year and expected to do so again this year.

With a cadence of getting funding every year for the last three years, Pace says this round may mark the end of that fundraising cycle for a time. He wasn’t ready to commit to the idea of an IPO, saying that is likely a couple of years away, but he says the company is close to profitability.

With the new influx of money, the company does plan to expand its workforce as moves into markets across the world . They currently have 300 employees, but within a year he expects to be between 400 and 450 worldwide.

The company’s last round was a $30 million Series C last June led by Meritech Capital Partners.

Jan
29
2018
--

Relx acquires ThreatMetrix for $817M to ramp up in risk-based authentication

 Another startup in the area of cyber security has been snapped up as platform businesses serving enterprises look for more ways of securing their own networks and working with customers to secure theirs. Today, the RELX Group (formerly known as Reed Elsevier) announced that it would acquire ThreatMetrix for £580 million (about $817 million) in cash. This is a big exit for ThreatMetrix, which… Read More

Jan
17
2018
--

Cloudflare Access aims to replace corporate VPNs

 If you’re part of a big company, chances are that there are resources that are only available via the internal network, or whatever your company calls it. The usual way to access these from outside company property is a VPN, but VPNs are a clumsy solution — one companies like Google and Amazon are leaving behind. Now Cloudflare wants you to do the same and use its new Access… Read More

Sep
05
2017
--

Identity management startup ForgeRock snags $88 million Series D

 Used to be when we thought about identity as it relates to computers and software, it involved a person logging into an application or service. Increasingly though, you also have machines and sensors attempting to authenticate as they access your systems. That creates complications for identity management vendors because instead of managing thousands or tens of thousands of identities, it… Read More

May
17
2017
--

MongoDB Authentication and Roles: Creating Your First Personalized Role

MongoDB Authentication and Roles

MongoDB Authentication and RolesIn this blog post, we’ll walk through the native MongoDB authentication and roles, and learn how to create personalized roles. It is a continuation of Securing MongoDB instances.

As said before, MongoDB features a few authentication methods and built-in roles that offer great control of both who is connecting to the database and what they are allowed to do. However, some companies have their own security policies that are often not covered by default roles. This blog post explains not only how to create personalized roles, but also how to grant minimum access to a user.

Authentication Methods

SCRAM-SHA-1 and MONGODB-CR are challenge-response protocols. All the users and passwords are saved encrypted in the MongoDB instance. Challenge-response authentication methods are widely used on the internet in several server-client software. These authentication methods do not send passwords as plain text to the server when the client is starting an authentication. Each new session has a different hash/code, which stops people from getting the password when sniffing the network.

The MONGODB-CR method was deprecated in version 3.0.

The x.509 authentication is an internal authentication that allows instances and clients to communicate to each other. All certificates are signed by the same Certificate Authority and must be valid. All the network traffic is encrypted by a given key, and it is only possible to read data with a valid certificate signed by such key.

MongoDB also offers external authentications such as LDAP and Kerberos. When using LDAP, users can log in to MongoDB using their centralized passwords. The LDAP application is commonly used to manage users and passwords in wide networks. Kerberos is a service that allows users to login only once, and then generates access tickets so that the users are allowed to access other services. Some configuration is necessary to use external authentication.

Built in roles

  • read: collStats,dbHash,dbStats,find,killCursors,listIndexes,listCollections,
  • readWrite: all read privileges + convertToCapped, createCollection,dbStats, dropCollection, createIndex, dropIndex, emptycapped, insert, listIndexes,remove, renameCollectionSameDB, update.
  • readAnyDatabase: allows the user to perform read in any database except the local and the config databases.

And so on…

In this tutorial, we are going to give specific privileges to a user who is allowed to only read the database, although he is allowed to write in a specific collection.

For this tutorial, we are using MongoDB 3.4 with previously configured authentication.

Steps:

  1. Create the database:
    mongo --authenticationDatbase admin -u superAdmin -p
    use percona
    db.foo.insert({x : 1})
    db.foo2.insert({x : 1})
  2. Create a new user:
    > db.createUser({user : 'client_read', pwd : '123', roles : ['read']})
    Successfully added user: { "user" : "client_read", "roles" : [ "read" ] }
  3. Log in with the user that has just been created and check the user access:
    ./mongo localhost/percona -u client_read -p
    MongoDB shell version v3.4.0-rc5
    Enter password:
    db.foo.find()
    { "_id" : ObjectId("586bc2e9cac0bbb93f325d11"), "x" : 1 }
    db.foo2.find().count()
    1
    // If user try to insert documents will receive an error:
    > db.foo.insert({x : 2})
    WriteResult({
                "writeError" : {
                "code" : 13,
                "errmsg" : "not authorized on percona to execute command
                     { insert: "foo", documents: [ { _id: ObjectId('586bc36e7b114fb2517462f3'), x: 2.0 } ], ordered: true }"
                }
    })
  4. Log out and log in again with administrator user to create a new role for this user:
    mongo --authenticationDatabase admin -u superAdmin -p
    db.createRole({
    role : 'write_foo2_Collection',
    privileges : [ {resource : {db : "percona", collection : "foo2"}, actions : ["insert","remove"]}
    ],
    roles : ["read"]
    })
    db.updateUser('client_read', roles : ['write_foo2_Collection'])
  5. Check the new access:
    ./mongo
    db.auth('client_read','123')
    1
    > show collections
    foo
    foo2
    > db.foo.find()
    { "_id" : ObjectId("586bc2e9cac0bbb93f325d11"), "x" : 1 }
    > db.foo2.insert({y : 2})
    WriteResult({ "nInserted" : 1 })
    > db.foo.insert({y : 2}) //does not have permission.
    WriteResult({
          "writeError" : {
                "code" : 13,
                "errmsg" : "not authorized on percona to execute command { insert: "foo", documents: [ { _id: ObjectId('586bc5e26f05b3a5db849359'), y: 2.0 } ], ordered: true }"
                         }
    })
  6. We can also add access to other database resources. Let’s suppose we would like to grant this just created user permission to execute a getLog command. This command is available in the clusterAdmin role, but we do not want to give all this role’s access to him. See https://docs.mongodb.com/v3.0/reference/privilege-actions/#authr.getLog.

    There is a caveat/detail/observation here. If we want to grant cluster privileges to a user, we should create the role in the admin database. Otherwise, the command will fail:

    db.grantPrivilegesToRole(
         "write_foo2_Collection",
               [
                      {resource : {cluster : true}, actions : ["getLog"] }
               ]
    )
    Roles on the 'percona' database cannot be granted privileges that target other databases or the cluster :
  7. We are creating the same role in the admin database. This user only works properly if the admin database is present in a possible restore. Otherwise, the privileges fail:

    use admin
    db.createRole({
         role : 'write_foo2_Collection_getLogs',
         privileges : [
                           {resource : {db : "percona", collection : "foo2"}, actions : ["insert","remove"]},
                           {resource : {cluster : true}, actions : ["getLog"]}],  
         roles : [ {role : "read", db: "percona"}]
    })
    use percona
    db.updateUser( "client_read",
    {
         roles : [
              { role : "write_foo2_Collection_getLogs", db : "admin" }
                    ]
    }
    )
  8. Now the user has the same privileges as before, plus the getLog permission. We can test this user new access with:

    mongo --authenticationDatabase percona -u read_user -p
    db.adminCommand({getLog : 'global'})
    {
              "totalLinesWritten" : 287,
              "log" : [....
    ….
    }

I hope you find this post useful. Please feel free to ping me on twitter @AdamoTonete or @percona and let us know your thoughts.

Feb
22
2017
--

Webinar Thursday, February 23, 2017: Troubleshooting MySQL Access Privileges Issues

Troubleshooting MySQL Access Privileges

Troubleshooting MySQL Access PrivilegesPlease join Sveta Smirnova, Percona’s Principal Technical Services Engineer, as she presents Troubleshooting MySQL Access Privileges Issues on
February 23, 2017 at 11:00 am PST / 2:00 pm EST (UTC-8).

Do you have registered users who can’t connect to the MySQL server? Strangers modifying data to which they shouldn’t have access?

MySQL supports a rich set of user privilege options and allows you to fine tune access to every object in the server. The latest versions support authentication plugins that help to create more access patterns.

However, finding errors in such a big set of options can be problematic. This is especially true for environments with hundreds of users, all with different privileges on multiple objects. In this webinar, I will show you how to decipher error messages and unravel the complicated setups that can lead to access errors. We will also cover network errors that mimic access privileges errors.

In this webinar, we will discuss:

  • Which privileges MySQL supports
  • What GRANT statements are
  • How privileges are stored
  • How to find out why a privilege does not work properly
  • How authentication plugins make difference
  • What the best access control practices are

To register for this webinar please click here.

InnoDB TroubleshootingSveta Smirnova, Principal Technical Services Engineer

Sveta joined Percona in 2015. Her main professional interests are problem-solving, working with tricky issues, bugs, finding patterns that can solve typical issues quicker, and teaching others how to deal with MySQL issues, bugs and gotchas effectively. Before joining Percona, Sveta worked as Support Engineer in the MySQL Bugs Analysis Support Group at MySQL AB-Sun-Oracle. She is the author of book “MySQL Troubleshooting” and JSON UDF functions for MySQL.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com