To revoke an object-level privilege, including any administrative privilege, execute:

REVOKE object_privilege_name

ON table_name

FROM userID [,...]

2. (Optional) To then re-grant the object-level privilege without administrative rights,

execute:

GRANT object_privilege_name

ON table_name TO userID [,...] Example:

This example assumes that Joe was granted the right to both perform deletes on the table Sales, and grant the DELETE privilege on the table to other users.

This statement revokes all DELETE privileges on the table Sales, which by definition

includes any administrative rights:

REVOKE DELETE ON Sales FROM Joe

This statement re-grants the privilege only, with no administrative rights:

GRANT DELETE ON Sales TO Joe

See also

• REVOKE Object-Level Privilege Statement on page 281 • REVOKE CREATE Statement on page 280

• REVOKE EXECUTE Statement on page 281

Privileges Required to Manage Table Objects in a Dbspace

There are specific system privileges required to create or move a table object in a dbspace. Requires the CREATE privilege on the dbspace. The CREATE privilege in a dbspace can be granted to or revoked from a user or a role. Any member in a role inherits CREATE privilege from the role. By default, CREATE privilege on IQ_SYSTEM_MAIN, IQ_SYSTEM_TEMP, and SYSTEM is granted to PUBLIC. For other IQ main dbspaces, the system administrator must explicitly grant CREATE privilege on the dbspace before a role or user can create or move objects into that dbspace. For example, if a table is to be placed on a new IQ main dbspace, the user must have CREATE privilege on that dbspace. Users must also have CREATE ANY OBJECT privilege to create objects.

Command Line Options That Control Privileges

The database server start-up command start_iq includes options that set the privilege level of some database and server functions.

Switches That Start and Stop Databases

The -gd option lets you limit the users who can start or stop a database on a running server to those with a certain level of privilege in the database to which he or she is already connected: • DBA – (default value) only users with SERVER OPERATOR system privilege can start an

extra database.

• ALL – (default in start_iq and default.cfg) any user can start and stop databases. This setting means that the DBA does not need to issue START DATABASE commands. Users still need the privileges to access a particular database once he or she has started it. • NONE – no one can start or stop a database from Interactive SQL on a running server.

Note: If -gd ALL is not set when you start the server, only a user with the SERVER OPERATOR system privilege can start additional databases on that server. This means that users cannot connect to databases that are not already started, either at the same time as the server, or since then by a user with the SERVER OPERATOR system privilege. However, it also lets a user without the SERVER OPERATOR system privilege stop a database. For this reason, you may want to change this setting to DBA on production databases.

Switches That Create and Delete Databases

The -gu option limits the users who can create and drop databases to those with a certain level of privilege in the database to which he or she is connected.

• DBA – only users with SERVER OPERATOR system privilege can create and drop databases.

• ALL(default) – any user can create and drop databases. • NONE – no user can create or drop a database.

• UTILITY_DB – only those users who can connect to the utility_db database can create

Stop Server Switch

The -gk option limits the users who can shut down a server with the dbstop utility or STOP ENGINE command:

• DBA (default) – only users with SERVER OPERATOR system privilege can stop the server.

• ALL – any user can stop the server.

• NONE – no user can shut down the server with the dbstop utility or STOP ENGINE

command.

Switches That Load and Unload Databases

The -gl option limits the users who can load data using LOAD TABLE to users with a certain level of privilege in the database.

• DBA – any user with the LOAD ANY TABLE, ALTER ANY TABLE or ALTER ANY OBJECT system privilege can load data.

• ALL (default for start_iq and default.cfg) – any user can load data. • NONE – data cannot be loaded.

See also

• -gl iqsrv16 Server Option on page 303

• -gu iqsrv16 database server option on page 304 • -gk iqsrv16 database server option on page 302

Revoking the Privilege to Run a Procedure

Remove the privilege to execute or call a specific procedure.

Prerequisites Revoker must either:

• Be the original grantor of the privilege that is being revoked • Have the MANAGE ANY OBJECT PRIVILEGE system privilege

Task

To revoke the EXECUTE privilege to run a specific procedure, execute: REVOKE EXECUTE ON procedure_name

FROM grantee [,...]

See also

How User Privilege Conflicts Are Resolved

Roles introduce complexities in the granting of privileges of individual users.

Suppose user Joe has been individually granted SELECT and UPDATE privileges on a specific table. Joe is also a member of two roles, one of which has no access to the table at all,

and one of which has only SELECT access. What are the privileges in effect for Joe?

This is how SAP Sybase IQ determines whether a user ID has privilege to carry out a specific action: