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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.