Filter
Top Products
CHAPTER 10 Managing User IDs and Permissions
369
SELECT *
FROM company.customers
To rectify the situation, make the Sales group a member of the company group.
GRANT GROUP TO company;
GRANT MEMBERSHIP IN GROUP company TO Sales;
Now Joe and Sally, being members of the Sales group, are indirectly members
of the
company group, and can reference their tables without qualifiers. The
following command will now work:
SELECT *
FROM Customers
Note
Joe and Sally do not have any extra permissions because of their membership
in the
company group. The company group has not been explicitly granted any
table permissions. (The
company user ID has implicit permission to look at
tables like
Salaries because it created the tables and has DBA authority.) Thus,
Joe and Sally still get an error executing either of these commands:
SELECT *
FROM Salaries;
SELECT *
FROM company.Salaries
In either case, Joe and Sally do not have permission to look at the Salaries table.
Using views and procedures for extra security
For databases that require a high level of security, defining permissions directly
on tables has limitations. Any permission granted to a user on a table applies to
the whole table. There are many cases when users’ permissions need to be
shaped more precisely than on a table-by-table basis. For example:
• It is not desirable to give access to personal or sensitive information stored
in an employee table to users who need access to other parts of the table.
• You may wish to give sales representatives update permissions on a table
containing descriptions of their sales calls, but limit such permissions to
their own calls.
In these cases, you can use views and stored procedures to tailor permissions
to suit the needs of your organization. This section describes some of the uses
of views and procedures for permission management.