[SERVER-27554] Authorization error when using the find() method on the system.views collection Created: 30/Dec/16  Updated: 27/Oct/23  Resolved: 03/Jan/17

Status: Closed
Project: Core Server
Component/s: Security
Affects Version/s: 3.4.1
Fix Version/s: None

Type: Bug Priority: Minor - P4
Reporter: Jason Swartzbaugh Assignee: Backlog - Query Team (Inactive)
Resolution: Works as Designed Votes: 0
Labels: read-only-views
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: Zip Archive dump.zip    
Issue Links:
Related
related to SERVER-27600 Queryable Backups need capabilities t... Closed
Assigned Teams:
Query
Operating System: ALL
Steps To Reproduce:
  1. Unzip the attached dump.zip file and run mongorestore on a mongod process with no access control enabled to restore the deviceInfo database, its collections, and view.
  2. Switch to the deviceInfo database.
  3. Execute db.system.views.find() to see the view metadata.
  4. Enable authentication/authorization (SCRAM-SHA-1) with username/password and assign the user to { role: "root", db: "admin" }
  5. Restart the mongod process with access controls enabled and provide username/password.
  6. Switch to the deviceInfo database.
  7. Execute db.system.views.find() to receive the error message detailed in the description above.
Participants:
Case:

 Description   

When access control is not enabled, I am able to execute a db.system.views.find() command and get back the metadata about any view(s) that has been created for that database.

db.system.views.findOne()
{
	"_id" : "deviceInfo.userDevices",
	"viewOn" : "users",
	"pipeline" : [
		{
			"$lookup" : {
				"from" : "devices",
				"localField" : "userID",
				"foreignField" : "userID",
				"as" : "devices"
			}
		},
		{
			"$project" : {
				"_id" : 0,
				"userID" : 1,
				"userName" : 1,
				"devices.deviceID" : 1,
				"devices.deviceName" : 1
			}
		}
	]
}

When access control has been enabled using SCRAM-SHA1 username/password and a role of root, I get an auth error when issuing the same db.system.views.find() command.

db.system.views.find()
Error: error: {
	"ok" : 0,
	"errmsg" : "not authorized on deviceInfo to execute command { find: \"system.views\", filter: {} }",
	"code" : 13,
	"codeName" : "Unauthorized"
}

My understanding is that the root role provides full privileges on all resources, so I would assume that I should be able to execute a find() against the system.views collection in the same way I did when security was not enabled.



 Comments   
Comment by David Storch [ 03/Jan/17 ]

kyle.suarez, no objections!

Comment by Kyle Suarez [ 03/Jan/17 ]

Hello jason.swartzbaugh and shawn.mccarthy,

Per the built-in roles documentation, the dbAdmin role "provides [a bunch of actions] on all non-system collections. This role does not include full read access on non-system collections." There are three exceptions for which the find action is granted: system.indexes, system.namespaces and system.profile.

The root role is similar: like dbAdmin, it is just a bundle of other roles, including readWriteAnyDatabase and dbAdminAnyDatabase, so it also does not get full read access to system collections.

As schwerin points out, the existence of the system.views collection is an implementation detail. A user with the dbAdmin or root roles should prefer to do all of their views-related maintenance using commands like listCollections, create and collMod, as the content of the data in this collection is subject to change. As such, I'm inclined to close this as Works as Designed, unless david.storch has any objections.

Comment by Jason Swartzbaugh [ 31/Dec/16 ]

Thanks schwerin and shawn.mccarthy, I created a custom role via Cloud Manager that allows me to use the find() method to see the view metadata. I guess I'm wondering if a privilege like this should be automatically included with the "root" and "dbOwner" roles (and possibly others) as well?

Comment by Shawn McCarthy (Inactive) [ 30/Dec/16 ]

