Breaking News: Grepper is joining You.com. Read the official announcement!

sql user

Innocent Iguana answered on February 23, 2023 Popularity 9/10 Helpfulness 6/10

answer sql user

related sql user

sql user

Comment

# Create a role
CREATE ROLE myrole; # MySQL, Postgres and SQL Server

# See all available roles
SELECT rolname FROM pg_roles; # Postgres
SELECT name FROM sys.database_principals WHERE type = 'R'; # SQL Server
SELECT * FROM mysql.user WHERE Host='%'; # MySQL

# Delete a role
DROP ROLE myrole; #  Postgres and SQL Server 
DROP USER myrole; # MySQL

# Grant permission to a role
GRANT SELECT, INSERT, UPDATE ON mydatabase.mytable TO myrole; # MySQL, Postgres and SQL Server

# Revoke permission from a role
REVOKE SELECT, INSERT, UPDATE ON mydatabase.mytable FROM myrole; # MySQL, Postgres and SQL Server

# Create a user
CREATE USER new_user WITH PASSWORD 'password'; # Postgres
CREATE LOGIN new_user WITH PASSWORD = 'password'; # SQL Server
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password'; # MySQL

# Change user password
ALTER USER existing_user WITH PASSWORD 'new_password'; # Postgres
ALTER LOGIN existing_user WITH PASSWORD = 'new_password'; # SQL Server
SET PASSWORD FOR 'user'@'localhost' = PASSWORD('newpassword'); # MySQL

# See all available users
SELECT usename FROM pg_user; # Postgres
SELECT name FROM sys.sql_logins; # SQL Server
SELECT User FROM mysql.user; # MySQL

# Delete a user
DROP USER existing_user; # Postgres
DROP LOGIN existing_user; # SQL Server
DROP USER 'existing_user'@'localhost'; # MySQL

# Add a user to a role
ALTER ROLE myuser SET ROLE myrole; # Postgres
ALTER ROLE myrole ADD MEMBER myuser; # SQL Server
GRANT myrole TO myuser WITH GRANT OPTION; # MySQL

# Remove a user from a role
ALTER ROLE myuser DROP ROLE myrole; # Postgres
ALTER ROLE myrole DROP MEMBER myuser; # SQL Server
REVOKE myrole FROM myuser; # MySQL


# Grant permission to a user
GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.mytable TO 'myuser'@'localhost'; # MySQL
GRANT SELECT, INSERT, UPDATE, DELETE ON mytable TO myuser; # Postgres and SQL Server

# Revoke permission from a user
REVOKE SELECT, INSERT, UPDATE, DELETE ON mydatabase.mytable FROM 'myuser'@'localhost';  # MySQL
REVOKE SELECT, INSERT, UPDATE, DELETE ON mytable FROM myuser; # Postgres and SQL Server

# Note : There is also the concept of group that is not supported by MySQL
# Always make sure to grant permission to connect to database and schema
GRANT CONNECT ON DATABASE db_name TO role;
GRANT USAGE ON SCHEMA schema_name TO role;

Popularity 9/10 Helpfulness 6/10 Language sql

Source: Grepper

Tags: sql

Link to this answer
Share Copy Link

Contributed on Feb 23 2023

Innocent Iguana

0 Answers Avg Quality 2/10

Closely Related Answers

sql user

Comment

The provided code demonstrates various SQL commands for creating and managing roles and users in different database management systems (MySQL, Postgres, and SQL Server). These commands include creating roles and users, granting and revoking permissions from roles and users, adding and removing users from roles, and setting passwords for users.

Here are some key points to note:

1. In MySQL, the terms "role" and "user" are often used interchangeably, and the concept of a role is not supported in the same way as in Postgres and SQL Server.

2. Always ensure to grant appropriate permissions to connect to a database and access the required schema for a role or user.

Remember to use the correct syntax for the specific database management system you are working with. Each system may have slight variations in the commands and keywords used. Additionally, take care when granting permissions to avoid exposing sensitive data or granting unnecessary privileges to users or roles. Properly managing roles and permissions is crucial for ensuring the security and integrity of your database.

Popularity 1/10 Helpfulness 1/10 Language sql

Source: www.interviewbit.com

Tags: sql

Link to this answer
Share Copy Link

Contributed on Aug 03 2023

Rashmi Singh

0 Answers Avg Quality 2/10

sql user

Contents

More Related Answers

sql user

Closely Related Answers

sql user

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.