Jun 19, 2018 by Robert Gravelle

Part 4: The Privilege Manager tool

In this series, we've been exploring how to perform common user administration tasks using Navicat's flagship product, Navicat Premium. In the last blog, we looked at the Server Privileges, Privileges, and SQL Preview tabs of the New User Object tab.

Setting privileges for each user as we did in the last blog is not the only way to do so; the Privilege Manager offers another way to set privileges for a connection as well as its database objects. Available for MySQL, Oracle, PostgreSQL, SQL Server and MariaDB, the Privilege Manager will be the subject of today's blog.

Working with Connection-level Privileges

To access the Privilege Manager, click the Privilege Manager button on the User Object toolbar. That will open the Privilege Manager in a new tab with the connections for the most recently opened connection.

From there, you can either work with Connection-level privileges or those associated with a particular database. Let's start with Connection-level privileges.

The Connection always appears at the top of the tree, with databases below it, along with individual objects within each:

If it is not already selected, click the Connection name to see a list of users that have access, along with their respective privileges.

Scroll the grid horizontally until reaching the target privilege that you're interested in.

Check the box beside the privilege to assign it to that user. For example, clicking the Trigger checkbox in the bob_s@localhost row will grant Trigger privileges that that user:

Conversely, unchecking the box for a privilege removes that privilege.

Don't forget to click the Save button on the Privilege Manager toolbar to commit your changes. That being said, if you do, Navicat will ask you if you'd like to save your changes when you close the Privilege Manager tab.

Working with Database-level Privileges

Clicking a Database name displays a list of users that have access to it, along with their respective privileges. To assign Trigger privileges to bob_s@localhost on the Sakila Database:

Click the Sakila Database in the Object tree.

Scroll the grid horizontally until reaching the Trigger privilege checkbox.

Check the box next to the Trigger privilege to assign it to that user:

Conversely, unchecking the box for the privilege listed removes that privilege.

Once again, don't forget to click the Save button on the Privilege Manager toolbar to commit your changes. If you do, Navicat will ask you if you'd like to save your changes when you close the Privilege Manager tab.

Managing Privileges for Database Objects

To grant privileges for specific Database objects such as Tables, Views, Functions, and Stored Procedures, use the Add Privilege Privilege Manager tab toolbar button.

For example:

Expand the node in the tree view until reaching to the target object. The following image shows the sakila database's film_in_stock stored procedure:

Choose the film_in_stock object and click the Add Privilege button to open the dialog.

Check the box beside the user on the left pane.

In the grid, check the relevant options against the privileges listed to grant that object privilege to the selected user. For instance, the following would grant Execute privileges to the bob_s@localhost and secure_admin_99@localhost users for the film_in_stock procedure on the sakila database:

Click the OK button to close the dialog and commit your changes. The new privileges will now appear in the grid:

To revoke privileges for a user on any Object, click the Delete Privilege button. For example, to revoke bob_s@localhost's privileges for the film_in_stock procedure on the sakila database that we just added: