When you downgrade Adaptive Server to version 15.5, Adaptive Server truncates and locks role passwords. In addition, Adaptive Server does not support the use of allow password downgrade for role passwords.
After a downgrade, the administrator should reset the role passwords and unlock the role accounts before using them again.
During the downgrade process, Adaptive Server: • Truncates role passwords and locks roles
• Removes any attributes in sysattributes under class 35, as well as class 35 itself
• Removes locksuid, lockreason, and lockdate columns from syssrvroles The actions to downgrade a password occur when you execute sp_downgrade. in single-user mode. A dataserver started with a “-m” command line option starts the server in single-user mode and allows only the system administrator to log in.
In this example, executing sp_downgrade results in the password of the “doctor_role” role becoming locked and truncated. The administrator can redirect this output to a file so that the passwords for these roles can be reset:
1> sp_downgrade 'downgrade','15.5',1 2> go
Downgrade from 15.7.0.0 to 15.5.0.0 (command: 'downgrade') Checking databases for downgrade readiness.
There are no errors which involve encrypted columns. Executing downgrade step 2 [dbcc markprocs(@dbid)] for : - Database: master (dbid: 1)
sql comman is: dbcc markprocs(@dbid) ...
Executing downgrade step 26 [delete statistics syssrvroles(password) if exists (select 1 from syssrvroles where password is not
null) begin print "Truncating password and locking following role(s)" select name from syssrvroles where password is not null update
syssrvroles set password = null, status = (status | @lockrole) where password is not null end update syscolumns set length = 30
where id = object_id('syssrvroles') and name = 'password' update syssrvroles set locksuid = null, lockreason = null, lockdate = null
where locksuid is not null or lockreason is not null or lockdate is not null delete syscolumns where id = object_id('syssrvroles')
- Database: master (dbid: 1)
sql comman is: delete statistics syssrvroles(password) if exists (select 1 from syssrvroles where password is not null) begin print
"Truncating password and locking following role(s)" select name from syssrvroles where password is not null update syssrvroles set
password = null, status = (status | @lockrole) where password is not null end update syscolumns set length = 30 where id =
object_id('syssrvroles') and name = 'password' update syssrvroles set locksuid = null, lockreason = null, lockdate = null where
locksuid is not null or lockreason is not null or lockdate is not null delete syscolumns where id = object_id('syssrvroles') and
name in ('locksuid', 'lockreason', 'lockdate') Truncating password and locking following role(s)
name
--- doctor_role
Executing downgrade step 27 [delete sysattributes where class = 35 delete sysattributes where class = 39 update syslogins set lpid =
null, crsuid = null where lpid is not null or crsuid is not null delete syscolumns where id = object_id('syslogins') and name in
('lpid', 'crsuid') delete syslogins where (status & @lp_status) = @lp_status update syslogins set status = status & ~(@exempt_lock)
where (status & @exempt_lock) = @exempt_lock] for : - Database: master (dbid: 1)
sql comman is: delete sysattributes where class = 35 delete sysattributes where class = 39 update syslogins set lpid = null, crsuid
= null where lpid is not null or crsuid is not null delete syscolumns where id = object_id('syslogins') and name in ('lpid',
'crsuid') delete syslogins where (status & @lp_status) = @lp_status update syslogins set status = status & ~(@exempt_lock) where
(status & @exempt_lock) = @exempt_lock ...
(return status = 0)
Additional messages appear in the error log to identify steps that occurred during sp_downgrade and any system errors that may occur, such as in this example of error log output for the example downgrade procedure:
00:0006:00000:00006:2011/06/28 06:21:23.95 server Preparing ASE downgrade from 15.7.0.0 to 15.5.0.0.
00:0006:00000:00006:2011/06/28 06:21:24.12 server Starting downgrading ASE.
00:0006:00000:00006:2011/06/28 06:21:24.12 server Downgrade : Marking stored procedures to be recreated from text.
00:0006:00000:00006:2011/06/28 06:21:26.13 server Downgrade : Removing full logging modes from sysattributes.
00:0006:00000:00006:2011/06/28 06:21:26.13 server Downgrade : Downgrading data-only locked table rows.
00:0006:00000:00006:2011/06/28 06:21:26.13 server Downgrade : Removing full logging modes from sysattributes.
00:0006:00000:00006:2011/06/28 06:21:26.13 server Downgrade : Removing column sysoptions.number.
00:0006:00000:00006:2011/06/28 06:21:26.13 server Downgrade : Removing srvprincipal column from sysservers system table
00:0006:00000:00006:2011/06/28 06:21:26.14 server Downgrade : Removing 'automatic master key access' configuration parameter.
00:0006:00000:00006:2011/06/28 06:21:26.14 server Downgrade : Removing DualControl sysattribute rows
00:0006:00000:00006:2011/06/28 06:21:26.14 server Downgrade : Downgrading sysattributes system table.
00:0006:00000:00006:2011/06/28 06:21:26.16 server Downgrade : Downgrading syscomments system table.
00:0006:00000:00006:2011/06/28 06:21:26.19 server Downgrade : Truncated role password, locked role and removed columns locksuid, lockreason, lockdate from syssrvroles
00:0006:00000:00006:2011/06/28 06:21:26.21 server Downgrade : Removing catalog changes for RSA Keypair Regeneration Period and Login Profile
00:0006:00000:00006:2011/06/28 06:21:26.21 server Downgrade : Turning on database downgrade indicator.
00:0006:00000:00006:2011/06/28 06:21:26.21 server Downgrade : Resetting database version indicator.
00:0006:00000:00006:2011/06/28 06:21:26.21 server ASE downgrade completed.
After running sp_downgrade, shut down the server to avoid new logins or other actions that may modify data or system catalogs.
If you restart Adaptive Server at version 15.7:
• After successfully executing sp_downgrade and shutting down the server, Adaptive Server performs internal upgrade actions again, and any changes to system tables are upgraded to version 15.7.
• Before starting an earlier version of Adaptive Server to which you are reverting, you must execute sp_downgrade again.
You can enable locked roles and trucnated password. In this example, the output of sp_displayroles shows that the downgrade process has locked “doctor_role” and truncated its password:
select srid,status,name,password from syssrvroles go
suid status name password --- --- --- --- 33 2 doctor_role NULL
This unlocks the role:
This sets a new password for the role:
alter role doctor_role add passwd "dProle1"
Running sp_displayroles now displays that the role is unlocked and has a password:
select srid,status,name,
"vers"=substring(password,2,1) from syssrvroles go
suid status name vers --- --- --- --- 33 0 doctor_role 0x05
This chapter describes the use and implementation of user permissions.
Overview
Discretionary access controls (DACs) allow you to restrict access to
objects and commands based on a user’s identity, group membership and active roles. The controls are “discretionary” because a user with a certain access permission, such as an object owner, can choose whether to pass that access permission on to other users.
Adaptive Server’s discretionary access control system recognizes the following types of users:
• Users possessing one or more system defined roles: system administrator, system security officer, operator, and other roles • Database owners
• Database object owners • Other users
Topic Page
Overview 177
Permissions on system procedures 182
Database owner privileges 180
Other database user privileges 182
Database object owner privileges 181
Granting and revoking permissions 183
Acquiring the permissions of another user 193 Changing database object ownership 198
Reporting on permissions 202
Using views and stored procedures as security mechanisms 207
System administrators (those users with sa_role) operate outside the DAC system and have access permissions on all database objects at all times except encryption keys (see User Guide for Encrypted Columns). System security officers can always access the audit trail tables in the sybsecurity database to track accesses by system administrators.
If you have the sa_role, all grants permissions for create database, set tracing, and connect as well, if you issue the grant command in the master database. Database owners do not automatically receive permissions on objects owned by other users; however, they can:
• Temporarily acquire all permissions of a user in the database by using the setuser command to assume the identity of that user.
• Permanently acquire permission on a specific object by using the setuser command to assume the identity of the object owner, and then using grant commands to grant the permissions.
For details on assuming another user’s identity to acquire permissions on a database or object, see “Acquiring the permissions of another user” on page 193.
Object owners can grant access to those objects to other users and can also grant other users the ability to pass the access permission to other users. You can give various permissions to users, groups, and roles with the grant command, and rescind them with the revoke command. Use grant and revoke to give users permission to:
• Create databases
• Create objects within a database
• Execute certain commands such as dbcc and set proxy
• Access specified tables, views, stored procedures, encryption keys, and columns
grant and revoke can also be used to set permissions on system tables. For permissions that default to “public,” no grant or revoke statements are needed.
Some commands can be used at any time by any user, with no permission required. Others can be used only by users of a particular status and they are not transferable.
The ability to assign permissions for the commands that can be granted and revoked is determined by each user’s role or status (as system administrator, database owner, system security officer, or database object owner), and by whether the user was granted a role with permission that includes the option to grant that permission to other users.
You can also use views and stored procedures as security mechanisms. See “Using views and stored procedures as security mechanisms” on page 207.