SQL create users
Create Users
Following the standard SQL syntax for creating how to create users, we have discussed how to create a user in different database platforms like DB2, Oracle, MySQL, PostgreSQL, and Microsoft SQL Server.
SQL Syntax:
GRANT CONNECT TO username IDENTIFIED BY password
Parameters:
Name | Description |
---|---|
username | A username. |
password | Password for a username. |
DB2 Syntax
GRANT privilege ON table_name TO USER username
Parameters:
Name | Description |
---|---|
privilege | A privilege (for example SELECT)you want to assign to the user. |
table_name | Table on which you want to assign permission to the user. |
username | A username already existing in the underlying Operating System. |
Oracle Syntax:
CREATE USER username IDENTIFIED BY password
Parameters:
Name | Description |
---|---|
username | A username. |
password | Password for the username. |
MySQL Syntax:
CREATE USER username IDENTIFIED BY password IDENTIFIED WITH auth_plugin
Parameters:
Name | Description |
---|---|
username | A username. |
password | Password for the username. |
auth_plugin | Authorization plugin. |
PostgreSQL Syntax:
CREATE USER username WITH SYSID uid | CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | IN GROUP groupname [, ...] | [ ENCRYPTED | UNENCRYPTED ]PASSWORD 'password' | VALID UNTIL 'time'
Parameters:
Name | Description |
---|---|
username | A username. |
uid | The SYSID clause can be used to choose the PostgreSQL user ID of the new user. |
CREATEDB NOCREATEDB | These clauses define a user's ability to create databases. If CREATEDB is specified, the user being defined will be allowed to create his own databases. Using NOCREATEDB will deny a user the ability to create databases. If not specified, NOCREATEDB is the default. |
CREATEUSER NOCREATEUSER | These clauses determine whether a user will be permitted to create new users himself. CREATEUSER will also make the user a superuser, who can override all access restrictions. If not specified, NOCREATEUSER is the default. |
groupname | A name of an existing group into which to insert the user as a new member. Multiple group names may be listed. |
password | Password for the username. |
ENCRYPTED UNENCRYPTED | These key words control whether the password is stored encrypted in the system catalogs. |
time | The VALID UNTIL clause sets an absolute time after which the user's password is no longer valid. If this clause is omitted the password will be valid for all time. |
SQL Server Syntax:
CREATE USER username [ { { FOR | FROM } { LOGIN loginname | CERTIFICATE cert_name | ASYMMETRIC KEY asym_key_name } | WITHOUT LOGIN ] [ WITH DEFAULT_SCHEMA =schema_name ]
Parameters:
Name | Description |
---|---|
username | A username. |
loginname | Specifies the SQL Server login for which the database user is being created. |
cert_name | Specifies the certificate for which the database user is being created. |
asym_key_name | Specifies the asymmetric key for which the database user is being created. |
schema_name | Specifies the first schema that will be searched by the server when it resolves the names of objects for this database user. |
WITHOUT LOGIN | Specifies that the user should not be mapped to an existing login. |
Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.
Practice SQL Exercises
- SQL Exercises, Practice, Solution
- SQL Retrieve data from tables [33 Exercises]
- SQL Boolean and Relational operators [12 Exercises]
- SQL Wildcard and Special operators [22 Exercises]
- SQL Aggregate Functions [25 Exercises]
- SQL Formatting query output [10 Exercises]
- SQL Quering on Multiple Tables [8 Exercises]
- FILTERING and SORTING on HR Database [38 Exercises]
- SQL JOINS
- SQL SUBQUERIES
- SQL Union[9 Exercises]
- SQL View[16 Exercises]
- SQL User Account Management [16 Exercise]
- Movie Database
- BASIC queries on movie Database [10 Exercises]
- SUBQUERIES on movie Database [16 Exercises]
- JOINS on movie Database [24 Exercises]
- Soccer Database
- Introduction
- BASIC queries on soccer Database [29 Exercises]
- SUBQUERIES on soccer Database [33 Exercises]
- Hospital Database
- Employee Database
- More to come!
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.
Previous: Controlling Transactions
Next: Change passwords
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework