What is the difference between system and object privileges?
First off, let’s define what a privilege is in a database. In a database, every user account can be granted a number of privileges, which are also known as permissions. These privileges allow a particular user account to do certain things, like DELETE and UPDATE certain tables, CREATE a database, SELECT from a certain table, and many other things.
System and object privileges in Oracle, SQL Server, and Sybase
In Oracle, Microsoft’s SQL Server, and in Sybase Adaptive Server privileges are further divided into two different categories: 1. system privileges and 2. object privileges. What’s the difference between system and object privileges? Well, lets go through an explanation of each one, and then we’ll discuss the differences between the two.
System privileges
System privileges are privileges given to users to allow them to perform certain functions that deal with managing the database and the server . Most of the different types of permissions supported by the database vendors fall under the system privilege category. Let’s go through some examples of system privileges in Oracle and SQL Server.
Examples of Oracle system privileges
- CREATE USER. The CREATE USER permission, when granted to a database user, allows that database user to create new users in the database.
- CREATE TABLE. The CREATE TABLE permission, when granted to a database user, allows that database user to create tables in their own schema. This type of privilege is also available for other object types – like stored procedures and indexes.
- CREATE SESSION. The CREATE SESSION permission, when granted to a database user, allows that database user to connect to the database.
Examples of Microsoft SQL Server System Privileges
- BACKUP DATABASE. The BACKUP DATABASE permission, when granted to a database user, allows that database user to create backups of the databases on the server.
- CREATE DATABASE. The CREATE DATABASE permission, when granted to a database user, allows that database user to create new databases on the server.
- SHUTDOWN. The SHUTDOWN permission, when granted to a database user, allows that database user to issue a command to shutdown the server.
Object privileges
Object privileges are privileges given to users so that they can perform certain actions upon certain database objects – where database objects are things like tables, stored procedures, indexes, etc. Some examples of object privileges include granting a particular database user the right to DELETE and/or SELECT from a particular table. This is done using the GRANT clause, which you can read more about here: SQL GRANT.
System versus Object privileges
So, now hopefully it’s clear that the difference between system and object privileges is that system privileges are used for server and database privileges. But object privileges are used to grant privileges on database objects like tables, stored procedures, indexes, etc.