Proper management of user IDs and permissions is essential in a data warehouse. It allows users to carry out their jobs effectively, while maintaining the security and privacy of appropriate information within the database.
Use SQL statements to assign user IDs to new users of a database, to grant and revoke permissions for database users, and to display the current permissions of users.
A permission grants the ability to create, modify, query, use, or delete database objects such as tables, views, users, and so on. An authority grants the ability to perform a task at the database level, such as backing up the database.
Database permissions are assigned to user IDs. Throughout this chapter, the term user serves as a synonym for user ID. Remember, however, that permissions are granted and revoked for each user ID.
Even if there are no security concerns regarding a multiuser database, there are good reasons for setting up an individual user ID for each user. The administrative overhead for individual user IDs is very low if a group with the appropriate permissions is set up. Groups of users are discussed later in this chapter.
Among the reasons for using individual user IDs are the following:
The network server screen and the listing of connections in Sybase Central are both much more useful with individual user IDs, as you can tell which connections are which users.
The backup log identifies the user ID that created the backup.
The message log displays the user ID for each database connection. For details, see “Message logging” in Chapter 1, “Overview of Sybase IQ System Administration” in System Administration Guide: Volume 1.
While all permissions are inheritable (from the groups to which the user belongs), only some authorities are inheritable.
Except for DBA, which has full administrative privileges, each authority has permissions to perform certain types of tasks. See “Using procedures for tailored security”.
