Chapter 12: Changes to Global Variables, Commands, and Stored Procedures

Changes to grant and revoke

This sections describes changes to the grant and revoke commands for Adaptive Server version 12.5.2.

grant and set proxy issue warning for fipsflagger

grant dbcc and set proxy issue the following warning when they are issued while set fipsflagger option is enabled:

SQL statement on line number 1 contains Non-ANSI text. The error is caused due to the use of DBCC.

Granting default permissions to system tables and stored procedures

Neither installmaster or installmodel for Adaptive Server version 12.5.2 grant default permissions for some system tables (listed below). Instead, the default permissions on the system tables are assigned when Adaptive Server builds a new database.

Adaptive Server version 12.5.2 adds the default permissions parameter to the grant and revoke commands, which allows you to grant and revoke the default permissions for the system tables listed below. The partial syntax is:

grant default permissions on system tables
revoke default permissions on system tables

where default permissions on system tables specifies that you grant or revoke the default permissions for the following system tables when you issue it from any database:

sysalternates
syscolumns
syscomments
sysdepends
sysindexes
syskeys
sysobjects
sysprocedures
sysprotects
syssegments
systypes
sysusers
syslogs
sysconstraints
sysreferences
sysusermessages
sysattributes
systabstats
sysxtypes
sysjars
systhresholds
syspartitions
sysstatistics
sysqueryplans

Default permissions on system tables also makes the following changes:

Revokes sysobjects(audflags) permissions from public
Grants permissions for sysobjects to sso_role

If you run this command from the master database, default permissions for the following system tables are granted or revoked:

sysdatabases
sysdevices
syslocks
sysmessages
sysprocesses
systransactions
sysusages
sysconfigures
syscurconfigs
syslanguages
syscharsets
sysservers
systimeranges
sysresourcelimits
syslogins
sysremotelogins

The command also makes the following changes:

Revokes select on sysdatabases(audflags) from public
Revokes select on sysdatabases(deftabaud) from public
Revokes select on sysdatabases(defvwaud) from public
Revokes select on sysdatabases(defpraud) from public
Grants select on sysdatabases to sso_role
Revokes select on syslogins(password) to public
Revokes select on syslogins(audflags) from public
Grants select on syslogins to sso_role

Granting and revoking permissions for update statistics, delete statistics, and truncate table

Adaptive Server version 12.5.2 allows you to grant and revoke permissions for users, roles, and groups for the update statistics, delete statistics, and truncate table commands. Table owners can also provide permissions through an implicit grant by adding update statistics, delete statistics, and truncate table to a stored procedure and then granting execute permissions on that procedure to a user or role.

You cannot grant or revoke permissions for update statistics at the column level. You must have the sso_role to run update statistics or delete statistics on sysroles, syssrvroles, and sysloginroles security tables.

By default, users with the sa_role have permission to run update statistics and delete statistics on system tables other then sysroles, syssrvroles and sysloginroles, and can transfer this privilege to other users.

The partial syntax for grant and revoke is:

grant [truncate table | update statistics | delete statistics] on table_name to {user_name | role_name}

revoke [truncate table | update statistics | delete statistics] on table_name from {user_name | role_name}

You can also issue grant all to grant permissions on update statistics, delete statistics, and truncate table.

For example, the following allows user “harry” to use truncate table and updates statistics on the authors table:

grant truncate table on authors to harry
grant update statistics on authors to harry

The following revokes truncate table and update statistics privileges from “harry” on the authors table:

revoke truncate table on authors from harry
revoke update statistics on authors from harry

The following allows user “billy” to use the delete statistics command on the authors table:

grant delete statistics on authors to billy

The following revokes the delete statistics privileges from user “billy” on the authors table:

revoke delete statistics on authors from billy

The following grants truncate table and update and delete statistics privileges to all users with the oper_role (if users “billy” and “harry” possess the oper_role, they can now run these commands on authors):

grant truncate table on authors to oper_role
grant update statistics on authors to oper_role
grant delete statistics on authors to oper_role

The following revokes truncate table and update and delete statistics privileges from all users with the oper_role:

revoke truncate table on authors from oper_role
revoke update statistics on authors from oper_role
revoke delete statistics on authors from oper_role

Users “billy” and “harry” can no longer run these commands on authors.

You can also implicitly grant permissions for truncate table, delete statistics, and update statistics through a stored procedure. For example, assuming “billy” owns the authors table, he can run the following to grant “harry” privileges to run truncate table and update statistics on authors:

