How to only allow admins to remove/update tuples in tables

Hi everyone, i'm new here.

How do I allow only a certain group of people of type 'Admin' to alter the table using query ? Other users such as Customers and Public can only view the data.
sleepymaoAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I repeat: you store the user levels in your own tables, so SQL Server is, in that regards, just a database.
it CANNOT solve this for you, you HAVE to solve this in the application code.

a constraint will just ensure data as such is logically correct, it will not prevent any users to actually modify data as long as it respects the constraints.

sql permissions cannot help, unless you don't create your application users, but rely on sql logins.
0
 
Lee SavidgeCommented:
This is a broad question and you will need to provide a lot more information (and possibly points) to get an answer. What sort of database? Bespoke system or off the shelf? How do you differentiate between admin and other types of users?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sleepymaoAuthor Commented:
Hi sorry for the vagueness.

I'm using MS SQL 2005.

I have a table called Users:

CREATE TABLE Users
(
NIC VARCHAR(20) NOT NULL,
userName VARCHAR(30),
birthday DATE,
password VARCHAR(16),
address VARCHAR(100),
contact VARCHAR(20),
userType VARCHAR(1) CHECK (userType IN ('A','B','C','G')),
PRIMARY KEY (NIC)
)

therefore, each of my user has a type, be in A=Admin, B=Broker, C=Customer,G=guest.

for my other tables, deletion/update can only be done by Admin and not by the other types of users. such as for the table below:

CREATE TABLE Stock
(
symbol VARCHAR(5) PRIMARY KEY NOT NULL,
corpRegNo VARCHAR(15) UNIQUE NOT NULL,
stockTimestamp DATETIME NOT NULL,
amountOfTrade REAL NOT NULL,
valueOfStock REAL NOT NULL,
indexNo REAL NOT NULL,
noOfLot int NOT NULL,
FOREIGN KEY (corpRegNo) REFERENCES Corporations (regNo)
ON DELETE CASCADE
ON UPDATE CASCADE
)

Hope it is clearer now :D
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, this needs to be handled in the application level ...
so, the application code will need to "check" the user's permission level, and allow or block the relevant actions.

that is nothing where sql server as such can help, actually
0
 
sleepymaoAuthor Commented:
is there a constraint or trigger that is possible to solve this prob ?
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
Well, this is not that hard because it is an Authorization thing.

If you don't grant update/delete to the table except to Admins then everyone else will not be able to update/delete out of the table.

If there are stored procedures that manipulate the data, you will have to be careful on the permissions of these as well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.