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
= <
password
|
NULL
>
Examples
The following example enables security for the database and sets the Master password to “mypasswd”.
SET SECURITY = mypasswd
The following example sets the password to 123456.
SET SECURITY = '123456'
============
The following example disables security.
SET SECURITY = 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 an unsecured database. A password must be assigned to Master to enable security for the database.
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
.
Note:
The SET SECURITY statement cannot be executed within the SQL Editor. An error results if you try. For a database with no security, the SQL Editor locks the dictionary files, which prevents your setting the password. For a secure database, the SQL Editor opens a second connection to the database files, which prevents your disabling security.
For password requirements, see
Password Characteristics
.
User Permissions
User 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 user creates users user1 and user2, and table t1 for database mydbase.
•
User2 is granted 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 on an object, you must explicitly revoke the permissions. 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.
See Also
ALTER USER
CREATE USER
GRANT
REVOKE
SET PASSWORD