create procedure sproc1
as
truncate table authors
update statistics authors
go
grant execute on sproc1 to harry
go

You can also implicitly grant permissions at the column level for update statistics and delete statistics through stored procedures.

Once you grant permission to execute update statistics to a user, they also have permission to execute variations of this command, such as update all statistics, update partition statistics, update index statistics, update statistics table, and so on. For example, the following grants “billy” permission to run all variations of update statistics on the authors table:

grant update statistics on authors to billy

If you revoke a user’s permission to execute update statistics, you also revoke their ability to execute the variations of this command.

You cannot grant variants of update statistics (for example, update index statistics) separately. That is, you cannot issue:

grant update all statistics to harry

However, you can write stored procedures that control who executes these commands. For example, the following grants “billy” execute permission for update index statistics on the authors table:

create proc sp_ups as
update index statistics on authors
go
revoke update statistics on authors from billy
go
grant execute on sp_ups to billy

You cannot grant and revoke delete statistics permissions at the column level.

Although Adaptive Server audits truncate table as a global, miscellaneous audit, it does not audit update statistics. To retain clear audit trails for both truncate table and update statistics, Sybase recommends that you include both commands in a stored procedure to which you grant users execute permission, as described above.

The command fails and generates error number 10330 if a user issues update statistics, delete statistics or truncate table and they:

Do not own the table.
Do not have the sa_role.
Are not a database owner who has successfully used setuser to become the user who is the owner of the table.
Have not been granted update statistics, delete statistics, or truncate table privileges.

grant set proxy command

In earlier versions of Adaptive Server, set proxy allowed you to switch your server user identity to any other server login, but it did not allow you to limit use of set proxy based on the target login roles. When you granted set proxy to a user, this user could become any other server user.

As of Adaptive Server version 12.5.2, you can grant set proxy...restricted role, which allows you to restrict which roles cannot be acquired when switching identities.

The syntax for set proxy is:

grant set proxy to user_or_role_list
[restricted role role_list | all | system]

Parameters

user_or_role_list – list of roles you are restricting for the target login. Both the grantee and target login must have all roles on this list or the command fails.
all – ensures that all roles belonging to the grantee are granted to the target login.
system – ensures the grantee has the same set of system roles as the target login.

Examples

Example 1: This example grants set proxy to user “joe” but restricts him from switching identities to any user with the sa, sso, or admin roles (however, if he already has these roles, he can set proxy for any user with these roles):
```
grant set proxy to joe
restricted role sa_role, sso_role, admin_role
```
When “joe” tries to switch his identity to a user with admin_role (in this example, Our_admin_role), the command fails unless he already has admin_role:
```
set proxy Our_admin_role
```
```
Msg 10368, Level 14, State 1:
Server 's', Line 2:Set session authorization permission denied because the target login has a role that you do not have and you have been restricted from using.
```
After “joe” is granted the admin_role and retries the command, it succeeds:
```
grant role admin_role to joe
set proxy Our_admin_role
```
Example 2: Restricts “joe” from being granted any new roles when switching identities:
```
grant set proxy to joe
restricted role all
```
“joe” can grant set proxy only to users who have the same (or lessor) roles than he has.
Example 3: Restricts Joe from acquiring any new system roles when using set proxy:
```
grant set proxy to joe
restricted role system
```
set proxy fails if the target login has system roles that joe lacks.

Usage

You can restrict roles incrementally with grant set proxy. For example, you can first restrict the sa_role, then the sso_role:
```
grant set proxy to joe
restrict role sa_role
grant set proxy to joe
restrict role sso role
```

You cannot unrestrict individual roles. You must revoke set proxy, to revoke permissions from all roles, as illustrated in this query:

select distinct user_name(p.uid), b.name, p.grantor,
                 Restricted_role=case 
convert(tinyint,substring(isnull(p.columns,0x1),1,1)) & 1
                       when 1 then
                            "None"
                       else
                            isnull(role_name(c.number - 1), "System 
"+convert(char,c.number))
                                    end
    from sysprotects p, master.dbo.spt_values b, master.dbo.spt_values c
    where
          convert(tinyint,substring(isnull(p.columns,0x1), c.low,1)) & 
c.high = 0
         and c.type = "P" and c.number <= 1024 and c.number >0 and 
p.action = 167
         and b.type = "T"
         and b.number = (p.protecttype + 204)
          and role_name(c.number - 1) is not null

View this book as PDF