Sybase User Management (Create, authorize, delete)

Source: Internet
Author: User
Tags sybase sybase database

First, login user management:
1. Create User:sp_addlogin loginame, passwd [, Defdb] [, Deflanguage] [, FullName] [, Passwdexp] [, Minpwdlen] [, Maxfail Edlogins] [, Auth_mech]
Such as: Create User Rusky, password: zxcvbnm, default database TestDB, default language: us_english
1> sp_addlogin Rusky,zxcvbnm,testdb,us_english
2> Go

2. Change the password
Sp_password old_passwd, new_passwd [, loginame, immediate]

3. Modify the default database
Sp_modifylogin loginame, option, value
Sp_modifylogin RUSKY,DEFDB,TESTDB2

4. Change the maximum number of failed logon attempts for the logged-on user:
Sp_modifylogin Rusky, "Max Failed_logins", "2"
(after two unsuccessful attempts to log in, the account will be locked.) Unable to log on after locking, prompt content with the wrong password, the client can not distinguish between the input is wrong, or account lockout)

5. Unlock Login user Account
Sp_locklogin "Rusky", "unlock"


6. Delete the logged-in user
1> sp_droplogin Rusky
2> Go
Account locked.
Login dropped.
(Return status = 0)
1>
If the login you want to delete is a user in any database on the server, sp_droplogin fails. You need to use Sp_dropuser to remove the user from the database first. If the user owns any objects in the database, the user cannot be removed from the database.
sp_dropuser Rusky should be used, and then sp_droplogin. If the database is offline, the login name is deleted.
---------------
second, the database user management:
1. About logged-in users and database users:
Because Sybase is a multi-database structure, it uses both logged-in and database users for unified management of users in multiple databases.
Logged-on User: Used to log in to the Sybase database, which is the user we use to connect to the database. If the logged-on user is not added to a database, it will not be able to manipulate a database
Database User: Used to manage the use of the database, which is unique in the owning database.
Therefore, the same logged-on user can correspond to multiple database users of different databases.
In order to use the database on the Sybase ASE, you must have a user in the database that is added by the database owner through sp_adduser.
sp_adduser Loginname,dbusername--Create DATABASE user LoginName and dbusername can be the same

Sp_helpuser username--Viewing user information

Sp_dropuser username--Deleting a database user
----------------------------------------------------Test Cases

1>sp_addlogin abc,zxcvbnm,testdb,us_english2>Gopassword correctlySet. Account unlocked. New Login created. (returnStatus =0)1>sp_adduser abc,abc2>gonew user added. (returnStatus =0)1>sp_adduser abc,abc2>gonew user added. (returnStatus =0)1>sp_helpuser ABC2>gousers_name Id_in_dbgroup_namelogin_name---------------------------------------- ----------------------------------------------------------------------- -----------------------------------------ABC4 PublicABC (1row affected) (returnStatus =0)1>

2. User authorization and revocation authorization

Granting users permission to create objects
Grant user ULTRANMS permissions to create tables, default values, rules, stored procedures, and views
Grant Create table,create default,create rule,create procedure,create view to ABC

Grant user ABC permission to create a function
Grant CREATE function to ABC


View permissions for user ABC
1> sp_helprotect ABC
2> Go

Revoke the user ABC create FUNCTION permission
Revoke CREATE function from ABC

Sybase User Management (Create, authorize, delete)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.