SET SECURITY
The SET SECURITY statement allows the Master user to enable or disable security for the database to which Master is logged on.
Syntax
SET SECURITY [USING authentication_type] = < 'password' | NULL >
Remarks
You must be logged on as Master to set security. You can then assign a password by using the SET SECURITY statement. No password is required for Master to log on to an unsecured database, but to set security for the database, that Master user must have a password assigned.
SET SECURITY can be issued only when the session for the Master user is the only current database connection. You can also set security from the PSQL Control Center (PCC). See
To turn on security using PSQL Explorer in
PSQL User's Guide.
The authentication type string is either local_db or domain. If the USING clause is not included, the authentication type is set to local_db.
When the authentication type is domain, execution of SQL scripts related to users returns an error message that the statement is not supported under domain authentication. Examples of the unsupported statements include ALTER USER, CREATE USER, DROP USER, GRANT in relation to users, SET PASSWORD (for non-Master user), and REVOKE.
For password requirements, see
Password Characteristics.
User Permissions
Permissions on objects such as tables, views, and stored procedures are retained in the system tables after SET SECURITY is set to NULL. Consider the following scenario:
•Security for database mydbase is enabled and user Master is logged in.
•Master creates users user1 and user2, and table t1 for database mydbase.
•Master grants User2 SELECT permission on t1.
•Security for mydbase is disabled.
•Table t1 is dropped.
Even though table t1 no longer exists, permissions for t1 are still retained in the system tables (the ID for t1 is still in X$Rights). Now consider the following:
•Security for database mydbase is enabled again.
•User1 logs in to the database.
•User1 creates a new table tbl1 for mydbase. It is possible for tbl1 to be assigned the same object ID that had been assigned to t1. In this particular scenario, the object IDs assigned to t1 and tbl1 are the same.
•The previous permissions for t1 are reinstated for tbl1. That is, user1 has SELECT permissions on tbl1 even though no permissions to the new table have been explicitly granted.
Note If you want to delete permissions for an object, you must explicitly revoke them. This applies to tables, views, and stored procedures because permissions are associated with object IDs and the database reuses object IDs of deleted objects for new objects.
Examples
The following example enables security for the database and sets the Master password to “mypasswd”.
SET SECURITY = 'mypasswd'
The following example enables domain authentication for the database and sets the Master password to 123456.
SET SECURITY USING domain = '123456'
============
The following example disables security.
SET SECURITY = NULL
See Also