Users and Owners in Sybase SQL Anywhere
Implementing Database in Sybase – Part 13
Division 4
Forward: In this part of the series, we look at users and owners in Sybase SQL Anywhere 12.
By: Chrysanthus Date Published: 27 Aug 2012
Introduction
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
User
After a database design is completed and implemented (and is being used in an office), anybody who works with the database is a user. A database does not have a fixed size. After one year of the use of a database, more tables can be added to the database. A user has a user ID and a password.
Owner
The owner of a table in the database is the person who created the table. The ordinary user cannot create a table. In SQL Anywhere, the owner of the entire database is the Database Administrator (DBA). So the DBA owns all the tables. If you own a table, then you and the DBA share the ownership of that table. All owners are users, but not vice-versa.
Differences Between Users and Owners
The main difference between a user and an owner is the permission to create a table. An owner can create a table, but a user cannot create a table. An owner has all permissions in a table. The owner can see all the columns of his table; he can update any row in his table, he can insert (add) rows into his table; he can delete rows from his table; above all, he can drop (delete) his table. The owner can give the user permission to see certain columns in his table, and/or give him the permission to update rows in his table, and/or give him the permission to delete rows in his table and/or give him the permission to insert rows in his table. The owner cannot give the user the permission to drop his table. In SQL Anywhere, the DBA has all the permissions of the owner.
Creating Users
The DBA creates users and gives each user a user ID and a password. To create a user with user ID, newguy and password, new44, the DBA would type the following SQL statement (in the Interactive SQL window):
CREATE USER newguy IDENTIFIED BY new44;
We saw this in one of the previous parts of the series.
The DBA has the authority to create owners. In other words, the DBA has the authority to turn a user into an owner; but remember: an owner is also a user. Once you are an owner, you can log into (connect to) the database and create your tables. When you create your tables, you own the tables, but you do not own the other tables created by the DBA. On the other hand, he owns your tables as well.
The DBA creates an owner by giving a user, what is known as the Resource authority. If the DBA wants to turn the user, newguy into an owner, he would type the following SQL statement (in the Interactive SQL window):
GRANT RESOURCE TO newguy;
Here, GRANT, RESOURCE and TO are reserved words. newguy is the userID we saw above. The GRANT Statement actually has 9 different syntaxes. We saw one in one of the previous parts of the series. The Grant Resource statement here, belongs to another GRANT Syntax (I will not go into the details here). After the execution of this statement, the user, newguy, who is now an owner can connect to the database and create his own tables.
Changing Passwords
The DBA can access (read and change) all the data in the database, no matter the passwords that the users have. The users can access only what they have been given the permission to do. If a user thinks that another user has discovered his password, he can change it, however, the DBA will still have access to user’s information even though he has changed his password; but the other user cannot have access to this user’s information gain.
If the DBA thinks that a user has known his password, he can change it. The user who discovered the password of the DBA would no longer be able to access the database as the DBA.
Note: the DBA is also a user. The ALTER USER SQL statement is used to change the password of a user. If you as a user want to change your password, you can use the simplified syntax:
ALTER USER user-name [ IDENTIFIED BY password ];
e.g.
ALTER USER newguy IDENTIFIED BY newpswd;
In the ALTER USER SQL statement, you have the new password.
That is it for this part of the series. We stop here and continue in the next part.
Chrys
Related Courses
C++ CourseRelational Database and Sybase
Windows User Interface
Computer Programmer – A Jack of all Trade – Poem