I was helping jason.swartzbaugh with the ticket, here is what I was able to do:

  • populated a collection called trade under risk db: risk.trade

    MongoDB Enterprise > db.trade.findOne()
    {
    	"_id" : ObjectId("586586fe2f215e28f69d8216"),
    	"valuation_date" : 1482688702450,
    	"region" : "US",
    	"source" : "source_1767243",
    	"book" : "book_4218",
    	"tradeid" : "tradeid_269577",
    	"assetid" : "null",
    	"tradetype" : "Swap",
    	"subtype" : "",
    	"buy_sell" : "S",
    	"cyy" : "USD",
    	"curve_currency" : "USD",
    	"curveindex" : "BRL_CONV",
    	"customer_code" : "customer_code_11639",
    	"inter_aff_cpty" : "inter_aff_cpty_2782346",
    	"tradedate" : 1482479902450,
    	"matdate" : 1486839502450,
    	"assignment_type" : "",
    	"broker" : "broker_34717",
    	"cust_underlying" : "",
    	"cust_underlying_full" : "cust_underlying_full_31250",
    	"executionvenuetype" : "executionvenuetype_37547",
    	"intra_cpty" : "",
    	"intra_cpty_und" : "",
    	"refstatus" : "",
    	"risk_type" : "IRZDISDEL",
    	"salesperson" : "salesperson_8576",
    	"tradestatus" : "VERIFIED",
    	"treats_code" : "treats_code_21467",
    	"risk" : -5449.312765872892,
    	"risk_unadj" : 15864290.738381157,
    	"notional" : -39630869208.55186
    }
    

  • created a dummy view that projects out a couple of fields

    MongoDB Enterprise > db.system.views.find()
    { "_id" : "risk.dummy", "viewOn" : "trade", "pipeline" : [ { "$project" : { "region" : 1, "source" : 1 } } ] }
     
    MongoDB Enterprise > db.dummy.findOne()
    {
    	"_id" : ObjectId("586586fe2f215e28f69d8216"),
    	"region" : "US",
    	"source" : "source_1767243"
    }
    

  • create a root user under admin and a dbOwner under risk

    MongoDB Enterprise > var root = {user: 'root', pwd: 'password', roles: [ {role: 'root', db: 'admin'}]}
    MongoDB Enterprise > var owner = {user: 'owner', pwd: 'password', roles: [ {role: 'dbOwner', db: 'risk'}]}
    MongoDB Enterprise > use admin
    switched to db admin
    MongoDB Enterprise > db.createUser(root)
    Successfully added user: {
    	"user" : "root",
    	"roles" : [
    		{
    			"role" : "root",
    			"db" : "admin"
    		}
    	]
    }
    MongoDB Enterprise > use risk
    switched to db risk
    MongoDB Enterprise > db.createUser(owner)
    Successfully added user: {
    	"user" : "owner",
    	"roles" : [
    		{
    			"role" : "dbOwner",
    			"db" : "risk"
    		}
    	]
    }
    MongoDB Enterprise > 
    

  • enabled authentication and restarted
  • first logged in as root, tested various access:

    [root@mdb ~]# /opt/mdb34/bin/mongo
    MongoDB shell version v3.4.1
    connecting to: mongodb://127.0.0.1:27017
    MongoDB server version: 3.4.1
    MongoDB Enterprise > show dbs;
    2016-12-30T22:59:40.491+0000 E QUERY    [main] Error: listDatabases failed:{
    	"ok" : 0,
    	"errmsg" : "not authorized on admin to execute command { listDatabases: 1.0 }",
    	"code" : 13,
    	"codeName" : "Unauthorized"
    } :
    _getErrorWithCode@src/mongo/shell/utils.js:25:13
    Mongo.prototype.getDBs@src/mongo/shell/mongo.js:62:1
    shellHelper.show@src/mongo/shell/utils.js:755:19
    shellHelper@src/mongo/shell/utils.js:645:15
    @(shellhelp2):1:1
    MongoDB Enterprise > use admin
    switched to db admin
    MongoDB Enterprise > db.auth('root', 'password');
    1
    MongoDB Enterprise > show dbs;
    admin    0.000GB
    local    0.000GB
    risk     0.683GB
    riskscp  0.000GB
    MongoDB Enterprise > use risk;
    switched to db risk
    MongoDB Enterprise > db.runCommand({listCollections: 1});
    {
    	"cursor" : {
    		"id" : NumberLong(0),
    		"ns" : "risk.$cmd.listCollections",
    		"firstBatch" : [
    			{
    				"name" : "system.views",
    				"type" : "collection",
    				"options" : {
    					
    				},
    				"info" : {
    					"readOnly" : false
    				},
    				"idIndex" : {
    					"v" : 2,
    					"key" : {
    						"_id" : 1
    					},
    					"name" : "_id_",
    					"ns" : "risk.system.views"
    				}
    			},
    			{
    				"name" : "trade",
    				"type" : "collection",
    				"options" : {
    					
    				},
    				"info" : {
    					"readOnly" : false
    				},
    				"idIndex" : {
    					"v" : 2,
    					"key" : {
    						"_id" : 1
    					},
    					"name" : "_id_",
    					"ns" : "risk.trade"
    				}
    			},
    			{
    				"name" : "dummy",
    				"type" : "view",
    				"options" : {
    					"viewOn" : "trade",
    					"pipeline" : [
    						{
    							"$project" : {
    								"region" : 1,
    								"source" : 1
    							}
    						}
    					]
    				},
    				"info" : {
    					"readOnly" : true
    				}
    			}
    		]
    	},
    	"ok" : 1
    }
    MongoDB Enterprise > show collections;
    dummy
    system.views
    trade
    MongoDB Enterprise > db.system.views.find()
    Error: error: {
    	"ok" : 0,
    	"errmsg" : "not authorized on risk to execute command { find: \"system.views\", filter: {} }",
    	"code" : 13,
    	"codeName" : "Unauthorized"
    }
    

    Everything worked except the find on the db.system.views collections, next with the same root user created an index and viewed it:

    MongoDB Enterprise > db.trade.createIndex({regions: 1});
    {
    	"createdCollectionAutomatically" : false,
    	"numIndexesBefore" : 1,
    	"numIndexesAfter" : 2,
    	"ok" : 1
    }
     
    MongoDB Enterprise > db.runCommand({listIndexes: 'trade'});
    {
    	"cursor" : {
    		"id" : NumberLong(0),
    		"ns" : "risk.$cmd.listIndexes.trade",
    		"firstBatch" : [
    			{
    				"v" : 2,
    				"key" : {
    					"_id" : 1
    				},
    				"name" : "_id_",
    				"ns" : "risk.trade"
    			},
    			{
    				"v" : 2,
    				"key" : {
    					"regions" : 1
    				},
    				"name" : "regions_1",
    				"ns" : "risk.trade"
    			}
    		]
    	},
    	"ok" : 1
    }
    

  • now logged in with dbOwner user, owner

    [root@mdb ~]# /opt/mdb34/bin/mongo
    MongoDB shell version v3.4.1
    connecting to: mongodb://127.0.0.1:27017
    MongoDB server version: 3.4.1
    MongoDB Enterprise > show dbs;
    2016-12-30T23:05:53.957+0000 E QUERY    [main] Error: listDatabases failed:{
    	"ok" : 0,
    	"errmsg" : "not authorized on admin to execute command { listDatabases: 1.0 }",
    	"code" : 13,
    	"codeName" : "Unauthorized"
    } :
    _getErrorWithCode@src/mongo/shell/utils.js:25:13
    Mongo.prototype.getDBs@src/mongo/shell/mongo.js:62:1
    shellHelper.show@src/mongo/shell/utils.js:755:19
    shellHelper@src/mongo/shell/utils.js:645:15
    @(shellhelp2):1:1
    MongoDB Enterprise > use risk
    switched to db risk
    MongoDB Enterprise > db.auth('owner', 'password');
    1
    MongoDB Enterprise > show dbs;
    2016-12-30T23:06:11.038+0000 E QUERY    [main] Error: listDatabases failed:{
    	"ok" : 0,
    	"errmsg" : "not authorized on admin to execute command { listDatabases: 1.0 }",
    	"code" : 13,
    	"codeName" : "Unauthorized"
    } :
    _getErrorWithCode@src/mongo/shell/utils.js:25:13
    Mongo.prototype.getDBs@src/mongo/shell/mongo.js:62:1
    shellHelper.show@src/mongo/shell/utils.js:755:19
    shellHelper@src/mongo/shell/utils.js:645:15
    @(shellhelp2):1:1
    MongoDB Enterprise > show collections;
    dummy
    system.views
    trade
    MongoDB Enterprise > db.runCommand({listCollections: 1});
    {
    	"cursor" : {
    		"id" : NumberLong(0),
    		"ns" : "risk.$cmd.listCollections",
    		"firstBatch" : [
    			{
    				"name" : "system.views",
    				"type" : "collection",
    				"options" : {
    					
    				},
    				"info" : {
    					"readOnly" : false
    				},
    				"idIndex" : {
    					"v" : 2,
    					"key" : {
    						"_id" : 1
    					},
    					"name" : "_id_",
    					"ns" : "risk.system.views"
    				}
    			},
    			{
    				"name" : "trade",
    				"type" : "collection",
    				"options" : {
    					
    				},
    				"info" : {
    					"readOnly" : false
    				},
    				"idIndex" : {
    					"v" : 2,
    					"key" : {
    						"_id" : 1
    					},
    					"name" : "_id_",
    					"ns" : "risk.trade"
    				}
    			},
    			{
    				"name" : "dummy",
    				"type" : "view",
    				"options" : {
    					"viewOn" : "trade",
    					"pipeline" : [
    						{
    							"$project" : {
    								"region" : 1,
    								"source" : 1
    							}
    						}
    					]
    				},
    				"info" : {
    					"readOnly" : true
    				}
    			}
    		]
    	},
    	"ok" : 1
    }
     
    MongoDB Enterprise > db.system.views.find()
    Error: error: {
    	"ok" : 0,
    	"errmsg" : "not authorized on risk to execute command { find: \"system.views\", filter: {} }",
    	"code" : 13,
    	"codeName" : "Unauthorized"
    }
    

    So I am able to list the collections and get the view information but not able to do a find on the system.views collection for either user.

  • next I created a custom role for the dbOwner and added it to the user

    MongoDB Enterprise > role
    {
    	"role" : "readviews",
    	"privileges" : [
    		{
    			"resource" : {
    				"db" : "risk",
    				"collection" : "system.views"
    			},
    			"actions" : [
    				"find"
    			]
    		}
    	],
    	"roles" : [
    		{
    			"role" : "dbOwner",
    			"db" : "risk"
    		}
    	]
    }
    MongoDB Enterprise > db
    risk
    MongoDB Enterprise > db.createRole(role);
    {
    	"role" : "readviews",
    	"privileges" : [
    		{
    			"resource" : {
    				"db" : "risk",
    				"collection" : "system.views"
    			},
    			"actions" : [
    				"find"
    			]
    		}
    	],
    	"roles" : [
    		{
    			"role" : "dbOwner",
    			"db" : "risk"
    		}
    	]
    }
    MongoDB Enterprise > db.grantRolesToUser('owner', ['readviews']);
    

  • finally without logging out I was then able to read the system.views collection:

    MongoDB Enterprise > db.system.views.find()
    { "_id" : "risk.dummy", "viewOn" : "trade", "pipeline" : [ { "$project" : { "region" : 1, "source" : 1 } } ] }
    

As the user with the dbOwner permission I was able to do everything - listCollections, create & read indexes, create roles and assign these roles to myself, but I was not able to execute a find on the system.views collection. I was assuming that I would be able to run a find on that collection to get the information.

Comment by Andy Schwerin [ 30/Dec/16 ]

The root role does not have every privilege, by design. However, I am not certain if it is intended to have this privilege. As a workaround, you can use listCollections to enumerate the views, or grant "find" on "db.system.views" to a custom role and assign it to your user. It's probably more future-proof to use listCollections. The schema and even existence of the system.views collection is an implementation detail.

Generated at Thu Feb 08 04:15:29 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.