SQL
MySQL UPDATE Query with Example
What is the UPDATE Query? UPDATE MySQL command is used to modify rows in a table. The update...
In this tutorial, you will learn
Step 1) Right click on Login/Group Role -> Create -> Click on Login/Group Roleā¦
Step 2) Enter the name for the Login
Step 3) In the definition pane,
Step 4) In the privilege section,
Step 5) In the SQL section
Step 6) Role is created and reflected in the object tree
Step 7) Create a Database and assign owner myguru to it
Step 8) In the command line you can see the owner is "myguru"
You can create a user by using the command-line command
CREATE USER
In PostgreSQL, this method is more convenient for programmers and administrators as they have access to the console of PostgreSQL server. Moreover, they need to execute with a single command in place of logging in and using the interface of the PostgreSQL client.
Syntax: CREATE USER name WITH option where the option can be: |SUPERUSER | NOSUPERUSER | CREATEROLE | NOCREATEROLE | CREATEDB | NOCREATEDB | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT | ( ENCRYPTED ] PASSWORD 'password.' | VALID UNTIL 'timestamp1 | IN ROLE role_name [, ...J | IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...) | USER role_name [, ...] | SYSID uid
Example:
CREATE USER tom;
will create a user tom
CREATE USER tom WITH SUPERUSER;
Will create a user tome with superuser privileges. Let's see the following example.
Step 1) We are creating a superuser valid till 3rd Apri 2025 11:50:38 IST. Enter the following command
CREATE USER mytest WITH LOGIN SUPERUSER CREATEDB CREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT -1 VALID UNTIL '2025-04-03T11:50:38+05:30' PASSWORD '123456';
Step 2) Enter command \du into the checklist of users
NOTE: CREATE USER is same as CREATE ROLE command. The difference between the two commands is when the CREATE USER command is written it is by default in the LOGIN state whereas NOLOGIN is assumed when the CRETE ROLE option is used.
You can grant a user privileges to access a database.
Example, we grant user "mytest" all privileges on user guru99
GRANT ALL PRIVILEGES ON DATABASE guru99 TO mytest;
After execution of this command user will able to access the database with the given permissions.
The command GRANT is very powerful. You can give granular privileges like Select, Insert, Update to a user.
Now that our new user "mytest" exists you can use ALTER USER to change the permissions granted to the librarian.
The format of ALTER USER includes the name of the user followed by some options to tell PostgreSQL which permissive alterations to make:
ALTER USER role_specification WITH OPTION1 OPTION2 OPTION3;
You need to use this command when you make a mistake and wrongly assign permission which you may want to revoke. You can use ALTER USER command with no prefix before the permissive options.
For example, we can remove the SUPERUSER status from mytest like:
ALTER USER mytest WITH NOSUPERUSER;
Using \du, you will observe that the Superuser privilege is removed.
You can use user SUPERUSER permission back "mytest" using the following command
ALTER USER mytest WITH SUPERUSER;
Using \du, you will observe that the Superuser privilege is added.
You can use delete any user if you are sure that the specific user is no longer for your database. Note that deleting the users will never affect the actual database.
Syntax: DROP USER [user]
To delete any user, you have to make certain that the user is an owner of the database. Otherwise, you may receive an error message.
ERROR: role "username" cannot be dropped because some objects depend on it
Example:
Once we change the database owner, the user can be deleted
Here are important commands
CREATE USER [user] | Command to Create a User |
ALTER USER role_specification | Altering Existing User Permissions |
ALTER USER [user] | Revoking Permissions |
ALTER USER [user name] WITH SUPERUSER | Assigning Permission |
DROP USER [user] | Command to Delete a User |
What is the UPDATE Query? UPDATE MySQL command is used to modify rows in a table. The update...
What is MySQL 5.6 Certification? The new release of MySQL 5.6 is designed for professionals...
What is SELECT query in MySQL? SELECT QUERY is used to fetch the data from the MySQL database....
$20.20 $9.99 for today 4.5 (125 ratings) Key Highlights of SQL Tutorial PDF 220+ pages eBook...
What is PL/SQL block? In PL/SQL, the code is not executed in single line format, but it is always...
What is ER Modeling? Entity Relationship Model (ER Modeling) is a graphical approach to database...