There is some confusion when dealing with some users and schemas. Some DBMS support several schemas per database, while other support only one. While some DBMS automatically create a schema per user, others support the use of a single schema as default schema for several users. In this article, we briefly discuss database schemas and users, presenting concepts and useful links.
Database Users
A database user is a representation of a login account that may have access to the database and to (some of) its objects.
In many multi-database systems (the ones that support the creation of several databases in the same DBMS instance, like Microsoft’s SQL Server), a DBMS login represents the credentials (including authentication method and password) used to access the DBMS instance, while a user is a representation of such login in each of the existing databases. In fact, user name and login may be distinct and a login may be associated to zero or one user in each database of the DBMS instance.
Object level and database level privileges are granted to users, while instance-level privileges are assigned to logins. Typical privileges assigned to database users include:
- create tables, indexes or views;
- select on some table or view;
- insert, update or delete on some table.
Users are usually created by the Create User command. In the following links, you can see more about Create User syntax in several DBMS (click on DBMS name):
Database Schemas
A database schema, on the other hand, is a set of database objects and their metadata (e.g. tables structures, including columns’ names, data types, etc). Therefore, every database object belongs to a schema. Typically, there would not exist two objects with equal names in a schema and many relational DBMS (including Oracle, IBM DB2, Microsoft SQL Server and PostgreSQL) support multiple schemas per database.
When a user creates a database object, it may specify the schema where such object should be placed. Usually, the schema name precedes the object name – schema and objet names are separated by a ‘.’ (dot). In the following command, a PRODUCTS table in created in the WEBSALES schema:
Create table websales.products (
id int not null primary key,
name varchar(60) not null
);
Schema names precede object names in many SQL commands, including DML’s Select, Insert, Update and Delete. But the use of schema names is optional and, most of the time, users do not specify them in SQL commands. In fact, many users do not even know the schema that stores accessed objects. This happens because a default schema is associated to each database user. Hence, when the schema is not specified in a SQL command, the DBMS first tries to execute the command in the user’s default schema (user’s default schema is usually set during user creation).
In some DBMS, schemas are created by the Create Schema command. Then, the WEBSALES schema would be created by the following command:
Create Schema WebSales;
Such create command has some additional clauses, which can be found in specific DBMS manuals, like (click on DBMS name):
Schemas and Users in widely used DBMS
Microsoft’s SQL Server, PostgreSQL, IBM DB2 are some DBMS that support the existence of several schemas per database and whose schemas may be created by database administrator when necessary, even though no user adopts such schema as its default.
Other DBMS (like Oracle) automatically creates a schema for each user during user’s creation. In Oracle, one cannot submit a command to create a schema without creating a user. In this case, the user’s default schema always has the same name of the user.
On the other hand, MySQL is one widely used DBMS that supports only one schema per database. In fact, schema and database are treated as synonyms during database creation in MySQL (more information can be found here).
Conclusion
So, to plan the database user and schema configurations of your applications, you should carefully study the options provided by the DBMS you will use. This will influence in configuration, security and management decisions.