You can grant access to certain columns to view in SQL without providing access to specific users to see the whole database. This is pretty useful when delegating reporting to users, without giving them access to all of the data in your database. For example, a user might be able to see a column with an address, but not a column with a credit card number, increasing database security while allowing you to delegate certain tasks when appropriate.
In this article, we’ll use the same “Customers” table from our first articles, signupdate:
ID Site Contact Address City Zip Country SignupDate
1 Krypted Charles Edge my house Minneapolis 55418 US 2005-01-01
2 Apple Tim Cook spaceship Cupertino 95014 US 2015-12-05
3 Microsoft Satya Nadella campus Redmond 98053 US 2014-11-01
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 2010-03-10
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US 2016-01-01
Next, we’ll create a view called SignupDate that only has customers that signed up on January 1st of 2005. This view returns the data set of contacts and signup dates:
CREATE VIEW signupdate AS SELECT * FROM Customers WHERE OrderDate='2005-01-01';
The syntax is similar to a SELECT, but with CREATE VIEW followed by the name of the view and then AS followed by the SELECT statement. The view is a virtual table containing the output of the query rather than data. Once created, use use the signupdate view in a query:
SELECT * FROM signupdate;
This SQL statement returns the following results:
1 Krypted Charles Edge my house Minneapolis 55418 US 2005-01-01
You can also use the view to contain a query with just the columns you want, according to how you structure your query, thus granting access to specific columns, without granting access to all of the columns in a table.