LogoLogo
DoltHubBlogDiscordGitHubDolt
  • Introduction
    • What Is Dolt?
    • Installation
      • Linux
      • Windows
      • Mac
      • Build from Source
      • Application Server
      • Docker
      • Upgrading
    • Getting Started
      • Version Controlled Database
      • Git For Data
      • Versioned MySQL Replica
    • Use Cases
      • Data Sharing
      • Data and Model Quality Control
      • Manual Data Curation
      • Version Control for your Application
      • Versioned MySQL Replica
      • Audit
      • Configuration Management
      • Offline First
  • Concepts
    • Dolt
      • Git
        • Commits
        • Log
        • Diff
        • Branch
        • Merge
        • Conflicts
        • Remotes
        • Working Set
      • SQL
        • Databases
        • Schema
        • Tables
        • Primary Keys
        • Types
        • Indexes
        • Views
        • Constraints
        • Triggers
        • Procedures
        • Users/Grants
        • Transactions
        • System Variables
      • RDBMS
        • Server
        • Backups
        • Replication
    • DoltHub/DoltLab
      • Permissions
      • Pull Requests
      • Issues
      • Forks
  • SQL Reference
    • Running the Server
      • Configuration
      • Access Management
      • Branch Permissions
      • Backups
      • Garbage Collection
      • Metrics
      • Replication
      • Troubleshooting
    • Version Control Features
      • Using Branches
      • Merges
      • Querying History
      • Using Remotes
      • Procedures
      • Functions
      • System Tables
      • System Variables
      • Saved Queries
    • SQL Language Support
      • Data Description
      • Expressions, Functions, Operators
      • Supported Statements
      • MySQL Information Schema
      • Collations and Character Sets
      • System Variables
      • Miscellaneous
    • Supported Clients
      • Programmatic
      • SQL Editors
    • Benchmarks and Metrics
      • Correctness
      • Latency
      • Import
  • CLI Reference
    • Commands
    • Git Comparison
  • Architecture
    • Overview
    • Storage Engine
      • Commit Graph
      • Prolly Trees
      • Block Store
    • SQL
      • Go MySQL Server
      • Vitess
  • Guides
    • Cheat Sheet
    • Contributing
      • dolt
      • go-mysql-server
    • MySQL to Dolt Replication
    • Importing Data
    • Integrations
  • Other
    • FAQ
    • Roadmap
    • Versioning
  • Products
    • Hosted Dolt
      • Getting Started
      • Notable Features
      • SQL Workbench
      • Cloning a Hosted Database
      • Using DoltHub as a Remote
      • Infrastructure
    • DoltHub
      • Data Sharing
      • API
        • Authentication
        • SQL
        • CSV
        • Database
        • Hooks
      • Continuous Integration
        • Getting Started
        • Workflow Reference
      • Transform File Uploads
      • Workspaces
    • DoltLab
    • Dolt Workbench
    • DoltgreSQL
Powered by GitBook
On this page
  • What are Users and Grants?
  • How to use Users and Grants
  • Difference between MySQL Users & Grants and Dolt Users & Grants
  • Interaction with Dolt Version Control
  • Example
  • Create a user and grant permissions
  • Access data as a user

Was this helpful?

Edit on GitHub
Export as PDF
  1. Concepts
  2. Dolt
  3. SQL

Users/Grants

PreviousProceduresNextTransactions

Last updated 1 year ago

Was this helpful?

What are Users and Grants?

Users and grants are SQL's permissions system. A database administrator creates users and grants them permissions to do certain actions, like read or write to tables.

How to use Users and Grants

As an administrator user, you create users and roles using CREATE USER and CREATE ROLE statements. You grant permissions to users using GRANT statements. You can grant privileges to specific users but this is generally not advised. It is better to grant privileges to roles and then assign users roles using GRANT statements.

Difference between MySQL Users & Grants and Dolt Users & Grants

The goal is for Dolt users and grants to match MySQL users and grants exactly. As of now, Dolt users and grants are missing some functionality, like column-level privileges and restricted access to stored procedures. if you need more functionality.

Interaction with Dolt Version Control

The users and grants tables exist outside of Dolt in a separate database named mysql. We decided to implement users and grants this way to maintain MySQL compatibility. Thus, user and grant functionality is not versioned in Dolt.

Grants can only apply to traditional SQL access to Dolt tables as of now. Our roadmap includes adding grant functionality to version control operations. For instance, we think it is powerful to grant write permissions to a user on a specific branch.

Example

Create a user and grant permissions

$ dolt sql
# Welcome to the DoltSQL shell.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
dolt> CREATE USER testuser@localhost IDENTIFIED BY 'password123';
dolt> GRANT SELECT ON db_name.example TO testuser@localhost;
dolt> CREATE ROLE testrole;
dolt> GRANT SELECT, INSERT, UPDATE, DELETE on *.* TO testrole;
dolt> exit;

Access data as a user

$ dolt --user="testuser" --password="password123" sql
# Welcome to the DoltSQL shell.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
dolt> USE db_name;
db_name> SELECT * FROM example;
+----+
| pk |
+----+
| 1  |
| 2  |
| 3  |
+----+
db_name> SELECT * FROM example2;
Error 1105: Access denied for user 'testuser'@'localhost' to table 'example2'
db_name> SELECT * FROM table_does_not_exist;
Error 1105: Access denied for user 'testuser'@'localhost' to table 'table_does_not_exist'
db_name> INSERT INTO example VALUES (4);
Error 1105: command denied to user 'testuser'@'localhost'
Submit an issue